Database Management Systems - Download as DOC by vxs15442


More Info
									                            CS 370/IT 376 Fall 2005 Exam 1                                Page 1
                            Database Management Systems
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]

   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
        ______ 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)
                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:
            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

   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.

To top