Assessment Task – Tutorial Questions
Unit Code: HS2021
Unit Name: Database Design and Use
Final Assessment
Due: 5:00pm 17th October 2020
Weighting: 50%
Purpose: This assignment is designed to assess your level of knowledge of the key topics covered in this unit
Unit Learning Outcomes Assessed.:
1. Design and implement a relational database by considering business needs and context of the systems.
2. Retrieve and present information from cross functional areas information using SQL tools and techniques.
3. Discuss implementation approaches and associated risks associated with the implementation of a Database Management System.
4. Contribute and influence business decisions by consolidating information from databases.
5. Communicate, to technical and non-technical stakeholders, the fundamental concepts of Database Management Systems and their applications in a contemporary business
6. Understand the ICT profession and the expectations of ICT professionals in Database Management Systems roles.
The questions to be answered are;
Question 1 (5 Marks)
Create the Entity Relationship (ER) Diagram for the following scenario.
We want to maintain a database for a Faculty within a multi-campus regional university. The faculty has a number of schools and each school is characterized by its school-code which is unique, name and campus location. Schools employ professors who are characterized by their unique staff-id, name and the school they work for. A school offers courses that are characterized by their course-code which is unique, course name and the school. There are subjects offered within the faculty and these are characterized by unique subject-code, title and the school that offers them. Students enroll into courses that are characterized by student-id which is unique, name, address and phone.
The following business rules apply
• A course must belong to only one school, but a school may have many courses
• A student must be enrolled in a course, but a course may have 0 or more students
• A course must have one or more subjects, but a subject may be taught as part of many courses
• A student may be enrolled in many subjects, and a subject may have many students enrolled
in it
• A subject must be taught by a professor, and a processor may teach many subjects
• A professor is employed by only one school, and a school may employ many professors
Question 2 (5 Marks)
What can you say about the relationships between Courses and Subject, and Courses and Students? Will there be any problems in creating a Relational Database from your ER diagram? If there is, then describe how you will address this and re-draw the ER diagram.
Question 3 (8 Marks)
Create the DDL statements to create the tables described in your modifies ER diagram in Question 2.
Question 4 (8 Marks)
In your Database you created in Question 3, assume there is a student record for student
12543 Paul Darbyshire
who is enrolled in the course
BIS Bachelor of Information Systems
It is the beginning of Semester 1 2020. Paul wants to drop all his current subjects for this semester and change courses from the BIS course to the BHR Bachelor of Human Resources course. This is a new course and won’t begin until Semester 2 2020. Paul needs to be enrolled in the following 3 subjects for semester 2 2020
HR101 Introduction to Human Resources
HR211 HR for Medium Level Enterprises
HR201 HR for Dummies
Discuss any changes you may need to make to your database from Question 3 given this information and provide the SQL statements in order to accomplish the above tasks.
Question 5 (12 Marks)
Assuming the current semester is semester 1 2020:
provide the SQL Query to retrieve the list subjects and their names that each student is enrolled in for the BIS course for semester 1 2020. (you should include the student name in the list, provide the SQL Query to list all the subjects taught in the IT school. The query should also retrieve the subjects’ names and make sure no duplicates are retrieved.)
Question 6 (12 Marks)
Consider the following table
Course School SubjectID Subject Name Sem StudentID Student name lectureID Lecturer name Room
BIS IT
HS2061 IT Project T1 87654 Sally Brown 102 George Costanza 222
IT Project 76230 Eric Tan
Ethics 103 Cosmo Kramer
BBus HR HS4561 Staff Care 114 Elaine Springer 345
ECO HS3215 Economics 101 T2 86512 Alfred Butler 115 George Takai
MIS Graduate MIS1221 Database 97734 Lucy Liu 116 Sandra West 435
Given the above table, determine its stage of normalization and normalize it to 3rd Normal Form (3NF), illustrate stepwise all the various intermediate stage of normalization, provide the Relational Schema associated with the 3NF version of the table.
Submission Directions:
The assignment has to be submitted via Blackboard. Each student will be permitted one submission to Blackboard only. Each student needs to ensure that the document submitted is the correct one.
Academic Integrity
Holmes Institute is committed to ensuring and upholding Academic Integrity, as Academic Integrity is integral to maintaining academic quality and the reputation of Holmes’ graduates. Accordingly, all assessment tasks need to comply with academic integrity guidelines. Table 1 identifies the six categories of Academic Integrity breaches. If you have any questions about Academic Integrity issues related to your assessment tasks, please consult your lecturer or tutor for relevant referencing guidelines and support resources. Many of these resources can also be found through the Study Skills link on Blackboard.
Academic Integrity breaches are a serious offence punishable by penalties that may range from deduction of marks, failure of the assessment task or unit involved, suspension of course enrolment, or cancellation of course enrolment.
Table 1: Six categories of Academic Integrity breaches
Plagiarism Reproducing the work of someone else without attribution. When a student submits their own work on multiple occasions this is known as self-plagiarism.
Collusion Working with one or more other individuals to complete an assignment, in a way that is not authorised.
Copying Reproducing and submitting the work of another student, with or without their knowledge. If a student fails to take reasonable precautions to prevent their own original work from being copied, this may also be considered an offence.
Impersonation Falsely presenting oneself, or engaging someone else to present as oneself, in an in-person examination.
Contract cheating Contracting a third party to complete an assessment task, generally in exchange for money or other manner of payment.
Data fabrication
falsification and Manipulating or inventing data with the intent of supporting false conclusions, including manipulating images.
Source: INQAAHE, 2020
If any words or ideas used the assignment submission do not represent your original words or ideas, you must cite all relevant sources and make clear the extent to which such sources were used.
In addition, written assignments that are similar or identical to those of another student is also a violation of the Holmes Institute’s Academic Conduct and Integrity policy. The consequence for a violation of this policy can incur a range of penalties varying from a 50% penalty through suspension of enrolment. The penalty would be dependent on the extent of academic misconduct and your history of academic misconduct issues.
All assessments will be automatically submitted to SelfAssign to assess their originality.
Further Information:
For further information and additional learning resources please refer to your Discussion Board for the unit.