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: Entire Database............................................................................................................................6
Figure 3: Student Table Structure................................................................................................................7
Figure 4: Assessment Table Structure.........................................................................................................7
Figure 5: Course Table Structure.................................................................................................................8
Figure 6: Student_Assessment Table Structure...........................................................................................8
Figure 7: Teacher Table Structure...............................................................................................................9
Figure 8: Unit Table Structure...................................................................................................................10
Figure 9: Unit_Offering Table Structure...................................................................................................10
Figure 10: Workshop Table Structure........................................................................................................11
Figure 11: Workshop_Enrolment Table Structure.....................................................................................11
Figure 12: Unit_Enrolment table Structure................................................................................................12
Figure 13: Course_enrolment table Structure............................................................................................12
Figure 14: Teacher table............................................................................................................................13
Figure 15: Course table.............................................................................................................................13
Figure 16: Unit Table................................................................................................................................13
Figure 17: Assessment table......................................................................................................................13
Figure 18: Workshop table........................................................................................................................14
Figure 19: Course_Enrolment table...........................................................................................................14
Figure 20: Unit_Enrolment table...............................................................................................................14
Figure 21: Student_Assessment table........................................................................................................14
Figure 22: Unit_Offering table..................................................................................................................14
Figure 23: Student Table...........................................................................................................................15
Figure 24: Workshop_Enrolment table......................................................................................................15
Figure 25: Assessment Data......................................................................................................................15
Figure 26: Course_enrolment data.............................................................................................................15
<Student name and ID> DTB91001 Assignment 1 Report Page 3

Figure 27: Student Data.............................................................................................................................15
Figure 28: Student_Assessment data.........................................................................................................16
Figure 29: Unit Data..................................................................................................................................16
Figure 30: Unit_Enrolment Data...............................................................................................................16
Figure 31: Unit_Offering data...................................................................................................................16
Figure 32: Workshop Enrolment data........................................................................................................16
Figure 33: Course Data..............................................................................................................................16
Figure 34: Teacher data.............................................................................................................................16
Figure 35: Workshop Data........................................................................................................................16
Figure 36: Q1............................................................................................................................................17
Figure 37: Q2............................................................................................................................................18
Figure 38: Q 3...........................................................................................................................................19
Figure 39: Q4............................................................................................................................................20
<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
 There are different Primary keys mentioned in the above mentioned ERD in all entities. Primary keys
are mainly being used to identify a particular column uniquely.
 Foreign Keys are also mentioned in various entities that are referenced from different entities.
 Various one to many relationships are also mentioned in the above ERD.
 UnitCode, StaffID, UnitOfferingID, StudentID, AssessmentID etc. are the Primary Keys mentioned.
2. Assumptions Made
The above mentioned assumptions are made on the created Entity Relationship Diagram. The entire client
business rules purely explain the working of the System that is named as the Student Enrolment System.
Different Primary keys are mentioned for uniquely identifying the data in the table whereas foreign keys
are created by referencing different entities that contains the similar named columns. Also, one to many
relationship are mentioned above.
3. Naming Conventions
Different Naming Conventions are being used such as:
 Names of the column is taken as Singular.
 Names of the tables is taken as Singular.
4. Data Types Chosen
Figure 2: Entire Database
<Student name and ID> DTB91001 Assignment 1 Report Page 6

Figure 3: Student Table Structure
The above show table is named as Assessment. This table consists of nine different columns named as
StudentID, FirstName, LastName, Address, City, State, PostCode, Phone and Email. There are two
different data types assigned such as varchar and int. Varchar is the data type used for entering the data in
the form of characters, special character, digits and etc. whereas integer is the data type used for entering
the integer values only. Field size are also mentioned along with the data types. Also, the values could be
null or not is also mentioned in the above table structure. No values in the database could be null.
StudentID here in the above table structure is mentioned as the Primary Key as no duplicate entries could
be made.
Figure 4: Assessment Table Structure
The above show table is named as Assessment. This table consists of six different columns named as
AssessmentID, UnitOfferingID, AssessmentName, Description, DueDate and PossibleMarks. There are
<Student name and ID> DTB91001 Assignment 1 Report Page 7

two different data types assigned such as varchar and int. Varchar is the data type used for entering the
data in the form of characters, special character, digits and etc. whereas integer is the data type used for
entering the integer values only. Field size are also mentioned along with the data types. Also, the values
could be null or not is also mentioned in the above table structure. No values in the database could be
null. AssessmentID here in the above table structure is mentioned as the Primary Key as no duplicate
entries could be made. UnitOfferingID is the foreign key mentioned referenced from Unit_Offered table.
Figure 5: Course Table Structure
The above show table is named as Course. This table consists of six different columns named as
CourseName, CourseCode, CreditPoints and Notes. There are two different data types assigned such as
varchar and integer. Varchar is the data type used for entering the data in the form of characters, special
character, digits and etc. whereas integer is the data type used for entering the integer values only. Field
sizes are also mentioned along with the data types. Field sizes set the limits for entering of the data. Also,
the values could be null or not is also mentioned in the above table structure. No values in the database
could be null. CourseCode here in the above table structure is mentioned as the Primary Key as no
duplicate entries could be made.
Figure 6: Student_Assessment Table Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 8

The above show table is named as Student_Assessment. This table consists of five different columns
named as AssessmentID, StudentID, DateSubmitted, DaysExtension and MarksAwarded. There are three
different data types assigned such as varchar, date and int. Varchar is the data type used for entering the
data in the form of characters, special character, digits and etc. whereas integer is the data type used for
entering the integer values only. Date is mainly being used for the Dates to be entered in the database.
Field sizes are also mentioned along with the data types, date has to limit but contains an appropriate
format. Also, the values could be null or not is also mentioned in the above table structure. No values in
the database could be null. AssessmentID here in the above table structure is mentioned as the Primary
Key as no duplicate entries could be made. StudentID is the foreign key mentioned referenced from
Student table.
Figure 7: Teacher Table Structure
The above show table is named as Teacher. This table consists of seven different columns named as
StaffID, FirstName, LastName, Campus, OfficeLocation, email and Phone. There are two different data
types assigned such as varchar and int. Varchar is the data type used for entering the data in the form of
characters, special character, digits and etc. whereas integer is the data type used for entering the integer
values only. Field sizes are also mentioned along with the data types. Also, the values could be null or not
is also mentioned in the above table structure. No values in the database could be null. StaffID here in the
above table structure is mentioned as the Primary Key as no duplicate entries could be made.
<Student name and ID> DTB91001 Assignment 1 Report Page 9

Figure 8: Unit Table Structure
The above show table is named as Unit. This table consists of four different columns named as UnitCode,
UnitName, and Prerequisite. There are two different data types assigned such as varchar and int. Varchar
is the data type used for entering the data in the form of characters, special character, digits and etc.
whereas integer is the data type used for entering the integer values only. Field sizes are also mentioned
along with the data types. Also, the values could be null or not is also mentioned in the above table
structure. No values in the database could be null. UnitCode here in the above table structure is mentioned
as the Primary Key as no duplicate entries could be made.
Figure 9: Unit_Offering Table Structure
The above show table is named as Unit_Offering. This table consists of six different columns named as
UnitOfferingID, UnitCode, Year, Session, StaffID and Locations. There are two different data types
assigned such as varchar and int. Varchar is the data type used for entering the data in the form of
characters, special character, digits and etc. whereas integer is the data type used for entering the integer
values only. Field sizes are also mentioned along with the data types. Also, the values could be null or not
is also mentioned in the above table structure. No values in the database could be null. UnitOfferingID
<Student name and ID> DTB91001 Assignment 1 Report Page 10

here in the above table structure is mentioned as the Primary Key as no duplicate entries could be made.
UnitCode is the foreign key made referenced from Unit table.
Figure 10: Workshop Table Structure
The above show table is named as Workshop. This table consists of seven different columns named as
WorkshopID, UnitOfferingID, Campus, Location, StaffID, Weekday and Classtimes. There is only one
data type assigned that is varchar. Varchar is the data type used for entering the data in the form of
characters, special character, digits and etc. Field size is also mentioned along with the data type. Also,
the values could be null or not is also mentioned in the above table structure. No values in the database
could be null. WorkshopID here in the above table structure is mentioned as the Primary Key as no
duplicate entries could be made. UnitOfferingID is the foreign key mentioned referenced from
Unit_Offered table.
Figure 11: Workshop_Enrolment Table Structure
The above show table is named as Workshop_Enrolment. This table consists of two different columns
named as WorkshopID and StudentID. There are two different data types assigned such as varchar and
int. Varchar is the data type used for entering the data in the form of characters, special character, digits
<Student name and ID> DTB91001 Assignment 1 Report Page 11

and etc. whereas integer is the data type used for entering the integer values only. Field sizes are also
mentioned along with the data types. Also, the values could be null or not is also mentioned in the above
table structure. No values in the database could be null. WorkshopID here in the above table structure is
mentioned as the Primary Key as no duplicate entries could be made.
Figure 12: Unit_Enrolment table Structure
The above show table is named as Unit_Enrolment. This table consists of seven different columns named
as UnitEnrolmentID, StudentID, UnitOffering, Year, Session, EnrolmentType and FinalGrade. There are
two different data types assigned such as varchar and int. Varchar is the data type used for entering the
data in the form of characters, special character, digits and etc. whereas integer is the data type used for
entering the integer values only. Field sizes are also mentioned along with the data types. Also, the values
could be null or not is also mentioned in the above table structure. No values in the database could be
null. UnitEnrolmentID here in the above table structure is mentioned as the Primary Key as no duplicate
entries could be made.
Figure 13: Course_enrolment table Structure
<Student name and ID> DTB91001 Assignment 1 Report Page 12