Database Systems Assignment 2 Report

This is a report for Assignment 2 in the Database Systems course. It includes analysis, testing queries, and an entity relationship diagram.

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 student in the system and
identified by their unique student identification numbers. The details of the course
enrolments and the workshop enrolments are also required to be stored in the system.
 The unit records are also stored in the system with unit entity and the unit code being
the identity of each units, which are being used in the data storage function.
 The details of teachers are also required to be stored in the database and the teachers
are to be identified by their staffID allocated to each of them.
 The database also stores the details of the workshop and identify each of the workshops
with their unique Ids.
 The database should be able to store the unit offering details that would store the
details of the units and the teachers who are offering the units. In addition to this, the
assessments of units are to be done with the students and hence the details of the
assessments are to be stored in the database.
2. Assumptions Made
It has been assumed that workshops and the assessments are two different entities for the
system. The gradation are to be done by the assessor and it has been assumed that the
gradation are not to be dependent on the total marks obtained by the students.
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:
- camelCase: The camel case is used for the development of the names for the
database when two phrases are used for the name the second phrase starts with a
Capital letter.
- Hungarian Notations: In the Hungarian notation the two phrased words are
separated by an underscore.
4. Data Types Chosen
There are two types of data are chosen for the database which would help them in storing
the details of the tables in the database. The two database datatypes that are being used in
the database storage system are int and varchar. Int is used for storing the integer values
and the varchar datatype is used for string values in the database. The int values are
generally used for the primary keys in the database.
<Student name and ID> CSC72001 Assessment 2 Report Page 5

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

Part B: Testing Queries
B.6.1 Workshop Enrolment List
Query:
Select unitoffering.Session, unitoffering.UnitCode, unit.UnitName, unitoffering.Locations,
workshop.Weekday, workshop.ClassTimes, workshop.TeacherID, workshop_enrolment.StudentID,
student.FirstName, student.LastName FROM unitoffering
INNER JOIN unit ON unitoffering.UnitCode = unit.UnitCode
INNER JOIN workshop ON workshop.UnitOfferingID = unitoffering.UnitOfferingID
INNER JOIN workshop_enrolment ON workshop.WorkshopID = workshop_enrolment.WorkshopID
INNER JOIN student ON workshop_enrolment.StudentID = student.StudentID
ORDER BY unitoffering.UnitCode, workshop.Weekday, workshop.ClassTimes, student.FirstName,
student.LastName;
Result:
B.6.2 Workshop Count List
Query:
SELECT unitoffering.Session, unitoffering.UnitCode, unit.UnitName, workshop.Location,
teacher.FirstName, teacher.LastName, COUNT(workshop_enrolment.StudentID)
FROM workshop_enrolment
INNER JOIN workshop ON workshop_enrolment.WorkshopID = workshop.WorkshopID
INNER JOIN unitoffering ON unitoffering.UnitOfferingID = workshop.UnitOfferingID
INNER JOIN teacher ON unitoffering.UnitAssesorID = teacher.StaffID
<Student name and ID> CSC72001 Assessment 2 Report Page 7

INNER JOIN unit ON unitoffering.UnitCode = unit.UnitCode
INNER JOIN unit_enrollment ON unit_enrollment.UnitOfferingID = unitoffering.UnitOfferingID
WHERE unit_enrollment.Year = 2014
ORDER BY unitoffering.Session, unitoffering.UnitCode,workshop.Location, workshop.Weekday,
workshop.ClassTimes;
Result:
B.6.3 Student Transcripts
Query:
SELECT student.StudentID, student.FirstName, student.LastName, unitoffering.Year,
unitoffering.Session, unit.UnitCode, unit.UnitName, unit_enrollment.FinalGrade
FROM student
INNER JOIN unit_enrollment ON unit_enrollment.StudentID = student.StudentID
INNER JOIN unitoffering ON unit_enrollment.UnitOfferingID = unitoffering.UnitOfferingID
INNER JOIN unit ON unit.UnitCode = unitoffering.UnitCode
ORDER BY student.FirstName, student.LastName, unitoffering.Year, unit.UnitCode;
Result:
<Student name and ID> CSC72001 Assessment 2 Report Page 8

B.6.4 Student Assessment Totals
Query:
SELECT student.FirstName, student.LastName, unitoffering.UnitCode,
SUM(student_assessment.MarksAwarded), unit_enrollment.FinalGrade
FROM student
INNER JOIN unit_enrollment ON unit_enrollment.StudentID = student.StudentID
INNER JOIN unitoffering ON unit_enrollment.UnitOfferingID = unitoffering.UnitOfferingID
INNER JOIN student_assessment ON student_assessment.StudentID = student.StudentID
GROUP BY student.FirstName, student.LastName
ORDER BY student.FirstName, student.LastName, unitoffering.UnitCode, unitoffering.Session;
Result:
<Student name and ID> CSC72001 Assessment 2 Report Page 9