Union of Tables

The union results to the following results

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.

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.

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

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;

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.

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

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;

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. 

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.