- Information
- AI Chat
Databases - Practical 3 memo
Computer Science (ComSci1)
Rhodes University
Recommended for you
Preview text
Computer Science 202 : Databases
Practical 3 : Normalisation
Work towards the practical must be submitted by Wednesday the 16th of October at 12:00pm on the
Practical3 submission link on RUConnected. This practical is on normalisation of databases. Show the
normalisation steps for each of the examples provided below and how you arrive at the conclusion on
what normal form the structure is at, up to a maximum of the Third Normal Form. The answers should
be provided for marking as a Word document.
The practical is marked out of 100.
Question 1 : [2 0 Marks]
In which normal form is the following structure and why have you arrived at the conclusion reached?
If, for example, you were to arrive at a conclusion that it is in 2NF, show why it cannot be in 3NF.
Show the working through showing the different tables. The example is on a database structure for
Book Orders, where a customer orders one or more books
*Order Number Customer name Order date **ISBN Book title Book author Quantity Price Order total
Candidate keys: (ISBN, Order Number)
Prime attributes: Book title, Book author, Price, Customer Name, Order Date, Quantity
Non-prime attributes: Order Total
First Normal Form
*Order Number Customer Name Order Date **ISBN Order Total
* ISBN
Book title Book author Quantity Price
The database violates 2NF as the column OrderTotal is partially dependent on both Price and Quantity columns. Price is a prime attribute of the candidate keys. Quantity is a non-prime attribute. Partial dependency occurs if a non-prime attribute is a proper subset of a candidate key. Therefore the 2NF is violated due to the existence of partial dependency.
The table is in First Normal Form, because there are no repeating columns; there are unique rows
and each field only contains one item
Question 2 : [ 20 marks]
In which normal form is the following structure and why have you arrived at the conclusion reached?
If, for example, you were to arrive at a conclusion that it is in 2NF, show why it cannot be in 3NF.
Show the working through showing the different tables. The example is on a database structure for a
car of a certain make and model.
Maximum speed Price
The criteria for 2NF are met: the table is in 1NF and no non-prime attribute dependent on a proper
subset of any candidate key, i. all non-prime attributes are fully dependent on the primary key. The
table is about a single entity.
The criteria for 3NF are not met: A table is in 3NF if it is in 2NF and has no transitive dependencies.
However, there is transitive dependencies in the database structure of the car, as follows.
Model-> Market
Market-> Main competitor
Model -> Main competitor
Therefore the table is in 2NF.
Question 3 : [ 20 marks]
In which normal form is the following structure and why have you arrived at the conclusion reached?
If, for example, you were to arrive at a conclusion that it is in 2NF, show why it cannot be in 3NF.
Show the working through showing the different tables. The example is on a database structure for
renting movies.
*Membership ID Full Name Salutation Address Movies Rented
Primary key: Membership ID
Candidate key (Membership ID, Full Name), (Membership ID)
Prime attributes: Address, Movies rented
Non-prime attributes: Salutation
*Membership ID Full Name Address Salutation
*Membership ID Movies Rented
The table is in 1NF because there are no repeating columns. There are unique rows and each field
only contains one item. However, this table is not in 2NF yet because there are partial dependencies
within the table. Movies rented is only partially dependent on the Membership ID. To get to 2NF,
this table will have to be separated into two: one for members and one for movies. The table is not
in 3NF as: it is not in 2NF, there are transitive functional dependencies of non-prime attributes on
the primary key, e. Membership ID -> Movies Rented
Membership ID-> Full Name
Membership ID -> Address
Membership ID -> Movies Rented
Question 4 : [ 20 marks]
In which normal form is the following structure and why have you arrived at the conclusion reached?
If, for example, you were to arrive at a conclusion that it is in 2NF, show why it cannot be in 3NF.
Show the working through showing the different tables. The example is on a database structure for
Sales Staff and their customers.
Employee ID Sales Person Name Sales Office Office Number Customer Customer ID Customer Name
The database qualifies as 1NF as every attribute in the relation is a single valued attribute and there
are no multi-valued attributes.
Primary key: Employee ID
Candidate Key(s): Employee ID, Customer ID
Prime attributes: Sales person, Name, sales Office, Office Number, Customer Name
*Employee ID Sales person Name Sales Office Office Number
*Customer ID *Employee ID Customer Name
Employee ID Customer ID
Employee ID -> (sales Person, Name, Sales Office, Office Number)
Primary key: cheque number Foreign key: Organisation ID Candidate key: Cheque Number Non-prime attributes: Payment date, Payment amount, Balance Outstanding, Number of Orders to Date
Cheque Number Payment Date Payment Amount Balance Outstanding Number of Orders to Date Organisation ID
The table is in Third Normal form because it is in 2NF, i. there are no partial dependencies
assuming that the data is separated into three tables (organisation table, invoice table and payment
table) and there are no transitive functional dependencies, i. there are no non-prime fields that
have to change if another non-prime field changes.
Functional dependencies:
Organisation ID -> (Billing address, Date account opened)
Invoice number -> (invoice date, Invoice amount, organisation ID)
Cheque Number -> (Payment Date, Payment Amount, Balance outstanding, Number of Orders to
date, Organisation ID)
There are no partial dependencies or transitive dependencies and the database is in 3NF
Databases - Practical 3 memo
Course: Computer Science (ComSci1)
University: Rhodes University
- Discover more from:
