CS145 Assignment #2

Due Wednesday, Oct 15, 1997

Reminder for on-campus students: Assignments are due at 5:00 PM on Wednesday. You are allowed one lateness of up to 48 hours; use that privilege carefully. Assignments should be turned in during class or at the course secretary's office: Gates Building room 495.

Reminder for SITN students: Assignments due on Wednesday must be timestamped by the Thursday morning courier to be considered on-time. Like the on-campus students, you have one free 48-hour exception.

Step 2 of Your PDA (Personal Database Application)

(a)

(15 pts.) Consider the ODL schema you designed for your PDA in Assignment #1. Please attach a copy of the schema. Use the method described in the text and in class for translating an ODL schema to relations to produce a set of relations for your database design. Please specify your relational schema using notation of Section 3.1.2, and underline key attributes. In cases where there are alternative mappings for ODL constructs (such as for sets and relationships), you may use whichever mapping you prefer.

(b)

(15 pts.) Consider the Entity-Relationship diagram you designed for your PDA in Assignment #1. Please attach a copy of the E/R diagram. Use the method for translating an E/R diagram to relations described in class and the text to produce a set of relations for your database design. Again, specify your relational schema using the notation of Section 3.1.2, and please be sure to underline key attributes.

(c)

(10 pts.) Which of the two relational schemas you obtained in parts (a) and (b) of this problem do you like better? Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer. You will be working with this schema quite a bit, so it's worth spending some time to make sure you're happy with it.

(d)

Login to Oracle, using your Oracle account, which should be set up shortly after Oct. 6. Use the Oracle Introductory Guide to see how to login and change your password. Try some simple commands, such as help or table (relation) creation. No credit will be given, but it is important that people try logging in as soon as there is a good chance your login will be recognized. We'll need time to handle login problems such as someone who thinks they are registered for the class and isn't.

Don't forget to save a copy of your PDA for reference as you do Step 3 of the PDA.

Problem Set

1. (6 pts.) This question is based on Exercise 2.5.3 in the text [p. 71]. In particular, the notion of ``smallest key'' is defined there. Consider a three way relation R

            E1 <----- R ----- E2
                      |
                      |
                      |
                      |
                     E3

where Key for Ei is Ki. Give the smallest posiible key for R

2. (6 pts.) Suppose there are three entity sets E, E1 and E2, and there are many-one relationships from E1 to E and from E to E2. Prove that there exists a many-one relationship from E1 to E2. Recall that a many-one relationship means there is ``at most one,'' not that there is ``exactly one.''

3. (8 pts.) We would like to design a database to maintain some information about books stocked in various libraries. The information includes:
   • Books: with their title and author
   • Libraries: with their address
   • Stocks: including the catalog number (which is unique to a particular library), the name of the book , and the library where they are stored

   (a)

   Give the E/R diagram for this design. Identify the keys and the weak sets and relationships

   (b)

   Now we need to add the entity set Issue_records to our database which include the name of the person to whom the book is issued, issue_date, due_date and the return_date (this attribute might be empty for books not returned as yet). Modify the E/R diagram to incorporate these changes.

   • (16 pts.) Our next task is to design a database for students and courses (again). The information includes

   Students with their name and student id, department, their advisor, and the courses in which enrolled

   Courses with the name, department, instructor and the TAs

   Faculty with the name and department and

   Department with the name and the building where it is located.

   a) Implement the above in the Network model

   b) Implement in the Heirarchical model. You should implement the design from scratch. No credit will be given to designs derived from the corresponding E/R model.
   
   

   • (8 pts.) Exercise 3.1.2(c) [p. 90].
     
     
   • (16 pts.) Now we consider a database for soccer players, where a player plays for his country and for various clubs. Club name is unique in a country, and changing of nationality isn't allowed. The E/R diagram is:
     
     
     
     The ODL model is

       interface Player {
           attribute string Name;
           attribute integer Age;
           relationship Country belongs
                   inverse Country::CountryPlayer;
           relationship Set<Club> PlaysIn
                   inverse Club::ClubPlayer;
       }
       
       interface Club {
           attribute string Name;
           attribute string President;
           relationship Country ClubIn
                   inverse Country::CountryClub;
           relationship Set<Player> ClubPlayer
                   inverse Player::PlaysIn;
       }
   
   
       interface Country {
           attribute string Name;
           relationship Set<Club> CountryClub
                   inverse Club::ClubIn;
           relationship Set<Player> CountryPlayer
                   inverse Player::belongs;
           relationship Confederation partof
                   inverse Confederation::Countries;
       }
   
       interface Confederation {
           attribute string Name;
           attribute string President;
           relationship Set<Country> Countries
                   inverse Country::partof;
       }
   
       

   Derive relational database schemas for the database from (a) the E/R diagram and (b) the ODL model.