CPSC 304 2005 MIDTERM 1 SOLUTIONS

Insaleforeignkeymidreferencesmerchandise

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: n part a, into a relational schema using the methods discussed in class/the book. Note that this ER diagram has been carefully chosen so that there is one “right” choice for each transformation. State any assumptions that you make – but your assumptions cannot contradict the facts given. Output: i. Your resulting schema in the form Relation(attribute1, …, attributeN), where you underline each relation’s primary key. Customer (cid) Employee(esin) Merchandise(mid, weight) AnimalLivesIn(Amid, Name, days, cage#) (combined because it’s many to one) Cage(cage_num) Cat(catid, HairLength) Dog(dogid, Breed) Feeding(Date) Sale(cid, esin, mid) TakesCareOf(sin,animal­id,date) Note that because each Is­a relation is not total, and each superclass has a relationship on it, we must use the method where we create one table for each of Merchandise, Animal (which is combined with Lives in since it’s a many to one relationship), Dog, and Cat. See slides 15 & 16 in second part of Relational model. ii. For any foreign keys that you have identify the table in which they appear, and write how they would appear in SQL DDL – note you only have to declare the foreign key constraint in SQL DDL – not the rest of the question. In Sale: Foreign Key (mid) references Merchandise Foreign Key (esin) references Employee Foreign Key (cid) references Customer In Animal: Foreign Key(id) references Merchandise In Cat: Foreign Key(catid) references Animal (We took t...
View Full Document

Ask a homework question - tutors are online