DTA(M) Database Theory & Applications
Lab 4 Tasks
Task 1: Normalization to 3NF
An engineering company supplies temporary specialized staff to bigger companies in the UK to work
on their projects for certain amount of time. The relation R in Table 1 below lists the time spent by
each of the company’s employees at other companies to carry out their projects. The National
Insurance Number (NIN) is unique for every member of staff.
NIN ContractNo Hours EmployeeName CompanyID CompanyLocation
616681B SC1025 72 P. White SC115 Belfast
674315A SC1025 48 R. Press SC115 Belfast
323113B SC1026 24 P. Smith SC23 Glasgow
616681B SC1026 24 P. White SC23 Glasgow
Table 1: Relation R
Your tasks are:
1. Identify the highest Normal Form (NF) of the relation R.
2. Find the Primary Key (PK) for this relation and explain your choice.
3. Find the Fully Functional Dependencies (FDs) on the PK and the Partial Dependencies on the
PK.
4. Normalise the relation R to 2NF (if not being in 2NF).
5. Find the transitive FDs on the derived 2NF relations.
6. Normalise the relational schema to 3NF.
7. Draw or describe the relational schema and show the PKs and FKs in all the relations after
normalization to 3NF.
Task 2: Normalization to BCNF
Consider the following relation:
ARTICLES(ID, title, journal, issue, year, startpage, endpage, TR-ID)
The relation contains information on articles published in scientific journals. Each article has a
unique ID, a title, and information on where we can find it: name of the journal, issue of the journal,
and in which pages in the journal. Also, if there are results of an article appeared as a “technical
report” (TR), then the ID of this technical report should be specified by the TR-ID attribute. We have
the following information on the attributes:
For each journal, a journal issue with a specific number is published in a single year.
The endpage of an article is never smaller than the startpage.
There is never (part of) more than one article on a single page.
DTA(M) Database Theory & Applications
An instance of the relation ARTICLES is as follows:
Table 2: ARTICLES
Task 2.1: Indicate for each of the following sets of attributes, whether it can be a candidate key or
not.
1. {ID}
2. {ID, TR-ID}
3. {ID, title, TR-ID}
4. {title}
5. {title, year}
6. {startpage, journal, issue}
Task 2.2: Indicate for each of the following functional dependencies, whether it holds true or not.
1. ID title;
2. startpage endpage;
3. {journal, issue} year
4. title ID;
5. ID {startpage, endpage, journal, issue}
6. TR-ID ID
Task 2.3: Based on the Task 2.1 and Task 2.2, perform normalization of the ARTICLES into BCNF.