BIT795 Assignment 2: Database Design and Data Normalization Solution

This document presents a comprehensive solution to BIT795 Assignment 2, focusing on relational database systems. The solution addresses various aspects of database design, starting with the application of relational operators such as UNION, INTERSECT, and DIFFERENCE. It then delves into the PRODUCT operator and extended entity-relationship models. The core of the solution revolves around data normalization, demonstrating functional dependencies, the creation of tables in 3NF, and the development of an entity-relationship diagram. The assignment continues with a conceptual entity-relationship diagram and a relational data model, including justifications for 3NF. Further, it explores data normalization, addressing 1NF and 2NF, and the elimination of transitive dependencies to achieve 3NF. The solution also covers functional dependencies, converting tables to 3NF, and handling repeating groups. The document then extends the database design to accommodate new requirements, including attribute definitions, primary key specifications, and indexes. Finally, it proposes changes to the database structure to support group reservations, illustrating the evolution of the database design to meet expanding business needs.

Question 1: Relational operators
a) Applying UNION operators
R1 UNION R2
The union results to the following results
Student Year if Study Lecturer
Mark 2 M. Taylor
Lorrain 3 K. Heel
Peter 3 J. Brown
Linda 1 S. Green
John 2 B. White
Lorrain 3 K. Heel
Linda 1 O. Roberts
Peter 1 S. Green
John 2 L. Young
Lorrain 3 L. Young
Peter 3 R. Fisher

The union of table results to the results shown in the table above which consists of all records of
R1 and all records of R2. Thus the result was achieved by adding up records of R2 to R1 but for
distinct values thus eliminating duplicates.
b) Intersect
Intersection of table R1 and R2 gets the rows of R1 and R2 that are identical. Based on the data
in table R1 and R2 intersection will result to the following data.
R1 INTERSECT R2.
Student Year if Study Lecturer
Mark 2 M. Taylor
Lorrain 3 K. Heel
Linda 1 S. Green
c) DIFFERENCE R1 and R2
 R1 DIFFERENCE R2
This operation will return all rows in R1 that are not in R2. Based on the data on the two
tables the following is the result of R1 DIFFERENCE R2.
Student Year if Study Lecturer
Peter 3 J. Brown
John 2 B. White
 R2 DIFFERENCE R1
This operation will return all rows of R2 that are not in R1. Based on the data on the
two tables R2 DIFFERENCE R1 will return the following results.
Student Year if Study Lecturer
John 2 B. White
Lorrain 3 K. Heel
Linda 1 O. Roberts
Peter 1 S. Green
John 2 L. Young
Lorrain 3 L. Young
Peter 3 R. Fisher
Question 2: The PRODUCT operator
PRODUCT operator returns all the attributes of table A followed by each attribute of table B thus each
record of table A pairs with each record of B. Considering table A and table B shown below

A PRODUCT B will result to;
A1 A2 A3 B1 B2 B3
5 7 8 2 4 5
5 7 8 1 6 9
5 7 8 7 2 8
4 3 9 2 4 5
4 3 9 1 6 9
4 3 9 7 2 8
Question 3: Extended entity relationship models

The EERD shown above was modelled based exactly on the requirements provided and no extended
assumptions were made.
Question 4: Data normalization
Based on this table;
A) Show functional dependencies.
Thhe following functional dependencies exist in the relation.
Item_Codeitem_description, building_ID, building_name, Room, Builiding_manager
Item_codedescription (transitive dependency)
Building_IDbuiliding_name,room, builidngManager
Item_code,RoomBuilidngID (partial dependency)
B) Set of tables in 3NF.
 Item (item_code, item_description, Room_No)
 Room (Room_NO,BuildingID)
 Building ( Building_ID, Building_name, Building_manager)
The entities above are in 3NF because all partial and transitive dependencies have been
eliminated. The relational schema below shows more details of each table.
Table Attributes Constraint
Item Item_code Primary key
Item_Description
Room_No Foreign key references Room
(Room_No)
Room Room_NO Primary key
Building_ID Foreign key references
Building (Building_ID)
Building Building_ID Primary key
Building_Name
Building_Manager

C) Entity relationship diagram
The ERD is modelled based on the following assumptions
 Each item is stored in one room
 A room can store multiple items
 A room exists in only one building
 A building can have many rooms
Colonial Adventure tours
Question 5: Conceptual entity-relationship diagram
a) ERD

b) Justifications
The ERD is modeled based on the following characteristics;
 A trip can have one or more guides. Its mandatory for a trip to have atleast one or more
guide
 A customer can make one or more reservations. Its mandatory for the customer to
make atleast one reservation
 A reservation is made for one and only one trip
Question 3: Relational data model
a) Relational schema
Table Attributes Constraint
Guide GuideNum Primary key
lastName
firstName
Address
city

State
PostalCode
PhoneNum
HireDate
Trip TripID Primary key
TripName
StartLocation
State
Distance
MaxGrpSize
Type
Season
Trip_Guide TripID Primary key,
Foreign key references Trip
(TripID)
GuideNum Primary key,
Foreign key references Guide
(GuideNum)
Reservation reservationID Primary key
TripID Foreign key references Trip
(TripID)
TripDate
NumPersons
TripPrice
OtherFees
CustomerNum Foreign key references
customer (CustomerNum)
b) Justification for 3NF.
The relation model is in 3NF because the following conditions hold;
 All tables are in 1NF because no table contains any repeating groups.
 All tables are in 2NF because no table contains any partial dependencies.
 All tables are in 3NF because no table contains any transitive dependencies thus every
table has key attribute that functionally determines all the other non-key attributes.
Question 7: Data normalization
First normal form but not in second normal form
Considering table trip_guides, the table can be added more attributes to demonstrate a table that is in
1NF and not in 2NF.
Trip_guides (TripID, GuideNum, hireDate)
Based on the table above, the following functional dependencies exist;
TripID,GuideNumHireDate

GuideNumHireDate (partial dependency)
This table is in 1NF because it does not contain any repeating groups but the relation is in not in 2NF
because it contains a partial dependency. The partial dependency exists because hireDate is dependent
on a part of the composite primary key and not the whole key thus it’s not in 2NF.
To normalize the table the partial dependency is eliminated leaving the table in 2NF. This will result to
the following tables
Trip_Guides( TripID, GuideNum)
Guide( GuideNum, HireDate
Second Normal Form and not in in third normal form
A relation is 3NF if its in 2NF and contains no transitive dependency thus considering the guide relation
and assuming that each trip has only one guide, the following relation is in 2NF.
Guide (guideNum, firstName, lastName,TripID, TripDate)
The following dependencies exist in the relation;
GuideNum,Firstname, lastName, TripID, TripDate
TripIDTripDate (transitive dependency)
To normalize the relation to 3NF, the transitive dependency is eliminated resulting to the following
tables;
Guide (GuideNum, firstName, lastName)
Trip (TripID, TripDate, GuideNum)
Question 8: Functional dependencies
Trip (TripID, TripName, stateAbbreviation, StateName, (GuideNum, GuideLastName, GuideFirstName)
(GuideNum, GuidelastName, GuideFirstName) is a repeating group.
a) Dependencies in the table;
TripIDTripName, stateAbbreviation, StateName, GuideNum, GuideLastName,
GuideFirstName)
GuideNumGuideLastName, GuideFirstName (transitive dependency)
b) Converting the table to 3NF.
To convert the table to 3NF the transitive dependency is eliminated thus resulting to the
following relations;
Trip (tripID, TripName, stateAbbreviation, StateName, GuideNum)
Guide (GuideNum, GuideLastName, GuideFirstName)

Question 9: Data normalization
Considering the Trip table;
Trip( TripID, TripName, startLocation, State, Distance, MaxGrpSize, Type, Season)
Considering the trip can be held in more than one season, this creates a repeating group for the season
attribute thus to normalize the repeating group is eliminated.
Trip (TripID, TripName, StartLocation, State, Distance, MaxGrpSiz, Type)
Trip_Seasons (TripID, Season)
This is based on the following assumptions;
 A trip is held for one or more seasons.
 One trip cannot be held twice in one season.
Question 10: Expand the database design
According to the changes proposed for the expanded database design, the following relations are as a
result of implementing the changes;
 Guide (GuideNum, lastName, firstName, address, city, state, postcode, phoneNumber)
 Trip (TripID, TripName, startLocaiton, state, Distance, MaxGrpSize, Type, Season, TripPrice,
OtherFees)
 Customer (CustomerNum, lastName, firstName, address, city, state, postalCode, phone)
 Reservation (reservationID, TripID, TripDate, CustomerNum, agentNumber)
 TripGuides (TripID, GuideNum)
 Agent (AgentNumber, lastName, firstName)
The updated relational model is shown in the table below;
Table Attributes Constraint
Guide GuideNum Primary key
lastName
firstName
Address
city
State
PostalCode
PhoneNum
HireDate
Trip TripID Primary key
TripName
StartLocation
State
Distance
MaxGrpSize
Type

Season
TripPrice
OtherFees
Trip_Guide TripID Primary key,
Foreign key references Trip
(TripID)
GuideNum Primary key,
Foreign key references Guide
(GuideNum)
Reservation reservationID Primary key
TripID Foreign key references Trip
(TripID)
TripDate
CustomerNum Foreign key references
customer (CustomerNum)
agentNumber Foreign key references agent
(agentNumber)
Agent AgentNumber Primary key
firstName
lastName
The proposed changes have been implemented based on the following assumptions;
 Each reservation is made by one and only one customer.
 Each reservation is handled by one and only one agent.
 A reservation is made for one and only one trip.
Question 11: Attribute definitions
Table Attributes Data Type Constraint Assumptions
made
Guide GuideNum Char(5) Primary key Every guide
number is
unique and
should consist
a mixture of
numbers and
characters
lastName Varchar(50) This is the
lastname of
the guide
firstName Varchar(50) This holds the
first name of
the guide
Address Varchar(50) This holds the
address of the

guide
city Varchar(50) This holds the
city that the
guide resides
in
State Varchar(50) This holds the
state that the
guide resides
in
PostalCode Integer This holds the
postal code of
the guide
PhoneNum Varchar(30) This holds the
phone
number of the
guide
HireDate Date This holds the
date that
guide was
hired
Trip TripID Integer Primary key This will be a
auto
incremented
integer
TripName Varchar(50) This will hold
the name of
the trip
StartLocation Varchar(50) This will hold
the starting
location of the
trip
State Varchar(50) This will hold
the state that
the trip will
take place in
Distance Decimal This is the
total distance
of the trip
MaxGrpSize Integer This is the
maximum
number of
persons that
the trip can
accommodate
Type Varchar(30) This will hold
the type of
the trip

Season Varchar(10) This is the
season that
the trip takes
place in
TripPrice Decimal This is the
price charged
for a trip
OtherFees Decimal This is are the
other fees
expected to
be paid for the
trip
Trip_Guide TripID Integer Primary key,
Foreign key
references Trip
(TripID)
This relates to
a unique trip
ID
GuideNum Char(5) Primary key,
Foreign key
references Guide
(GuideNum)
This relates to
unique guide
number
Reservation reservationID Integer Primary key This will be an
auto
incremented
integer
TripID Integer Foreign key
references Trip
(TripID)
This relates to
a unique trip
id
TripDate Date This is the
actual date
that the trip
will take place
CustomerNum Integer Foreign key
references customer
(CustomerNum)
This relates to
a unique
customer
number
agentNumber Integer Foreign key
references agent
(agentNumber)
This relates to
a unique
agent number
Agent agentNumber integer Primary key This will be an
auto
incremented
integer
Firstname Varchar(50) This is the first
name of the
agent
lastName Varchar(50) This is the last
name of the