similar_to_ECE356_W2013_final.pdf

10 Pages
164 Views
Unlock Document

Department
Electrical and Computer Engineering
Course
ECE 356
Professor
Wojciech Golab
Semester
Fall

Description
Student name: Student ID number: Question 1: Entity-relationship modeling [5 marks] A restaurant would like to construct a database of their food and beverage menu. The following requirements have been identified:  The restaurant serves dishes and beverages.  Each dish has a name, price, weight, and category (e.g., appetizer, entrée, dessert).  Each beverage has a name, price, weight, and temperature (e.g., hot or cold).  Some dish-beverage pairs are compatible with each other, while other pairs are not.  A dish may be compatible with more than one beverage, and a beverage may be compatible with more than one dish.  Each dish must be compatible with at least one beverage.  A beverage need not be compatible with any dish. Use an entity-relationship diagram to depict the above scenario. Your diagram should follow the notational conventions described in the lecture notes or the course textbook. Include all relevant entities and relationships, cardinality constraints, and participation constraints. Indicate primary keys of entities by underlining them. (You do not need to translate your diagram into tables.) Page 3 of 12 Student name: Student ID number: Question 2: Query languages [5 marks] An animal shelter uses the following relational schema to model adoption data: Animal (ID, Name, DateAdmitted, Age, Type) Person (SIN, Name, Address, Phone, Age, Occupation) Adoption (ID, SIN, DateAdopted)  Adoption[ID] is a foreign key that references Animal[ID]  Adoption[SIN] is a foreign key that references Person[SIN] Answer the following queries using the indicated query languages. Part (a) [1 mark] Using tuple relational calculus, find all the animals in the shelter that were adopted by a person of age 65 or more. Part (b) [1 mark] Using extended relational algebra, find every animal type such that there exists no adoption record for any animal of that type. Part (c) [1 mark] Using SQL, find the ID and name of every animal of Type='cat' that has ever been adopted. Part (d) [2 marks] Using SQL, find the dates in September of 2012 on which at least five adoptions occurred, along with the corresponding number of adoptions on each of these dates (all in one query). Assume that the attribute Adoption[DateAdopted] is an ordinary SQL date. Page 4 of 12 Student name: Student ID number: Question 3: Constraints and database design [5 marks] The following questions pertain to the schema from question 2. Part (a) [1 mark] Identify the parent and child tables corresponding to the foreign keys in the schema. Part (b) [1 mark] What referential action (associated with a foreign key constraint) would ensure that if an animal is deleted from the database, all of its corresponding adoption records are also deleted? Part (c) [1 mark] Suppose that the database stores each table in a heap file, and that no indices exist initially. What index or indices should be created to speed up the query in part (d) of question 2? For each + index, indicate the table it applies to, the attribute(s), and the type (B -tree vs. hash). Use as few indices as possible. You do not need to provide SQL. Part (d) [2 marks] Repeat part (c) of this question, but consider the following query instead: find all animals of Type='dog' admitted to the shelter in August 2012. Page 5 of 12 Student name: Student ID number: Question 4: Functional dependencies [5 marks] Let R denote a relation scheme with four attributes: (A,B,C,D). Assume that R is in 1NF. Consider the following set of functional dependencies over the attributes of R: F = { D  B CA  D DA  C } Part (a) [1 mark] + Compute the attribute closure (DA) . Part (b) [3 marks] Prove using Armstrong's Axioms (and only those axioms) that DA  BC follows from F. Show all steps of your proof, and for each step state clearly which axiom you are applying. Part (c) [1 mark] Determine whether C is extraneous on the right side of DA  C with respect to F. Explain your answer. Page 6 of 12 Student name: Student ID number: Question 5: Normalization [5 marks] Let R denote a relation scheme with four attributes: (A,B,C,D). Assume that R is in 1NF. Consider the following canonical cover of functional dependencies over the attributes of R: Fc= { AB  C C  B } Part (a) [3 marks] Test whether R is in 3NF, and if not then use the algorithm taught in lecture to obtain a 3NF decomposition of R with respect to Fc. Show your work in detail. State your final answer clearly. Part (b) [1 mark] Does your final answer from part (a) also satisfy BCNF? Explain briefly why or why not. Part (c) [1 mark] (Note: this is a hard question. Do not get stuck here!) Let R' denote an arbitrary relation scheme and let F' denote a set of functional dependencies over the attributes of R'. Is it possible that two distinct canonical covers exist for F'? Explain briefly. Page 7 of 12 Student name: Student ID number:
More Less

Related notes for ECE 356

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit