Data Management Systems Assignment 1: Southern Cross University

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: Fig 2.................................................................................................................................6
Figure 3: Student Table Structure....................................................................................................7
Figure 4: Assessment.......................................................................................................................9
Figure 5: Course Table Structure...................................................................................................10
Figure 6: Student_Assessment Table Structure.............................................................................11
Figure 7: Teacher Table Structure.................................................................................................12
Figure 8: Unit Table Structure.......................................................................................................13
Figure 9: Unit_Offering Table Structure.......................................................................................13
Figure 10: Workshop Table Structure...........................................................................................14
Figure 11: Workshop_Enrolment Table Structure........................................................................15
Figure 12: Unit_Enrolment table Structure...................................................................................16
Figure 13: Course_enrolment table Structure................................................................................17
Figure 14: Create Student.............................................................................................................18
Figure 15: Create Assessment.......................................................................................................18
Figure 16: Create Course...............................................................................................................18
Figure 17: Create StudentAssessment...........................................................................................18
Figure 18: Create Teacher.............................................................................................................18
Figure 19: Create Unit...................................................................................................................19
Figure 20: Create UnitOffering....................................................................................................19
Figure 21: Create Workshop..........................................................................................................19
Figure 22: Create Workshop_Enrolment.......................................................................................19
Figure 23: Create UnitEnrolment..................................................................................................19
Figure 24: Create CourseEnrolment..............................................................................................19
Figure 25: Insert Student...............................................................................................................20
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 26: Insert Assessment.........................................................................................................20
Figure 27: Insert Course................................................................................................................20
Figure 28: Insert StudentAssessment.............................................................................................20
Figure 29: Insert Teacher...............................................................................................................21
Figure 30: Insert Unit.....................................................................................................................21
Figure 31: Insert UnitOffering.......................................................................................................21
Figure 32: Insert Workshop...........................................................................................................21
Figure 33: Insert WorkshopEnrolment..........................................................................................22
Figure 34: Insert UnitEnrolment....................................................................................................22
Figure 35: Insert CcourseEnrolment..............................................................................................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

Diagram generated by PHPMyAdmin:
Figure 2: Fig 2
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Part A: Analysis
1. Client Business Rules
 Different primary keys are available so that the column can be identified uniquely and no
duplicate entries are possible in primary keys.
 Foreign keys are entered as they take reference from other tables.
 Relationships are building so that dependencies can be given.
2. Assumptions Made
Assumptions include the primary keys that cannot take duplicate entries in them, an error can be
seen otherwise. The foreign keys are given as they take references from the different table where
that specific attribute assigned as the primary key. Different type of relationships is present.
3. Naming Conventions
The naming convention includes that the name of the columns is in Single form as well as the
name of tables.
4. Data Types Chosen
Figure 3: Student Table Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 7

This is the student table where the overall table is comprised of nine different attributes and
every different attribute has different data types. All of them are discussed below:
 StudentID: This is the primary key as there are no duplicate entries possible in this
attribute. This stores only numbers that is why the data type used here is an integer.
 FirstName: This attribute contains special characters and digits and many other characters
so this attribute has a varchar data type and has not null values. This will store the student
first name information.
 LastName: The LastName attribute has varchar data type as it stores many types of data
and contains information regarding the last name of the student.
 Address: This is the attribute where the address is stored and has the data type varchar.
 City: This attribute stores the information about the city of the students and has the data
type varchar.
 State: This attribute stores the information of the state of the students and has the data
type varchar.
 PostCode: This attribute stores the information of Postcode of the students and has the
data type integer.
 Phone: This attribute stores the information on the phone number of the students and has
the data type integer.
 Email: This attribute stores the information of email addresses of the students and has the
data type varchar.
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Figure 4: Assessment
This is the assessment table where the overall table is comprised of six different attributes and
every different attribute has different data types. All of them are discussed below:
 AssessmentID: This is the primary key as there are no duplicate entries possible in this
attribute. This stores characters as well as special characters.
 UnitOfferingID: This is the foreign key here as it is taking reference from another table
named as UnitOffering. This attribute contains special characters and digits and many
other characters so this attribute has a varchar data type and has not null values.
 AssessmentName: The AssessmentName attribute has varchar data type as it stores many
types of data and contains information regarding the assessment name of the student.
 Description: This is the attribute where the description is stored and has the data type
varchar.
 DueDate: This attributes stores the information of Due date and has the data type date as
it just stores date.
 PossibleMarks: This attribute stores the information of possible marks and it can store
only integer so the integer data type is used.
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 5: Course Table Structure
This is the course table where the overall table is comprised of four different attributes and every
different attribute has different data types. All of them are discussed below:
 CourseName: This stores the course name and stores the data type of varchar with size
45.
 CourseCode: This is the primary key as it cannot store duplicate entry and it stores the
code in the form of an integer and has the data type integer.
 CreditPoints: This stores the credit points and has the data type integer with size 11.
 Notes: These stores the notes so it may contain digits as well as characters, therefore, it
has a varchar data type.
<Student name and ID> DTB91001 Assignment 1 Report Page 10

Figure 6: Student_Assessment Table Structure
This is the student assessment table where the overall table is comprised of five different
attributes and every different attribute has different data types. All of them are discussed below:
 AssessmentID: This is the primary key and has a data type of varchar as it can contain
different data types. It is the primary key as it cannot store multiple entries.
 StudentID: This stores id of the students and stores the integer values so the data type
store here is an integer.
 DateSubmitted: The date data type is given here as it only stores the date and contains the
information regarding the date of submission.
 DaysExtension: This data type is used to store values of the days that are extended and
here the days are mentioned so the data type used here is an integer.
 MarksAwarded: The data type store here is an integer as information regarding the marks
is given here.
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Figure 7: Teacher Table Structure
This is the Teacher table where the overall table is comprised of seven different attributes and
every different attribute has different data types. All of them are discussed below:
 StaffID: This is the primary key and has a data type of varchar as it can contain different
data types. It is the primary key as it cannot store multiple entries.
 FirstName: This store's first name of teachers and has a varchar data type.
 LastName: This stores last name of teachers and has a varchar data type.
 Campus: This stores campus details of the teacher with data type varchar as many data
can be entered here.
 OfficeLocation: The stores location of the office and has a varchar data type.
 Email: helps in storing the email of the teachers with the varchar data type.
 Phone: Stores phone number of teachers and has an integer data type.
<Student name and ID> DTB91001 Assignment 1 Report Page 12