CSE2DCX ABC Vocational School

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 enroll 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 enroll 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.

Tasks

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:

• Give the entity an appropriate name.
• 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.
• Annotate the primary key attribute(s) as PK.
• Annotate the foreign key attribute(s) as FK.

For each relationship:

• 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).
• Use an arrow to represent any inheritance relationship(s).
• 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.

Task 2

In this task, you will create a relational model for the above-mentioned database.

• 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.
• You must also check that your solutions for Tasks 1 and 2 are compatible with each other. Update your solution(s) as necessary.