Database Systems CSC72001: Assessment 2 Student Enrollment Report

This document presents a comprehensive assessment report on a student enrollment database system. The report begins with an overview of the client's business rules, assumptions made during the database design process, and the naming conventions employed. It outlines the data types chosen for various database fields and includes an Entity Relationship Diagram (ERD) to illustrate the database structure. The core of the report focuses on testing queries, providing SQL queries for workshop enrollment lists, workshop count lists, student transcripts, and student assessment totals. Each query is accompanied by its corresponding result, demonstrating the functionality and effectiveness of the database design. The assessment aims to showcase the student's understanding of database design principles, query writing, and database implementation within the context of a student enrollment system at Southern Cross University.

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
0
<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 all the courses in the system and
each course in the system should be identified with a unique identity that would be
helpful in distinguishing the different entries in the tables.
 The database should be able to store the details of all the students each data should be
identified by a unique identification key.
 The database should be able to store the details of the enrolments made by the student.
 The database should be able to store the details of the assessments and the date of
submission of the assessments.
 The database should be able to store the details of the teachers in the system.
 The database should be able to store the details of the workshops which are arranged
for the system.
 The data base should be able to store the details of the student who are to be enrolled
for the workshops in the system.
 The database should be storing the details of the units that are offered by the system
and the enrolment of the students in the unit are to be stored in the system as well.
 The database would be storing the details of the units offered and the different courses
that are included in the system.
 The details of the assessments which are to be performed by the students are also
stored in the system
 The results that the students have obtained from the assessments that have been
performed by the students.
 The system would be storing the details of the results which have been obtained by the
students.
2. Assumptions Made
For the implementation of the database some assumptions have been made. There has
been some assumption about the datatypes as well as the relationships in between the
different type of relationships in between the different tables in the database. It has
been assumed that it is not necessary for all the students to be allocated to a particular
workshop or any workshop for that matter. In addition to this, it is also assumed that a
single student would be offered only a single course and they would be assigned with,
multiple units in which they would appear for the assessments and would obtain the
desired results.
<Student name and ID> CSC72001 Assessment 2 Report Page 5

3. Naming Conventions
To keep consistency, the database will use strict naming conventions that will allow easy
implementation and maintenance of the database when completed. The following
conventions apply:
- Firstly, the singular naming conventions have been used for the database tables and
columns. All the columns and the attributes in the data models have been named
using the singular naming basis.
- Secondly, for naming the entities in the database system plain English terms has
been used and some tables have made of the phrases.
- In addition to this, the camel case notations have been used in the data base of
naming the entities and some of the entities in the system have also been unnamed
using the underscore space. For maintaining the consistency in the database the use
of space in between two words have been avoided.
4. Data Types Chosen
The data types chosen for the implementation of the database are integer for the primary keys
and the foreign keys in the tables. The other fields which include the description of the
datatypes in the system are allocated with the varchar datatypes. The other fields such as date
and time fields have been used in the database for storing the data in the tables. The other
varchar data type has been selected according to the requirement of storing the data in the
database. The length of the data has been varying from 50 to 100.
<Student name and ID> CSC72001 Assessment 2 Report Page 6

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 = 2017 AND unitoffering.Session = 'Session 1'
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
GROUP BY student.LastName, student.FirstName;
Result:
<Student name and ID> CSC72001 Assessment 2 Report Page 7

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
<Student name and ID> CSC72001 Assessment 2 Report Page 8

GROUP BY student.FirstName, student.LastName;
Result:
<Student name and ID> CSC72001 Assessment 2 Report Page 9