CS 370/IT 376 Fall 2005 Exam 1 Page 1 Database Management Systems 10/3/05 Name__________________________ 1. 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 (0, 1, or many) [12 pts] a. Conceptual level—(schema count = ____) b. External level—(schema count = ____) c. Physical (internal) level—(schema count = ____) 2. List for each of these database users which of the three schemas or levels they would interact with in a database. There may be more than one level used by a user. [6 pts] DBA: Naïve user: Applications programmer: 3. Definitions. [12 pts] Data dictionary: Data-program independence: Integrity constraint: Multivalued attribute: CS 370/IT 376 Fall 2005 Exam 1 Page 2 4. True/false on functional dependency theory. [10 pts] ______ Functional dependency analysis aids in the normalization of a relational schema ______ Normalization attempts to reduce redundant facts stored in a database. ______ Redundancy is at the root of insertion and deletion anomalies in a relational database. ______ A null value has exactly one interpretation. ______ A functional dependency is a relationship between an attribute "Y" and a determinant (1 or more other attributes) such that for a given value of a determinant the value of the attribute Y is uniquely defined. ______ Functional dependencies can be ascertained algorithmically (automatically) by analysis of the relation. ______ In practical terms, we want non-key attributes to be functionally dependent on the primary key in any relation. ______ Transitivity of functional dependencies means that if a →b and a→c then c→b. ______ Decomposition of functional dependencies mean that if a→bc then a→b and a→c ______ Functional dependencies are reflexive, i.e., if a →b then b →a For the remaining questions, use the following relational schema for a professional baseball team database. Keys are (mostly) underlined. Example, albeit inaccurate, data are given. The attributes should be self-evident. If not, please ask for clarification. DIVISION(DivID, League, Area, StartYear) (3,‟American‟,East,1995) TEAM(TName, DivID, OwnerID, City, Stadium) („Yankees‟,3,666, „New York‟, „Yankee‟) OWNER(ID, Name, Address) (666, „Steinbrenner‟, NULL) PLAYER(ID, Name, Address, Salary, Position, DOB) (2833,‟Randy Johnson‟, NULL, 2000000, „Pitcher‟,”1963-09-10”) PLAYSFOR(ID, TName, StartDate, ReleaseDate,Number) (2833,‟Yankees‟,”1995-01-01”, NULL, 41) STAFF(ID, Name, Address, Salary, DOB) (399, “Joe Torre”, NULL, 1500000, „1941-03-02‟) STAFF_FOR(ID, Tname, StartDate, ReleaseDate, Role) (399, „Yankees‟, „1994-01-01‟, NULL, „Manager‟) GAME(Date, HomeTeam, VisitingTeam, HomeScore, VisitorScore, InningsPlayed) („2005-10-01‟,‟Red Sox‟, „Yankees‟, 4,8,9) 5. Underline the appropriate attribute(s) of the primary key for the relation GAME. [3 pts] 6. List the non-trivial functional dependencies in relation TEAM that you think exist. [7 pts] CS 370/IT 376 Fall 2005 Exam 1 Page 3 Carefully monitor your time for these last two pages!! 7. Draw an E-R diagram for this baseball schema. Be sure to identify multivalued attributes (if any) and the cardinality of each relationship (1:n, m:n). Be sure to carefully consider table(s) that really represent relationships. Be sure to identify the ISA ‘relationship’ in this schema. [25 pts] CS 370/IT 376 Fall 2005 Exam 1 Page 4 8. Give Relational Algebra statements for the following queries on the Baseball database schema. [25 pts] Syntax reminder for Relational Algebra expressions: condition(relation) attribute-list(relation) relation1 OP relation2, where OP is , , - , , , and ||cond new-rel(attr) R.A. expression a) List all players who currently earn more than a million dollars. b) List all the pitchers’ names who currently play for teams in New York City. Assume you do not know which teams they are to construct the query. Hint: there are three relations involved. c) List managers and coaches who were never players. d) List all the teams that played the Pirates during interleague play this year (the teams were from the other league). e) List all personnel names and addresses currently associated with the Braves.
Pages to are hidden for
"Database Management Systems - Download as DOC"Please download to view full document