Information Technology Question Database Design Assignment Questions Part-1

Our Information Technology subject matter experts have worked on this assignment already in the recent past. You can buy a fresh copy of the solution for this assignment question. Please fill in your assignment details in the Order Now form or Live Chat with our expert now.

Here is a case study that will help you understand the Database Designing concepts & questions…

Good Luck 

The airline case study

Section I

Task Description

An airline company needs a database to track its staff, airplanes, flights and passengers and so on. The detailed system specification is given in Section II. You can make your own reasonable assumptions if necessary. Below are the tasks.

Task 1 Conceptual design

Draw an EER or ER diagram for the conceptual design of the database.
List all of your assumptions.

Task 2 ERD to table translation

Map your EERD or ERD to tables, using the rules in the text or lecture slides.
Clearly show the primary key, foreign keys, and alternate keys.

 

 

Task 3 Schema refinement and documentation

Check your tables are at least in 3NF. If not, modify your ERD or do normalization. If you choose to use non-3NF tables, provide the reasons why.
Assign appropriate data types and lengths to all attributes.

Task 4: SQL Script files

Make an SQL script file (and name it, for example airline.sql) that contain SQL statements to create the tables, insert sample data (minimum 5 records for each table), and do the queries (1), (5), (8), (9) and (10). You need to make sure your script files work correctly.

Create a transcript of the execution of all your solutions (and name it for example airline.txt). Refine the schema to include necessary integrity constraints such as domain constraints, null value constraints and possibly more complex business rules.
Document your final database schema clearly, e.g., you may use a form similar to the following one.

 

 

Section II Terra Firma Airlines Case Study

Terra Firma Airlines requires a system to schedule flights and to control passenger reservations. The system must be designed to satisfy the following requirements:

1. The airline has several types of planes of varying passenger capacities and flight ranges. There are one or more airplanes of each type in the airline’s fleet. Each airplane has a unique serial number.
2. The airline has pilots, flight attendants as well as other staff. For each member of staff it keeps data about his / her staff number (which is a unique identifier), the staff name, date of birth, home address, and date joined the airline. In addition, previous work experience including company, position, start and finish dates are also recorded.
3. For each staff member, an emergency contact person, including name, postal address, phone, email, and relationship with the staff, must be recorded.
4. Pilots are certified only on certain types of planes. Information on the types of planes certified for each pilot, as well as the date of certification, must be maintained.
5. The airline provides regular training to flight attendants. Records of all trainings received by each flight attendant must be recorded. Such records include the training program name, the start and finish dates, and a brief description about the content.
6. A flight, which is identified by a unique flight number, has an original city, a destination city and possibly one stop-over city. The time of departure from the origination city, time of arrival at the destination city, and the arrival and departure times at the stop-over city must be recorded (For example, flight TF88 departs Brisbane at 10:00, arrives at Sydney at 11:15, departs Sydney at 12:15, and arrives at Perth at 13:40 local time every day). In addition to all of the above information, an actual scheduled flight has a date, an airplane, a captain, a co-pilot and up to 8 flight attendants. The captain and co- pilot must both be pilots certified for the assigned type of airplane, and we assume that a pilot can work as captain on one scheduled flight, and as co-pilot on another. A scheduled flight is uniquely identified by the combination of a flight number and date.
7. The airline needs an online booking system that people can use to search for available flights and purchase tickets. For each ticket sold, the system must record the ticket number (which is an identifier of the ticket), date purchased, payment type (e.g., credit card, paypal), the flight number, flight date, departure city, arrival city, ticket type (e.g., promotion-fare, flexi-fare, premium-fare), ticket price (for each scheduled flight, the ticket type, departure city, arrival city and purchase date determine the ticket price) as well as details of the passenger: ID type and ID number (a valid ID can be a passport or a driver’s license), first name, last name, sex, address, contact phone, and email address. The booking system must ensure that the same passenger is never booked on the same scheduled flight for the same departure or arrival cities more than once.

 

 

Find some more important questions in the second part of this post

Information Technology Questions Database Design Assignment Questions Part-2

I hoped this page helped you out in your quest for assignment questions…