1. Use 'SQL Developer Data Modeler' or any other software to draw Logical Data Model. 2....

  

Transcribed Image Text:

1. Use 'SQL Developer Data Modeler' or any other software to draw Logical Data Model. 2. Take screen capture of the data model and embed it in the word document. 3. Resave the document with your name_lab#_db. (Eyourname_lab#_db) 4. SUBMIT ONLY WORD DOCUMENT OR PDF. 5. Submit it in the link provided in the assignment section. a) Complete the Database design that satisfies the following constraints and USER VIEW REQUIREMENTS for a company that stores information about their operations. To transform each user view into ERD, examine the requirements and create the necessary entities, keys, and relationships. USER VIEW 1 REQUIREMENTS: For a sales rep, store the sales rep's number, name, address, and commission rate. USER VIEW 2 REQUIREMENTS: For a customer, store the customer's number, name, address, balance, and credit limit. In addition, store the number and name of the sales rep who represents this customer. A sales rep can represent many customers, but a customer must have exactly one sales rep (a customer must have a sales rep and cannot have more than one sales rep). USER VIEW 3 REQUIREMENTS: For a part, store the part's number, description, units on hand, the number of the warehouse in which the part is located, and the price. All units of a particular part are stored in the same warehouse. For example, all dishwashers of the same type are stored in the same warehouse. The company really needs to keep a history of all the prices of each part as prices are changeable. Each time a price is changed, they would like to keep track of the date it changed. USER VIEW 4 REQUIREMENTS: For an order, store the order number, the order date, the number, name, and address of the customer that placed the order, and the number of the sales rep who represents that customer. In addition, for each line item within the order, store the part number and description, the number of the part that was ordered, and the quoted price. Following is the visual of user view 4 requirements: Customer Number: Name: Phone: Address: City: Province: Postal Code: Item Code A1234 B2345 E6789 H8790 CUSTOMER ORDER FORM C1234 Patrick Launder (647) 123-2345 456 My Street Toronto Ontario M9M 4G8 Item Description HP DesignJet T650 Printer HP DesignJet T250 Printer Canon MAXIFY MB2720 Copier Plain Paper Fax Total: Unit Price $ 1,995.00 $1,295.00 $ 250.00 $ 50.00 Jrder Number: Rep Number: 20 Quoted Price $ 1,900.00 $ 1,200.00 $ 250.00 $ 50.00 $ 2300.00 Quantity Ordered 10 25 30 20 The user also has supplied the following constraints: Each order must be placed by a customer that is already in the CUSTOMER table. There is only one customer per order. On a given order, there is at most a one-line item for a given part. For example, part DR93 cannot appear in several lines within the same order. 2345 The quoted price might not match the current price in the part table, allowing the company to sell the same parts to different customers at different prices. It also allows you to change the price for a part without affecting orders that are currently on file. 1. Use 'SQL Developer Data Modeler' or any other software to draw Logical Data Model. 2. Take screen capture of the data model and embed it in the word document. 3. Resave the document with your name_lab#_db. (Eyourname_lab#_db) 4. SUBMIT ONLY WORD DOCUMENT OR PDF. 5. Submit it in the link provided in the assignment section. a) Complete the Database design that satisfies the following constraints and USER VIEW REQUIREMENTS for a company that stores information about their operations. To transform each user view into ERD, examine the requirements and create the necessary entities, keys, and relationships. USER VIEW 1 REQUIREMENTS: For a sales rep, store the sales rep's number, name, address, and commission rate. USER VIEW 2 REQUIREMENTS: For a customer, store the customer's number, name, address, balance, and credit limit. In addition, store the number and name of the sales rep who represents this customer. A sales rep can represent many customers, but a customer must have exactly one sales rep (a customer must have a sales rep and cannot have more than one sales rep). USER VIEW 3 REQUIREMENTS: For a part, store the part's number, description, units on hand, the number of the warehouse in which the part is located, and the price. All units of a particular part are stored in the same warehouse. For example, all dishwashers of the same type are stored in the same warehouse. The company really needs to keep a history of all the prices of each part as prices are changeable. Each time a price is changed, they would like to keep track of the date it changed. USER VIEW 4 REQUIREMENTS: For an order, store the order number, the order date, the number, name, and address of the customer that placed the order, and the number of the sales rep who represents that customer. In addition, for each line item within the order, store the part number and description, the number of the part that was ordered, and the quoted price. Following is the visual of user view 4 requirements: Customer Number: Name: Phone: Address: City: Province: Postal Code: Item Code A1234 B2345 E6789 H8790 CUSTOMER ORDER FORM C1234 Patrick Launder (647) 123-2345 456 My Street Toronto Ontario M9M 4G8 Item Description HP DesignJet T650 Printer HP DesignJet T250 Printer Canon MAXIFY MB2720 Copier Plain Paper Fax Total: Unit Price $ 1,995.00 $1,295.00 $ 250.00 $ 50.00 Jrder Number: Rep Number: 20 Quoted Price $ 1,900.00 $ 1,200.00 $ 250.00 $ 50.00 $ 2300.00 Quantity Ordered 10 25 30 20 The user also has supplied the following constraints: Each order must be placed by a customer that is already in the CUSTOMER table. There is only one customer per order. On a given order, there is at most a one-line item for a given part. For example, part DR93 cannot appear in several lines within the same order. 2345 The quoted price might not match the current price in the part table, allowing the company to sell the same parts to different customers at different prices. It also allows you to change the price for a part without affecting orders that are currently on file.

Answer rating: 100% (QA)

Answer SQL code Create tables CREATE TABLE SalesRep RepNumber INT PRIMARY KEY Name VARCHAR255 Addres... View the full answer