http://imgur.com/a/qyfw2 (this is a link because I could not get the upload to w
ID: 3686785 • Letter: H
Question
http://imgur.com/a/qyfw2 (this is a link because I could not get the upload to work in this site). Please help me with this.
1. Indicate the changes (using the shorthand representation) that you would need to make to the original TAL Distributors database design in the event that the original Requirement 3 is changed as follows. For an item, store the items number, description, category, and price. In addition, for each storehouse in which the item is located, store the number of the store- house, the description of the storehouse, and the number of units of the item stored in the storehouse. Draw an E-R diagram for the new design.
2. Using your knowledge of TAL Distributors, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form. ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE, (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME, NUM_ORDERED, QUOTED_PRICE))
3. Indicate the changes you need to make to the TAL Distributors database to support the fol- lowing additional requirement. Each storehouse has a manager who is identified by a man- ager number, a manager last name, and a manager first name.
Explanation / Answer
An Entity Relationship Diagram (ERD) is a visual representation of different data using conventions that describe how these data are related to each other
ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE, (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME, NUM_ORDERED, QUOTED_PRICE))
CREATE TABLE Items(
iid int PRIMARY KEY,
description text NOT NULL,
price DECIMAL(8, 2) NOT NULL
);
CREATE TABLE Customers(
cid int PRIMARY KEY,
name text NOT NULL,
address text NOT NULL
);
CREATE TABLE Orders(
oid int PRIMARY KEY,
cid INT NOT NULL REFERENCES Customers,
date date NOT NULL
);
CREATE TABLE OrderLines(
oid int REFERENCES Orders,
iid int REFERENCES Items,
quantity int NOT NULL,
PRIMARY KEY(oid, iid))
Rep (Rep_Num, Last_Name, First_Name, Street, City, State, Zip, Commission, Rate)
Customer (Customer_Num, Customer_Name, Street, City, State, Zip, Balance, Credit_Limit, Rep_Num)
Orders (Order_Num, Order_Date, Customer_Num)
Part(Part_Num, Description, On_Hand, Class, Warehouse, Price)
Order_Line (Order_Num, Part_Num, Num_Ordered, Quoted_Price)
Rep (Rep_Num, Last_Name, First_Name, Street, City, State, Zip, Commission, Rate)
Customer (Customer_Num, Customer_Name, Street, City, State, Zip, Balance, Credit_Limit)
Represents(Represents_Id, Customer_Num, Rep_Num)
Orders (Order_Num, Order_Date, Represents_Id)
Part (Part_Num, Description, On_Hand, Class, Warehouse, Price)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.