DTB91001 Data Management Systems: Semester 1, 2024 Assignment Report

Desklib provides solved assignments and past papers to help students succeed.

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.
Student Name:
Student ID No.:
Unit Name: Data Management Systems
Unit Code: DTB91001
Tutor’s name:
Assignment No.: Assignment 1
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> DTB91001 Assignment 1 Report Page 1

Assignment 1
REPORT
BY <Insert your name>
<Student name and ID> DTB91001 Assignment 1 Report Page 2

Contents
Entity Relationship Diagram...........................................................................................................5
Part A: Analysis...............................................................................................................................7
1. Client Business Rules...............................................................................................................7
2. Assumptions Made...................................................................................................................7
3. Naming Conventions................................................................................................................7
4. Data Types Chosen..................................................................................................................7
Part B: Testing Queries..................................................................................................................23
B.6.1 Workshop Enrolment List...........................................................................................23
B.6.2 Workshop Count List..................................................................................................24
B.6.3 Student Transcripts......................................................................................................24
B.6.4 Student Assessment Totals..........................................................................................25
Figure 1: ERD..................................................................................................................................5
Figure 2: Course..............................................................................................................................7
Figure 3: Assessment.......................................................................................................................7
Figure 4: Teacher.............................................................................................................................8
Figure 5: Student_Assessment.........................................................................................................8
Figure 6: Unit_Offering...................................................................................................................9
Figure 7: Student..............................................................................................................................9
Figure 8: Unit.................................................................................................................................10
Figure 9: Course_enrolment..........................................................................................................10
Figure 10: Workshop.....................................................................................................................11
Figure 11: Unit_Enrolment............................................................................................................11
Figure 12: Workshop_Enrolment..................................................................................................12
Figure 13: Course..........................................................................................................................13
Figure 14: Assessment...................................................................................................................13
Figure 15: Teacher.........................................................................................................................13
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 16: StudentAssessment.......................................................................................................13
Figure 17: UnitOffering................................................................................................................14
Figure 18: Student.........................................................................................................................14
Figure 19: Unit...............................................................................................................................14
Figure 20: CourseEnrolment..........................................................................................................14
Figure 21: Workshop.....................................................................................................................15
Figure 22: UnitEnrolment..............................................................................................................15
Figure 23: Workshop_Enrolment..................................................................................................15
Figure 24: Course..........................................................................................................................16
Figure 25: Assessment...................................................................................................................16
Figure 26: Teacher.........................................................................................................................16
Figure 27: StudentAssessment.......................................................................................................16
Figure 28: UnitOffering.................................................................................................................16
Figure 29: Student..........................................................................................................................17
Figure 30: Unit...............................................................................................................................17
Figure 31: CourseEnrolment..........................................................................................................17
Figure 32: Workshop.....................................................................................................................17
Figure 33: UnitEnrolment..............................................................................................................17
Figure 34: WorkshopEnrolment....................................................................................................18
Figure 35: Result 1.........................................................................................................................19
Figure 36: Result 2.........................................................................................................................20
Figure 37: Result 3.........................................................................................................................21
Figure 38: Result 4.........................................................................................................................22
<Student name and ID> DTB91001 Assignment 1 Report Page 4

Entity Relationship Diagram
ERD of a “Student Enrolment System”
Figure 1: ERD
<Student name and ID> DTB91001 Assignment 1 Report Page 5

Part A: Analysis
1. Client Business Rules
There are various rules related to the database and they are:
 There are various constraints like foreign keys and primary keys that are added in the
diagram and the primary key is added when the column need to be identified uniquely.
 Foreign key constraint occurs when one attribute exists as the primary key and in another
entity, it is taking reference then it is assigned as a foreign key.
 One-to-many relationships and one-to-one relationships connect entities with each other
so that dependencies of entities on each other can be seen clearly.
2. Assumptions Made
 Assumptions are made during the creation of the diagram so they are discussed here. The
first assumption is that in every table there is a primary key that differentiates the column
and in that duplicate entries cannot exist.
 Foreign keys are created as they take references from other tables and in another table,
they exist as the primary key.
 There are many relationships and they define the relationships among the entities.
 There can be any relationship like one-to-many and one-to-one relationships as well.
3. Naming Conventions
There are various naming conventions such as:
Names are singular for the tables as well as for columns that exist. The prefix of schema name
for tables and Pascal casing as well in which upper camel case is used.
<Student name and ID> DTB91001 Assignment 1 Report Page 6

4. Data Types Chosen
Figure 2: Course
The above structure is of table Course where all the information regarding the course is stored
and the primary key attribute here is CourseCode with data type integer and along with this
CreditPoint attribute also has data type integer. Other two attributes named as CourseName and
Notes have the data type varchar. And there is no foreign key assigned here.
Figure 3: Assessment
The given structure is of Assessment table as it stores the data regarding the assessment. Here,
there are six different attributes, from which AssessmentID is assigned as primary key and
UnitOfferingID is assigned as foreign key as this attribute is taking reference from table
UnitOffering where it is assigned as the primary key. For every attribute there are different data
types, for attributes AssessmentID, UnitOfferingID, AssessmentName and Description the data
<Student name and ID> DTB91001 Assignment 1 Report Page 7

type is varchar and for DueDate attributes, it is assigned as the date and PossibleMarks stores
only numbers so it is assigned as an integer data type.
Figure 4: Teacher
The table structure is given here where the data that is stored is related to the teacher in which
StaffID is assigned as primary key with the data type varchar as it stores multiple types of data
and is primary key as it cannot store duplicate values. Other attributes like the First name, Last
name, Campus, office location and Email also has the varchar data type as they contain multiple
types of data. The attribute phone has an integer data type as it stores the numeric values only.
Figure 5: Student_Assessment
The given structure is of table StudentAssessment where different attributes are present along
with different data types. There are five different attributes from which AssessmentID is
assigned as the primary key as there is no possibility of having duplicate entries in this attribute,
<Student name and ID> DTB91001 Assignment 1 Report Page 8

every entry will be unique. This attribute has data type varchar as it stores the characters, digits
or special characters. The attribute StudentID, DaysExtension, MarkAwarded have data type
integers as they just store the numeric values. The attribute DateSubmitted is the only one
attribute here that stores the date and therefore, has the date data type.
Figure 6: Unit_Offering
The table above is UnitOffering table that contains six different attributes among which
UnitOfferingID is primary key with data type varchar. UnitCode here is foreign key as it is
taking reference from UnitCode table, this also has a data type of varchar. Including these, the
other two attributes also have a varchar data type that is named as StaffID and Locations. Year
and Session attributes have integer data type as they only store numeric values.
Figure 7: Student
<Student name and ID> DTB91001 Assignment 1 Report Page 9

The given table is the student table where the information of the students is stored. There are
overall nine different attributes in which different data is stored regarding the student. These are
in different data type as they are storing different type of values.
StudentID is assigned as the primary key as it stores a unique value of the ID of every student
that is unique. Because it stores only numbers so the data type is an integer with the storage size
of 11.
Other columns that are named as the First name, Last name, Address, City, State, and Email are
of varchar data types as they stores the characters as well as other values. The attributes
PostCode and Phone stores integer values as here data get stored only in the form of numbers.
Figure 8: Unit
The above table is of Unit, it contains 4 different columns and two different data types that are
varchar and integer. For inserting multiple types of data, varchar is used and for entering only
numeric values, an integer data type is used. For every attribute along with their respective data
types, their field size along with their value of null or not null is also given. For attributes
UnitCode, UnitName and description, the data type used is varchar and for prerequisite, the data
type is an integer.
<Student name and ID> DTB91001 Assignment 1 Report Page 10

Figure 9: Course_enrolment
The given table is of CourseEnrolment that stores different values and have multiple attributes
namely StudentID, CourseCode, EnrolmentDate, and Status. All these attributes have multiple
data types with different fields. The attribute StudentID is primary key as every entry in this
attribute is unique. It has integer data type because it can store only numeric entries and along
with this CourseCode attribute also has an integer data type. The attribute status has varchar data
type as it can store data of multiple types. At last the attribute EnrolmentDate has a date data
type that can only store date.
Figure 10: Workshop
The table workshop is given above that comprised of seven different attributes with different
information that they store. The names of the attributes are WorkshopID, UnitOfferingID,
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Campus, Location, StaffID, Weekday, Classtimes. All these attributes have varchar data type as
they all stores multiple types of data like characters, digits or special characters. Here
UnitOfferingID is taking reference from UnitOffering table so here it is the foreign key.
Figure 11: Unit_Enrolment
UnitEnrollment table is given here that contains seven attributes with different data types and
information. The UnitEnrollmentID is the primary key here as for every here store is unique; this
attribute has a varchar data type. StudentID is another attribute with data type integer as it just
stores the numeric values. UnitOfferingID, EnrolmentType, and FinalGrade attributes are having
varchar data type as they all can store multiple types of data but attributes StudentId, Year and
Session stores numeric values so they have an integer data type.
Figure 12: Workshop_Enrolment
<Student name and ID> DTB91001 Assignment 1 Report Page 12