CS 317: Assignment #4

"Decomposing Relations"

General Info

Out: Friday, April 26th, 2002

Due: Thursday, May 2nd, 2002

Changed: 4/29/2002

In this assignment you will will take a poorly designed relation schema, establish what's wrong with it, and fix it. Do not take shortcuts in the fixing process: although you should have no trouble recognizing the correct decompositions (since we've been working in the CDs domain so much), in the general case, you will be well-served by understanding the refinement process.

Assignment History

Assignment Checklist

Part 1: What's wrong with this relation schema?

CDs: CDtitle       year   artistName       artistAddress  length genre   recordCoName  recordCoAddr fanClub
     Best of      | 1994 | Bootsy Collins | Outer Space  | 75   | Funk  | Warner Bros | USA       | Rubber Fan Club
     Time Capsule | 1998 | B-52's         | Athens, GA   | 79   | Party | Reprise     | USA       | www.theb52s.com
     Best of      | 1998 | Santana        | San Fran, CA | 75   | Latin | Arista      | USA       | www.santana.com
     Cosmic Thing | 1989 | B-52's         | Athens, GA   | 45   | Party | Reprise     | USA       | www.theb52s.com
     Supernatural | 1999 | Santana        | San Fran, CA | 60   | Latin | Arista      | USA       | www.santana.com

Look at the relationship above. It is poorly designed. In particular, it isn't in Boyce-Codd Normal Form (BCNF). Your first task is to identify all of the BCNF violations. There are a few steps involved in accomplishing this (do all of them):

Part 2: Decompose the relations

Using the techniques from the book (3.6.4 Decomposition into BCNF), split the original relation into a set of relations that are in BCNF. In each decomposition (I needed two):

Part 3: Project the new schema

Draw tables for your final set of schema (i.e. all the schema that didn't violate BCNF) and project the data from the original relation into them.