Data Management Systems Assignment 1: Database Design and Querying

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

Table of contents:
Contents
Table of contents:........................................................................................................................................3
Entity Relationship Diagram........................................................................................................................4
Part A: Analysis............................................................................................................................................5
1. Client Business Rules.......................................................................................................................5
2. Assumptions Made..........................................................................................................................5
3. Naming Conventions........................................................................................................................5
4. Data Types Chosen..........................................................................................................................5
Part B: Testing Queries................................................................................................................................6
B.6.1 Workshop Enrolment List........................................................................................................6
B.6.2 Workshop Count List................................................................................................................6
B.6.3 Student Transcripts..................................................................................................................6
B.6.4 Student Assessment Totals......................................................................................................6
Table of figure
Figure 1: ERD...............................................................................................................................................4
Figure 2: Student table................................................................................................................................5
Figure 3: Course Table.................................................................................................................................6
Figure 4: Teacher Table...............................................................................................................................7
Figure 5: Unit Enrolment Table....................................................................................................................8
Figure 6: Student Assessment.....................................................................................................................9
Figure 7: Workshop...................................................................................................................................10
Figure 8: Unit.............................................................................................................................................11
Figure 9: Workshop Enrolment Table........................................................................................................11
Figure 10: Course Enrolment table............................................................................................................12
Figure 11: Assessment...............................................................................................................................12
Figure 12: Unitoffering..............................................................................................................................13
Entity Relationship Diagram
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 1: ERD
Part A: Analysis
<Student name and ID> DTB91001 Assignment 1 Report Page 4

1. Client Business Rules
 There are different relationship explained such as one to many and here ERD does not consist of
the many to many relationship.
 There are implementation of primary key which will help to have the unique identification.
 The table consist of the ID’s are usually primary key.
 For joining the tables there is implementation of foreign key.
2. Assumptions Made
The ERD is based on the student enrolment system which will help the student to enrol into
the particular subject or course. Therefore in above there is explanation of the key which
are used and the one to many relationship.
3. Naming Conventions
Name of the column: The name of the column will help to get the functionalities of the system.
Name of the tables: the name of the tables will help to understand about the functionalities of the
tables.
4. Data Types Chosen
Figure 2: Database
Table Student
<Student name and ID> DTB91001 Assignment 1 Report Page 5

Figure 3: Student table
Table 1: Student table
Attributes Description
StudentID This will be considering as the primary key
which has to be different and unique. The
primary key is never not NULL because there
is some ID given to them. The data type here
is INTEGER with the length 11.
FirstName: The first name is in the varchar which is of
length 45 and the name of the student will
not be considered are NOT NULL.
LastName: The last name is in the varchar which is of
length 45 and the name of the student will
not be consider are NOT NULL
Address The address of the student will be in varchar
which is of length 45 and the address of the
student will not be considered are NOT
NULL.
City The city where the student lives will be in
varchar which is of length 45 and the city of
the student will not be considered are NOT
NULL.
State The states where the student will be in
varchar which is of length 45 and the state of
the student will not be considered are NOT
NULL.
PostCode The data type here is INTEGER with the
length 11.
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Phone The phone has the data type and here is
INTEGER with the length 11.
Email The Email where the student will get the
messages and this will in varchar which is of
length 45 and the email of the student will
not be considered is NOT NULL.
Course:
Figure 4: Course Table
Table 2: Course Table
Attributes Description
CourseName The name of the course will be in
VARCHAR which is of length 45. This will
be NOT NULL
CourseCode The course codes have the data type which is
INTEGER with the length 11. This will be
considering as the primary key which has to
be different and unique. The primary key
CreditPoints The credit points have the data type which is
INTEGER with the length 11.
Notes The note of the course will be in VARCHAR
which is of length 45. This will be NOT
NULL.
Teacher
<Student name and ID> DTB91001 Assignment 1 Report Page 7

Figure 5: Teacher Table
Table 3: Teacher Table
Attributes Description
StaffID This will be considering as the primary key
which has to be different and unique. The
primary key is never not NULL because there
is some ID given to them. The data type here
is INTEGER with the length 11.
FirstName The first name is in the varchar which is of
length 45 and the name of the student will
not be considered are NOT NULL.
LastName The last name is in the varchar which is of
length 45 and the name of the student will
not be consider are NOT NULL
Campus The campus of the teacher will be in
VARCHAR which is of length 45. This will
be NOT NULL
OfficeLocation The office location of the teacher will be in
VARCHAR which is of length 45. This will
be NOT NULL.
Email The Email where the staff will get the
messages and this will in varchar which is of
length 45 and the email of the student will
not be considered is NOT NULL.
Phone The phone number has the data type which is
INTEGER with the length 11.
UnitEnrolment:
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Figure 6: Unit Enrolment Table
Table 4: Unit Enroment Table
Attributtes Description
UnitEnrolmentID This will be considering as the primary key
which has to be different and unique. The
primary key is never not NULL because there
is some ID given to them. The data type here
is INTEGER with the length 11.
StudentID This will be considering as the foreign key.
The primary key is never not NULL because
there is some ID given to them. The data type
here is INTEGER with the length 11.
UnitOfferingID This will be considering as the primary key
which has to be different and unique. The
primary key is never not NULL because there
is some ID given to them. The data type here
is VARCHAR with the length 45.
Year The year has the data type which is
INTEGER with the length 11.
Session The year has the data type which is
INTEGER with the length 11.
Enrolment Type The enrolment type will be in VARCHAR
which is of length 45. This will be NOT
NULL.
FinalGrade The final grade will be in VARCHAR which
is of length 45. This will be NOT NULL.
Student_Assessment:
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 7: Student Assessment
Table 5: Student Assessment
Attributes Description
AssessmentID This will be considering as the primary key
which has to be different and unique. The
primary key is never not NULL because there
is some ID given to them. The data type here
is INTEGER with the length 11.
StudentID This will be considering as the foreign key.
and primary key is never not NULL because
there is some ID given to them. The data type
here is INTEGER with the length 11.
Date Submitted This will be in the date format which will be
the date of submission of course.
Days Extension The day’s extension has the data type which
is INTEGER with the length 11.
MarkAwarded The marks will be awarded with the data type
which is INTEGER with the length 11.
Workshop:
Figure 8: Workshop
Table 6:Workshop Table
Attributes Description
WorkshopID This will be considering as the primary key
which has to be different and unique. The
<Student name and ID> DTB91001 Assignment 1 Report Page 10

primary key is never not NULL because there
is some ID given to them. The data type here
is INTEGER with the length 11.
UnitOfferingID This will be considering as the foreign key
which has is consider as primary key in
different table. The primary key is never not
NULL because there is some ID given to
them. The data type here is VARCHAR with
the length 45.
Campus The campus of the workshop will be in
VARCHAR which is of length 45. This will
be NOT NULL.
Location The location will be in VARCHAR which is
of length 45. This will be NOT NULL.
StaffID The staffID will be in VARCHAR which is
of length 45. This will be NOT NULL.
WeekDay The weekday will be in VARCHAR which is
of length 45. This will be NOT NULL.
Classtimes The classtime will be in VARCHAR which is
of length 45. This will be NOT NULL.
UNIT
Figure 9: Unit
Table 7: Unit
Attributes Description
UnitCode The unit code of the units will be in
VARCHAR which is of length 45. This will
be NOT NULL.
UnitName The unit name of the units will be in
VARCHAR which is of length 45. This will
be NOT NULL.
Description The description of the units will be in
VARCHAR which is of length 45. This will
be NOT NULL.
Prerequisite This will be with the data type which is
INTEGER with the length 11.
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Workshop_Enrollment
Figure 10: Workshop Enrolment Table
Table 8: Workshop enrolment table
Attributes Description
Workshop_ID: This will be considering as the foreign key
and primary key is workshop table which is
never not NULL because there is some ID
given to them. The data type here is
INTEGER with the length 11.
Student_ID This will be considering as the foreign key
and primary key is student table which is
never not NULL because there is some ID
given to them. The data type here is
INTEGER with the length 11.
Course_Enrolment
Figure 11: Course Enrolment table
Table 9: Course Enrolment Table
Attributes Description
StudentID: This will be considering as the foreign key
and primary key is student table which is
never not NULL because there is some ID
given to them. The data type here is
INTEGER with the length 11.
CourseCode The course codes have the data type which is
INTEGER with the length 11.
EnrolmentDate This will be in the date format.
Status The status of the course enrolment will be in
VARCHAR which is of length 45. This will
<Student name and ID> DTB91001 Assignment 1 Report Page 12