Database Management Systems



Database Management SystemsOctober 2, 2015Name__________________________Consider the simplified three level database system architecture of external, internal/physical and conceptual/logical levels. List the level that best matches the description. [8 pts]________ Entity-relationship modeling is at this level. ________ Conversion of the ER model to a relational schema is found at this level. ________ Tables and data records are structured for storage are defined in this level. ________ Simplifying and/or hiding portions of the data base tables for the end users occur at this level. ________ There can be many views of the database found at this level. ________ There is only one instance of the model at this level. ________ The operating system interacts with the database management system at this level.________ An application written in Java that accesses the database occurs at this level.True/False on database approaches. [6 pts]_____ The relational model was the earliest major database approach._____ The hierarchical model represents the database schema as a tree._____ A object oriented model database is most closely related to mathematical set theory._____ An object oriented database model has similarities to a network database model._____ A relational database in early days were considered too inefficient for practical use._____ The relational database is expected to remain the primary approach in foreseeable future.Define each of these concepts [12 pts]Database transaction:Cardinality of a relationship:Weak entity: True/false on functional dependency theory.[10 pts]______ A functional dependency of Y on X is an extension of the mathematics concept that a value in X clearly determines the corresponding value in Y.______ Functional dependencies can be determined by algorithms to analyze the relation’s attributes.______ Functional dependencies are reflexive, i.e., if a →b then b →a______ Transitivity of functional dependencies means that if a →b and c→b then a→c.______ It’s ok to rewrite a→bc as a→b and a→c, and vice versa.______ Functional dependency analysis is required in the normalization of a relational schema, but is irrelevant in the analysis of an entity’s attributes in the ER model.______ Multi-valued attributes are permitted in a normalized relational database.______ First normal form (1NF) prevents nulls to be stored in database tables.______ A null value is equivalent to the empty string in the case of a non-numeric type attribute.______ In practical terms, we want other all non-key attributes in the relation to be functionally dependent only on the foreign keys of the relation.For parts a-c, assume we have a relation with the schema where a physician can only work at one hospital: Hospital (HospID, HospName ,HospAddr, HospCity, PhysicianID, PhysicianName, PhysicianSalary )[14 pts] What would be the likely primary key attribute? _________________________________[2]List all non-trivial functional dependencies [8]?If this relation were used as defined (not normalized), describe an update anomaly that could arise. [4]For the following Presidents ER diagram develop the relational schema using the table pattern TableName(attribute-list). Just list the tables for the entity Election, the two entities directly related to Election and any tables arising from the connecting relationships. Underline the key attribute(s) in each relation. Be sure to include the foreign key.[15 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 with 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 each 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, labelID, prodPID, engPID, length, price)CONTRIBS (trID, PID, role)LABELS(labelID, name, address, zip)[10 pts]a) Underline the appropriate attribute(s) of the primary key for the relation CONTRIBS. b) Underline the appropriate attribute(s) of the primary key for the relation LABELS.c) Draw arrows from foreign keys to their corresponding relation and attribute.d) What candidate keys are there in the ALBUMS 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 |X|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 labelIDs with a list price of more than $18.List all the contributor names and their role on all jazz type tracks.Get a list of names of people who produced OR engineered an album, AND also performed on any track. (Hint: using set operations is simplest) List names of musicians and their roles who have contributed in at least two different roles on the same tracks that are ‘pop’ genre. (Hint: self-join) ................
................

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

Google Online Preview   Download