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...........................................................................................................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..................................................................................................................21
B.6.1 Workshop Enrolment List...........................................................................................21
B.6.2 Workshop Count List..................................................................................................21
B.6.3 Student Transcripts......................................................................................................21
B.6.4 Student Assessment Totals..........................................................................................21
Figure 1: ERD..................................................................................................................................5
Figure 2: Overall structure...............................................................................................................7
Figure 3: Teacher table....................................................................................................................7
Figure 4: Assessment table..............................................................................................................8
Figure 5: Course table......................................................................................................................9
Figure 6: CourseEnrollment table....................................................................................................9
Figure 7: Student table...................................................................................................................10
Figure 8: StudentAssessment table................................................................................................11
Figure 9: Unit table........................................................................................................................11
Figure 10: UnitEnrollment table....................................................................................................12
Figure 11: UnitOffering table........................................................................................................13
Figure 12: Workshop table............................................................................................................13
Figure 13: WorkshopEnrollment table..........................................................................................14
Figure 14: Teacher creation...........................................................................................................15
Figure 15: Assessment creation.....................................................................................................15
Figure 16: Course creation.............................................................................................................15
Figure 17: Course_enrollment creation.........................................................................................16
Figure 18: Student creation............................................................................................................16
Figure 19: Student_assessment creation........................................................................................17
Figure 20: Unit creation.................................................................................................................17
Figure 21: Unit_Enrollment creation.............................................................................................18
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 22: Unit_Offering creation.................................................................................................18
Figure 23: Workshop creation.......................................................................................................19
Figure 24: Worskhop_enrolment creation.....................................................................................19
Figure 25: Teacher Insertion..........................................................................................................20
Figure 26: Assessment Insertion....................................................................................................20
Figure 27: Course Insertion...........................................................................................................20
Figure 28: Course_Enrolment Insertion........................................................................................20
Figure 29: Student Insertion..........................................................................................................21
Figure 30: Student_Assessment Insertion.....................................................................................21
Figure 31: Unit Insertion...............................................................................................................21
Figure 32: Unit_Offering Insertion................................................................................................21
Figure 33: Unit_Enrolment Insertion.............................................................................................22
Figure 34: Workshop Insertion......................................................................................................22
Figure 35: Workshop_Enrolment Insertion...................................................................................22
<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
In the given database there are various primary keys in every table that is present and these
primary keys are used to identify a single column uniquely. Moreover, there are some other
constraints also that include the foreign key that is mentioned in the table and taking reference
from other tables. Relationships are given in the diagram that defines the dependencies of tables
on each other like one-to-many.
2. Assumptions Made
 The student table contains the information regarding the student information where the
primary key defines the unique identification of every student.
 Similarly, in other tables also there are various primary keys that help in the identification
of different column uniquely and no duplicate entry can exist in the primary key.
 Foreign key helps in taking reference from another table and in the diagram, there are
various foreign keys as well.
 Dependencies on each other can be seen with the help of relationships that exists among
the entities.
3. Naming Conventions
There are various conventions that can be used like Singular names of the entities and attributes
are such naming conventions.
4. Data Types Chosen
This is the overall structure of the database that is given below and contains 11 different tables.
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Figure 2: Overall structure
TEACHER:
Figure 3: Teacher table
There are 6 different types of columns present in this table and they are listed as:
StaffID: This is the primary key in this table with the data type varchar and the size that is given
here is 45 so that staff id can be stored.
First Name: This column is used to store the first name only. Here, varchar is used to store the
values with size 45.
Last Name: This is used to store the last name and here also varchar data type is used for storing
the characters with size 45.
<Student name and ID> DTB91001 Assignment 1 Report Page 7

Campus: This is another column where the campus name is stored for locating campus
accurately. Here also data type used is varchar.
Office Location: This column is created for storing the location of the office and the data type
used here also is varchar.
Email: Email column is to store the email address of the teachers and here also character data
type is used so that it can store varchar along with the alphabets.
Phone: This column is used to store the phone number of every teacher and the data type that is
used here is an integer.
ASSESSMENT:
Figure 4: Assessment table
There are 6 different types of columns present in this table and they are listed as:
AssessmentID: The assessment ID is the primary key here with data type varchar and size of 45
to store the id of the assessment.
UnitOfferingID: This is another column that is the foreign key here with the varchar data type
having size 45.
AssessmentName: This is the column that is created for storing the name of assessment with the
data type varchar with size 45.
Description: This column contains the description with data type varchar having a size of 45.
DueDate: This column stores the date and contains the data type of date.
PossibleMarks: This column stores the integer values so the data type used here is an integer
with size 11.
<Student name and ID> DTB91001 Assignment 1 Report Page 8

COURSE:
Figure 5: Course table
There are 4 different types of columns present in this table and they are listed as:
CourseName: Course name is created to store the name of the course and the data type used here
is varchar with size 45.
CourseCode: This is created in order to store the code of the course and it is the primary key of
the table with data type integer having size 11.
CreditPoints: This is created for storing the points so it has the data type of integer with size 11.
Notes: This column is used to store notes and this has a data type of varchar with size 45.
COURSE_ENROLLMENT:
Figure 6: CourseEnrollment table
There are 4 different types of columns present in this table and they are listed as:
StudentID: This column is the primary key that stores the unique ID of every student and has the
data type integer with size 11.
CourseCode: This is used to store the code of course and have the data type of integer with size
11.
EnrolmentDate: This stores date of the enrollment and have a data type of date.
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Status: This stores the status and has a data type of varchar with size 45.
STUDENT:
Figure 7: Student table
There are 9 different types of columns present in this table and they are listed as:
StudentID: This column is the primary key having an integer as its data type with size 11. It is
used to store the unique ID of students.
FirstName: This is used to store the first name of students so it has a data type of varchar with
size 45.
LastName: This column stores the last name of the students with data type varchar only and the
size is also 45.
Address: This column stores the address of the students and has the data type varchar with size
45.
City: This column has city information of the students and has varchar as its data type with size
45.
State: This stores state information of the students and have a varchar data type.
PostCode: This stores postcode of students with data type integer and size 11.
Phone: This stores phone numbers of students with data type integer and size 11.
<Student name and ID> DTB91001 Assignment 1 Report Page 10

Email: This stores email addresses of the students and has varchar data type with 45 sizes.
STUDENT_ASSESSMENT:
Figure 8: StudentAssessment table
AssessmentID: This column is the primary key having the information and unique ID of
students. This has a data type of varchar with size 45.
StudentID: This column stores ID of students and has integer data type with size 11.
DateSubmitted: This column has date data type as it stores the date of the submission.
DaysExtension: This stores the extension days and have an integer data type.
MarksAwarded: It stores marks of the student with an integer data type.
UNIT:
Figure 9: Unit table
UnitCode: This is the primary key as it stores unique code and has data type varchar with size
45.
UnitName: This stores the name of the unit so it also has a varchar data type.
<Student name and ID> DTB91001 Assignment 1 Report Page 11

Description: It stores the description and has data type varchar with size 45.
Prerequisite: This stores integer values so it has data type integer.
UNIT_ENROLLMENT:
Figure 10: UnitEnrollment table
UnitEnrollmentID: This is the primary here and stores the ID and contains data type of varchar
with size 45.
StudentID: This stores the ID of students with the integer data type.
UnitOfferingID: This column has varchar data type which stores information regarding
UnitOffering ID.
Year: As it stores year so it has an integer data type.
Session: This store session information so it also has integer data type with size 11.
EnrolmentType: This columns store the information of the type of enrolment and have varchar
data type with size 45.
FinalGrade: This stores final grades and have varchar data type with size 45.
UNIT_OFFERING:
<Student name and ID> DTB91001 Assignment 1 Report Page 12