B561 Assignment Relational Databases and Basic SQL

B561 Assignment 1 Solution
Dirk Van Gucht & Mo Zhou
September 26, 2008
Entity Relationship Diagrams
1. Model the entities and relationships (including attributes and proper-
ties of relationships) described below in an ER-diagram.1
Write down any assumptions you make.
Zoo The LMF Zoo has many types ofanimals. Every type has
a unique name.Every animalof the same type has a unique
animal ID. Animals in two types may have the same animal ID.
Animals also have age and gender.Animals may have diseases.
The beginning time and the duration ofa disease need to be
recorded.A disease has a unique name.A type keeper takes
care ofonly one type ofanimals. Every type may have many
type keepers.A type keeper may or may not be familiar with
diseases.But every disease must be handled by at least one type
keeper.Type keepers have name, employee ID, ssn, address and
phone number.
All animals are in cages.Some cage may be empty.Every cage
has a cage ID, space and height.A cage keeper may take care of
many cages.Every non-empty cage must have at least one cage
keeper.Empty cages don’t need any cage keepers.Cage keepers
have name, employee ID, ssn, address and phone number.
Solution:
check points:
(a) Animal is a weak entity.
1Note that there are severaltools for drawing ER-diagrams, for exampledia:
http://www.gnome.org/projects/dia/.
1

(b) Use ISA
(c) Type keepers take care of only one type.It is a function and total
participate.
(d) All non-empty cages must have at least one cage keeper.It is a
total participate.
2

2. Exercise 2.2 in the text book.
Solution:Assume that semesterID is unique for every semester.Oth-
erwise you need another entity called semester and make the teach a
ternary relation.If you assume that each course can only have one
professor, you need add an arrow from course to teach.
3

Professor courseteach
semesterIDSSN CID
1.
Professor courseteach
SSN CID
2
Professor courseteach
SSN CID
3
Professor courseteach
SSN CID
Professor courseteach
SSN CID
4
5
Professor courseteach
SSN CID
Group
belong
Groupteach
6
4