CSE 444 Homework 1

Objectives:
To understand and be able to manipulate ODL definitions and E/R diagrams
Reading Assignments:
2.1 - 2.3, 2.4.3, 2.4.4, 2.5.3-2.5.7, 2.6
Number of points:
100 points
Due date:
October 8
  1. [10 points] The Washington State Department of Licensing wants to create a database of people who have taken their driver's license test while they possessed a learner's permit. There are three entities that you need to consider, with the following attributes:
    Exam
    Date
    Passed
    Examiner
    Badge#
    Name
    Applicant
    Learner'sPermit#
    Name
    The three of these together constitute a Driver's Test.
    1. Create an E/R diagram to model this situation.
    2. Create an ODL model to model this situation.
  2. [10 points] Give an example of a relation that would be more difficult to model in an ODL description than in an E/R diagram, and justify your answer.
  3. [40 points] The CSE department has decided that they need a new set of databases to keep track of their data. Ignore the rest of the university (and the world) in creating this database. They need to have a database that will allow them to keep track of the following entities: and many of the relationships and attributes there in. In this problem you will map out an entity relationship diagram for the department. You should only turn in one diagram that has all of the requirements in parts a through i; make sure you leave enough room. ;)

    1. Build an E/R diagram with the following:
      Person
      LastName
      FirstName
      LoginID
      URL
      Office
      PhoneNumber
      Location
      ResearchArea
      Name
      Class
      Quarter (i.e., Spring 1999)
      Room
      Time
      Title
      Number
    2. The department wants to be able to have finer separation over the members of the department. Create a two subclasses for Person; Prof and Student. Students have a year that they enrolled in the department.
    3. There can be further division among the students; subclass Student into Grad and Undergrad. Graduate students also have an undergraduate institution as an attribute; add this into your diagram.
    4. From our design (and your knowledge of the department), Person, Office, and Class have keys. Label them on the diagram.
    5. Now model the following relationships:
      1. Grads have Offices
      2. Profs also have Offices
      3. Grads TA Classes
      4. Profs have ResearchAreas
      5. Grads have Advisors, and Profs have advisees
      6. Undergrads take Classes
    6. Each Prof has exactly one office; label this on the diagram
    7. Each Grad has only one Office, but each Office can have more than one GraduateStudent; label this on the graph.
    8. The department doesn't have the records for students who entered before 1989. Record this fact on the diagram.
    9. [deleted]
    10. Now answer the following questions about the E/R diagram.
      1. In part e.4 you modelled that profs have research areas. Instead of having an entity for ResearchArea, we could just model it as an attribute of the relation in e.4. Give one reason why this is not a good idea.
      2. Suppose the rest of the university wants to access the department database. At least two of the entities in our diagram are now no longer have valid keys. Which ones are they, what information would the university need to give in order to provide a key? How would you represent the weak entity sets in the E/R diagram (be specific)? Depending on your choice of keys and your interpretation of the data stored in the Location attribute for Office, it may or may not be a valid key; is yours? Why or why not? (note, if for you Office had an invalid key, you need to find *three* invalid keys).
  4. [35 points] The University district chamber of commerce has come to you requesting that you design them a database. In their database they want to have information on businesses. They are particularly interested in producing a restaurant guide complete with information on how much dishes cost, what dishes they serve, what kind of cuisine they serve, where they are located, etc. You will be graded on how extensible your design is, how closely it patterns the real world, how complete it is, and how much you have avoided redundancy. If you make any assumptions, you must state them; you should turn in one E/R diagram for this problem.
    1. Create an E/R diagram for them. Your diagram should have at least one of each of the following ideas represented
      1. Inheritance
      2. Many to one relationships or one to one relationships
      3. Many to many relationships
      4. Keys
      5. Single value constraints
      6. Referential integrity constraints
      7. A weak entity set; note you cannot do this by assuming that an external database will access your database in some manner.
    2. The Chamber of Commerce has decided that it would also like to model bookstores. Is your model capable of being extended without much replication? If not, please redesign your model so that it is. Now add bookstores to your E/R diagram (with at least three attributes).
  5. [5 points] Please answer the following questions
    1. How long did it take you to complete this assignment?
    2. What did you like the best about this assignment?
    3. What did you like the least about this assignment?
    4. What helped you learn the best in this assignment?
    5. What distracted from your learning in this assignment?