Database Design Assignment (Gold Coast Campus, Semester 2, 2013) – the University Accommodation Office case study. Due date: 15:00 Friday 11/10/2013 – late submission will be penalized –see course profile for details. Note: This assignment contributes 20% to your overall assessment. It is recommended to be done in groups of 2. You can do it individually if you so wish, but your workload will be higher, and it will not increase your marks. Both members of a group should contribute equally to the assignment. During marking, each member may be asked to explain the work independently. If one cannot explain the work, then the marks for him or her will be lowered. The accommodation office of a university has asked you to design a relational database to manage the data used for the leasing of rooms owned by the office. The detailed requirements are given in the case study. You can make your own reasonable assumptions if necessary. For students in 2002ICT, your tasks are Tasks 1, 2 and 3. The three tasks are 55, 30 and 15 marks respectively For students in 7003ICT, your tasks are Tasks 1, 2, 3 and 4 below. The four tasks are 50, 25, 15 and 10 marks respectively. Task 1: Conceptual design (all students) List your assumptions if you have. Draw an EER or ER diagram for the conceptual design of the database. (If your ERD is well-done and your translation is correct, your tables should be in 3NF in most cases). Task 2: ERD to table translation (all students) Translate your EERD or ERD to tables. Clearly show the primary key, foreign keys, and alternate keys. In this task, you need to list the tables obtained directly from your ERD or EERD, including all attributes, PKs and FKs. This task should not be mixed together with the next task. Task 3: Schema refinement and documentation (all students) Check your tables are at least in 3NF. If not, modify your ERD or do normalization here. If you choose to use non-3NF tables, provide reasons why. Assign appropriate data types and lengths…