Guys i really need help University student doing SQL

[pithhelmet]

Here is the script i would use for the primary customer table -

CREATE TABLE [dbo].[CUSTOMER_0110020] (
[Customer_number] [int] IDENTITY (1, 1) NOT NULL ,
[Customer_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer_address_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer_address_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_address_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Customer_postcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Customer_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


Notice the customer number is an autoincrement - set as the primary key



Now the purchase history table should be a detail table of the customer table -

Here is the script for this -

CREATE TABLE [dbo].[purchase_history] (
[purchase_id] [int] IDENTITY (1, 1) NOT NULL ,
[customer_number] [int] NOT NULL ,
[sale_date_time] [datetime] NOT NULL ,
[sale_amount] [money] NOT NULL ,
[cash_tendered] [money] NOT NULL
) ON [PRIMARY]





and for the final setup - the item detail -
CREATE TABLE [dbo].[purchase_items] (
[sale_id] [int] IDENTITY (1, 1) NOT NULL ,
[purchase_id] [int] NOT NULL ,
[item_upc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[price] [money] NULL
) ON [PRIMARY]



Now i didn't add in any foreign constraints and all that crap - because i feel the programmer should be able to handle that -

This is basically a master - detail - detail table setup -

The first thing you would do is to add in a customer -

insert into customer_0110020
(customer_name, customer_address_1, customer_address_2, customer_address_3, customer_postcode, customer_phone) values
('tony', '123 west main', 'lake mary', 'florida', '32746', '123-123-1234'); select @@identity;

NOTE - the @@identity at the end - that will return the customer number that is created - you will use this when you add in a sale.....


I'm sure there are folks out there that will say that you should be combine the purchase history and the items detail into the same database - but i like to bust them out myself -



its a start -


good luck!!