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

Contents
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................................................................................................................................16
B.6.1 Workshop Enrolment List........................................................................................................16
B.6.2 Workshop Count List...............................................................................................................16
B.6.3 Student Transcripts..................................................................................................................16
B.6.4 Student Assessment Totals......................................................................................................16
List of Figures
Figure 1: ERD.............................................................................................................................................5
Figure 2: Student Table Structure................................................................................................................6
Figure 3: Assessment Table Structure.........................................................................................................7
Figure 4: Course Table Structure.................................................................................................................7
Figure 5: Student_Assessment Table Structure...........................................................................................8
Figure 6: Teacher Table Structure...............................................................................................................8
Figure 7: Unit Table Structure.....................................................................................................................8
Figure 8: Unit_Offering Table Structure.....................................................................................................9
Figure 9: Workshop Table Structure............................................................................................................9
Figure 10: Workshop_Enrolment Table Structure.....................................................................................10
Figure 11: Unit_Enrolment table Structure................................................................................................10
Figure 12: Course_enrolment table Structure............................................................................................10
Figure 13: Table Teacher...........................................................................................................................11
Figure 14: Table Course............................................................................................................................11
Figure 15: Table Unit................................................................................................................................11
Figure 16: Table Assessment.....................................................................................................................11
Figure 17: Table Workshop.......................................................................................................................12
Figure 18: Table Course_Enrolment..........................................................................................................12
Figure 19: Table Unit_Enrolment..............................................................................................................12
Figure 20: Table Student_Assessment.......................................................................................................12
Figure 21: Table Unit_Offering.................................................................................................................12
Figure 22: Student Table...........................................................................................................................13
Figure 23: Table Workshop_Enrolment....................................................................................................13
Figure 24: Assessment Data......................................................................................................................13
Figure 25: Course_enrolment data.............................................................................................................13
Figure 26: Student Data.............................................................................................................................14
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 27: Student_Assessment data.........................................................................................................14
Figure 28: Unit Data..................................................................................................................................14
Figure 29: Unit_Enrolment Data...............................................................................................................14
Figure 30: Unit_Offering data...................................................................................................................14
Figure 31: Workshop Enrolment data........................................................................................................15
Figure 32: Course Data..............................................................................................................................15
Figure 33: Teacher data.............................................................................................................................15
Figure 34: Workshop Data........................................................................................................................15
Figure 35: Result 1....................................................................................................................................16
Figure 36: Result 2.....................................................................................................................................17
Figure 37: Result 3.....................................................................................................................................18
Figure 38: Result 4.....................................................................................................................................19
<Student name and ID> DTB91001 Assignment 1 Report Page 4

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

Part A: Analysis
1. Client Business Rules
 Many entities or the tables in provided ERD contains the proper Primary keys for removing
the identical values.
 Proper relationships between the entities are also mentioned that helps in giving proper idea
regarding the ERD.
 Foreign keys are properly used for joining the two tables and accessing the data easily from
two or many different tables.
2. Assumptions Made
The given ERD is based on student enrolment system that mainly contains the above mentioned
assumptions. Assumptions are mainly used for creating any type of ERD. Proper mentioning of
the Primary Keys, Foreign Keys are the main consideration in the assumptions and that helps in
creating proper ERD.
3. Naming Conventions
Given below are the naming conventions used in this assignment for the creation of the ERD:
 Singular name must be given for the column.
 Singular table must be given for the column.
4. Data Types Chosen
Figure 2: Student Table Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Student table is the table creating for entering the Student values. StudentID, FirstName, LastName,
Address, City, State, PostCode, Phone and Email are the columns mentioned in this table. Varchar is used
as a datatype for storing values such as names or any kind of characters and integer is used for storing the
phone_no, marks etc. StudentID in this table is used for unique values. There is no particular type of
foreign key mentioned.
Figure 3: Assessment Table Structure
Assessment table is the table creating for entering the Assessment values. AssessmentID,
UnitOfferingID, AssessmentName, Description, DueDate and PossibleMarks are the columns mentioned
in this table. Varchar is used as a datatype for storing values such as names or any kind of characters and
integer is used for storing the phone_no, marks etc. AssessmentID in this table is used for unique values.
UnitOfferingID is the particular type of foreign key mentioned.
Figure 4: Course Table Structure
Course table is the table creating for entering the Course values. CourseName, CourseCode,
CreditPoints and Notes are the columns mentioned in this table. Varchar is used as a datatype for storing
values such as names or any kind of characters and integer is used for storing the phone_no, marks etc.
CourseCode in this table is used for unique values. There is no particular type of foreign key mentioned.
<Student name and ID> DTB91001 Assignment 1 Report Page 7

Figure 5: Student_Assessment Table Structure
Student_Assessment table is the table creating for entering the Student_Assessment values.
AssessmentID, StudentID, DateSubmitted, DaysExtension and MarksAwarded are the columns
mentioned in this table. Varchar is used as a datatype for storing values such as names or any kind of
characters and integer is used for storing the phone_no, marks etc. AssessmentID in this table is used for
unique values. StudentID is the particular type of foreign key mentioned.
Figure 6: Teacher Table Structure
Teacher table is the table creating for entering the Teacher values. StaffID, FirstName, LastName,
Campus, OfficeLocation, email and Phone are the columns mentioned in this table. Varchar is used as a
datatype for storing values such as names or any kind of characters and integer is used for storing the
phone_no, marks etc. StaffID in this table is used for unique values. There is no particular type of foreign
key mentioned.
Figure 7: Unit Table Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Unit table is the table creating for entering the Unit values. UnitCode, UnitName, and Prerequisite
are the columns mentioned in this table. Varchar is used as a datatype for storing values such as names or
any kind of characters and integer is used for storing the phone_no, marks etc. UnitCode in this table is
used for unique values. There is no particular type of foreign key mentioned.
Figure 8: Unit_Offering Table Structure
Unit_Offering table is the table creating for entering the Unit_Offering values. UnitOfferingID,
UnitCode, Year, Session, StaffID and Locations are the columns mentioned in this table. Varchar is used
as a datatype for storing values such as names or any kind of characters and integer is used for storing the
phone_no, marks etc. UnitOfferingID in this table is used for unique values. UnitCode is the particular
type of foreign key mentioned.
Figure 9: Workshop Table Structure
Workshop table is the table creating for entering the Workshop values. WorkshopID,
UnitOfferingID, Campus, Location, StaffID, Weekday and Classtimes are the columns mentioned in this
table. Varchar is used as a datatype for storing values such as names or any kind of characters and integer
is used for storing the phone_no, marks etc. WorkshopID in this table is used for unique values.
UnitOfferingID and StaffID are the particular type of foreign keys mentioned.
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 10: Workshop_Enrolment Table Structure
Workshop_Enrolment table is the table creating for entering the Workshop_Enrolment values.
WorkshopID and StudentID are the columns mentioned in this table. Varchar is used as a datatype for
storing values such as names or any kind of characters and integer is used for storing the phone_no, marks
etc. WorkshopID in this table is used for unique values. StudentID is the particular type of foreign key
mentioned.
Figure 11: Unit_Enrolment table Structure
Unit_Enrolment table is the table creating for entering the Unit_Enrolment values. UnitEnrolmentID,
StudentID, UnitOffering, Year, Session, EnrolmentType and FinalGrade are the columns mentioned in
this table. Varchar is used as a datatype for storing values such as names or any kind of characters and
integer is used for storing the phone_no, marks etc. UnitEnrolmentID in this table is used for unique
values. StudentID is the particular type of foreign key mentioned.
Figure 12: Course_enrolment table Structure
Course_enrolment table is the table creating for entering the Course_enrolment values. StudentID,
CourseCode, EnrolmentDate and Status are the columns mentioned in this table. Varchar is used as a
<Student name and ID> DTB91001 Assignment 1 Report Page 10

datatype for storing values such as names or any kind of characters and integer is used for storing the
phone_no, marks etc. StudentID in this table is used for unique values.
Create Table Queries
Figure 13: Table Teacher
Figure 14: Table Course
Figure 15: Table Unit
Figure 16: Table Assessment
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Figure 17: Table Workshop
Figure 18: Table Course_Enrolment
Figure 19: Table Unit_Enrolment
Figure 20: Table Student_Assessment
Figure 21: Table Unit_Offering
<Student name and ID> DTB91001 Assignment 1 Report Page 12