CS610/710 Review exercise 2

Decomposition exercises:

1.Consider a database of ship voyages with the following attributes: S (Ship name), T (type of ship), V (voyage identifier), C (cargo carried by one ship on one voyage), P (port), and D (day). We assume that a voyage consists of a sequence of events where one ship picks up a single cargo, and delivers it to a sequence of ports. A ship can visit only one port in a single day. Thus, the following functional dependencies may be assumed: S  T, V  SC, and SD  PV.

  1. Find a decomposition into BCNF.
  2. Find a dependency-preserving decomposition into 3NF.
  3. Explain why there is no lossless-join, dependency-preserving BCNF decomposition for this database.
  1. Suppose we have a database for a bank, consisting of the following attributes: K (banker), N (branch name), L (city branch is located in), C (customer name), A (account number), and B (balance), with the following functional dependencies: N  L, A  BCN, K  N, and AN  K.
  1. Find the key(s) for the relation schema R = KNLCAB. Justify your answer.
  1. Find a decomposition of R into collections of relations that are in 3NF.
  1. Find a decomposition of R into collections of relations that are in BCNF.

SQL exercises:

  1. Based on the suppliers-parts-projects database of the review exercise, create the relations for the database in Oracle using SQLPLUS. The data for the relations are provided in a text file, assignment2_data.txt, which is posted on the course web page.
  1. For the suppliers-parts-projects database of review exercise, write the following queries in SQL. Validate the correctness of your answers using Oracle’s SQLPLUS.
  2. Print the projects that are supplied by at least one supplier not in the same city.
  3. Print the projects that are not supplied with any red part by any London supplier.
  4. Print the parts that are supplied to all projects in London.
  5. For each part being supplied to a project, print the part number, the project number, and the corresponding total quantity.
  6. For each supplier, print the total number of parts being supplied to all projects.
  7. Print the supplier numbers for suppliers supplying some project with part P1 in a quantity greater than the average shipment quantity of part P1 for that project.
  1. Using SQL, write queries to perform the following operations on the suppliers-parts-projects database. Validate the correctness of your answers using Oracle’s SQLPLUS. Print the appropriate relations to demonstrate the correctness of your queries.
  2. Change the city of all blue parts to Athens.
  3. Delete all parts for which the total number of shipments is less than 1000.
  4. Insert a new project (J8) into relation J. The name and city are Multimedia and San Francisco, respectively.