Database Systems (CSC72001) Assessment 2: Student Enrollment Report

This report details the design and implementation of a student enrollment database for Southern Cross University, as part of the CSC72001 Database Systems course. The assignment involves creating a relational database using MySQL and PhpMyAdmin, based on provided requirements and an Entity Relationship Diagram (ERD). The report covers the application of business rules, assumptions made during the design process, and the selection of appropriate data types and naming conventions. The student created and tested the database, including the creation of tables for students, courses, teachers, units, and enrollment details. Furthermore, the report includes SQL queries to retrieve specific data, such as student transcripts, workshop enrollments, and assessment results. The assignment aims to demonstrate the student's ability to design, implement, and query a database system to meet the needs of a university student enrollment scenario.

Running head: DATABASE SYSTEMS (CSC72001)
Database Systems (CSC72001)
Name of the Student
Name of the University
Authors note

1DATABASE SYSTEMS (CSC72001)
B1
The business rules for the database
Each student entry will have a unique studentID,
An individua student can enroll to multiple units that are not overlapping with each
other.
Every workshop_enrollment has a student ID.
Any entry in the student assessment table must have the final grades for the unit.
None of the Teachers can be assigned to the workshops that have the overlapping time
schedule.
Assessments should not have submission date after the due date (Student having
extensions can have different scenario)
Assumptions made for this project
For this project, It is assumed that the personnel who will handle the data entry will be
correct one for different fields for the students.
As the tables are normalized thus it is assumed that the database schema will use the
lesser amount of storage.
The Users of the database will not be able to delete any record if there are any foreign
references and they have to be careful in deleting or modifying any record.

2DATABASE SYSTEMS (CSC72001)
The database is developed in order to maintain the consistency of the data.
Changes/modifications knowingly/unknowingly may will lead to inconsistency of the developed
database.
End users of the developed database ae assumed to be able to use the operations in the
database with basic computer knowledge.
Naming Conventions
For the naming of the column name the camelCase naming convention is used. In this
naming conventions each word inside the compound word is made capital. In this convention,
except the first word the initial of the next word is capitalized. In Software /database
development the camelCase is used for writing source code. The camelCase naming convention
is very helpful in developing database or application using programming as element/variable
names does not contain spaces. In this way this convention in the database the column names are
more human readable.
Datatypes selected
Following are the selected datatypes for the tables;
For the names of the students, teachers and descriptionsVARCHAR datatypes are selected. For
the numeric data the INTEGER data type is selected and finally for the date related columns the
DATE datatype is selected.
StudentNumber int

3DATABASE SYSTEMS (CSC72001)
FirstName Text
LastName Text
Address Varchar
City Text
State Text
Postcode Varchar
Email varchar
Phone Varchar
Techer table
StaffID int(11)
FirstName varchar(30)
LastName varchar(30)
Campus varchar(40)
OfficeLocation varchar(20)
Email varchar(45)
Following are some of the table creation and data insertion commands

4DATABASE SYSTEMS (CSC72001)
CREATE TABLE `student` (
`StudentNumber` int(12) NOT NULL,
`FirstName` text CHARACTER SET latin1 NOT NULL,
`LastName` text CHARACTER SET latin1 NOT NULL,
`Address` varchar(75) CHARACTER SET latin1 NOT NULL,
`City` text CHARACTER SET latin1 NOT NULL,
`State` text CHARACTER SET latin1 NOT NULL,
`Postcode` varchar(12) CHARACTER SET latin1 NOT NULL,
`Email` varchar(100) CHARACTER SET latin1 NOT NULL,
`Phone` varchar(15) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Course table

5DATABASE SYSTEMS (CSC72001)
CREATE TABLE `course` (
`CourseCode` int(11) NOT NULL,
`CourseName` varchar(100) CHARACTER SET latin1 NOT NULL,
`CreditPoints` int(11) NOT NULL,
`Notes` varchar(100) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
B.6.1
SELECT DISTINCT(wo.`StudentID`),uo.`Session`,wo.`StudentID`,uo.UnitCode,
u.UnitName, t.FirstName, w.Weekday,w.ClassTimes FROM `unit_enrolment`, unit_offering uo,

6DATABASE SYSTEMS (CSC72001)
unit u,teacher t,workshop_enrolment wo,workshop w WHERE
wo.StudentID=unit_enrolment.StudentID
B.6.2
SELECT COUNT(workshop_enrolment.StudentID), uo.UnitCode,uo.Session,uo.Locations FROM `wo
rkshop_enrolment`, student ,unit_offering uo WHERE uo.Year=2019

7DATABASE SYSTEMS (CSC72001)
B.6.3 Student Transcript
SELECT `StudentNumber`,`FirstName`,`LastName`, Year,ue.Session,u.UnitCode FROM
`student`, unit u,unit_enrolment ue where ue.StudentID=student.StudentNumber.

8DATABASE SYSTEMS (CSC72001)
B.6.4
SELECT SUM(MarkAwarded),s.FirstName,u.UnitCode FROM student_assessment ,
student s, unit_enrolment eo, unit u WHERE s.StudentNumber=student_assessment.StudentID
GROUP BY student_assessment.StudentID

9DATABASE SYSTEMS (CSC72001)