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................................................................................................................................................5
Figure 2: Database.........................................................................................................................................6
Figure 3: Workshop_Enrolment structure.....................................................................................................7
Figure 4: UnitEnrolment Structure................................................................................................................7
Figure 5:CourseEnrolment structure..............................................................................................................8
Figure 6: Workshop structure........................................................................................................................8
Figure 7: Unitoffering structure.....................................................................................................................9
Figure 8:Unit structure...................................................................................................................................9
Figure 9: Teacher structure..........................................................................................................................10
Figure 10: Student_Assessment structure....................................................................................................10
Figure 11: Student structure.........................................................................................................................11
Figure 12: Assessment structure..................................................................................................................12
Figure 13: Course Structure.........................................................................................................................13
Figure 14: Creation of course table..............................................................................................................14
Figure 15: Creation of teacher table.............................................................................................................14
Figure 16: Creation of unit table..................................................................................................................14
Figure 17: Creation of workshop table........................................................................................................14
Figure 18: Creation of workshop enrolment table.......................................................................................15
Figure 19: creation of Course Enrolment table............................................................................................15
Figure 20: Creation of unit enrolment table.................................................................................................15
Figure 21: Creation of Assessment table.....................................................................................................15
Figure 22:Creation of Student Assessment..................................................................................................15
Figure 23: Creation of Unit Offering table..................................................................................................16
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 24: Creation of Student Table...........................................................................................................16
Figure 25: Assessment insertion..................................................................................................................17
Figure 26: Student Assessment insertion.....................................................................................................17
Figure 27: Insertion of Unit.........................................................................................................................17
Figure 28: Course Enrolment insertion........................................................................................................17
Figure 29: Student insertion table................................................................................................................17
Figure 30: Unit Enrolment insertion............................................................................................................17
Figure 31: Unit offering insertion................................................................................................................18
Figure 32: Workshop enrolment insertion...................................................................................................18
Figure 33: Course insertion..........................................................................................................................18
Figure 34: Teacher insertion........................................................................................................................18
Figure 35: Workshop insertion....................................................................................................................18
Figure 36: R1...............................................................................................................................................20
Figure 37: R2...............................................................................................................................................21
Figure 38: R3...............................................................................................................................................22
Figure 39: R4...............................................................................................................................................23
Entity Relationship Diagram
<Student name and ID> DTB91001 Assignment 1 Report Page 4

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

1. Client Business Rules
 For the different identification of the row there is primary defined which helps to have
the particular row details. The primary key is never considered as the NULL. The
primary key are shown in the above ERD which is represented by the PK.
 There is foreign key build which helps to join the tables. The forgein key in the table is
somewhere primary key in the table.
 All the ID’s are considered as the primary key in the tables.
 There are relationships present which have the proper ERD which are one to one, one to
many etc.
2. Assumptions Made
As here the ERD shows the Student enrolment system which will help to enrol the student, in
business rule which explanation done of the enrolment. There are various tables made and
queries are shown which will help to have the proper database. Here the important
assumption taken that is they have one to many relationship.
3. Naming Conventions
There are tables name which will help to understand the functionality by the table only and this have the
name which is singular. The name of the column are also easy and singular which will help to understand
by the name that what the functionality of the column is.
4. Data Types Chosen
Figure 2: Database
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Workshop_Enrolment
Figure 3: Workshop_Enrolment structure
WorhsopID: This is the primary key which will help to uniquely identify the workshops. This is of
varchar data type of length 45.
StudentID: This is in the integer data type with the length of 11.
UnitEnrolment
Figure 4: UnitEnrolment Structure
UnitEnrolment: This is the primary key which will help to uniquely identify the unit. This is of varchar
data type of length 45.
StudentID: This is in the integer data type with the length of 11.
UnitOfferingID: This is in the integer data type with the length of 11.
Year: This is in the integer data type with the length of 11.
Session: This is in the integer data type with the length of 11.
Enrolment_type: This is the varchar data type with the length of 45.
FinalGrade: This is the varchar data type with the length of 45.
Course Enrolment
<Student name and ID> DTB91001 Assignment 1 Report Page 7

Figure 5:CourseEnrolment structure
StudentID: This is the primary key which will help to uniquely identify the unit. This is of int data type of
length 11.
CourseCode: This is in the integer data type with the length of 11.
EnrolmentDate: This will in the date data type.
Status: This is the varchar data type with the length of 45.
Workshop:
Figure 6: Workshop structure
WorkshopID: This is the primary key which will help to uniquely identify the unit. This is of varchar
data type of length 45.
UnitOffering: This is the foreign key with the varchar type of length 45.
Campus: campus is varchar data type with the length of 45.
Location: Location is the varchar data type with the length of 45.
StaffID: Staffid is in varchar data type with the length of 45.
Weekday: Weekday in varchar data type with the length of 45.
Classtimes: classtime is in varchar data type with the length of 45.
UnitOffering
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Figure 7: Unitoffering structure
UnitOffering: This is the primary key which will help to uniquely identify the unit. This is of varchar data
type of length 45.
UnitCode: This is the foreign key with the varchar type of length 45.
Year: year is in integer data type with the length of 11.
Session: session integer data type with the length of 11.
StaffID: staffid in varchar data type with the length of 45.
Location: Location is in varchar data type with the length of 45.
Unit
Figure 8:Unit structure
UnitCode: Unit code is the primary key which will help to uniquely identify the unit. This is of varchar
data type of length 45.
UnitName: unit name is in varchar data type with the length of 45.
Description: Description is in varchar data type with the length of 45.
Prerequisite: Prerequisite integer data type with the length of 11.
Teacher
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 9: Teacher structure
StaffId: This is the primary key which will help to uniquely identify the teachers. This is of varchar data
type of length 45.
FirstName: First name of the teacher is in varchar data type with the length of 45.
LastName: Last name of the teacher is in varchar data type with the length of 45.
Campus: campus is varchar data type with the length of 45.
OfficeLocation: Location is the varchar data type with the length of 45.
Email: Email is in varchar data type with the length of 45.
Phone: Phone is in integer data type with the length of 11.
Student Assessment
Figure 10: Student_Assessment structure
AssessmentID: This is the primary key which will help to uniquely identify the assessment This is of
varchar data type of length 45.
StudentID: Phone is in integer data type with the length of 11.
Date Submitted: The format of the date will be in the data type named as Date.
<Student name and ID> DTB91001 Assignment 1 Report Page 10

DaysExtension: Day Extension is in integer data type with the length of 11.
MarkAwarded: Mark awards is in integer data type with the length of 11.
Student snipping
Figure 11: Student structure
StudentID: This is the primary key which will help to uniquely identify the student. This is of varchar
data type of length 45.
FiestName: First name of the student is in varchar data type with the length of 45.
LastName: Last name the student is in name of varchar data type with the length of 45.
Address: Address of the student is in name of varchar data type with the length of 45.
City: City from where the student is in name of varchar data type with the length of 45.
State: State of the student is in name of varchar data type with the length of 45.
PostCode: Post code is in integer data type with the length of 11.
Phone: Phone is in integer data type with the length of 11.
Email: Email of the student is in varchar data type with the length of 45.
Assessment
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Figure 12: Assessment structure
AssessmentID: This is the primary key which will help to uniquely identify the student. This is of
varchar data type of length 45.
UnitOffering: unit offering of the student is in varchar data type with the length of 45.
AssessmentName: name of assessment of the student is in varchar data type with the length of 45.
Description: description of the assessment for the student is in varchar data type with the length of 45.
DueDate: the due date of the student will be in date format.
PossibleMarks: marks the student will get is in integer data type with the length of 11.
Course
Figure 13: Course Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 12