DTA(M) Database Theory & Applications
Lab 4 Tasks: Solutions
Task 1 Solution
Tasks: 1.1 & 1.2
The table (relation) is at least in 1NF. To check for 2NF, the primary key needs to be defined.
Some attributes to be included in a PK are NIN, ContractNo and CompanyID. We choose NIN
and ContractNo as the prime attributes of the composite PK because these two will be the
minimum attributes required to uniquely identify each row in the table. Hence, we obtain
(renaming the relation R to StaffContract):
StaffContract (NIN, ContractNo, Hours, EmployeeName, CompanyID, CompanyLocation)
Given the PK: {NIN, ContractNo}, each non-prime attribute needs to be checked for having a
full functional dependency on the PK. Employee Name is fully functionally dependent on
NIN (NIN → Employee Name). But, as NIN is part of the PK, the Employee Name is not fully
functionally dependent on the PK therefore the relation is not in 2NF.
Task : 1.3
We need to find dependencies on the PK and on parts of the PK (partial FDs). By examining
each of the non-prime attributes, we determine what type of dependency is associated with
the PK.
Hours: This is the main non-prime attribute that needs to be recorded–it is fully functionally
dependent on the employee and the associated contract, i.e., the whole PK. The
corresponding FD is then: {NIN, ContractNo} → Hours
Employee Name: As stated in Task 1.2, this is fully functionally dependent on NIN, but not
on the ContractNo, therefore, there exists a partial dependency, which is: NIN →
EmployeeName (partial dependency on the PK).
Company ID and Company Location: These non-rime attributes are dependent on the
ContractNo, but are not related to NIN, therefore, they are partially dependent on the PK,
with FD: ContractNo → {CompanyID, CompanyLocation} (partial dependency on the PK).
Task: 1. 4
To normalise the relation to 2NF, we need to remove all the partial dependencies from the
relation and relocate them to new relations. Hence, we obtain:
DTA(M) Database Theory & Applications
StaffContract(NIN*, ContractNo*, Hours)
StaffDetails(NIN, EmployeeName)
ContractDetails (ContractNo, CompanyID, CompanyLocation)
Note: Primary Keys are underlined and Foreign Keys are marked with an (*).
Task: 5
To find the transitive FDs, we need to look at the non-prime attributes for each of the
derived relations from Task 4. In this case the non-prime attribute location of the company
(CompanyLocation) is functionally dependent on the CompanyID (non-prime attribute);
therefore, it is transitively dependent on the PK (ContractNo) in the ContractDetails relation
via the following FDs:
ContractNo → CompanyID
CompanyID → CompanyLocation
Task: 6
To normalise the relational schema in Task 4 to 3NF, the transitive dependency in Task 5
needs to be transferred in a new relation with a copy of the determinant. That is, the
ContractDetails table is split in two relations:
Contracts(ContractNo, CompanyID*)
Company (CompanyID, CompanyLocation)
And the previous tables in Task 4’s relational schema are left the way they are because they
are already in 3NF. Hence, the relational schema in 3NF is:
Staff Details(NIN, EmployeeName)
StaffContract (NIN*, ContractNo*, hours)
Contracts(ContractNo, CompanyID*)
Company(CompanyID, CompanyLocation)
Task: 7
We have now the relations:
StaffDetails {NIN, EmployeeName}
Primary Key: NIN
Foreign Key: None
Staff Contract {NIN*, ContractNo*, Hours}
DTA(M) Database Theory & Applications
Primary Key: NIN, ContractNo
Foreign Key: NIN references StaffDetails(NIN)
Foreign Key: ContractNo references Contracts(ContractNo)
Contracts {ContractNo, CompanyID*}
Primary Key: ContractNo
Foreign Key: CompanyID references Company(CompanyID)
Company {CompanyID, CompanyLocation}
Primary Key: CompanyID
Foreign Key: None
Task 2 Solution
Task 2.1:
Task 2.2:
Task 2.3:
The PK is the {ID, TR-ID}. This determines all the attributes in the relation ARTICLES.
Now, the FD: ID→Title is a partial FD.
Moreover, the FD: {journal, issue} → Year is a FD, where the Left-Hand Side is not a (super
key) PK, thus, the relation is not in BCNF.
The FD: ID → {title, journal, issue, startpage, endpage} refers to a partial FD to the PK.
To normalize the relation in BCNF, first, we need to transform it to at least in the 2NF (it is
already in the 1NF). Let’s see which the steps are:
DTA(M) Database Theory & Applications
Focus on the partial FDs:
• ID→ title
• ID → {title, journal, issue, startpage, endpage}
We need to define a separate relation such that the ID prime-attribute will be the new
prime attribute in the new relation that fully functionally determines the attributes {title,
journal, issue, startpage, endpage}. Hence, we obtain:
• Articles1(ID, title, journal, issue, startpage, endpage)
And then, we obtain also the relation without any partial dependencies (since there are no
prime attributes at all )
• Articles2(ID, TR-ID)
Now, we must deal with the FD: {journal, issue} → year. In the original relation ARTICLES,
this FD violates the BCNF since the {journal, issue} is not a PK or any candidate key. Let us
apply the BCNF Theorem to deal with decomposing the original relation, thus, obtaining
potentially two relations in BCNF.
The violating FD: {journal, issue} → year leads the decomposition into two relations:
• R1: ARTICLES \ {year}, that is, keep all the attributes in ARTILES apart from the ‘year’
attribute, and
• R2: {journal, issue} U {year}, that is put in the relation R2 the attributes found in the
violating FD.
The relation R2 is then: R2(journal, issue, year) which is in BCNF (also in 2NF, and 3NF
trivially). The derived R1 is then:
R1(ID, TR-ID, title, journal, issue, startpage, endpage}
Based on our previous tasks, R1 is not in 2NF due to the partial dependency of ID → {title,
journal, issue, startpage, endpage}. Thus, obviously, R1 is not in the BCNF. However, we
have split R1 into our Articles1 and Articles2 relations to be in 2NF, 3NF and BCNF, trivially.
Hence, putting them up all together, we obtain the three BCNF relations:
• Articles1(ID, title, journal*, issue*, startpage, endpage)
• Articles2(ID*, TR-ID)
• R2(journal, issue, year)