Need help

(Please PRINT)

MMIS 630: Database Management and Applications

Final Exam, Fall 2018

Maximum Score: 30 Points

· Answer all questions submit a single consolidated document on Blackboard

· Penalty for late submission: 20 points

· Penalty for collaboration: 25 points

· You may use reference material.

· Your submission should strictly reflect your individual effort.

· There should be no consultations or collaboration.

· Please let me know in class on November 28 if you need any clarifications.

· Answer question 2 with reference to the database specified for homework 2.

· Your SQL queries should work for every valid instance of the database.

Question 1: Data Normalization. [10 points]

Table TABC has its primary key attributes underlined:

Decompose TABC into tables in the third normal form using the functional dependencies specified below:

1. A D, E, F, G

2. B H, I, J

3. C K, L

4. D E, F

5. H I

6. M N, O

Give each table a unique name TNAME where “NAME” is formed by concatenating the attributes in its primary key. Underline the primary key attribute(s) in each table.

For each applicable table with foreign key(s) specify the foreign key attribute(s) as:

FK [Attribute_Name] references [Table_Name]

2NF:

FK: A references TA;

FK: B references TB;

FK: C references TC

TB (B, H, I, J)

3NF:

TABC (A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R)

TA (A, D, G)

FK: A references TA;

TB (B, H, J)

FK: B references TB;

TC (C, K, L)

FK: C references TC

TD (D, E, F)

FK: D references TD

TH (H, I)

FK: H references TH

TM (M, N, O)

FK: M references TM

Question 2: SQL [2 10 = 20 Points]

Formulate SQL queries for the following (a and b) with reference to the database schema specified for homework 2. Ensure that your SQL queries will produce a correct response for every valid instance of the database.

a. [10 points]

List the customerid of customers who have placed, both, in-state orders as well as out-of-state orders.

In-state orders are defined as transactions between customers and salespersons located in the same state. Out-of-state orders are defined as transactions between customers and salespersons located in different states. A salesperson’s state is the state in which the salesperson’s branch office is located.

b. [10 points]

List those states where, both, the product revenue and installation revenue from transactions between salespersons and customers in the state exceed $1,000,000.

3