DTA(M) Database Theory & Applications
Lab 2 Tasks
Task 1: Mapping from E-R Diagram to Relational Schema
Consider the ‘Dog’ conceptual schema from Lab 1 and the enhanced E-R diagram as shown in Figure
1: there are some new attributes in certain entities and some new entities such that:
• Owner (Entity) of a dog has a name (Attribute), phone (Attribute) and a unique ownerid
(Attribute). An owner can have many dogs.
• Dog (Entity) has a unique dogid (Attribute), name (Attribute), a unique owner (associated
with the Owner entity via the attribute ownerid), a breedname (associated with the Breed),
a kennel (associated with the Kennel entity via the kennelname attribute), a mother-name
(Attribute) and a father-name (Attribute). A dog belongs only to one owner, has one
breedname, and has one kennel.
• Breed (Entity) has a unique breedname (Attribute).
• Kennel (Entity) has a unique kennelname (Attribute), address (Attribute), and phone
(Attribute).
• Show (Entity) has a showname (Attribute), an open-date (Attribute), and close-date
(Attribute). The show is uniquely identified by the combination of showname and opendate,
i.e, the pair of attributes: (showname, opendate) uniquely identifies a show.
• Attendance (Entity) associates a dog (via the dogid attribute) with a show (via the pair of
attributes: showname and opendate) and a place (Attribute denoting e.g., dog’s
performance ranking).
Note: the unique attributes are underlined in the enhanced E-R diagram illustrated below:
DTA(M) Database Theory & Applications
DogOwner
Show
Kennel
Breed
Name Phone Name
Close
Date
Open
Date
Show
Name
Place
owns
h
a
s
re
fe
rs
1 M
Attendance
1
N
M
1
OwnerID
DogID
Kennelname
Breedname
K
1
Phone
Address
1
L
Father
Name
Mother
Name
Figure 1. Enhanced E-R Diagram
Your task is to map the entities, attributes, and relationships from the E-R diagram in Figure 1 to
relations and attributes in a Relational Schema. Specifically:
1. Identify the relations and their attributes.
2. Assign attributes to candidate key(s) and define the primary key per relation.
3. Define the foreign keys, if exist, per relation.
4. Identify whether the foreign keys can be NULL or not.
Task 2: Mapping from Textual Description to Relational Schema
(Optional)
The UK academic world is an example of international cooperation and exchange. This problem is
concerned with modelling of a database schema that contains information on researchers, academic
institutions, and collaborations among researchers. A researcher can either be employed as a
professor or a lab assistant. There are three kinds of professors: lecturer, senior lecturer, and full
professor. The following information should be stored:
• For each institution, its name, country, and inauguration year.
• For each institution, the names of its schools, e.g., School of Law, School of Business, School
of Computing Science. A school belongs to exactly one institution.
DTA(M) Database Theory & Applications
• For each researcher, their name, year of birth, and current position (if any).
• An employment/position history, including information on all employments (start and end
date, position, and what school).
• Information about co-authorships, i.e., which researchers have co-authored a research
paper. The titles of common research papers should also be stored.
• For each researcher, information on his/her highest degree (BSc, MSc or PhD), including who
was the main supervisor (professor), and at what school.
• For each professor, information on what research projects (title, start date, and end date)
he/she is involved in, and the total amount of grant money for which he/she was the main
applicant.
Task: Given the above context, your task is to identify the following:
• Relations and for each identified relation, attach a set of attributes and their domains, e.g.,
real number, date, text.
• Identify the primary keys of each relation.
• Identify the relationships between relations and define the foreign keys.
• Define whether a foreign key can be NULL or not.
• Based on the relations and their corresponding relationships you have identified, how we
could query/ask the database to: retrieve all the researchers from the School of Computing
Science whose highest degree is PhD and were supervised by the same supervisor. Plan
your query by determining which relations, attributes and relationships to involve.
Task 3: Relational Constraints & Reverse Engineering
Task 3.1: Consider the following relational schema:
EMPLOYEE(EID, ENAME, AGE, SALARY)
WORKS(EID, DID, HOURS)
DEPARTMENT(DID, DNAME, MANAGERID)
such that the relation WORKS associates an employee (EID) with a department (DID). Give the
referential constraints involving Employee and the Department relations, in other words, define the
foreign keys in the schema.
Task 3.2: Assume that a database user attempts to delete a Department tuple (a department with a
specific DID) from the schema in Task 3.1. Which tuples and relations are affected by this deletion?
Task 3.3: Given the E-R diagram in Figure 2, define the corresponding relational schema along with
the corresponding PKs and FKs.
DTA(M) Database Theory & Applications
Customer Loan
Borrower
LoadID AmountCustomerID Name
LoanAccessDate
1
M N
1
Figure 2. ER Diagram (Loan/Customer)
Task 3.4: Given the E-R diagram in Figure 3, define the corresponding relational schema along with
the corresponding PKs and FKs. Note: an employee playing the role of a worker ‘works for’ one (1)
line manager. An employee playing the role of a line manager has many (M) workers.
Employee
EmployeeID Name
M
Works For
1
worker
line manager
U
Figure 3. ER Diagram (Employee/Line Manager)
Task 3.5: Consider the instance of the Instructor relation shown in Figure 4: no two instructors have
the same name. From this, can we conclude that the attribute name can be used as a superkey (or
primary key) of the relation instructor?
DTA(M) Database Theory & Applications
Figure 4. Instructor Relation
Task 3.6: A relational schema for movies and casts is given below. The primary key for each relation
is underlined. The foreign keys are denoted with an ‘*’. The attributes title and year in Person
indicate a person’s favourite movie. The attribute job in Person takes one of the values: ‘actor’,
‘director’. Only actors can act in movies (participating in a movie cast). A movie is produced by a
studio. A person (actor or director) is employed by only one studio. Your task is to reverse engineer
this relational schema into an E-R diagram that reflects the same semantics of this description.
• Person(ssn, name, gender, job, studio*, title*, year*)
FK is studio references to Studio
FK is the pair (title, year) references to Movie
• Movie(title, year, length, director*, studio*, category)
FK is director references to Person (playing the role of a director)
FK is studio references to Studio
• Cast(ssn*, title*, year*, character)
FK is ssn references to Person (playing the role of an actor)
FK is the pair (title, year) references to Movie
• Studio(studio, city)