SQL Oracle Help

Assignment Type: INDIVIDUAL

Due Date: 2-October-2013 (Wednesday), 10 a.m.

Submission: via D2L drop-box; please take all of the files documented below and combine them into a single .zip file using WinZip so that your D2L submission is a single .zip file. In addition, paper output (script and results for marking) is due in class on 3 October 2013 (or before).

Instructions:

· Ensure that you have run the latest version of the database creation script files located in K:\GPAULLEY\PROG3040\DATABASES\PLSQL_WORKBOOK\storedb\create_workbook_store.sql to create the required tables and data. 

· Familiarize yourself with the assignment standards. You should, at a minimum, capitalize SQL language keywords. Identifiers may be in lower case or mixed case – strive for consistency. Be neat.

Task 1. Database design and functional dependencies. [10 marks]

Closure: Certain functional dependencies imply others. Given a set F of functional dependencies that hold for a table R , the closure F+ of that set is the set of all FDs implied by the FDs in F . F+ can be computed by applying Armstrong’s axioms to F . Furthermore, given a subset Z of the attributes of a table R , and a set F of functional dependencies that hold for R , the closure Z+ of Z is defined as the set X of the attributes of R such that the functional dependency Z X is a member of F+ .

If the closures F+ and G+ of two sets of dependencies F and G are equal, then we say that F and G are equivalent.

To repeat from class, here are Armstrong’s axioms for functional dependencies, and additional axioms that can be derived from Armstrong’s original three axioms. Remember that X, Y, and Z are sets of attributes:

1. Inclusion: if Y is a subset of X, then X Y.

2. Augmentation: if X Y then WX WY.

3. Transitivity: if X Y and Y Z then X Z.

Other axioms:

· Self-determination: for any X, X X. X X is known as a trivial functional dependency.

· Union: if X Y and X Z then X YZ.

· Decomposition: if X YZ then X Y and X Z.

· Composition: if X Y and W Z, then XW YZ.

· Pseudo-transitivity: if X Y and WY Z then XW Z.

Example. Suppose we have a table R with non-null attributes A, B, C, D; we commonly write this as

R ( A, B, C, D).

Further suppose we have the following set F of non-trivial functional dependencies:

· A B

· B C

· BC D

We can determine the closure A+ of A as follows:

We are given A B. Trivially, attribute A functionally determines itself, so we have A A. Since we are given B C, by transitivity we have A C (because A B and B C, thus A C). Combining these through union, we have A ABC. Finally, with BC D, again by transitivity we have A D, because A BC and BC D, and therefore A D. Combining again, we have A ABCD, that is, A functionally determines all of the attributes of R . (Aside: hence A is called a superkey of R ).

Task 1, Question 1:

Suppose we have the table S with non-null attributes A through G; that is, we have

S( A, B, C, D, E, F, G).

Suppose we are given that S satisfies the following set G of non-trivial functional dependencies:

· A B

· BC DE

· AEF G

(1a). Determine the closure { A,C }+ under this set G of functional dependencies; remember that closure includes self-determination. Remember from mathematics that a pair of braces ({}) denotes a set; hence {A,C} denotes a set made up of the union of attributes from the sets A and C.

Is the closure {A,C }+ equal to (choose one only):

i.) ABC

ii.) ABCDE

iii.) AC

iv.) ABCDEFG

v.) ACDFG

(1b). Is the functional dependency ACF DG implied by G ? (Answer yes or no).

Submit your responses to 1(a) and 1(b) in a separate text file. Name your file Task1Q1XX.txt where XX are your first and last initials. You’ll include this file in your D2L submission.

Task 1, Question 2: [30 marks]

Create an SQL script that creates a set of tables in Boyce-Codd Normal Form to represent entities in an order/entry system. Note: as is often the case, there is no “right answer” for doing this; logical database design is always subject to tradeoffs because normalizing every functional dependency usually isn’t desirable. For example, we usually do not decompose the functional dependency address postal code because there is little value in doing so. An example from the schema used in the textbook are the superfluous tables for telephone numbers and street addresses.

This order/entry system will retain information similar to that modelled by the Video Store schema documented in the textbook, but with some differences. To prevent confusion with the Video Store tables, prefix all of your table names for this new set of tables with “PG$_”. Use lowercase and underscores for table and column names and for constraint names.

Your new schema is to include at least the following information:

· For each customer:

· Customer number (unique)

· Personal information (names)

· “Ship-to” addresses (multiple addresses per customer)

· Balance

· Credit Limit

· Discount percentage

· For each order:

· Order information: customer number, ship-to address, date of order

· Detail lines (several per order): item number, quantity ordered

· For each item:

· Item number (unique)

· Manufacturing plants (a plant may manufacture many items; each item may be manufactured by different plants)

· Quantity-on-hand at each plant

· Stock danger level at each plant

· Item description

· For each plant:

· Mailing Address

· Contact information

· Telephone number

· Assume that the company intends to handle tens of thousands of items in total, and thousands of orders per month.

· Also, for processing reasons a “quantity outstanding” value is associated with each detail line of each order; this value is initially set equal to the quantity of the item ordered and is progressively reduced to zero as (partial) shipments are made.

If you need to create surrogate keys for entities in this schema, choose data types and formats that are appropriate for the purpose. Include comment lines in your schema creation script that documents your proposed surrogate key formats, and why you chose those formats and data types. In addition to creating the appropriate tables and their columns, define the appropriate referential integrity constraints (primary keys and foreign keys) that relate the tables together. Give each referential constraint an appropriate name.

Your marks for this question will be based on the “reasonable-ness” of your design choices. You should provide commentary (in the form of comments in your SQL script) that explains your design choices, particularly if there might be debate about such a choice. Appropriate commentary will enhance your mark.

Create a script file (Task1Q2XX.sql, where XX are your initials in upper case letters) that creates the tables. You will include both your completed script file and its output file (Task1Q2XX.out) to your .zip file submission to D2L.

Task 2. SQL Queries [10 marks each]

Create a script file (Task2XX.sql, where XX are your initials in upper case letters) to combine the SQL queries for each problem below, in order. You will submit both your completed script file and its combined output file (Task2XX.out) to D2L.

1) Write a single SQL query that computes, for each contact, the average and sum of the rental transactions for rentals that were made in June 2011 (they could have possibly been returned in July or later). Include the overall grand total and overall transaction average in the result. You may not use a UNION query expression for your answer. For each contact, output:

a. Contact ID

b. Contact last name

c. Contact first name

d. Sum of the contact’s transactions for June 2011, in the format $9999.99

e. Average of the contact’s transactions for June 2011, in the format $99.99

f. For the grand total, substitute the literal string ‘June 2011 totals’ for the contact’s last name.

g. Output the list of contacts ordered by first name within last name.

Hints:

i. Use COALESCE() to substitute the ‘June 2011 totals’ string in the output

ii. Use GROUP BY ROLLUP

iii. Use a derived table in the FROM clause

iv. You will find the EXTRACT() and TO_CHAR() functions useful

2) Using the Video Store schema, write a query to produce a list of items that have never been rented. (Note: there is currently only one, with the Item ID of 1000). Rewrite your query in at least three different ways – that are semantically equivalent – that will return the identical result. Your rewrites may not be trivial ones – for example, reordering tables in the FROM clause or reordering predicates in the WHERE clause, or reversing or negating comparison operations.

3) Using the Video Store schema, write a query to produce a report that lists the number of times each of the Star Wars movies were rented in October, 2011. Include the total rental amount for each of the films. Consider only those movies in Wide Screen format on DVD. Output the ID of each film, its title, the number of times it was rented in October 2011 (use the column heading “Rentals”) and the total rental amount for each film, using proper money formatting in dollars and cents – use the column header “Total Amt” for this column. Order your result by film title.

4) Using the HR schema, write a query that lists all of the departments located in Seattle, Washington, along with their managers and the start date for each manager. If a department does not have a manager, output the value “Unknown”. Output the manager’s start date using the format “YYYY-MM-DD”. If the start date for that manager is unknown, also use the literal “Unknown”. Use appropriate headings for each column in the result. Order the list by department name.

Task 3. Supplementary task. [5 marks]

You have come into possession of a vintage VHS tape (on two cassettes) of the film “Lawrence of Arabia”. Add the tape to the Item table in the Video Store database using an INSERT statement that contains the necessary SELECT subquery to determine the proper item type code for this VHS double-set. COMMIT the change.

Once the film has been added to the database, re-run your queries from Task 2, Item 2. Ensure that your queries find both the new “Lawrence of Arabia” film, and the “Pirates of the Caribbean” film, both of which have yet to be rented.

After your query executes, delete the “Lawrence of Arabia” film using a DELETE statement, and COMMIT the deletion as part of your SQL script.

Create a script file (Task3ExtraXX.sql) that creates the new item, answers the queries, and deletes the item. Submit to D2L both your completed script file and output file (Task3ExtraXX.lst).