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.
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.
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:
Assignments will be evaluated based on completeness and correctness, relative to other assignments in the subject. Content and format are both important.
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.
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:
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.