Question

In: Computer Science

Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams...

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

Solutions

Expert Solution

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.

  • Seller
  • Buyer
  • Item
  • Bid
  • Website
  • Pictures
  • Auction

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:

  • Each seller can sell items.
  • When buyer’s login to Web site
  • Buyers can bid on items.
  • Buyers can also search for an item.

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);


Related Solutions

Part 1: Please read the business statement below and draw ER, NER, and Table Schema diagrams...
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,...
1. Please read the business statement below and draw ER, NER, and Table Schema diagrams for...
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...
Please read the business statement below and draw ER, NER. Business Statement: The project is about...
Please read the business statement below and draw ER, NER. 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...
Need SQL Tables Final Project should be included ER, NER, Table diagrams and SQL statements. The...
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...
Final Project must be included HER, NER, Table diagrams and SQL statements. The final project is...
Final Project must be included HER, 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...
Given the business rule below. Draw the Data directories for each entities & ER Diagram. A...
Given the business rule below. Draw the Data directories for each entities & ER Diagram. A DOCTOR can be scheduled for many APPOINTMENTS, but may not have any scheduled at all. Doctor has the attributes such as DoctorID, Firstname, & Lastname. Appointment has attributes such as AppointmentID, Date & Reason. A PATIENT can schedule one or more appointments. Patient entity has attributes such as PatientID, PatientName & Address. One appointment is scheduled with exactly one patient. An appointment must generate...
Only needs DB SQL statements. Final Project must be included HER, NER, Table diagrams and SQL...
Only needs DB SQL statements. Final Project must be included HER, 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,...
Assignment #1 Develop a set of Data Flow Diagrams (DFD) and ER-D depicting the business processes...
Assignment #1 Develop a set of Data Flow Diagrams (DFD) and ER-D depicting the business processes or activities of an online banking system. The business processes/activities of your online banking system shall be identical to those of an ATM machine. Bank of America Your DFDs MUST include Context DFD, Level-0 DFD and at least one Level-1 DFD. Your Data Stores must exist to support the business processes.
For this question, normalize the table to 3NF and draw the relational schema showing cardinalities. UCLA...
For this question, normalize the table to 3NF and draw the relational schema showing cardinalities. UCLA just purchased several servers that are to be used for enterprise resource planning (ERP). The individual servers were purchased from different suppliers and these servers are running different software applications. UCLA also trained several of its staff to use these servers and each trained staff has an authentication code that allows the staff to use a specific software application on a specific server for...
Please use Excel to analyze the statement of cash flows for the table below. 1) What...
Please use Excel to analyze the statement of cash flows for the table below. 1) What is the correlation between net income and operating cash flow? 2) Explain the trends of cash flow from the table below. 3) What is the free cash flow from the table below? Cash Flow All numbers in thousands Period Ending 1/31/2018 1/31/2017 1/31/2016 1/31/2015 Net Income 9,862,000 13,643,000 14,694,000 16,363,000 Operating Activities, Cash Flows Provided By or Used In Depreciation 10,529,000 10,080,000 9,454,000 9,173,000...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT