Database Management Systems



Database Management SystemsSept. 27, 2013Name__________________________Define each of the three levels of a database system architecture. What is represented at each of these levels? Be sure to include how many schemas are found at each level in a database implementation (exactly 1 or many)[9 pts]a. Conceptual level—(schema count = ________)b. External level—(schema count = _________)c. Physical (internal) level—(schema count = _________)True/False on database approaches. [6 pts]_____ The hierarchical model was the earliest major database approach._____ The hierarchical model represents the database schema as a tree._____ A network model database is most closely related to mathematical set theory._____ An object oriented database model is akin to a network database model._____ A relational database approach has been the most efficient in performance from the beginning._____ An object oriented database is today’s primary database approach.Define each of these terms [12 pts]Database integrity:Database administrator:Weak entity:Cardinality of a relationship: True/false on functional dependency theory.[10 pts]______ Functional dependency analysis is required in the normalization of a relational schema.______ Multiple entries of a fact is permitted in a normalized relational database.______ Normalization attempts to eliminate nulls stored in a database.______ A null value is equivalent to zero.______ A functional dependency is a relationship between an attribute "Y" and a determinant “X” (1 or more other attributes) such that for a given value of X the value of the attribute Y is uniquely defined.______ Functional dependencies can be determined by algorithms to analyze the relation.______ In practical terms, we want other all non-key attributes to functionally dependent only on the primary key in any relation.______ Functional dependencies are reflexive, i.e., if a →b then b →a______ Transitivity of functional dependencies means that if a →c and a→b then b→c.______ Decomposition of functional dependencies means that if a→bc then a→b and b→cFor parts a-c, assume we have a relation with the scheme Book (Title, Author, Publisher, PubAddress, PubZip, CopyrightYear, ISBN ) //ISBN = International Standard Book Number[12 pts] What would be the likely primary key attribute(s)? _________________________________[2]List all non-trivial functional dependencies [7]?If this relation were used as defined (not normalized), describe the insertion and deletion anomalies that could arise. [3]For the following Presidential ER diagram develop the relational schema using the pattern TableName(attribute-list). In the interest of time and space, just list 3 more non-key attributes in your table from the entities, not all attributes are necessary. Only specify the relations the entities Presidents, Election and Party_History. Include only the relationship that fall between these four entities. Underline the key attribute(s) in each relation.[16 pts]For the remaining questions, use the following relational schema for a music albums database. Keys are (mostly) underlined. The attributes should be self-evident. If not, please ask for clarification. For a given music track, we code the title, its play length in time (minutes:seconds), its genre (pop, metal, jazz, etc.) and a 5 star maximum rating. The musicians, singers and instrumentalists are all listed in on their contribution to the track. A person may have 1 or more listing for a track. For example someone may both sing and play the piano. The album is a collection of tracks. An album is distributed and owned by a company called the label and has a producer and an engineer.PEOPLE (PID, name, address, zip, phone)CSZ (zip, city, state)TRACKS (trID, title, length, genre, rating, albID) //trID is unique across all albumsALBUMS (albID, albumTitle, year, label, prodPID, engPID, length, price)CONTRIBS (trID, PID, role)[10 pts]a) Underline the appropriate attribute(s) of the primary key for the relation CONTRIBS. b) Circle attributes that could be virtual in the ALBUMS relation.c) Draw arrows from foreign keys to their corresponding relation and attribute.d) What candidate keys are there in the PEOPLE relation? _________________________________________Give Relational Algebra statements for the following queries on the rental company database schema. Use the R.A. notation below. BE EXPLICIT in the join condition which attributes make the join where necessary.[25 pts]Syntax reminder for Relational Algebra expressions:SELECT?: condition(relation)PROJECT?: attribute-list(relation)SET Operations and JOIN: relation1 OP relation2, where OP is , , - , , , and ||conditionRENAME: relation[new attribute names]ASSIGN: new-relation(attrs) R.A. expression a) List all names and phone numbers of people from zip 90210.List album titles and labels with a list price of more than $18.List all the musicians and what they played or contributed to on all jazz type tracks.Get a list of names of people who produced OR engineered an album, but did not perform on any track. (Hint: set operations are very helpful) List names of musicians who have contributed in at least two different roles on the same tracks with ratings 4 or higher. (Hint: self-join) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download