Task 1: Conceptual design (50 marks)
Draw an EER or ER diagram for the conceptual design of the database.
List all of your assumptions.
Task 2: ERD to table translation (20 marks)
Map your EERD or ERD to tables, 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, e.g., you may use a form similar to the following
one.
Task 4: SQL Script files (20 marks)
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).
IMPORTANT: Ensure that MySQL is run with the --verbose option so that the SQL queries are
included in the output. In the transcript include the question number in SQL statements using
comments (‘‐‐') for example: SELECT * FROM employee E
Task 1:
You have recognised every entity (and represented it as an entity or as an attribute as
appropriate).
You have recognised every weak entity.
You have correctly recognised generalisation hierarchies.
All relations are recognised and represented, or you have given suitable alternative
representation of the corresponding facts.
Identifying relationships recognised and represented.
Correct primary keys represented for each entity participation and cardinality constraints are
represented for each relationship (either of the two cardinality notations taught in the
lectures is acceptable, as long as no ambiguity arises).
Partial keys are correctly represented for each weak entity.
Correct representation of complex and/or multi-valued attributes was given
Any necessary assumptions are written on a separate sheet. I.e., you have listed all facts that you
needed to complete your diagram, but the text did not contain the information (or the text was
ambiguous).
Task 2:
You have submitted a relational schema, and
You have marked all primary key and foreign key constraints.
You have correctly represented every attribute of each entity and relation.
You have chosen a correct way to map every entity, relation and attribute.
Task 3:
All you tables are in 3NF (and otherwise convincing reasons are provided).
An appropriate data type and length for each attribute. Other important constraints such as
null value constraints and domain constraints are present. Important business rules are also
identified.
Task 4:
All the SQL commands for creating tables are included.
All the SQL commands for inserting sample data (at 5 rows per table) are included.
All the SQL commands for doing the five queries are included.
You have included the complete execution results of all your commands from the transcript