IS 342 School of Computer Sciences Western Illinois University

Final Review Problems Amaravadi

  1. Consider the following Orders table.

Ord# / Salesperson / Cust# / Ord Amt
6785 / McMohan / 121 / $340
7345 / Wells / 113 / $6,000
7545 / Riefstock / 131 / $1,549
7685 / Wells / 115 / $300
8567 / Narayan / 121 / $6,000
9768 / McMohan / 113 / $217
9800 / Riefstock / 121 / $1,400

Identify the functional dependencies in the table above, assuming only the sample data.

2.Consider the following Automobile repair table

Invoice# / Invoice Dt. / Repair Code / Repair Descr. / Cust Name
AB46621 / 1/14 / B2, B3 / Brake, Lube / Jones
AB56745 / 1/14 / B15 / Valve timing / Dzak Motors
AF33201 / 1/15 / B1, B32 / Oil change, Tune up / Dzak Motors
AF34200 / 2/22 / B32 / Tune up / Lopez
AF35609 / 2/23 / B44 / Cylinder reboring / Barrington
AF45675 / 2/23 / B23 / Replace radiator / Lopez
AF53211 / 2/25 / B2, B3 / Brake, Lube / Lester
  1. Draw a functional dependency diagram based on the sample data.
  1. If we know that the FD diagram is incomplete, what steps do we need to take as database

developers?

  1. What is the normal form of the table above?
  1. Assuming Invoice# as the primary key, are there partial and transitive dependencies?
  1. Normalize the table above and rationalize your design.
  1. Estimate the storage requirements of the table in the unnormalized form and normalized

forms, with the records as given.

  1. Write a query to create the invoice table (in its normalized form)

g.Write a query which will list the customers and the repairs they have had, assuming normalized tables.

h.Assuming that the tables (after normalization) will have substantial number of records, you were asked to index, what would be your indexing strategy?

  1. Given the following functional dependencies, identify a set of 3rd NF relations.

P,Q - R, S, T, U, X

S  T

T  X

4.Draw an FD among the following: Author, Title, ISBN#, Price, Publisher, Pub. Code, Author affiliation.

5.Draw an ER Diagram and develop a design for SFS financial with the following data items:

PARCEL, BUILDING, FLOOR, TENANT, LEASE, OWNER, RENT and the following additional constraints:

  1. A building can occupy one or more land parcels. Parcels have a parcel id and an assessment. Each building has a unique name, a (non unique) location and the date it was built.
  2. A building consists of one or more floors; each floor has a floor number unique within the building.
  3. Each floor has a number of office spaces, each with a unique number within the floor.
  4. Tenants take leases on one or more office spaces
  5. Tenants have a company name, a contact name and a contact phone
  6. The office space has a rental amount and type of lease (yearly or monthly).