2002ICT/7003ICT (semester2, 2014) Assignment – the airline case study.
Due date: 15:00 Friday 17/10/2014 – late submission will be penalized –see course profile for details.
Note: This assignment contributes 25% to your final assessment. It should be done in groups of maximal size 2. Students in each group should contribute equally on all tasks.
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.