Lab 10
Statement Purpose:
The goal of relational database design is to generate a set of relational schemas that allows us to
store information without unnecessary redundancy, yet also allows us to retrieve information
easily. This is accomplished by designing schemas that are in an appropriate normal form.
Activity Outcomes:
After performing this lab students should be able to:
1. Identify functional dependencies in a relation.
2. Identify if a relation is in 3NF or not.
3. Decompose a relation to achieve 3NF.
4. Create database schema from the design of relations.
Instructor Note:
As pre-lab activity, attend theory class.
Introduction
The goal of relational database design is to generate a set of relational schemas that allows us to
store information without unnecessary redundancy, yet also allows us to retrieve information
easily. This is accomplished by designing schemas that are in an appropriate normal form.
In Lab:
Activity 1:
Carefully, study and analyze the following data table and perform the tasks
mentioned below.
1. Convert this data to 1NF,2NF and 3NF.
Activity 2:
1. Identify the information items that should be recorded in a
database. Outcome of this task is a list of attributes. Make sure
that attribute domains are atomic.
2. Assuming the list of attributes as a single large
relation, identify functional dependencies in the
relation and also find closure set.
3. Based on the studied rules for lossless decomposition, translate
the large relation into 3NF.
4. Use DDL to create the relation schema in MySQL. Choose an
appropriate domain for each attribute and an appropriate
primary key (and foreign key) for each relation.
5. Use DML to record all necessary information from the
given sheet into the newly created database.
Deliverable
Submit a PDF or plain-text document including the SQL queries to
implement the database.
