Understanding Database Table Operations And Normalization

Union of Tables

The union results to the following results

Student	Year if Study	Lecturer
Mark Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	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.

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.

Student	Year if Study	Lecturer
Mark	2	M. Taylor
Lorrain	3	K. Heel
Linda	1	S. Green

Difference R1 and 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

Prodcut 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

Based on this table;

1. 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) 

1. 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

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

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

1. 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)

1. 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.

à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)

Trip (TripID, TripName, stateAbbreviation, StateName, (GuideNum, GuideLastName, GuideFirstName)

(GuideNum, GuidelastName, GuideFirstName) is a repeating group.

1. Dependencies in the table;

TripIDàTripName, stateAbbreviation, StateName, GuideNum, GuideLastName, GuideFirstName)

GuideNumàGuideLastName, GuideFirstName (transitive dependency) 

1. 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) 

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.

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. 

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 agent

Table	Primary key	Unique, Not null index (on candidate key)	Explanation
Guide	GuideNum	Unique Not null	Unique key containing a mixture of both integers and characters limited to only 5 characters
Trip	TripID	Unique not null	Unique auto incremented integer
Trip_Guide	TripID	Not null Index	Part of the composite primary key
	GuideNum	Not null Index	Part of the composite primary key
Reservation	reservationID	Unique, Not null	Unique key which will be automatically auto incremented
agent	AgentNumber	Unique not null	Unique key which will be automatically incremented of each record

To accommodate changes for the database to accept reservations for groups the existing database can be modified by implementing the following changes;

• Since a reservation can be for an individual or for a group a reservation type table can be added for reservation types. Each reservation will be identified by a certain reservation type.
• Another table group reservation people will be added to hold customers who are attending the trip as group.

The following will be the new structure of the database;

• 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, reservation_type_ID)
• TripGuides (TripID, GuideNum)
• Agent (AgentNumber, lastName, firstName)
• Reservation_type (reservation_type_ID, type)
• Group_reservation_persons (reservationID, customerNum)

This is based on the following assumptions

• All group members are recorded individually as customers

Johnson, E., & Jones, J. (2008). A Developer’s Guide to Data Modeling for SQL Server. Addison-Wesley.

Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd Edition ed.). New York: McGraw-Hill Education.