BookTitleAuthorCategoryShelfCopy#2003 Fall CS157A Final Exam Study Guide2003 Fall CS157A Final Exam Study GuideNote: Please bring 882E scantron!Thursday Dec11 14:45 pm---17:00 pmProf. Sin-Min LeeThe exam will be comprehensive. Test material will be drawn from the text book, lectures, assignments and any supplementary material provided in class.You should review the following: - all the lecture notes - all the assigned readings (if you don't have the text book, then the lecture notes will suffice) - both midterm exams For the exam you should be able to Understand the problems of file based systems and what the DBMS is intended to solve. Understand the meaning of all of the E-R symbols -give an example of 3NF-give an example of 4NF-define functional dependency -define key of a relation -define superkey of a relation -describe the algorithm for computing a closure of a set of attributes,given a set of functional dependencies -describe the three kinds of anomalies that can occur when we try to cramtoo much into a single relation -define the condition called BCNF -define the condition called 3NF -state why someone might reasonable choose to keep a relational schemain 3NF rather than decompose further into BCNF -extract functional dependencies from a textual description of a domain -take a set of FDs and find all keys -take a set of FDs and determine whether a set of attributes is a superkey -take a set of FDs and a relational schema and decompose the schemainto BCNF -recognize the existence of multivalued dependencies from a textualdescription of a domain and a relational schema, and the decompose theschema into 4NF -take a set of relational schemas and combining relations based on samekeys or candidate keys-algorithm to test the losslessness of a decompositionIndexes and B-treesoProperties and uses of indexes oCreating indexes in SQL Sample Problems.1)Consider a relational database containing relations with the following schemes: Customer-scheme = (name, address, cust-num)Flyrod-scheme = (manufacture, length, line-weight, flyrod-stock-num) Purchased-scheme = (cust-num, flyrod-stock-num, date)One of the referential integrity constraints associated with this flyshop database is: a) When a tuple is inserted into purchased, what test needs to be performed to insurethat this referential integrity constraint is maintained?b) When a tuple is inserted into customer, what test needs to be performed to insurethat this referential integrity constraint is maintained?2).A company that manufactures lava lamps uses a relational database to keep track of what parts ithas in stock, suppliers of parts, and which suppliers supply what parts. The schemes of these relationsare: Part-scheme = (part-num, description, groovyness, quantity) Supplier-scheme = (supplier-name, address, phone-number) Supplies-scheme = (part-num, supplier-name, address, cost)The primary key for relations on Part-scheme is part-num, and the primary key forrelations on Supplier-scheme is {supplier-name, address}.a) What referential integrity constraint should hold between the supplier and supplies relations?)Is there any referential integrity constraint between the part and3)Consider the relation scheme and set of functional dependencies: that relations on scheme R must satisfy. a)Which rule or axiom allows us to conclude from ?b) Which rule or axiom allows us to conclude from and?c) Which rule or axiom allows us to conclude fromand ?d) Is G a superkey for R? Why or why not?4) Consider the following example Advisor ( Student-SSN, Major, Faculty name) . Only one major is associated with one faculty name. Astudent can take many major. (i) What is the primary key(ii) List the functional dependencies(iii) What are insertion, deletion and modification anomalies with this relation. (iv) Check if the relation satisfies BCNF and if not, break the relation. 5) Consider the relational schema BOOKS(BookTitle, Author, Category, Shelf, Copy#) storing information about books in a library. Books have a title, an author, a position (shelf) and are subdivided into categories (poem, play, etc.). Several copies of a book can be maintained in the library.KEY = {BookTitle, Copy#}FUNCTIONAL DEPENDENCY = BookTitle -> Author, Category1. Is the schema in 3rd normal form?2. If not, decompose it appropriately.Verify whether the decomposition also satisfies the Boyce-Codd normal form6).Consider the relation scheme and set of functional dependencies: that relations on scheme R must satisfy. Find a lossless join decomposition of R into BCNF.7) Construct a B tree for the following key values: (2, 3, 4, 7, 11, 17, 19, 23, 29, 31). Assume that insertions happen in the order that the keys are listed in. You do not need to show the file that the leaves are pointing into - just the B tree.8) You are a technical expert employed by the local police department. The department has just found the secret hide-out of a gang of bank robbers. The leader of the gang has been using a relational database to store information about banks, robberies, and the members of the gang. You are called in to extract information from the database that will help in catching the robbers. The database has relations bank-robbed (which stores information about banks that the gang has already robbed), bank- to-rob (which stores information about banks that the gang plans to rob), member (which stores information about gang members), and has-robbed (which stores information about which gang members BookTitle Author Category Shelf Copy#Decameron Boccaccio stories C75 01The Divine Comedy Dante poem A90 01The Divine Comedy Dante poem A90 02Le Bourgeois Gentilhomme Moliere play A90 01Le Bourgeois Gentilhomme Moliere play A22 02Ulysses Joyce novel B50 01Richard III Shakespeare play B33 01have participated in robberies of what banks). Note that the gang may be planningto rob a bank that they have already robbed one or more times in the past. The schemes of these relations are: Bank-robbed-scheme = (name: string, address:string, number-of-depositors:integer)Bank-to-rob-scheme = (name: string, address:string, number-of-depositors:integer)Member-scheme = (name:string, address:string, years-in-prison:integer, banks-at:string, skill1:skilltype, skill2:skilltype)Has-robbed-scheme = (member-name:string, bank-name:string, date:string, amount:real) where attribute banks-at of member is the name
View Full Document
Unlocking...