CSE 444 Homework 1
- 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,
- Number of points:
- 100 points
- Due date:
- October 8
- [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:
The three of these together constitute a Driver's Test.
- Create an E/R diagram to model this situation.
- Create an ODL model to model this situation.
- [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.
- [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
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
- Build an E/R diagram with the following:
- Quarter (i.e., Spring 1999)
- 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.
- 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
- From our design (and your knowledge of the
department), Person, Office, and Class have keys. Label them on the
- Now model the following relationships:
- Grads have Offices
- Profs also have Offices
- Grads TA Classes
- Profs have ResearchAreas
- Grads have Advisors, and Profs have advisees
- Undergrads take Classes
- Each Prof has exactly one office; label this on the diagram
- Each Grad has only one Office, but each Office can
have more than one GraduateStudent; label this on the graph.
- The department doesn't have the records for students
who entered before 1989. Record this fact on the diagram.
Now answer the following questions about the E/R diagram.
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.
- 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).
- [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.
- Create an E/R diagram for them. Your diagram should have at
least one of each of the following ideas represented
- Many to one relationships or one to one relationships
- Many to many relationships
- Single value constraints
- Referential integrity constraints
- A weak entity set; note you cannot do this by
assuming that an external database will access your database in some
- 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 points] Please answer the following questions
- How long did it take you to complete this assignment?
- What did you like the best about this assignment?
- What did you like the least about this assignment?
- What helped you learn the best in this assignment?
- What distracted from your learning in this assignment?