An airline company needs a database to track its staff

Reference no: EM13342020

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

Task 1: Conceptual design (50 marks) List all of your assumptions.

Draw an EER or ER diagram for the conceptual design of the database.

Task 2: ERD to table translation (20 marks)

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

Task 3: Schema refinement and documentation (10 marks)

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.

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, eg, you may use a form similar to the following

Table name attributes Description Type constraint comment

Customer

custNo Customer number Char(6)

custName Customer name Varchar2(30)

custPhone Customer telephone number Char(10)

gender Customer gender

Char(1) ‘M’ or ‘F’

Product

pCode Product code Char(8)

pName Product name Varchar2(20)

price Produce price

Number(6,2) 0, =1000

Orders

orderno Order number

Orddate Order date

custNo prodNo Date

= current date

Quantity Quantity ordered

FKs: custNo -- Customer(custNo) prodNo -- Product (prodNo)

Number(3) 0

Legend: 1. primary key attributes are underlined

2. Only attributes marked with * allow for null values

Task 4: SQL Script files (20 marks)

Make an SQL script file 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). Make another script file drop.sql that contains SQL statements to drop all of your tables. You need to make sure your script files work correctly.

Submission: Both soft copy and printed copy are required.

1) Upload a zip file that contains all of your electronic files. The zip file must be using the name format: CourseCode_studentNumber_name.zip

2) Drop a printed copy of your assignment into the assignment box in G23. The printed copy should contain everything except the SQL script files.

On the cover sheet clearly list all members of your group. Please note: all members of a group should contribute equally to the assignment and each member should fully understand the entire work. You may be required to give a 3 minute presentation of your work during one of the workshops. Alternatively, you may be asked to explain your work while your submission is being marked. Your marks will be deducted if you cannot explain your work.

Reference no: EM13342020

Questions Cloud

Explain what is the thickness of the meat loaf : A meat loaf (moisture content = 85%) took 20 hours to freeze in a blast freezer. What is the thickness of the meat loaf assuming infinite plate geometry. The convective heat transfer coefficient

A project plan using a gantt chart and clearly defined miles : A project plan using a Gantt chart and clearly defined milestones (if in a group with clearly defined individual tasks, contributions and milestones) Use Microsoft Project or other software to produce the Gantt Chart.

Find electric field and charge stored between the plates : The plates of a parallel-plate capacitor each have an area of 0.1 m2 and are separated by a 0.6 mm thick layer of paper. The capacitor is connected to a 13 V battery. (The dielectric constant for paper is 4.)

Calculate the angular frequency of oscillation : A torsion pendulum is made from a disk of mass m = 7.1 kg and radius R = 0.71 m. What is the angular frequency of oscillation of this torsion pendulum

An airline company needs a database to track its staff : An airline company needs a database to track its staff, airplanes, flights and passengers and so on. The detailed system specification is given to you in the document AirlineCaseStudy.pdf. You can make your own reasonable assumptions if necessary. Yo..

Explain the convective heat transfer coefficient : Plank's equation is being used to estimate the convective heat transfer coefficient in a food being frozen in an air blast freezer. It took a product 20 minutes to freeze in the freezer.

How much charge in micro-coulombs was exchanged : After they have exchanged charge and are placed 24 cm apart the magnitude of the electric force is now 3.6 Newtons. How much charge, in micro-coulombs, was exchanged

Explain freezing with a convective heat coefficient : Partially frozen ice cream is being placed in a package before completion of the freezing process. The package has the dimensions 10 cm in diameter by 20 cm in height, and is placed in air-blast freezing with a convective heat coefficient of 50 W/..

Understand the principles of data management and relational : Develop a time sheet entry/customer account management software system using relational database software and prepare an associated report detailing the technical and learning issues encountered. • Work effectively as a team member.

User Account

All Pages