A1.3 Campus Cuisine Schema

Consider the fictitious Campus Cuisine database. The database at the start of this assignment is simply a table that contains the number one voted restaurant of a cuisine for a campus. The CAMPUS and CUISINE fields together make up the composite primary key because there can only be one number one place for a cuisine at a campus. One favorite place per cuisine per campus.

CAMPUS	CUISINE	PLACE
KCC	Burgers	Big City Diner Waialae
KCC	Chicken	Popeyes Kapahulu
KCC	Italian	Angelo Pietro
KCC	Mexican	Azteca Mexican Restaurant
KCC	Nepalese	Himalayan Kitchen
KCC	Sandwiches	Subway Iliahi Building
UHM	Burgers	Teddy's Bigger Burgers
UHM	Italian	Paesano Ristorante Italiano
UHM	Mexican	Serg's Mexican Kitchen
UHM	Nepalese	Himalayan Kitchen
UHM	Sandwiches	Andy's Sandwiches Manoa
UHWO	Burgers	Chilis Grill and Bar
UHWO	Chicken	Popeyes Waipahu
UHWO	Italian	Assaggio Kapolei
UHWO	Sandwiches	Jersey Mike's Kapolei

One problem with this table is that although it is in 3NF (no non-key attributes depend on any other non-key attributes), it�s not BCNF, because we have one of the key attributes dependent on a non-key attribute, namely, the PLACE determines the CUISINE. In this version of the problem, we assume a place can only be one cuisine.

An anomaly could be that a place is entered more than once as different cuisines. A solution to this would be to add another table PLACE_CUISINE with PLACE the primary key and CUISINE dependent on it. We would also like to protect against any misspellings causing invalid entries.

In this database we will see how we can normalize this table and add some code to protect against these and other anomalies.

Begin by creating a new database named CAMPUS (all uppercase).

Add the above table and name it ORIGINAL. Decompose the table using projections into 4 new tables: CAMPUSES (1 column), CUISINES (1 column), PLACE_CUISINES (2 columns) and CAMPUS_PLACES (2 columns). Make CAMPUS varchar(10), CUISINE varchar(25) and PLACE varchar(50).

Make CAMPUS primary key in CAMPUSES. CUISINE primary key in CUISINES. PLACE the primary key in PLACE_CUISINES and (CAMPUS, PLACE) the composite primary key in CAMPUS_PLACES.

Add referential integrity constraints so campuses must be in the CAMPUSES table before they are referenced in other tables. Cuisines must be in the CUISINES table before they can be referenced in other tables. Places must be in the PLACES_CUISINES table before they can appear in CAMPUS_PLACES. A change to the name of a campus, cuisine or place can change and the change will propagate. Make sure the tables are named as described. Watch this video if you need help.

Once you are done, confirm the following SELECT statement produces the original table:

SELECT CAMPUS, PLACE, CUISINE FROM CAMPUS_PLACES NATURAL JOIN PLACE_CUISINES NATURAL JOIN (CAMPUSES JOIN CUISINES)

Note that (CAMPUS_PLACES NATURAL JOIN PLACE_CUISINES) gives us back our original table and (CAMPUSES JOIN CUISINES) gives us every valid CAMPUS, CUISINE combination.

So CAMPUS_PLACES NATURAL JOIN PLACE_CUISINES NATURAL JOIN (CAMPUSES JOIN CUISINES) gives us every valid CAMPUS, PLACES relation that follows all of our rules (and protecting against misspellings).

When you are finished, make sure you imported the database correctly using the grading assistant indicated above. Make sure you leave the correct database as is throughout the semester to insure you get full credit at the end when the grades are recomputed.

We say that the relations are JOIN DEPENDENT. Since joining all the relations defines the CAMPUS, CUISINE, PLACE relation, the database is in 5th normal form.