We are going to populate the database with some sample data. Your assignment is to write a series of INSERT and UPDATE statements that will enter you as a customer, place two orders on your behalf, and select four products for each order that you wish to purchase. Finally, you will assign a commission to the employee(s) who helped you with the purchase.

Create a simple text file called yourname.sql that will contain the individual statements that will accomplish all of the data insertion in this assignment. You can create an empty text file, or alternatively, you can type all your SQL into the editor in Workbench and then save the script with File Save Script As...

Put each SQL command on its own line and end each line with a semicolon. When you're finished, run your script against the sample database platiado4. Double check that you have no errors. Once you're done, submit the script file to blackboard.

Procedure

Since there are foreign key constraints among the tables, you must proceed in a sequence that will not violate the referential integrity rules. Here is the suggested sequence of inserts:

• ï‚·  language
• ï‚·  customer
• ï‚·  order_t without the gross amount
• ï‚·  orderline
• ï‚·  update order_t with appropriate gross amount
• ï‚·  commission Follow these detailed steps:

1. Since each customer must have a preferred language, you must insert the language of your choice into the Language table. The language could be fictional, i.e., use can choose French or Swedish, even though you don't speak it in real life. Example: INSERT INTO LANGUAGE VALUES('French');
2. Add yourself as a customer. Insert your name and your preferred language of communication (from the previous step), as well as a customerid. Use your FIU student ID as a customerid to make this number unique since it is the primary key. Do this only once - you

 

can have many orders but you are going to have only one customer record in the Customer table.LeaveReferredbyempty.Example: INSERT INTO CUSTOMER (customerid, name, language) VALUES (1234567, 'Sam Smith', 'French');

1. Insert an order for yourself into the Order_t table. Each line in this table represents one order, even though the order contains multiple products (that is captured next in the Orderline table). Decide which branch you are shopping at (look at the Branch table). Insert one line for your order, including orderdate, branchid, and your customerid. For orderid, make a code by taking your customerid and add another digit to it for order number. So for your first order, the orderid will be in the form customerid1 and your second order will have orderid in the form customerid2. For example, if your FIU studentid is 1234567, your first orderid will be 12345671, and second orderid 12345672. Do not enter any data for Grossamount, Netamount, or Promoid. Note the format for the date. Example:INSERT INTO ORDER_T (orderid, customerid, branchid, orderdate) VALUES (12345671, 1234567, 1, '2016-02-22');
2. Insert at least 4 records into the Orderline table that will represent the 4 different products that are on your order number 1. Pick 3 simple products and 1 product that is a collection. You can tell the product type by looking at the type column. Insert the orderid, productid, and quantity of the product you're buying. The 4 products will have the same orderid, as you are entering the same order. Example: To buy 4 pieces of product 2: INSERT INTO ORDERLINE (orderid, productid, quantity) VALUES (12345671, 2, 4);
3. Now it's time to update the Grossamount on your order. Calculate what is the total of all your purchases (price * quantity) and update the Gross amount. Make sure that your UPDATE statement has the limiting WHERE clause set to your orderid. Example: UPDATE ORDER_T SET Grossamount = 160 WHERE ORDERID=12345671;
4. Decide how much commission the employee(s) are going to make on this order and how many employees are going to share it. Make sure that the employees helping with the order are working in the branch where your order was placed! Insert the appropriate data in the Commission table. Insert the employeeID, the appropriate orderid, and the commission amount (as a dollar value, not a percentage). If two employees are splitting the commission on this order, you must enter 2 lines in the commission table, both with the same orderid but different employeeid and appropriately split commission amount. Split the commission as you see fit. Example: INSERT INTO COMMISSION (ORDERID, EMPLOYEEID, AMOUNT) VALUES (12345671, 2, 15);
5. Repeat steps 3 to 7 one more time for a second order. The orderid for your second order will be your customerid with a 2 added to it as the last digit. Choose a different branch for your

 

order. Purchase 4 products - 3 simple ones and 1 collection. Calculate the order gross amount and update the correct order. Insert the commission data for this order as well.

8. This assignment is due on Monday March 6, 2017 at 11:00am. No late submissions will be accepted.

Grading (maximum 100 points):

deduct 4 points for each missing bullet point:

• ï‚·  customer data correctly entered, with student's full name and preferred language, and unique customerid
• ï‚·  preferred language entered into language table
• ï‚·  order table has valid orderid, orderdate, customerid and branchid, with unique orderid
• ï‚·  the orderdate is a valid date
• ï‚·  order table has a correctly updated grossamount
• ï‚·  there are 2 orders per customer
• ï‚·  orderline table has valid data: 4 products per order, of which 1 must be a collection
• ï‚·  commission table has valid data given the orderid and a reasonable commission dollar amount
• ï‚·  employee(s) getting commission on an order works at the branch where the order was placed
• ï‚·  two orders are not placed in the same branch deduct 4 points for each error preventing the SQL from running against the sample database platiado4 (provided on blackboard)

ComputerScienceExpert

(11)

Status NEW Posted 28 Apr 2017 03:04 AM My Price 11.00

-----------

Attachments

file 1493350116-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer