Data Management Systems Assignment 1: Southern Cross University

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:

Assignment 1
REPORT
BY <Insert your name>

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

ERD
ERD of a “Student Enrolment System”
Figure 1: Entity Relationship Diagram

Part A: Analysis
1. Client Business Rules
 StaffID, UnitOfferingID, UnitCode and many other Primary keys are being mentioned in the
entity-relationship diagram above. The main reason behind having the Primary keys is a unique
identification of some of the column values.
 If a table contains a primary key and then there is a similarly named column in some other table
then that column is considered or taken as the Foreign Key, here in this ERD, a number of foreign
keys are mentioned such as StudentID, UnitOfferingID, etc.
 Foreign keys are mainly considered to extract the proper data from the various tables.
 Different relationships are also shown in the ERD to provide a proper understanding of the entire
diagram.
2. Assumptions Made
The proper assumptions are provided above related to the Entity-relationship diagram created
and all the business rules that are compulsorily required are shown properly as well. The
entire diagram is related to a scenario of ‘Student Enrolment System’.
3. Naming Conventions
Naming conventions are considered as some of the protocols used for defining or giving the
names of the columns or anything regarding the entity-relationship diagram. Two of the main
naming conventions used are giving the single name to the columns names as well as the
table name.
4. Data Types Chosen
Figure 2: Design of Student Table

Design of Student Table contains the following details as:
 StudentID is being used as the Primary Key here in this table. It helps in identifying each of the
ID of the students uniquely.
 Other different attributes such as the first name of the Student, the last name of the Student and
various other important attributes are also mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 3: Design of Assessment Table
Design of Assessment Table contains the following details as:
 AssessmentID is being used as the Primary Key here in this table. It helps in identifying each of
the ID of the assessment uniquely.
 Other different attributes such as the name of the Assessment, Description of the Assessment and
various other important attributes are also mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 4: Design of Course Table
Design of Course Table contains the following details as:
 CourseCode is being used as the Primary Key here in this table. It helps in identifying each of the
Code of the Course uniquely.

 Other different attributes such as the name of the Course, credit points and various other
important attributes are also mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 5: Design of Student_Assessment Table
Design of Student_Assessment Table contains the following details as:
 AssessmentID is being used as the Primary Key here in this table. It helps in identifying each of
the ID of the Assessment uniquely.
 Other different attributes such as the ID of the Student, date submitted and various other
important attributes are also mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 6: Design of Teacher Table
Design of Teacher Table contains the following details as:
 StaffID is being used as the Primary Key here in this table. It helps in identifying each of the ID
of the Staff uniquely.
 Other different attributes such as first name, last name, Campus and various other important
attributes are also mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.

Figure 7: Design of Unit Table
Design of Unit Table contains the following details as:
 UnitCode is being used as the Primary Key here in this table. It helps in identifying each of the
Unit code uniquely.
 Other different attributes such as Unit Name, Description and prerequisite are also mentioned in
the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 8: Design of Unit_Offering Table
Design of Unit_Offering Table contains the following details as:
 UnitOfferingID is being used as the Primary Key here in this table. It helps in identifying each of
the ID of offered Units uniquely.
 Other different attributes such as Unit Code, Year and various other attributes are also mentioned
in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.

Figure 9: Design of Workshop Table
Design of Workshop Table contains the following details as:
 WorkshopID is being used as the Primary Key here in this table. It helps in identifying each of the
ID of workshops uniquely.
 Other different attributes such as UnitOfferingID, Campus and various other attributes are also
mentioned in the design of this table.
 UnitOfferingID is the foreign key here.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 10: Design of Workshop_Enrolment Table
Design of Workshop_Enrolment Table contains the following details as:
 WorkshopID is being used as the Primary Key here in this table. It helps in identifying each of the
ID of workshops uniquely.
 StudentID is the foreign key here.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 11: Design of Unit_Enrolment Table

Design of Unit_ Enrolment Table contains the following details as:
 UnitEnrolmentID is being used as the Primary Key here in this table. It helps in identifying each
of the ID of UnitEnrolment uniquely.
 StudentID is the foreign key here.
 Other different attributes such as UnitOfferingID, Years, Sessions and various other attributes are
also mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.
Figure 12: Course_enrolment table Structure
Design of Course_enrolment Table contains the following details as:
 StudentID is being used as the Primary Key here in this table. It helps in identifying each of the
ID of students uniquely.
 Other different attributes such as Course code, Status, and various other attributes are also
mentioned in the design of this table.
 Each of the values has the different appropriate data types associated with them.
 Not Null is the Constraint associated with each of the column names.

Create Tables