Hey everyone,

Can someone check over my normalisation exercise from 0NF to 3NF please.

Case Study: A holiday is a standard package to a particular destination for a given number of days (NumDays) on a particular date and is uniquely identified by HolidayId. A holiday manager (HolManagerId) will manage many holidays. Customers for a particular holiday, travel on an airline scheduled flight, identified by FlightNo, and stay in one of several hotels at the destination resort. A customer will have one or more members in their holiday party (NumInParty). The basic price of the holiday is held in BasicPrice. The travel agent booking the holiday is held in AgencyId, whilst AgencyRepId identifies the contact employee of the travel agent. There are several trips that can be taken from the resort, identified by TripId and these are run daily or on two or three days per week. A tour operator representative (guideId) is allocated to each trip on a daily basis. It can be assumed that a customer will not undertake the same trip more than once during their holiday and that all members of the party take the trip. Customers are given a unique identifier, CustId, when they first use the services of TCC Travel.

0NF:
Holiday (HolidayID, Destination, HolManagerID, NumDays, BasicPrice, Airline, FlightNo, FlightDate (CustID, CustName, HotelID, HotelName, NumInParty, AgencyID, AgencyRepID (TripID, TripDestination, TripDate, GuideID)

Assumptions:
• Only one customer is present on a single trip, NumInParty makes up all the members of the trip
• A customer will go on more than one trip per holiday
• A customer stays in one hotel while on holiday and has one agency rep

1NF:
Holiday (HolidayID, Destination, HoliayManagerID, NumDays, BasicPrice,
FlightNo, FlightDate, Airline)

Customer (CustomerID, CustName, NumInParty, HotelID, HotelName,

NumInParty, AgencyID, AgencyRepID, HolidayID)

Trip (TripID, TripDestination, TripDate, GuideID, CustomerID)

2NF:
Holiday (HolidayID, Destination, HolidayManagerID, NumDays, BasicPrice,
FlightNo, FlightDate, Airline)

Customer (CustomerID, CustomerName, NumInParty)

AgencyBooking (CustomerID, HolidayID, AgencyID, AgencyRepID)

HotelBooking (CustomerID, HolidayID, HotelID, HotelName)

Trip (TripID, TripDestination, TripDate, GuideID)

CustTrip (CustomerID, TripID)


3NF:
Holiday (HolidayID, Destination, NumDays, BasicPrice, HolidayManagerID,
FlightNo)

Flight (FlightNo, FlightDate, Airline)

Customer (CustomerID, CustName, NumInParty)

AgencyBooking (CustomerID, HolidayID, NumInParty)

AgencyContact (AgencyID, AgencyRepID)

HotelBooking (CustomerID, HolidayID, HotelID)

Hotel (HotelID, HotelName)

Trip (TripID, TripDestination, TripDate, GuideID)

CustTrip (CustomerID, TripID)

Sorry its a bit long, thanks to anyone who can offer advice.