Database Design Assignment
Due dates:
• Part A: Friday 8th April 2016
• Part B: Friday 22th April 2016
• Part C: Friday 6th May 2016
Introduction
The assignment involves the conceptual and logical design of a database to support an online insurance company.
The data model can be based on an existing system or a fictional system, but in the latter case, we suggest that you base it primarily on a real system to ensure that you include all the necessary information. In your submission, include details about which website you used as a source for your ideas. The data model details should be as complex as your group can reasonably manage and model correctly. Generally, individuals will tend to have smaller, less complex data models than groups, which should be dealing with more entities and relationships.
Please ensure that you adhere to the generally accepted principles of privacy and confidentiality of data. In particular, if you are basing your assignment on an actual system to which you have access, do not use details such as names, contact details etc of real customers or other people in your case study. You should change this data so that it cannot be linked to a real person or company.
Assignment Requirements
The submission includes the following deliverables:
Part A
1) Database design case study:
Give a short description of the insurance application/website for which you will be designing the database, outline the basic functions that your database needs to support, and identify the data requirements. Please include some sample documentation (forms, statements, webpage etc.).
Your case study description should be similar to the New Oriental Hospital case study given for your data modelling tutorials (See Tutorials 1 and 2), but will probably be shorter, with 3 or 4 forms. You will only need 6-8 entities in total when doing the conceptual data modelling, so bear this in mind when deciding the scope of your case study).
Part B
2) Conceptual data modelling:
Produce the final ERD reflecting the data requirements given in the case study (using the Crow’s foot or textbook notation). Justify your design decisions by documenting all your assumptions for your conceptual data model. Your conceptual data model will be marked according to your ability to identify appropriate entities, attributes, relationships and cardinalities, and to structure a data model using these elements.
Part C
3) Logical design / Schema conversion:
Convert your ERD (conceptual schema) into a set of relational tables. Use the following format to list each table, where the primary keys are underlined and the foreign keys are marked with asterisk(s):
TableName (Identifier, non-key attributes) or
TableName (Identifier, non-key attributes, ForeignKey*)
ForeignKey references OtherTable
Your relational model will be marked according to your correct application of the appropriate conversion rules.
4) Logical design / Normalisation:
a) Identify and list the functional dependencies (FDs) (based on the business rules and forms of your case study)
Please use the following format for your functional dependencies:
X ® Y
i.e. determinantAttribute(s) ® dependentAttribute(s)
b) Use these FDs to determine the highest normal form for each table/relation defined in deliverable 3. Justify your decisions. If there are tables which are not fully normalised (i.e. not in BCNF), perform normalisation for the tables until all the tables are in BCNF. You must document in detail each part of the normalisation process.
Please use the following format for your final set of relations, where the primary keys are underlined and the foreign keys are marked with asterisk(s):
RelationName (Identifier, non-key attributes) or
RelationName(Identifier, non-key attributes, ForeignKey*)
ForeignKey references OtherRelation
Note: Your assignments will be marked according to your demonstration of knowledge, i.e. you must demonstrate that you understand each of the processes of database design, such as conceptual data modelling, logical design of a database and normalisation,. You need to justify your design decisions, so do not simply show your final answers. Show your working and reasoning as much as possible.
5) Reflection on your design and lessons learnt
Write a reflection on your design and lessons learnt from this assignment. You need to roughly rank your design out of 10 and give the reasons you have assigned this ranking. Also, you need to point out specifically what you can improve.

Marking Scheme
In marking your submissions, the total mark of 40 is split among the deliverables as follows:
Assignment Part Deliverable Marks
Part A Case Study 4 5 (Bonus marks)
Self-assessment 1
Part B Data Modeling 14 15*
Self-assessment 1
Part C Schema Conversion 5 25
Normalisation 13
Reflection 5
Self-assessment 2
* This mark will be multiplied by the complexity weighting (CW), determined by the following:
Checking points CW (out of 1.0)
Regular entity 0.3
Weak entity 0.1
Associative entity or M-N relationship 0.1
Super/sub-type entity/ self-referencing entity 0.1
Optional cardinality 0.1
Mandatory cardinality 0.1
1-M relationship 0.2
Total 1.0
The total mark of your assignment will be capped to 40, in other words, if your mark is 43, your recorded mark will be 40. The final mark is therefore calculated via the following formula:
Final mark = [Case Study + Self-assessment]
+[Data modeling * CW+ Self-assessment]
+[Schema conversion + Normalisation + Reflection + Self-assessment]

Appendix A: Marking Criteria
Each student/pair/group is required to submit self-assessment marking sheets with each assignment part. Students must assess their own work, according to the given criteria, allocating marks and completing the marking sheets as they deem appropriate for each part, before they submit their work for assessment by the teaching team.
Making the marking criteria available to students before they attempt the assignment clarifies what knowledge and skills they are expected to master through constructing a solution for the assignment.
Furthermore, part of an effective learning experience is learning how to make accurate judgments about the quality of one’s own work, an essential skill for both students and professionals. Self-assessment enables students to judge whether or not they have achieved each of the outcomes (criteria) throughout the construction of their assignment solution. This encourages reflection, with revision where necessary, and an active learning experience.
The teaching team will use exactly the same marking sheets and criteria as the students when they mark the assignments. The marking criteria for each assignment part are given on the following pages.
Part A Marking Criteria
Question 1: Case Study and Self-assessment (5 marks)
1. Overview of the case study 1
2. Outline functions that the database supports 1
3. Description of data requirements 1
4. Description of business rules 1
5. Submitting of completed self-assessment form 1
Subtotal

Part B Marking Criteria
Question 2: ERD and Self-assessment (15 marks)
Overall ERD design
1. ERD reflects case study 1
2. Justification of design decisions by documenting assumptions or referring to case study (logical assumptions are clearly stated, and do not violate the case study specifications) 1
3. Overall notations: Correct and consistent naming and drawing conventions (Either crow’s foot notation or textbook notation) 1
Identification of necessary properties (No extra properties and necessary properties should be included)
4. Identification of appropriate entities 1
5. Identification of appropriate relationships between entities 1
6. Identification of appropriate attributes in appropriate entities 1
ERD details (You will lose mark for following factors if you don’t have any)
7. Correct choice of identifying attributes for regular, weak, and associative entities 1
8. Correct choice of minimum and maximum cardinalities based on stated assumptions and/or specifications 1
9. Correct choice of strong and weak entities by using appropriate notations 1
10. Correct use of sub-type and super-type entities and self-referencing relationships (If there exists any) 1
11. Identification of M:N relationships or associative entities (if M:N relationships are not converted to associative entities then appropriate attributes on relationship) 1
12. Correct use of relationship notations: Solid and dotted lines in crow’s foot notation or single and double line in textbook notation 1
13. Correct use of notations for super-type and sub-type: (1) sub-types are overlapping or disjoint, (2) sub-types have total or partial completeness 1
14. No foreign keys to be shown in ERD 1
Self-Assessment
15. Submitting of completed self-assessment form 1
Subtotal
Complexity Weighting (CW)
Factor CW
Regular entity 0.3
Weak entity 0.1
Associative entity or M-N relationship 0.1
super/sub-type entity/self-referencing entity 0.1
Optional cardinality 0.1
Mandatory cardinality 0.1
1-M relationship 0.2
Total=1.0
Student Subtotal for Question 2: (ERD * CW)+Self-assessment
Part C Marking Criteria
Question 3: Schema Conversion (5 marks)
1. Following specified format for writing relations 1
2. Conversion of every entity into a relation 1
3. Conversion of M:N relationships into new relations 1
4. Correct identification of primary keys (Underling primary keys) 1
5. Correct identification of foreign keys (Marking foreign keys with asterisks) 1
Subtotal
Question 4: Normalisation (13 marks)
a) Identification and Listing of Functional Dependencies (7 marks)
1. Following specified format for writing functional dependencies, i.e. X?Y 1
2. Correct identification of functional dependencies from case study 6
Subtotal
b) Determination of normal forms (6 marks)
1. Correct definition of each normal form and violation causes 1
2. Following specified order for writing normalization process 1
3. Correct identification of FDs causing violations, if all relations are normalized use this for discretionary mark 1
4. Correct identification of attributes in new normalized relations 1
5. Correct identification of primary keys in new normalized relations 1
6. Correct identification of foreign keys in new normalized relations 1
Subtotal
Question 5: Reflection (5 marks)
1. What did you already know about the content and skills that were presented in this lecture? 1
2. What did you learn about the content or skills that were presented in this class? 1
3. How did doing the assignment change your understanding about this subject? 1
4. What connections have you made between this subject and other subjects you have taken? 1
5. What do you suggest for your further improvements in this subject? 1
Subtotal
Self-assessment (2 marks)
1. Submitting of completed self-assessment form 1
2. Quality of self-assessment (Design ranking reasonably accurate for marked design) 1
Subtotal