Question

Need SQL Tables

Final Project should be included ER, NER, Table diagrams and SQL statements.

The final project is about developing an auction Web site. The details are as follows:

BA is an online auction Web site. People can buy and sell items in this Web site. Buyers are people who like to buy items, and sellers are people who like to sell items.

•Each seller can sell items.

•Each item has a bidding start time, an end time, and an owner. Sellers are owners of their item. The start time and end time include the date as well.

•Each seller has a name, contact information, and credit card information. They also have a user name and a password.

•Contact information consists of an address, an email, and a telephone.

•An address consists of a street number and name, city, state, and zip code.

•Credit card information consists of owner name, card number, and expiration date.

•Each item has a name, condition, an initial price, a description, quantity, one or more pictures, and an owner.

•The condition could be New, Refurbished, or Explained. If the condition of an item is set to Explained, the seller should explain about the item condition in the item description.

•Each buyer has a name, contact information, and credit card information. They also have a user name and a password.

•Buyers can bid on items. Once a bid is made, buyers are accountable for their bid. In other words, buyers cannot simply remove their bid. If they change their mind, all they can do is to update their bid with the price of zero. Of course, they can do that before the auction expires.

•After an auction expires, the buyer with the highest bid is the winner.

•BA likes to have a set of statistics about the system as follows:

•The most active seller (the one who has offered the most number of items)

•The most active buyer (the one who has bought the most number of items)

•The most popular seller (the one who sold the most number of items)

•The most expensive item sold ever

•The most expensive item available

•The cheapest item sold ever

The cheapest item available

Answer 1

CREATE TABLE [Item] (

[I_Code] numeric(50) NOT NULL IDENTITY(1,1),

[Bid_Stime ] datetime2(0) NULL,

[Bid_Etime] datetime2(0) NULL,

[o_code] int NULL,

PRIMARY KEY ([I_Code])

)

GO

CREATE TABLE [Credit_Card] (

[o_code] int NOT NULL,

[card_no] int NOT NULL,

[expiry_date] datetime2 NOT NULL,

PRIMARY KEY ([o_code], [card_no])

)

GO

CREATE TABLE [Condition] (

[Con_code] int NOT NULL,

[Condition] varchar(10) NOT NULL,

PRIMARY KEY ([Con_code])

)

GO

CREATE TABLE [Buyer_seller] (

[Name] varchar(100) NOT NULL,

[Contact_code] int NOT NULL,

[o_code] int NOT NULL,

[Username] varchar(50) NOT NULL,

[Password] varchar(50) NOT NULL,

[Buy_sell] char(1) NOT NULL,

PRIMARY KEY ([o_code])

)

GO

CREATE TABLE [Item_desc] (

[I_Code] numeric(50) NOT NULL,

[I_Name] varchar(100) NOT NULL,

[Price] money NOT NULL,

[Desc] text NOT NULL,

[Quantity] numeric(10) NOT NULL,

[Pic] image NOT NULL,

[o_code] numeric(50) NOT NULL,

[Condition] int NOT NULL,

PRIMARY KEY ([I_Code])

)

GO

CREATE TABLE [Bid] (

[I_code] numeric(50) NOT NULL,

[o_code] numeric(50) NOT NULL,

[Price] money NOT NULL,

PRIMARY KEY ([I_code], [o_code])

)

GO

CREATE TABLE [bs_desc] (

[o_code] int NOT NULL,

[street_name] varchar(100) NOT NULL,

[street_no] int NOT NULL,

[state] varchar(50) NOT NULL,

[zip_code] numeric(10) NOT NULL,

[email] varchar(50) NOT NULL,

[telephone] numeric(20) NOT NULL,

PRIMARY KEY ([o_code])

)

GO

ALTER TABLE [Buyer_seller] ADD CONSTRAINT [fk_Buyer_seller_Credit_Card_1] FOREIGN KEY ([o_code]) REFERENCES [Credit_Card] ([o_code])

GO

ALTER TABLE [Item_desc] ADD CONSTRAINT [fk_Item_desc_Condition_1] FOREIGN KEY ([Condition]) REFERENCES [Condition] ([Con_code])

GO

ALTER TABLE [Item_desc] ADD CONSTRAINT [fk_Item_desc_Item_1] FOREIGN KEY ([I_Code]) REFERENCES [Item] ([I_Code])

GO

ALTER TABLE [Buyer_seller] ADD CONSTRAINT [fk_Buyer_seller_Item_1] FOREIGN KEY ([o_code]) REFERENCES [Item] ([o_code])

GO

ALTER TABLE [Buyer_seller] ADD CONSTRAINT [fk_Buyer_seller_Item_desc_1] FOREIGN KEY ([o_code]) REFERENCES [Item_desc] ([o_code])

GO

ALTER TABLE [Bid] ADD CONSTRAINT [fk_Bid_Buyer_seller_1] FOREIGN KEY ([o_code]) REFERENCES [Buyer_seller] ([o_code])

GO

ALTER TABLE [Bid] ADD CONSTRAINT [fk_Bid_Item_desc_1] FOREIGN KEY ([I_code]) REFERENCES [Item_desc] ([I_Code])

GO

ALTER TABLE [Buyer_seller] ADD CONSTRAINT [fk_Buyer_seller_bs_desc_1] FOREIGN KEY ([o_code]) REFERENCES [bs_desc] ([o_code])

GO