Assessment 1: Database Design Assessment type: PDF Word limit: N/A Final Due Date: 28 August at 23:59 (Melbourne time) – Week 6 Weighting: 20%, 20 Marks Overview The objective of this assignment is to measure your understanding of the basic concepts in the relational database model and using entity-relationship model for database design. The assessment is in two parts, split into four tasks which cover Basic ER Modelling and Basic Relational Modelling. The tasks are as follows. Part A: Entity-Relationship Modelling (12 Marks) 1. Design and plan for the implementation of a database system, diagramming the design to a high standard using UML notation through the diagramming tool Lucidchart. 2. Model the activities of an organisation and present the model as an Entity-Relationship (ER) diagram. Analyse this ER diagram, and possibly modify it, based on additional client requirements. 3. Map an ER diagram into a relational database schema, showing every step of the mapping. Part B: Relational Database Model (8 Marks) 4. Answer a series of short questions about a Relational Database model. To complete this assessment, you must be familiar with Lucidchart, which is covered during the Week 1- 4 activities. Assessment criteria This assessment will measure your ability to: • Describe various data modelling and database system technologies. • Explain the main concepts for data modelling and characteristics of database systems. Course learning outcomes This assessment is relevant to the following course learning outcomes: Page 1 of 9 CLO1 Describe the underlying theoretical basis of the relational database model and apply the theories into practice. CLO2 Explain the main concepts for data modelling and characteristics of database systems. CLO3 Develop a sound database design using conceptual modelling mechanisms such as entity-relationship diagrams. CLO4 Develop a database based on a sound database design. Page 2 of 9 Part A: Entity-Relationship Modelling Task 1: Designing an Entity-Relationship Model Elite Construction Case Study Elite Construction (EC) is an Australian building construction company. The following are the requirements for managing data about staff, clients, construction projects and contractors for EC. Construction Projects EC is responsible for managing construction projects throughout Australia. Each construction project has a unique address and the system records the number of rooms and floors the construction has. For each construction project there is a contract which has a start date, end date and a total fee. Staff It is important to distinguish between various staff involved in contruction projects. There are project managers that are responsible for overseeing the management of various construction projects – such as organising sub contracts for completing various tasks in the contruction of the project, as well as liasing with the client. They are assigned when the client enters into a contract. In addition there are contractors that are responsible for working on various sub contract tasks associated with the contruction project. Project managers have a distinct employee ID and have a name. Contractors have an Australian Business Number (ABN) and a phone number. Sub Contracts A contract consists of several sub contracts which outline the details of the task to be completed (eg Plastering), its fee and the outstanding balance (identifying how much of the fee is still unpaid). One contractor is responsible for the supervision of the sub contract task and must be identified when the sub contract is created. There are many contractors working on the sub contract task itself (the supervisor also works on the sub contract task). Client Each client has a unique ID and has a name. The date that clients enter into a contract for a specific project is recorded – note that this may be before the start date of the contract itself. Furthermore, clients nominate a specific bank account which will be used for payment of all contracts that they enter into. The account has a BSB and an account number. Based on the given description, model the given business rules, and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and their cardinality and participation constraints. If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model. You must use UML notation and the diagramming tool Lucidchart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model. A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on how accurately it represents business rules described above.