IT2351-Assignment 2 Solved

A key component to understanding joins is first understanding the database in which you are working.  Therefore, you will spend some time examining the OM database.  This will also start the process of preparing you for normalization and database design in chapter 10. The bulk of your work in assignment 2 will be on joins in chapter 4 with an emphasis on inner and outer joins since they are the most prevalent and specific (rather than natural joins.)

 

1)     Look at the OM database and list the following:

a)      The names of the tables.

b)     The names of the primary keys for each table.

c)      The names of the foreign keys for each table.

d)     A drawing of the relationships between the tables (show one-to-many relationships between all tables in the database).

 

Take a screenshot of your list and diagram (or submit in anyway you find logical) and name the file: Assignment2Part1.  

   

2)     Queries using the OM database.:

Double-click on the om database so that it is bold (be sure to go through chapter 4 to practice and prepare for the exercises).  This means that it is the database currently being used.  You can also issue the command “use om;” in the SQL window. ***Note: for each query below be sure to explain your join logic in comments at the top of your query (for example: what was the primary and foreign keys of the tables that you selected to formulate your join logic) and save all of your queries to a script file to submit in addition to your screenshot of the query run.

 

a)      Output the title, artist, unit_price and order_qty for each order in the OM database.  Use an INNER JOIN (INNER keyword is optional rather than a natural join -  you will have an ON keyword), sort your output by title and then artist. Name your image and script file IT2351Assignment2Part2A.  In upcoming chapters, we will learn how to subtotal the data to compress detail.

 

b)     Output customer name (concatenated), city, state, order_date and ship_date for each order.  

Sort the output by state, city, last name, first name.  Name your image and script files:  Assignment2Part2B.   

 

c)      Output the detail for each order, in other words show the customer_id, order_date, item_id and order_qty for each order. Name the image and script files: Assignment2Part2C.

 

d)     Looking at your last query, it would be helpful to have item names (title) rather than item_id.  

Update the last query to replace item_id with artist (add another join). Name the image and

script files: Assignment2Part2D.  

 

e)     Once again you will add to the last query replacing customer_id with customer name.  Add another join that will accomplish this goal. Name the image and script files: Assignment2Part2E.

 

f)       Output all the titles with an order quantity over one. Name the image and script files:  Assignment2Part2F.  

 

g)      Output all the orders for customers in Ohio. Include the customer names and titles purchased in your output. Name your image and script files: Assignment2Part2G.

 

h)     Insert a row into the customers table (data of your choice). Name your image and script files Assignment2Part2H.

 

i)       Update the unit_price by 10% for all items with a unit_price 17. Name your image and script files: Assignment2Part2I.

 

j)       Explain what you would need to consider to delete a customer from the customers table.  

Include this discussion in the lab submission text box.