Database Management Systems



Database Management SystemsOctober 2, 2015Name_________KEY_____________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]_Logical_ Entity-relationship modeling is at this level. _Logical_ Conversion of the ER model to a relational schema is found at this level. _Internal_ Tables and data records are structured for storage are defined in this level. _External Simplifying and/or hiding portions of the data base tables for the end users occur at this level. _External There can be many views of the database found at this level. _Logical | Internal_ There is only one instance of the model at this level. _Internal_ The operating system interacts with the database management system at this level._External An application written in Java that accesses the database occurs at this level.True/False on database approaches. [6 pts]__F__ The relational model was the earliest major database approach.__T__ The hierarchical model represents the database schema as a tree.__F__ A object oriented model database is most closely related to mathematical set theory.__T__ An object oriented database model has similarities to a network database model.__T__ A relational database in early days were considered too inefficient for practical use.__T__ The relational database is expected to remain the primary approach in foreseeable future.Define each of these concepts [12 pts]Database transaction:A transaction is a logical sequence of database operations that need to be considered as a unit; all or nothing. That is, if there’s failure the transaction must be as if it never occurred.Cardinality of a relationship:Cardinality of a relationship is how many entities of one is related to entities of another (one-many or many-many),Weak entity:Weak entities rely on existence of an entity in the relationship. True/false on functional dependency theory.[10 pts]__T__ 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.__F__ Functional dependencies can be determined by algorithms to analyze the relation’s attributes.__F__ Functional dependencies are reflexive, i.e., if a →b then b →a__F__ Transitivity of functional dependencies means that if a →b and c→b then a→c.__T__ It’s ok to rewrite a→bc as a→b and a→c, and vice versa.__F__ 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.__F__ Multi-valued attributes are permitted in a normalized relational database.__T__ First normal form (1NF) prevents nulls to be stored in database tables.__F__ A null value is equivalent to the empty string in the case of a non-numeric type attribute.__F__ 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? ________PhysicianID____________[2]List all non-trivial functional dependencies [8]?HospID HospName ,HospAddr, HospCity,PhysicianID HospID ,HospName ,HospAddr, HospCity, PhysicianName, PhysicianSalaryIf this relation were used as defined (not normalized), describe an update anomaly that could arise. [4]No hospital information can be stored without a physician.Any changes made to the hospital address would have to be replicated.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]PRESIDENTS (Pres, Spouse, State, Party, BirthDate, DeathDate)ELECTIONS (Year, Pres, TotalVotes))ELECTIONLOSERS(Year, Name, Party) PARTYHISTORIES(Party, YearStart)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.17780001524001968500152400177800076200139700076200PEOPLE (PID, name, address, zip, phone)1397000118110CSZ (zip, city, state)1905000838201778000160020TRACKS (trID, title, length, genre, rating, albID) //trID is unique across all albums1905000125730ALBUMS (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? ___{albID} {albumTitle, year} {albumTitle,labelID} ________________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.name, phone(zip=90210(PEOPLE))List album titles and labelIDs with a list price of more than $18.albumTitle, labelID(price>18(ALBUMS))List all the contributor names and their role on all jazz type tracks.name, role(genre=?‘jazz’(TRACKS |X| trID=trID CONTRIBS |X| PID=?PID PEOPLE))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)name(((prodPID ALBUMS)[PID] (engrPID ALBUMS)[PID]) ∩ PID CONTRIB) |X| PID=?PID PEOPLE)name(((prodPID=PID or engrPID=PID ALBUMS) |X| CONTRIB) |X| PID=?PID PEOPLE) 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)name, role(genre=’pop’ and role <>role2(CONTRIBS |X| trID=trID and PID=PID CONTRIBS[trID, PID, role2] ) |X| PID=?PID PEOPLE)) ................
................

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

Google Online Preview   Download