Data Management Systems Assignment 1: Report on Database Design

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
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...........................................................................................................5
Part A: Analysis...............................................................................................................................6
1. Client Business Rules...............................................................................................................6
2. Assumptions Made...................................................................................................................6
3. Naming Conventions................................................................................................................6
4. Data Types Chosen..................................................................................................................6
Part B: Testing Queries..................................................................................................................20
B.6.1 Workshop Enrolment List...........................................................................................20
B.6.2 Workshop Count List..................................................................................................20
B.6.3 Student Transcripts......................................................................................................21
B.6.4 Student Assessment Totals..........................................................................................21
Figure 1: ERD..................................................................................................................................5
Figure 2: Database Structure...........................................................................................................7
Figure 3: Assessment.......................................................................................................................7
Figure 4: Teacher.............................................................................................................................8
Figure 5: Course..............................................................................................................................8
Figure 6: CourseEnrollment table....................................................................................................9
Figure 7: Student..............................................................................................................................9
Figure 8: Student_Assessment.......................................................................................................10
Figure 9: Unit.................................................................................................................................10
Figure 10: Unit_Enrolment............................................................................................................11
Figure 11: Unit_Offering...............................................................................................................11
Figure 12: Workshop.....................................................................................................................12
Figure 13: Workshop_Enrollment.................................................................................................12
Figure 14: Assessment...................................................................................................................13
Figure 15: Teacher.........................................................................................................................13
Figure 16: Course..........................................................................................................................14
Figure 17: Course_enrolment........................................................................................................14
Figure 18: Student creation............................................................................................................14
Figure 19: Student_assessment......................................................................................................15
Figure 20: Unit creation.................................................................................................................15
Figure 21: Unit_Enrollment...........................................................................................................15
Figure 22: Unit_Offering...............................................................................................................16
Figure 23: Workshop.....................................................................................................................16
Figure 24: Worskhop_enrolment...................................................................................................16
Figure 25: Teacher.........................................................................................................................17
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 26: Assessment...................................................................................................................17
Figure 27: Course..........................................................................................................................17
Figure 28: Course_Enrolment........................................................................................................17
Figure 29: Student..........................................................................................................................18
Figure 30: Student_Assessment.....................................................................................................18
Figure 31: Unit Insertion...............................................................................................................18
Figure 32: Unit_Offering...............................................................................................................18
Figure 33: Unit_Enrolment............................................................................................................19
Figure 34: Workshop.....................................................................................................................19
Figure 35: Workshop_Enrolment..................................................................................................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
Business rules in databases contain various rules that need to be specified and should be kept in
mind so these are mentioned here:
 The primary keys that are assigned are used for the unique identification of the columns
and they cannot accept duplicate values in them.
 Relationships are shown by the help of different arrows and it helps in showing the
dependencies of the entities on each other.
 Foreign is used to create in one table when it takes reference from another one.
2. Assumptions Made
 All the tables have unique identification so a primary key is assigned in almost every
entity.
 For different foreign keys, there are taking references from other tables and are
mentioned in the tables.
 Entities relationships display dependencies on each other.
3. Naming Conventions
The naming conventions include:
 Name of the columns is Singular.
 Name of the tables is singular.
4. Data Types Chosen
There are eleven rows that are created and the structure of the whole database is given here:
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Figure 2: Database Structure
TABLES
Assessment:
Figure 3: Assessment
The table Assessment contains six tables from which primary key is AssessmentID and has the
VARCHAR data type. UnitOfferingID is the foreign key with data type VARCHAR and it is
taking reference from UnitOffering table. Assessment name and Description columns have
VARCHAR data type as multiple characters can enter in the columns. Due Date will store date
so it has a date data type and last is possiblemarks which has an integer data type.
<Student name and ID> DTB91001 Assignment 1 Report Page 7

Teacher:
Figure 4: Teacher
This table comprises of seven tables of different data types and StaffID has VARCHAR data
type as multiple types of data can be a store here. This is the primary key that will not accept
duplicate values. These table stores details of the teacher, so the attributes are First name, last
name, campus, office location and email, are there with VARCHAR data type and the phone
column has the integer data type.
Course:
Figure 5: Course
This table has four columns with multiple types of data present, this contains Course Code as its
primary key as there cannot be similar code for every course, it has integer data type along with
the Credit points data type and the name of the course and notes are in the VARCHAR type.
Course_Enrolment:
<Student name and ID> DTB91001 Assignment 1 Report Page 8

Figure 6: CourseEnrollment table
There is four attribute present here in this table that contains multiple data types like integer,
date, and VARCHAR. The student ID is the primary key having the integer data type and Course
code also has the integer data type. The column Enrolment date has the date data type that stored
the date of the enrolment of students and the status of the course enrolment has the VARCHAR
data type.
Student:
Figure 7: Student
Student table has the nine columns with diverse data types and this table stores the information
regarding students. Student ID is unique for every student so it is the primary key and has the
integer data type. The other attributes like the First name, last name, Address, City, State, and
Email has VARCHAR type of data with Post Code and Phone with the integer data type.
Student_Assessment:
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 8: Student_Assessment
This table has five attributes with multiple types of data and Assessment ID is unique so it is
primary key here with VARCHAR data type. The student ID is an integer so it will just store the
numeric values and the date submits column will store date so it has a data type of date. Date
Extension and Marks awarded stored numeric values so they have an integer data type.
Unit:
Figure 9: Unit
This has four attributes with VARCHAR and integer data types from which UnitCode is the
primary key having VARCHAR, unit name and description also has VARCHAR data type and
the prerequisite with an integer data type.
Unit_Enrolment:
<Student name and ID> DTB91001 Assignment 1 Report Page 10

Figure 10: Unit_Enrolment
This has seven tables with UnitEnrolmentID as primary key and data type VARCHAR. A
student ID, Year and Sessions have integer data types. The UnitOfferingID, Enrolment type and
final grade have VARCHAR data type.
Unit_Offering:
Figure 11: Unit_Offering
It has six attributes with UnitOfferingID as the primary key and VARCHAR as its data type.
Unit Code here is the foreign key taking reference from other table having the data type of
VARCHAR. StaffID and Location also have similar data types and the Year and Sessions are of
integer type.
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Workshop:
Figure 12: Workshop
This table has seven attributes with multiple types of data and Workshop ID is unique so it is
primary key here with VARCHAR data type. UnitOfferingID here is the foreign key taking
reference from other table having the data type of VARCHAR. Other attributes have VARCHAR
data types that include Campus, Location, StaffID, Weekday and classtimes.
Workshop_Enrolment:
Figure 13: Workshop_Enrollment
It has two attributes with WorkshopID as primary key with VARCHAR data type and Student ID
has the integer data type that will store the ID of every integer.
<Student name and ID> DTB91001 Assignment 1 Report Page 12