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 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
<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..........................................................................................................................4
Part A: Analysis.............................................................................................................................................6
1. Client Business Rules........................................................................................................................6
2. Assumptions Made.............................................................................................................................6
3. Naming Conventions.........................................................................................................................6
4. Data Types Chosen............................................................................................................................7
Create table Commands...........................................................................................................................14
Part B: Testing Queries................................................................................................................................20
B.6.1 Workshop Enrolment List........................................................................................................20
B.6.2 Workshop Count List...............................................................................................................21
B.6.3 Student Transcripts..................................................................................................................21
B.6.4 Student Assessment Totals......................................................................................................22
Table of figure
Figure 1: ERD................................................................................................................................................5
Figure 2: Studnet............................................................................................................................................7
Figure 3: Course.............................................................................................................................................8
Figure 4: Workshop.......................................................................................................................................9
Figure 5: Unit_Enrolment............................................................................................................................10
Figure 6: Assessment...................................................................................................................................11
Figure 7: Course_Enrolment........................................................................................................................11
Figure 8: Assessment...................................................................................................................................12
Figure 9: Unit...............................................................................................................................................13
Figure 10: Teacher.......................................................................................................................................13
Figure 11: Create Student............................................................................................................................14
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 12: Create Teacher...........................................................................................................................15
Figure 13: Create Course.............................................................................................................................15
Figure 14: Create Workshop........................................................................................................................15
Figure 15: Create Unit.................................................................................................................................15
Figure 16: Create Workshop_Enrolmnet.....................................................................................................16
Figure 17: Create Unit Enrolment...............................................................................................................16
Figure 18: Create Course_ Enrolment.........................................................................................................16
Figure 19: Create Student_Assessment......................................................................................................16
Figure 20: Create Unit_Offering..................................................................................................................16
Figure 21: Create Assessment......................................................................................................................17
Figure 22: Unit.............................................................................................................................................18
Figure 23: Assessment.................................................................................................................................18
Figure 24: Student........................................................................................................................................18
Figure 25: Course Enrolment.......................................................................................................................18
Figure 26: Unit_Offering.............................................................................................................................18
Figure 27: Workshop_ Enrolment...............................................................................................................19
Figure 28: Enrolment...................................................................................................................................19
Figure 29: Teacher.......................................................................................................................................19
Figure 30: Course.........................................................................................................................................19
Figure 31: Workshop...................................................................................................................................19
Figure 32: R1...............................................................................................................................................20
Figure 33: R2...............................................................................................................................................21
Figure 34: R3...............................................................................................................................................22
Figure 35: R4...............................................................................................................................................23
Entity Relationship Diagram
<Student name and ID> DTB91001 Assignment 1 Report Page 4

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.
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.
3. Naming Conventions
Name: Tables of the name are simple and understandable.
Column: Column names are easy and understandable.
<Student name and ID> DTB91001 Assignment 1 Report Page 6

4. Data Types Chosen
Table Student
Figure 2: Studnet
StudentNumber: This is considered as the primary key which will be considered as the
INTEGER data type and the number given to this datatype is 11. As this is primary key it can’t
be NULL therefore either the values are inserted or auto-increment but here in this assessment
there is primary inserted.
FirstName: This will be the name of the student who is having a first name as well as last name
but here only first name will be considered and the data type will be VARCHAR which is of
number 45
LastName: This will be the surname of the student which is in the VARCHAR data type with the
number 45.
Address: The address will be of student that where he/she live so that if any post has to send the
address of there should be available. The database used here is VARCHAR (45)as the address
consists of the street number and the name of the street.
City: The city is the place where the student lives, the data type will be used here is VARCHAR
and the number of character are 45
State: The state of the city where the student lives. The data type of the state will be VARCHAR
and the number of this will be 45
<Student name and ID> DTB91001 Assignment 1 Report Page 7

PostCode: The postcode will be the address where they are living, the data type which is present
for this is INTEGER and the number to this integer is 45.
Email: the Email will of the student so if any type of the message has to send they will send it
with the help of Email and here the Email consist of the data type which is VARCHAR. The
number of Varchar is 45
Course:
Figure 3: Course
CoursesCode: There are various courses there code thy all are separated by the different course
code so they consist of Code. The code is INTEGER which is of number 11.
Course Name: This is the name of course in VARCHAR which is not NULL as this is not the
primary key. The Length of the course will be 45.
CreditPoints: The point which is given to the student will be marked as credit points and this is
in the INTEGER which is in 11 and this is considered as the not NULL as this is not the primary
key.
Notes: This is VARCHAR which is in 45 lengths. This is considered as not NULL as this is not
the primary key.
Workshop:
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Figure 4: Workshop
workshop: There are many workshops so each workshop will have their respective ID and this
will be unique and this will be considered as the primary key. This is the name of course in
VARCHAR which is not NULL as this is not the primary key. The Length of the workshop will
be 45.
UnitOfferingID: This is considered as ID of the unit offering which is of data type VARCHAR
(45) in number and this is not considered as the primary key.
Campus: This is considered as the unit of the campus of the unit offering which is of data type
VARCHAR (45) in number and this is not considered as the primary key.
Location: As there are many workshops and therefore there is VARCHAR with the length of
45. This is considering as not null as there are entries present.
StaffID: This is the foreign key as this is considered as primary in the teacher table. This is
consider as the staffID with the VARCHAR of length 45.
WeekDay: This is the days on which the course will be present. The data type which is used in
the VARCHAR (45) and this is not null.
Classtimes: The time of the course classes will be defined as the class times and the data type is
VARCHAR in the length of 45.
UnitEnrolment:
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 5: Unit_Enrolment
UnitEmrolmentID: This is considered as the primary key which will be considered as the
INTEGER data type and the number given to this datatype is 11. As this is primary key it can’t
be NULL therefore either the values are inserted or auto-increment but here in this assessment
there is primary inserted.
StudentID: This is a foreign key which is the primary key in the student table. This is not NULL
and is 11 in length.
UnitoferringID: This is considered as ID of the unit offering which is of data type VARCHAR
(45) in number and this is not considered as the primary key.
Year: The year will be the enrolment time which has the data type INTEGER and is not NULL.
Session: This will have the Datatype INTEGER which is 11 in number and is considered as not
NULL.
EnrolmentType: This the types to which enrolment have done and these have the VARCHAR
which is of length 45 and is considered as not NULL.
Final Grade: This has the datatype VARCHAR which is of not NULL.
Student_Assessment
<Student name and ID> DTB91001 Assignment 1 Report Page 10

Figure 6: Assessment
AssessmentID: This is considered as the primary key which will be considered as the INTEGER
data type and the number given to this datatype is 11. As this is primary key it can’t be NULL
therefore either the values are inserted or auto-increment but here in this assessment there is
primary inserted.
StudentID: This is a foreign key which is the primary key in the student table. This is not NULL
and is 11 in length.
DateSubmitted: This will be in the format of date which itself is the data type.
DaysExtension: This will be in INTEGER and have the length of 11, this is not NULL.
MarkAwarded: This will be in INTEGER and have the length of 11, this is not NULL.
Course_Enrolment
Figure 7: Course_Enrolment
Student_ID: This is a foreign key which is the primary key in the student table. This is not
NULL and is 11 in length.
<Student name and ID> DTB91001 Assignment 1 Report Page 11

CourseCode: There are various courses there code thy all are separated by the different course
code so they consist of Code. The code is INTEGER which is of number 11.
EnrolmentDate:: This will be in the format of date which itself is the data type.
Status: This is of the data type which is VARCHAR and with the length of 45 and this will be
considered as not NULL.
Assessment
Figure 8: Assessment
AssessmenrID: This is considered as the primary key which will be considered as the INTEGER
data type and the number given to this datatype is 11. As this is primary key it can’t be NULL
therefore either the values are inserted or auto-increment but here in this assessment there is
primary inserted.
UnitOfferingID: This is considered as ID of the unit offering which is of data type VARCHAR
(45) in number and this is not considered as the primary key.
AssessmentName: This is of the data type which is VARCHAR and with the length of 45 and
this will be considered as not NULL.
Description: This is of the data type which is VARCHAR and with the length of 45 and this will
be considered as not NULL.
DueDate: This will be in the format of date which itself is the data type.
PossibleMarks: This will be in INTEGER and have the length of 11, this is not NULL.
<Student name and ID> DTB91001 Assignment 1 Report Page 12