VUTECA Travel Agency

General Description

You have been hired as a database consultant by the VUTECA Travel Agency, who up to this point has never ventured onto the web. This travel agency specializes in developing and promoting package tours of various kinds. They have decided that they can significantly increase their business with a web presence. Since their current PC database is a mess, they have decided to totally scrap it in favor of a client-server database system as a Java-based web site.

The travel agency basically needs to store information on customers and packages in its database, in order to track customer bookings. Customers can either book packages via the web site, or by calling the agency and interacting with a travel agent.

For this assignment you are to produce a detailed design and implementation for a Travel Agency’s Database System (TADS) particularly suited to this.

The primary data components in this system are the following:

• Packages: A package represents a particular tour that is being sold by the travel agency. Packages have a set of planned departures. Packages also have a set of destinations, a set of hotels, a capacity - the total number of people that can be accommodated on the tour, and a basic type (cruise, bus, hiking, ski, etc.). For those customers preferring to travel alone, it should be possible to hire a rental car as well. Finally, each package has a descriptive summary. Only travel agents can add or delete packages to the system. The travel agency has a rather draconian policy on cancellations and ticket changes that incur penalties and/or increase fares and taxes.
• Customers: A customer enters the system by booking a package departure. Information to be stored for each customer includes name, customer id, address, email address, age, and phone number and number of travellers in the party.
• Destinations: A destination has a name, country, a set of hotels serving the destination, and a set of attractions.
• Hotels: A hotel has a name, an address, a phone number, and a star ranking (out of 5 possible stars corresponding to luxury, budget, etc. in half star intervals).
• Attractions: An attraction has a name, and a type (museum, mountain, ruin, etc.)
• Departures: A departure represents one specific occurrence of a package tour. Customers book places on departures. A departure has a start date, an end date, and a record of the number of people currently booked.

Note that this is a guide. You may need to add additional entities and attributes to meet the requirements for the queries or application programs.

Queries and Database modifications

In order to assess the potential of your MySQL database system for use, the database should minimally be able to supply answers to the following queries:

Queries:

• List full information on all customers.
• List information on all customers booked on a particular tour entitled “City and Movie Stars Homes Tour.”
• List repeat customers with package and destination information, and total money spent.
• List all package information for the destination of Bali.
• List all package information the use the “Pennisula Hotel.”
• List information for all departures that start on a particular date.
• List information for all packages that cost less than some amount.
• List information for all packages of a particular type, e.g., skiing packages.

Use of SQL functions:

• As a travel agent, you want to add a new package. Hotel and destination information may have to be added as well.
• As a travel agent you want to book a departure for a customer. A customer may not be booked on a departure that is already full.
• As a customer, you want to book a package departure on the 7th Oct., 2013 to go to a “Seven Night Queensland Coast Cruise.”

Evaluation

Assignments will be evaluated based on completeness and correctness, relative to other assignments in the subject. Content and format are both important.

Assignment format and organization:

Treat this assignment as you would a report to be delivered to a customer. While content is obviously the most important part of the task, the format should be neat and well organized. All documentation and reports should be typed.

Assignment Deliverables:

To make the assignment easier to do, divide it into several steps.

Step 1: Design the EER model and specify all constraints on it. Design a conceptual model (EER). Document by drawing the EER diagram and fully describing your design choices. Specify key attributes and all constraints on each relationship type, e.g., include cardinality and participation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. Fully document any assumptions that you make.

It is not necessary to fit the whole diagram in one page. If the diagram is too big, then break it up in multiple parts and repeat entity sets in each part as needed. If an entity set is repeated in multiple parts, you need to specify the domains of its attributes only once.

Step 2: Translate your EER diagram to an appropriate relational schema in your MySQL database. Use the CREATE TABLE command to specify each relation, its attributes, and its attribute types. In your table definitions, include the appropriate PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL clauses to denote the constraints of your relational schema. For other constraints, insert suitable CHECK clauses in the schema definition of the corresponding table.

Step 3: Normalize the relational schema. Document how your design avoids update anomalies. Address:

• Normalization -- in what normal form are your tables? How do you know they are in 3NF? If they are not in 3NF, why not?
• Check whether you schema is in BCNF. If not, then provide a BCNF decomposition of your tables.
• The update behavior you defined through SQL, e.g., cascade deletes, etc.
• Treatment of null values -- what attributes allow null values? What impact does this have on database updating or querying?
• Lossless joins -- are they possible? How do you know?
• Discuss the effect of one "complicated" insert, i.e., one that should concurrently modify multiple tables. If your design permits any anomalies, document those anomalies and your rationale.
• Describe how the deletion of a particular package should affect the database. In other words, if we delete the package “Seven Night Queensland Coast Cruise” from the database, should any other rows in other tables be modified or deleted?

Step 4: Implement the relations in MySQL Implement your relational schema in MySQL. Most of the documentation for this task will be in the MySQL files that create the tables and define integrity constraints.

Step 5: Populate your database For this assignment you should populate your database will a minimal set of records, create the common queries shown above. Populate your database with sufficient sample data to demonstrate queries and applications. Document by submitting a listing that shows the contents of each table.

Step 6: Design the queries that must be issued on the DB to perform each action. Implement all queries and database modifications described in the section above as well as the two additional queries of you own choice.

Step 7: File Organisation Pay particular attention to file organisation for each relation. (Make assumptions about the sizes of the tables, and, based on the volume and transaction analysis, determine file organisation for each relation). Identify

Identify all Secondary indexes.

Comment about any Controlled redundancy that you have used and the reasons that you have applied the redundancy.

For queries 1 and 6 (above), give one or more MySQL CREATE INDEX statements that would help the query to be processed more efficiently. If you think that no index can help, or that one exists already that will help, state this instead of providing an SQL CREATE INDEX statement. If you do create an index, you must specify which access method is being used.

Step 7: Define one view for the customer and one view for a travel agent. In order to make the demonstration more believable, you should alter your test data so that each view contains at least two tuples in total.

System functions should be made as robust as possible (error checking) and should preserve database integrity.