Objectives
• To develop a conceptual data model diagram
• To perform logical design and physical design
• To implement a database
Case Study
CQ constructions Pty Ltd (CQC) is a successful construction company that implements many residential unit construction projects in Australia. Each of those construction projects will have a name, project start date and number of towers that will be constructed in that project. Each tower is expected to have many floors and each of those floors can have many units in it. However, all floors in the same tower will have the same layout; same type and respective number of units. The units can be of single bedroom unit, double bedroom unit or three bedroom units only. If a floor, in a tower, contains two numbers of three bedroom units and four numbers of two bedroom units, then all the other floors in the same tower should also have the same number of two and three bedroom units.
If a customer would like to purchase a unit, he/she has to execute a purchase agreement with CQC and agree to pay the installments as specified in the purchase contract. Generally a purchase contract will specify the details of many mile-stones related to construction such as completion of excavation, completion of foundation, etc. At each of the mile-stone achievement, an installment payment is required from the customer. The details of those installment payments have to be recorded for auditing purpose.
As part of your assignment, you are expected to find out the relevant attributes of the entities and relationships. Each entity should have at least one additional attribute in addition to the identifying attribute. To avoid plagiarism, the case study does not clearly specify the details such as identifying attribute, attributes and their types, relationship constraints, etc., that are required to design and draw the ERD. Hence, you are expected to assume such details which have not been mentioned clearly in the above case study.
1 Draw an appropriate ER diagram
Use the symbols as prescribed in your unit-textbook to draw the ER diagram for the above case study.
2 Answer the following question:
Your answers have to be relevant to your ER diagram for the given case study only.
List your assumptions, relating to attributes and relationships that are related to the following:
• Purchase contract
• Installment payment
3 Create Logical Design
Map your Entity Relationship diagram into relations and make sure that all the relations are in BCNF. Provide all the relations in the following format:
Studnet (StudentId, StudentName, Street, Suburb, State, PostCode, Email, ContactNumber)
Course (CourseId, CourseName)
Course_Student(CourseId, StudentId, grade)
foreign key (CourseId) references Course.CourseId
foreign key (StudentId) references Student.StudentId
4 Provide Physical Design
As part of the physical design, provide the column specifications for any one of the relations/tables that you have created. Column specifications should be presented in a tabular format showing the details such as attribute name, data type/length, key, required, default value and validation rule(s) if any.
5 Implementation
Create all tables and their relationships that are relevant to your case study in a Microsoft Access database. All tables should contain their respective columns. All the relationships should have appropriate referential integrity and cascade options applied. The properties for all the columns of the table, that you have selected in the above physical design section, should have all the column specifications properly applied. You need not create any form, query or report. You need not insert any records in the tables.
How and what to submit:
You must make an electronic submission for this assignment using the appropriate assignment submission link in the unit web site. Your submission must be a single ZIP file which contains your assignment work in the following two files:
A Microsoft Word document containing
1. Entity Relationship Diagram (ERD)
2. Assumptions related to Purchase contract and Installment Payment
3. All the relations (after the logical design)
4. Column specifications for any one table only