Data Management Systems Assignment 1: DTB91001 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 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
<Student name and ID> DTB91001 Assignment 1 Report Page 1

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

Table of contents:
Contents
Table of contents:........................................................................................................................................3
Entity Relationship Diagram.......................................................................................................................5
Part A: Analysis..........................................................................................................................................6
1. Client Business Rules......................................................................................................................6
2. Assumptions Made..........................................................................................................................7
3. Naming Conventions.......................................................................................................................7
4. Data Types Chosen..........................................................................................................................7
Create table Commands.........................................................................................................................17
Part B: Testing Queries.............................................................................................................................23
B.6.1 Workshop Enrolment List......................................................................................................23
B.6.2 Workshop Count List.............................................................................................................23
B.6.3 Student Transcripts................................................................................................................24
B.6.4 Student Assessment Totals....................................................................................................25
Table of figure
Figure 1: ERD...............................................................................................................................................5
Figure 2: Student.........................................................................................................................................7
Figure 3: UNenrol........................................................................................................................................8
Figure 4: St_Ass...........................................................................................................................................9
Figure 5: Course.........................................................................................................................................10
Figure 6: Unit.............................................................................................................................................11
Figure 7: Teacher.......................................................................................................................................12
Figure 8: Workshop...................................................................................................................................13
Figure 9: Course Enrol...............................................................................................................................14
Figure 10: Workshop Enroll.......................................................................................................................14
Figure 11: Unit Offering.............................................................................................................................15
Figure 12: Workshop.................................................................................................................................16
Figure 13: Create teacher..........................................................................................................................17
Figure 14: Create student.........................................................................................................................17
Figure 15: Create workshop.......................................................................................................................18
Figure 16: Create course............................................................................................................................18
Figure 17: Create workShop......................................................................................................................18
Figure 18: Create unit................................................................................................................................18
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 19: Create unit Enrolment.............................................................................................................19
Figure 20: Create Course_ Enrolment........................................................................................................19
Figure 21: Create Unit_Offering................................................................................................................19
Figure 22: Create StudentAss....................................................................................................................20
Figure 23: Create Assessment...................................................................................................................20
Figure 24: Assess.......................................................................................................................................21
Figure 25:Unit............................................................................................................................................21
Figure 26: Course Enrol.............................................................................................................................21
Figure 27: Student.....................................................................................................................................21
Figure 28: workshop..................................................................................................................................22
Figure 29: Unit_offering............................................................................................................................22
Figure 30: Teacher.....................................................................................................................................22
Figure 31: Unit enrol..................................................................................................................................22
Figure 32: workshop..................................................................................................................................22
Figure 33: Course.......................................................................................................................................22
Figure 34: R1..............................................................................................................................................23
Figure 35: R2..............................................................................................................................................24
Figure 36: R3..............................................................................................................................................25
Figure 37: R4..............................................................................................................................................26
<Student name and ID> DTB91001 Assignment 1 Report Page 4

Entity Relationship Diagram
Figure 1: ERD
<Student name and ID> DTB91001 Assignment 1 Report Page 5

Part A: Analysis
1. Client Business Rules
 There is the entire primary key taken which helps to uniquely identify the row.
 There are all the many to many relationships has been removed.
 The foreign are made which are the primary key in their own table and have a one-to-
many relationship.
 Primary keys and foreign keys are defined.

<Student name and ID> DTB91001 Assignment 1 Report Page 6

2. Assumptions Made
The ERD which is present above is of the Student enrolment system which has various one-
to-many-relationships. There are tables design for showing the linked among the tables this
will help to understand more about the system.
3. Naming Conventions
There is a column present which has the names according to their functionalities.
There are tables present which have the names according to their functionalities.
4. Data Types Chosen
Table Student
Figure 2: Student
<Student name and ID> DTB91001 Assignment 1 Report Page 7

Table 1: Student
Attributes Description of Data type Length
StudentID This consists of the integer
which is the primary key
which has a unique ID.
11
Firstname The first name of the student
will be in Varchar
45
LastName The last name of the student
will be in Varchar.
45
Address The address will be in
Varchar.
45
City The city where student live is
in Varchar.
45
State The state where student live is
in Varchar.
45
Postcode The postcode where student
live is in Integer.
11
Phone The postcode where student
live is in Integer.
11
Email The Email of the student will
be in Varchar.
45s
Unit Enrolment
Figure 3: UNenrol
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Table 2: UnitEnrol
Attributes Description of Data type Length
Unitenrolment This consists of the integer
which is the primary key
which has a unique ID.
11
StudentID The ID will be a foreign key
in this table. This is of integer
type.
11
UnitOffering The unit offers is in Varchar 45
Year The year will be in integer
type.
11
Session The session will be in integer
type.
11
Enrolmenttype The Enrolment type wherein
student will enrol in Varchar.
45
final grades The final grade wherein
student will enrol in Varchar.
45
Student_Assessment
Figure 4: St_Ass
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Table 3: Assessment
Attributes Description of Data type Length
AssessmentID This consists of the Varchar
which is the primary key
which have unique ID.
45
StudentID The ID will be a foreign key
in this table. This is of integer
type.
11
DateSubmitted The data type here used is of
date which has the default
type.
No length.
DateSubmitted The data type here used is of
date which has the default
type.
No Length
DaysExtension The day’s extension will be
in integer type.
11
MarkAwarded The marks awarded will be in
integer type.
11
Course
Figure 5: Course
Table 4: Course
Attributes Description of Data type Length
CourseName The coursename where the 45
<Student name and ID> DTB91001 Assignment 1 Report Page 10

student will enrol in Varchar.
CourseCode This consists of the integer
which is the primary key
which has a unique ID.
11
Creditpoints The CreditPoints the student
will get will be in integer.
11
Notes The notes where the student
will enrol in Varchar
45
Unit
Figure 6: Unit
Table 5: Unit
Attributes Description of Data type Length
UnitCode This consists of the varchar
which is the primary key
which has a unique ID.
45
UnitName The where the student will
enrol in Varchar
45
Description The description is in Varchar 45
Prerequisite The prerequisite will be in
Integer
11
Teacher
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Figure 7: Teacher
Table 6: Teacher
Attributes Description of Data type Length
StaffID This consists of the integer
which is the primary key
which has unique ID.
11
Firstname The first name of the staff
will be in Varchar
45
LastName The last name of the staff
will be in Varchar.
45
Campus The campus where the
student will live is in
Varchar.
45
OfficLocation The location where staff lives
are in Varchar.
45
Phone The postcode where student
live is in Integer.
11
Email The Email of the student will
be in Varchar.
45
WorkShop:
<Student name and ID> DTB91001 Assignment 1 Report Page 12