Case Study

Only available on StudyMode
  • Download(s) : 745
  • Published : December 4, 2012
Open Document
Text Preview
1.0Overview of the case study
Background of the Study
Sweeney Tours is a holiday tour operator offering package holidays to a number of resorts in the Mediterranean. The holidays fly from a number of airports in the UK and have a fairly complicated pricing Structure. There is a great deal of information in the brochures that Sweeney Tours produce along with an Accompanying price guide. Some of the information is on computer but this is somewhat cumbersome as it is in separate systems that are incompatible, incomplete and difficult to use. It is particularly difficult to find holidays that match a customer's specific requests unless the brochure is browsed carefully. Sweeney Tours have decided to computerize the information in their brochure and to include booking information to make it easier to: * Gather specific information from customers and answer questions about the holidays * Check availability of specific packages

* Give costs of packages
* Take bookings

1.1Purpose
1. To become familiar with MySql.
2. To understand and experience a data dictionary
3. To become familiar with the data definition and manipulation language SQL. 4. To understand referential integrity
5. To explore the existing database for the Sweeney Tour system.

1.2Problems
COUNTRY (country, language, timezone, currency)
REGION (region, landtype, country, scenery, page)
RESORT (resort, region, transfertime, beach, beachnum, page) HOTELS (hotel_id, hotelname, sunbeam, ya, rating, stdbasis, page, resort, resortloc, roomtotal) FACILITIES (facid, description, category)

FACINRESORT (resort, facid)
FACINHOTEL (hotelid, facid, numof)

1.3Normalization
1.3.1 First Norm
COUNTRY (countryCode, countryName, language, timezone, currency) REGION (regionNo, landtype, countryCode, scenery, resortID)
RESORT (resortID, resortloc, regionNo, transfertime, beach, beachnum, page) HOTELS (hotel_id, hotelname, sunbeam, ya, rating, stdbasis, resortID, roomtotal) FACILITIES (facid, description, category)

FACINRESORT (facsort_Code, resortID, facid)
FACINHOTEL (factel_Code, hotel_id, facid, numof)

1.3.2Second Norm
COUNTRY (countryCode, countryName, language, timezone, currency) REGION (regionNo, landtype, scenery)
RESORT (resortID, resortloc, transfertime, beach, beachnum, page) HOTELS (hotel_id, hotelname, sunbeam, ya, rating, stdbasis, roomtotal) FACILITIES (facid, description, category)
FACINRESORT (facsort_Code)
FACINHOTEL (factel_Code, numof)

1.3.3 Third Norm
COUNTRY (countryCode, countryName, language, timezone, currency) REGION (regionNo, landtype, scenery)
RESORT (resortID, resortloc, transfertime, beach, beachnum, page) HOTELS (hotel_id, hotelname, sunbeam, ya, rating, stdbasis, roomtotal) FACILITIES (facid, description, category)
FACINRESORT (facsort_Code)
FACINHOTEL (factel_Code, numof)

countryCode (counrtyName, language, timezone, currency)
regionNo (landtype, countryCode, scenery, resortID)
resortID (regionNo, transfertime, beach, beachnum, page)
hotel_id (hotelname, sunbeam, ya, rating, stdbasis, resortID, roomtotal) facid (description, category)
facsortCode (resortID,facid)
factelCode (hotel_id, facid, numof)
1.3.4 Fourth Norm
many to many relationsip allowed in ERD but not in Relational Database. country
countryCode
language
timezone
currency
has
landtype
regionNo
countryCode
scenery
region
countryName
has
resort
regionNo
page
beachnum
beach
transfertime
has
resortName
stdbasis
resortID
rating
hotel_id

sunbeam
ya
resortID
hotelname
roomtotal
hotels
resortid
has
hotel_id
factelCode
numof
facid
description
has
facid
category
facility
facsortCode
resortID
facinresort
facinhotel
has
has
resortloc
1.3.5 Fifth norm

1.3.6 Six norm
country
countryCode
language
timezone
currency
has
landtype
regionNo
countryCode
scenery
region
countryName
has
resort
regionNo
page
beachnum
beach
transfertime
has
resortName...
tracking img