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 understood 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...............................................................................................................................6
1. Client Business Rules...............................................................................................................6
2. Assumptions Made...................................................................................................................6
3. Naming Conventions................................................................................................................6
4. Data Types Chosen..................................................................................................................6
Part B: Testing Queries..................................................................................................................21
B.6.1 Workshop Enrolment List...........................................................................................21
B.6.2 Workshop Count List..................................................................................................21
B.6.3 Student Transcripts......................................................................................................22
B.6.4 Student Assessment Totals..........................................................................................23
Figure 1: ERD..................................................................................................................................5
Figure 2: Structure...........................................................................................................................6
Figure 3: Course..............................................................................................................................7
Figure 4: Teacher.............................................................................................................................8
Figure 5: Student..............................................................................................................................8
Figure 6: Student Assessment..........................................................................................................9
Figure 7: Workshop.......................................................................................................................10
Figure 8: Workshop Enrolment.....................................................................................................10
Figure 9: Unit Enrolment...............................................................................................................11
Figure 10: Course Enrolment.........................................................................................................12
Figure 11: Unit...............................................................................................................................12
Figure 12: Unit Offering................................................................................................................13
Figure 13: Assessment...................................................................................................................14
Figure 1: Course............................................................................................................................14
Figure 2: Teacher...........................................................................................................................15
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 3: Student............................................................................................................................15
Figure 4: Student Assessment........................................................................................................15
Figure 5: Workshop.......................................................................................................................15
Figure 6: Workshop Enrolment.....................................................................................................15
Figure 7: Unit Enrolment...............................................................................................................16
Figure 8: Course Enrolment...........................................................................................................16
Figure 9: UNit................................................................................................................................16
Figure 10: Unit Offering................................................................................................................16
Figure 11: Assessment...................................................................................................................16
Figure 12: Course Data..................................................................................................................17
Figure 13: Teacher Data................................................................................................................17
Figure 14: Student Data.................................................................................................................17
Figure 15: Student Assessment Data.............................................................................................18
Figure 16: Workshop Data.............................................................................................................18
Figure 17: Workshop Enrolment data............................................................................................18
Figure 18: Unit Enrolment data.....................................................................................................19
Figure 19: Course Enrolment data.................................................................................................19
Figure 20: Unit Data......................................................................................................................19
Figure 21: Unit Offering Data.......................................................................................................19
Figure 22: Assessment data...........................................................................................................20
<Student name and ID> DTB91001 Assignment 1 Report Page 4

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

Figure 2: ER
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Part A: Analysis
1. Client Business Rules
There are three major business rules which give a different way to look in the database. These
rules help to involve different primary key which can be defined uniquely. Basically, Foreign
key or primary key plays a major role this help to extract data from other tables. Relationship
between the tables is also an important part.
2. Assumptions Made
The assumption is defined that primary key cannot be the same for other tables .one table
primary key is called another table foreign key. Assumptions are used for extracting data from
one table to another table.
3. Naming Conventions
The naming conversation includes the column which defined the data in a single form for every
table.
4. Data Types Chosen
Figure 3: Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 7

The structure of the table is given here:
Figure 4: Course
This is the course table which has a total of 4 attributes. Which are defined below:
 CourseName: This attribute data type is varchar which contains the entire course name
which is available in the university for the student.
 CourseCode: This is a special key which known as primary key which can be same for
two-course code.
 CreditPoints: This attribute stores the credit points and has the data types which are
integer and size of data type is 11.
 Notes: this attribute is for notes which provide the notes for courses and data type is
varchar.
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Figure 5: Teacher
This is the Teacher table which has a total seven different attributes and every different attribute
has different data types.
 StaffID: This is the primary key which can be same for two staffID and have varchar data
types.
 FirstName: This store's first name of staff and has a varchar data type.
 LastName: This stores the last name of staff and has a varchar data type.
 Campus: This stores campus details of the staff which data type is varchar or many data
can be a store here.
 OfficeLocation: Office location is an attribute of the office staff and data type is varchar.
 Email: email attribute is especially for university staffs which have varchar datatypes.
 Phone: Stores phone number of staff and has an integer data type.
Figure 6: Student
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Here the student table has a total nine attributes which help to define all the important data which
are discussed below.
 StudentID: As the primary key is most important for a table and that’s mean it cannot be
same for any 2 persons.
 FirstName: This attribute defines the name of the student which in only on characters. It
cannot be in numeric.
 LastName: this is same as FirstName and varchar are data types for these attributes.
 Address: Address is an attribute which declares in varchar.
 City: this attributes help to store city information which has varchar data types.
 State: this is also same as City and defines in Varchar DataTypes.
 Postcode: it can be defined in integer because postcode for any city should be in numeric.
 Phone: information has stored in integer data types.
 Email: Email address is a way to contact the student which datatypes are varchar.

Figure 7: Student Assessment
The student assessment table has five different attributes and every different attribute has
different data types. Which are discussed here:
 AssessmentID: This is the primary key and has a data type of varchar which cannot be
same for two assessmentID.
 StudentID: Here studentID has integer data types whose size is 11.
 DateSubmitted: date of submission provides by the teacher to submit the assessment and
date is a data type for DateSubmitted.
<Student name and ID> DTB91001 Assignment 1 Report Page 10

 DaysExtension: This data type is used for extension day which is given by the teacher.
And the data type is an integer.
 MarksAwarded: The data type store here is an integer as information regarding the marks
is given here.
Figure 8: Workshop
The Workshop table has seven different attributes and every attribute has different data types
which are discussed below:
 WorkshopID: This attribute is the primary key which define in varchar and it cannot be
same for two WorkshopID . It is the primary key as it cannot store multiple entries.
 UnitOfferingID: This attribute stores code of the unit and it is foreign key as a reference
from another table and data type is varchar.
 Campus: This attribute stores campus details and data type is varchar.
 Locations: this attributes Stores location information and has a varchar data type.
 StaffID: This attribute has a varchar data type.
 Weekday: this attributes Stores information regarding the weekdays and Data type is
varchar.
 ClassTimes: this attribute Stores information regarding the time of the classes and has a
varchar type of data.
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Figure 9: Workshop Enrolment
The Workshop_Enrolment table has two different attributes which are discussed below.
 WorkshopID: This attribute is the primary key and has a data type of varchar which
cannot be same for two WorkshopID.
 StudentID: This attribute stores information of the student that is the ID and data type is
an integer with size 11.
Figure 10: Unit Enrolment
The Unit_Enrolment table has seven different attributes and every different attribute has different
data types which are discussed below:
 UnitEnrolmentID: This attribute is the primary key and has a data type of varchar and can
not be the same for two UnitEnrollementId.
 StudentID: This attribute stores information of the student that has the integer data type.
 UnitOfferingID: This attribute stores the code of the unit with a varchar type of data.
 Year: This attribute stores year and cannot be defined in another attribute instead of a
number so it has an integer data type.
<Student name and ID> DTB91001 Assignment 1 Report Page 12