In: Computer Science
Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams for it.
Business Statement:
The 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.
When buyers login to Web site, they can go to the list of all available items and then go to the item detail page. Buyers can also search for an item. The application will search through item names for the search phrase.
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.
Part 2: Please write SQL Query for each statement below.
• 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 expensive item sold ever
The most expensive item available
The cheapest item sold ever
The cheapest item available
1.
An Entity relationship model of the BA is an online auction Web site database is as follows:
Entity
Entities are represented by rectangles. The BA is an online auction Web site database contains the following entities.
Attribute
An attribute describes the characteristics of an entity. Attributes are represented by ellipses. The BA is an online auction Web site database contains the following attributes of each entity.
Relationships:
ER Diagram:
NER diagram:
Table Schema Diagram:
Table Schema:
BA is an online auction Web site database:
create table creditCardInfo
(
credit_CardID int
IDENTITY(1000,1) NOT NULL,
credit_CardNo
varchar(16) UNIQUE NOT
NULL,
credit_ExptDate
date
NOT NULL,
credit_OwnerName
varchar(20)
NOT NULL,
PRIMARY KEY(credit_CardID));
create table Address
(
addressID int IDENTITY(1000,1) NOT NULL,
Street
varchar(50)
NOT NULL,
City
varchar(20)
NOT NULL,
State
char(2)
NOT NULL,
ZIP
char(10)
NOT NULL,
primary key (addressID));
create table contactInfo
(
contactID int IDENTITY (100,1) NOT
NULL,
telephone char(10) NOT NULL UNIQUE,
Email varchar(50) NOT NULL UNIQUE,
address_ID int ,
PRIMARY KEY(contactID),
FOREIGN KEY (address_ID) references Address(addressID));
create table seller
(
sellerID INT
IDENTITY(1000,1) NOT NULL,
sellerName
varchar(20) NOT NULL,
sellerUserName varchar(20) UNIQUE,
sellerPassword varchar(32) NOT NULL,
item_ID int NOT
NULL,
contact_ID int NOT NULL,
creditCardID int NOT NULL,
primary key(sellerID),
Foreign Key(contact_ID) references
contactInfo(contactID),
Foreign Key(creditCardID) references
creditCardInfo(credit_CardID));
create table item
(
itemID
int IDENTITY(1000,1) NOT NULL,
itemName
varchar(15)
NOT NULL,
Item_desc
varchar(255),
Item_initialPrice MONEY,
ItemQty
int,
ownerID
int
NOT NULL,
condition
varchar(20)
NOT NULL,
PRIMARY KEY (itemID),
FOREIGN KEY (ownerID) references seller
(sellerID));
create table buyer
(
buyerID
INT IDENTITY(1000,1) NOT NULL,
buyerName
varchar(20)
NOT NULL,
buyerUsername varchar(20)
UNIQUE NOT NULL,
buyerPassowrd
varchar(32)
NOT NULL,
contact_ID
int,
creditCardID int,
primary key(buyerID),
Foreign Key(contact_ID) references
contactInfo(contactID),
Foreign Key(creditCardID) references
creditCardInfo(credit_CardID));
create table buyerItem
(
buyer_ID int NOT NULL,
item_ID int NOT
NULL,
PRIMARY KEY(buyer_ID,item_ID),
FOREIGN KEY (buyer_ID) REFERENCES
buyer(buyerID),
FOREIGN KEY (item_ID) REFERENCES item(itemID));
create table picture
(
pictureID int IDENTITY (1,1) NOT
NULL,
pictureIMG VarBinary(max),
item_ID int,
PRIMARY KEY(pictureID),
FOREIGN KEY(item_ID) references item(itemID));
create table bid
(
bidID
int IDENTITY (1000,1) NOT NULL,
bid_startTime date DEFAULT GETDATE()NOT
NULL,
bid_endTime date
DEFAULT GETDATE()NOT NULL,
item_ID
int
NOT NULL,
PRIMARY KEY(bidID),
Foreign Key(item_ID) references
item(itemID));
create table buyerBid
(
buyer_ID int NOT NULL,
bid_ID int NOT
NULL,
PRIMARY KEY(buyer_ID,bid_ID),
FOREIGN KEY (buyer_ID) REFERENCES
buyer(buyerID),
FOREIGN KEY (bid_ID) REFERENCES bid(bidID));
---------------------------
Part 2: SQL Query for each statement below.
• 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)
select sellerName from seller where sellerName in
(Select owner from item having max(count(itemID));
(or)
Product(product_id, product name,seller_id, list_out_date,cost)
SELECT Seller_id,COUNT(Seller_id) AS 'Count1' FROM Product GROUP BY Seller_id ORDER BY count1 DESC LIMIT 1;
The most active buyer (the one who has bought the most number of items)
Select buyerName from buyer where buyerName in
(select buyerID from auction having max(count(itemID));
(or)
SELECT Buyer_id, COUNT(Buyer_id) AS 'Count1' FROM Bidding_Info GROUP BY Buyer_id ORDER BY count1 DESC LIMIT 1;
The most expensive item sold ever
Select OwnerID from Auctions having max(count(itemID))
order by itemID;
(or)
SELECT Seller_id, COUNT(Seller_id) AS 'Count1'
FROM Items_Sold_Info GROUP BY Seller_id ORDER BY count1 DESC LIMIT 1;
select product_id,product_name from Items_Sold_Info a join Product b
on a.product_id=b.product_id where a.cost_sold = (select max(cost_sold) from Items_Sold_Info);
The most expensive item available
Select itemID from Auction having price in
(select max(price) from Auctions);
(or)
select product_id, product_name from Product where cost=(select max(cost) from product);
The cheapest item sold ever
Select itemID from Auction having price in
(select min(price) from Auctions);
(or)
select product_id,product_name from Items_Sold_Info a join Product b
on a.product_id=b.product_id where a.cost_sold = (select min(cost_sold) from Items_Sold_Info);
The cheapest item available
Select itemName from item having Item_initialPrice in
(select min(Item_initialPrice from item);
(or)
select product_id, product_name from Product where cost=(select min(cost) from product);