© Didasko 2018. All rights reserved. 1
CSE2DBX
Database Fundamentals
Assessment 2 – Database Modelling
| Objectives This is an INDIVIDUAL assignment. Students are not permitted to work in a group when writing this assignment. Copying, Plagiarism This is an individual assignment. Students are not permitted to work in a group when writing this assignment. Plagiarism is the submission of another person’s work in a manner that gives the impression that the work is their own. La Trobe University treats plagiarism seriously. When detected, penalties are strictly imposed. Further information can be found on http://www.latrobe.edu.au/students/academic integrity/explanation/plagiarism Submission Guidelines • Your assignment submission should be typed, not written/drawn by hand. • Submit the electronic copy of your assignment through the subject LMS. • Submission after the deadline will incur a penalty of 5% of the final assignment mark per day capped at 5 days. No assignment will be accepted after 5 days. If you have encountered difficulties that lead to late submission or no submission, you should apply for special consideration. |
2 © Didasko 2018. All rights reserved.
Background
For years ABC Vocational School has depended on their manual information recording procedures
to store information about their students, teachers and courses. The school maintains these
different types of information in separate paper-based files.
This manual procedure is quite difficult to handle and maintain, especially in the instances where
retrieval of data or a search for information is required. As you can imagine, retrieving a required
record from a massive file is both difficult and time consuming. Apart from these problems storing
many paper-based files requires significant storage space.
To overcome these problems ABC Vocational School has decided to take advantage of technology
available today to create an application that will allow them to perform these functions in a more
efficient manner.
The school has decided to handover the design of the database of this project to an Information
Technology student, you! Below is a description of the data needed and how it interrelates. You are
expected to model this information via an ER / EER diagram and map it to relations.
| The database will need to model information about three different types of people that the application will be concerned with: students, teachers, and administrators. Each person will have some common information recorded against them, including their ID, first name, last name, date of birth, phone, address 1, address 2, suburb, postcode, and type (which can be “student”, “teacher”, or “administrator”). In addition to the general information above, these types of people have some additional information that pertains themselves only. For students, the qualification code and start date will be stored. For teachers, their registration number and password will be stored. For administrators, the role and password will be stored. Students can enrol in a qualification. Each qualification has qualification code, name, and duration. As examples only, qualifications might be the “Diploma of Website Development” or the “Diploma of Software Development”. Note that a student may only enrol in one qualification and the qualification can have any number of students. Each qualification has courses mapped to it. The course information comprises of the course ID, name, year, semester, and the teacher responsible. In addition, each course can be mapped in any number of qualifications. For example, the “Diploma of Website Development” might include in part “Programming in HTML” and “Programming in Java”, whilst the “Diploma of Software Development” might include in part “Data Structures” and “Programming in Java”. Each course only has one teacher and a teacher can teach any number of courses. Students will be enrolled in their courses to complete their qualification. Students will therefore be enrolled in multiple courses and each course will have any number of enrolments. Each enrolment record will also record the mark that was obtained. |
Refer to the next page for the specific tasks to be performed as part of this assessment.
© Didasko 2018. All rights reserved. 3
Task 1
Draw an ER/EER diagram for the above-mentioned database in Microsoft Visio 2016 using Crow’s
Foot Database Notation.
For each entity:
| 1. Give the entity an appropriate name. 2. Include all attributes. Be aware that not all primary keys and/or foreign keys may have been explicitly mentioned in the problem statement above. It is your job to add these as necessary for a correct solution. 3. Annotate the primary key attribute(s) as . 4. Annotate the foreign key attribute(s) as . For each relationship: 5. Annotate the cardinality at each end as “zero or more”, “one or more”, “one and only one”, or “zero or one” (“Database Systems”, Coronel and Morris, 2017, p. 132). 6. Use an arrow to represent any inheritance relationship(s). 7. Add a label to each relationship. For example, if you wanted to model the relationship between a Supplier and a Product in an inventory database, you could use the annotation “provide” to say that suppliers “provide” products. |
[24 marks]
Task 2
In this task, you will create a textual representation of the relational model for the above-mentioned
database.
1. Map the above diagram from Task 1 to a relational model for all relations in
Microsoft Word. For example, for a product relation, Product(ProductID,
ProductName, Price, SupplierID), the primary key attribute(s) are in underline, the
foreign key attribute(s) are in italics, and both styles would need to be applied if an
attribute is both primary and foreign.
2. You must also check that your solutions for Tasks 1 and 2 are compatible with
each other. Update your solution(s) as necessary.
[16 marks]
Submitting your Assignment
When you have completed your answers, submit the assessment on the LMS system. You should
submit the following:
4 © Didasko 2018. All rights reserved.
• Submit your Task 1 answers in a drawing called xxx_cse2dbx_assessment2.vsdx
(where xxx is your student number).
• Submit your Task 2 answers in a document called xxx_cse2dbx_assessment2.docx
(where xxx is your student number).
Assessment 2 – Marking Rubric
| Task | Marks | Description | Total |
| 1.1 | 4 | All entities have been identified and named correctly. | 4 |
| 3 | 1-2 entities have been identified or named incorrectly. | ||
| 2 | 3-4 entities have been identified or named incorrectly. | ||
| 1 | 5-7 entities have been identified or named incorrectly. | ||
| 0 | All entities have been identified or named incorrectly. | ||
| 1.2 | 4 | All non-key attributes in all entities were correct. | 4 |
| 3 | A number of non-key attributes in 1-2 entities were incorrect. | ||
| 2 | A number of non-key attributes in 3-4 entities were incorrect. | ||
| 1 | A number of non-key attributes in 5-7 entities were incorrect. | ||
| 0 | A number of non-key attributes in all entities were incorrect. | ||
| 1.3 | 4 | All primary key attributes in all entities were correct. | 4 |
| 3 | A number of primary key attributes in 1-2 entities were incorrect. | ||
| 2 | A number of primary key attributes in 3-4 entities were incorrect. | ||
| 1 | A number of primary key attributes in 5-7 entities were incorrect. | ||
| 0 | A number of primary key attributes in all entities were incorrect. | ||
| 1.4 | 3 | All foreign key attributes in all entities were correct. | 3 |
| 2 | A number of foreign key attributes in 1-2 entities were incorrect. | ||
| 1 | A number of foreign key attributes in 3-5 entities were incorrect. | ||
| 0 | A number of foreign key attributes in all entities were incorrect. | ||
| 1.5 | 3 | Cardinality was correct on all association relationships. | 3 |
| 2 | Cardinality was incorrect on 1-2 association relationships. | ||
| 1 | Cardinality was incorrect on 3-5 association relationships. | ||
| 0 | Cardinality was incorrect on all association relationships. | ||
| 1.6 | 3 | Any/all generalisation relationship(s) were identified and implemented correctly. |
3 |
| 2 | Any/all generalisation relationship(s) were identified and implemented correctly in part only. |
||
| 1 | Any/all generalisation relationship(s) were identified but were not implemented correctly. |
||
| 0 | Any/all generalisation relationship(s) were not identified. | ||
| 1.7 | 3 | Labels were correct on all association relationships. | 3 |
| 2 | Labels was incorrect on 1-2 association relationships. | ||
| 1 | Labels was incorrect on 3-5 association relationships. | ||
| 0 | Labels was incorrect on all association relationships. | ||
| 2.1 | 8 | The relational model syntax is fully correct. | 8 |
| 6 | The relational model syntax is mostly correct. | ||
| 4 | The relational model syntax is partly correct. | ||
| 2 | The relational model syntax is seldom correct. | ||
| 0 | The relational model syntax is incorrect. | ||
| 2.2 | 8 | The Task 1 and Task 2 solutions fully agree with each other. | 8 |
| 6 | The Task 1 and Task 2 solutions mostly agree with each other. | ||
| 4 | The Task 1 and Task 2 solutions partly agree with each other. |
© Didasko 2018. All rights reserved. 5
| 2 | The Task 1 and Task 2 solutions seldom agree with each other. |
| 0 | The Task 1 and Task 2 solutions do not agree with each other. |
| TOTAL | 40 |
6 © Didasko 2018. All rights reserved.
The post CSE2DBX Database Fundamentals appeared first on My Assignment Online.