How to Use SQL on Premiere Products

Exercise 1

In the following exercises, you will use the data in the Premiere Products database (shown in the attachment). Explain how you would use SQL to obtain the desired results:

1.Craete the TopLevelCust view described in the following:
Using data from the Premiere Products database, define a view named TopLevelCust. It consists of the number, name, address, balance, and credit limit for all customers with credit limits that are greater than or equal to $100,000.
a.Using SQL, write the view definition for SmallCust.
b.Write an SQL query to retrieve the number and name of all customers in the SmallCust view with balances that exceed their credit limits.
c.Convert the query you wrote in Question 1b to the query that the DBMS will actually execute.
Display the data in the view.

2.Create thePartOrder view described in the following:
Define a view named PartOrder. It consists of the part number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file.
a.Using SQL, write the view definition for PartOrder.
b.Write an SQL query to retrieve the part number, description, order number, and quoted price for all orders in the PartOrder view for parts with quoted prices that exceed $100.
c.Convert the query you wrote in Question 2b to the query that the DBMS will actually execute.
Display the data in the view.

3.Create a view named OrdTot. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number ordered multiplied by the quoted price on each order line for each order.) Display the data in the view.

4.Create the following indexes. If it necessary to name the index in your DBMS, use the indicated name.

a.Create an index named PartIndex1 on the PartNum field in the OrderLine table.
b.Create an index named PartIndex2 on the Warehouse field in the Part table.
c.Create an index named PartIndex3 on the Warehouse and Class fields in the Part table.
d.Create an index named PartIndex4 on the Warehouse and OnHand fields in the Part table and list units on hand in descending order.

5.Drop the PartIndex3 index.

Note: You will not be able to execute the code for view on the MySQL database, but you can test the SELECT statement that creates it. Questions 1, 2, and 3 require that you show the CREATE VIEW SQL as well as the SQL to select from the view itself.

---------------- End Exercise 1-----------------

Exercise 2

The following exercises are based on the Premiere Products database:

1.Using your knowledge of Premiere Products, 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.

Part (Part Num, Description, OnHand, Class, Warehouse, Price, (OrderNum, OrderDate, CustomerNum, CustomerName, RepNum, LastName, FirstName, NumOrdered, QuotedPrice) )

2.List the functional dependencies in the following table that concerns invoicing (an application Premiere Products in considering adding to its database), subject to the specified conditions. For a given invoice (identified by the InvoiceNum), there will be a single customer. The customer's number, name, and complete address appear on the invoice, as does the data. Also, there may be several different parts appearing on the invoice. For each part that appears, display the part number, description, price, and number shipped. Each customer that orders a particular part pays the same price. Convert this table to an equivalent collection of tables that are in third normal form.

Invoice (InvoiceNum, CustomerNum, LastName, FirstName, Street, City, State, Zip, Date, (PartNum, Description, Price, NumShipped) )

---------------- End Exercise 2-----------------