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,animalid,date) Note that because each Isa 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
This document was uploaded on 03/18/2014 for the course CPSC 304 at University of British Columbia.
- Fall '11
- Relational Database