CSC72001 Database Systems: Student Enrolment Assessment 2 Report

This report details the design, implementation, and testing of a student enrolment database for Southern Cross University, fulfilling the requirements of CSC72001 Assessment 2. The assignment involves creating a relational database in MySQL using PhpMyAdmin, based on a provided Entity Relationship Diagram (ERD) and client business rules. The report includes an analysis of the client's requirements, assumptions made during the database design, and the chosen naming conventions and data types. Furthermore, the report presents and tests SQL queries to retrieve specific information, such as workshop enrolment lists, workshop counts, student transcripts, and student assessment totals. The solution demonstrates a practical understanding of database concepts and SQL query writing, providing a comprehensive response to the assignment brief.

SOUTHERN CROSS UNIVERSITY
ASSIGNMENT COVER SHEET
For use with online submission of assignments
Please complete all of the following details and then make this sheet the first page of each file
of your assignment – do not send it as a separate document.
Your assignments must be submitted as either Word documents, text documents with .rtf
extension or as .pdf documents. If you wish to submit in any other file format please discuss
this with your lecturer well before the assignment submission date.
Student Name:
Student ID No.:
Unit Name: Database Systems
Unit Code: CSC72001
Tutor’s name:
Assignment No.: Assessment 2
Assignment Title:
Due date:
Date submitted:
Declaration:
I have read and understand the Rules Relating to Awards (Rule 3 Section 18 –
Academic Misconduct Including Plagiarism) as contained in the SCU Policy
Library. I understand the penalties that apply for plagiarism and agree to be bound
by these rules. The work I am submitting electronically is entirely my own work.
Signed:
(please type
your name)
Date:
<Student name and ID> CSC72001 Assessment 2 Report Page 1

ASSESSMENT TWO
REPORT
BY <Insert your name>
<Student name and ID> CSC72001 Assessment 2 Report Page 2

Table of contents:
Contents
Table of contents:........................................................................................................................................3
Entity Relationship Diagram........................................................................................................................4
Part A: Analysis............................................................................................................................................5
1. Client Business Rules.......................................................................................................................5
2. Assumptions Made..........................................................................................................................5
3. Naming Conventions........................................................................................................................5
4. Data Types Chosen..........................................................................................................................5
Part B: Testing Queries................................................................................................................................6
B.6.1 Workshop Enrolment List........................................................................................................6
B.6.2 Workshop Count List................................................................................................................6
B.6.3 Student Transcripts..................................................................................................................6
B.6.4 Student Assessment Totals......................................................................................................6
<Remove this text before submission of your assessment>
<After you have completed your assessment, right-click on the table of contents above and click
“Update Field” then “Update Page numbers only”. This will build the table of contents for you.>
<Student name and ID> CSC72001 Assessment 2 Report Page 3

Entity Relationship Diagram
<Student name and ID> CSC72001 Assessment 2 Report Page 4

Part A: Analysis
1. Client Business Rules
The database should be able to store the details of the students in the system, which would be used to
identify the students with the help of the StudentID. In addition to this, the details of the address, email
and phone numbers of the students are to be stored in the system.
The details of the courses and the course durations and names are to be recorded by the system. In
addition to this, the units offered for the courses in the details of the units that are stored in the system.
The university offers a number of units to students and the details of the units offered to the students
are to be stored in the system. The unit enrollment details are to be stored in the system along with the
date of the enrollment.
The details of the teachers in the university are required to be stored in the system who would be
identified their unique ID and names.
The workshops details are required to be stored and the details of the enrolments to the workshops are
required to be stored in the database systems of the university.
Additionally, the details of the assignments and the test results are required to be stored in system for
all the students.
2. Assumptions Made
Mainly assumptions are made for storing the data for the workshops and the enrolments. It
has been assumed that a student can make a number of enrolments and the number of
enrolments have not been limited.
3. Naming Conventions
- The CamelCase naming convention has been selected for the development of the
database. This convention is useful for the double phased words for any type of
objects. The first letters of the phrases are used in capital letters.
4. Data Types Chosen
The varchar has been selected for the strings such as addresses and emails. In addition to this
the int datatype has been selected for the integers and the date is used for the dates.
<Student name and ID> CSC72001 Assessment 2 Report Page 5

Part B: Testing Queries
B.6.1 Workshop Enrolment List
Query:
SELECT workshop_enrolment.StudentID, unitoffering.UnitCode, unitoffering.Year, unitoffering.Session,
workshop.Campus, workshop.Location, workshop.Weekday, workshop.ClassTimes, student.StudentID,
student.FirstName, student.LastName, teacher.FirstName, teacher.LastName From
workshop_enrolment INNER JOIN workshop ON workshop_enrolment.WorkshopID =
workshop.WorkshopID INNER JOIN student ON student.StudentID = workshop_enrolment.StudentID
INNER JOIN unitoffering ON unitoffering.UnitOfferingID = workshop.UnitOfferingID INNER JOIN teacher
ON teacher.StaffID = unitoffering.UnitAssesorID WHERE unitoffering.Year = 2019 AND
unitoffering.Session = 'Session 3' ORDER BY workshop.Weekday, workshop.ClassTimes,
student.LastName, student.FirstName
Result:
B.6.2 Workshop Count List
Query:
SELECT student.LastName, student.FirstName, COUNT(workshop_enrolment.WorkshopID)
FROM workshop_enrolment
INNER JOIN student ON student.StudentID = workshop_enrolment.StudentID
INNER JOIN workshop ON workshop.WorkshopID = workshop_enrolment.WorkshopID
INNER JOIN unitoffering ON workshop.UnitOfferingID = unitoffering.UnitOfferingID
Where unitoffering.Year = 2019
GROUP BY student.LastName, student.FirstName;
Result:
<Student name and ID> CSC72001 Assessment 2 Report Page 6

B.6.3 Student Transcripts
Query:
SELECT student.StudentID, student.FirstName, student.LastName, unitoffering.Year,
unitoffering.Session, unit.UnitName FROM student INNER JOIN unit_enrollment ON
unit_enrollment.StudentID = student.StudentID INNER JOIN unitoffering ON
unitoffering.UnitOfferingID = unit_enrollment.UnitOfferingID INNER JOIN unit ON
UnitOffering.UnitCode = unit.UnitCode
Result:
B.6.4 Student Assessment Totals
Query:
SELECT student.FirstName, student.LastName, SUM(student_assessment.MarksAwarded)
FROM student INNER JOIN student_assessment ON student.StudentID =
student_assessment.StudentID GROUP BY student.FirstName, student.LastName
Result:
<Student name and ID> CSC72001 Assessment 2 Report Page 7

<Student name and ID> CSC72001 Assessment 2 Report Page 8