Database Systems (CSC72001) 

Assignment 2 CSC72001 – Database Systems Page 1 of 4
School of Business and Tourism
Database Systems (CSC72001)

A. Task Description
A.1. Overview
Your task is to create and test a database in MySQL using PhpMyAdmin. You are provided
with a scenario and supporting documents, describing the requirements that Southern Cross
University may have for a database to handle their student enrolments. You are provided
with most of the design for such a database and will need to create a relational database to
meet the client needs. You will also need to add sample data and create SQL queries to
provide results suitable for reporting.
A.2 Scenario
Southern Cross University provides a variety of Bachelor degrees to students studying
internally across campuses at Coffs Harbour, Lismore and the Gold Coast, as well as by online
education. Student enrolments and their progress in units need to be stored in a database.
A systems analyst has partially designed this database (see Entity Relationship Diagram
provided). You have been brought into the project to finish creating and testing the database
to support SCU needs. Additional to the ERD, SCU has provided you with some lists and
details about what information needs to be stored for enrolment and recording of student
results. Some of the data for each of these lists has been provided in the file
SCUStudentEnrolment.xlsx, included with this assessment. The client had provided you with
as much as they know about the data requirements. As with most client-provided data, much
information is duplicated in the sample data (the data is not normalised), and the
spreadsheets do not reflect the finished database table design.
You will have to decide on and set the data-types and lengths, as well as finish the design of
the database. In addition to storage of information about students, courses (such as Bachelor
of IT), units (such as CSC72001), staff and workshops, you must allow for the following:
A.2.1 Record student enrolment in a course, including enrolment date;
A.2.2 Record student enrolment in a unit, including the Session and the type of enrolment
(internal or external);
A.2.3 Record student enrolment in a workshop;
A.2.4 Record student assessment submission, with date submitted and marks given.
Assignment 2 CSC72001 – Database Systems Page 2 of 4
B. Assessment Requirements
B.1 Assignment 2 Report 2 marks
Using the ERD supplied and the data requirements provided in the Excel file, you must
analyse the database needs of the client. You should provide an explanation of your
database decisions or data you feel is relevant in your Assignment 1 report. Some suggested
headings for this report are included in the report template (available in this
assignment folder):
 Client Business Rules
 Assumptions Made
 Naming Conventions
 Data types chosen
B.2 MySQL database 5 marks
Create a MySQL database using phpMyAdmin. You must name this database as your
username followed by A2. For example: eyuwon10A2.
You will build the required tables, columns, data types and relationships based on your
analysis. You are free to add any tables you feel are needed or would enhance the system.
You must include, but are not limited to, the client’s specific data requirements. You may
choose to add additional data columns to store other information about students, teachers,
etc if you wish to do so.
B.3 Test Data 1 mark
You must provide enough valid data in your database to run the SQL queries below
successfully with at least 5-10 resulting rows. In particular, association tables will need to be
sufficiently populated to give meaningful test results. You may refer to the provided sample
data for references on the data format. However, you need to analyse and transform the
sample data to be suitable for your database, e.g. you will be required to use your
assumption on some fields that are not available in the sample data.
B.4 Export Script 1 mark
You must create an export script (.sql) to create a backup of all database structures, including
table definitions and data.
B.5 Proof of Testing 1 mark
The results of your report queries should be added to your Assignment 2 report (see B1).
This can be a screen dump of each query result inserted into your report, but MUST be
readable. You should paste each query into your report, then the results of that query.
Assignment 2 CSC72001 – Database Systems Page 3 of 4
B.6 SQL Queries:
B.6.1 Workshop enrolment list 1 mark
Create a query to display the workshop enrolment lists for all students enrolled in
Session 3, 2019. The result should include the session, unit code, unit name, campus
location, workshop day & time, tutor name, student number and student name. The
result should be sorted by the unit code, campus location, workshop day and time
then student last name and first name.
B.6.2 Workshop count list 1 mark
Create a query to display the number of students who were enrolled in workshops in
all sessions for 2019. The result should include the session, unit code, unit name,
campus location, tutor name and the total count of students enrolled. The result
should be sorted by the Session, unit code, campus location and workshop day & time.
B.6.3 Student Transcripts 1 mark
Create a query to display the information required for a Student Transcript. The result
should include the student number, student name, year, session, unit
B.6.4 Student Assessment Totals 2 marks
Create a query to display the total marks for each student’s assessments in all sessions
for 2019. The result should include the student names, unit code, the sum of marks
given for their assessments and the final grade given. It should be sorted by the
student’s last name and first name, session, and unit code. Note: the final grade is
entered by the unit assessor into the database, not calculated automatically from the
total.
C. Other Notes
C.1 Marking Criteria
Marking Criteria will be made available via a rubric on the MySCU website.
C.2 Submission Format
You will be required to submit your assignment materials both on the Infotech server, and via
the MySCU unit site. Your assignment submission should be in the form of a Word document
and a SQL script text file. Your Word document should be named as your username_A2.doc
or username_A2.docx. (eg: eyuwon10_A2.docx) Your SQL script should be named as your
username_A2.sql (eg: eyuwon10_A2.sql).
C.3 Original work
This assignment must be completed individually and must be your own original work.
Exchange of ideas with other people can be considered educationally valuable however
excessive collaboration will be regarded as plagiarism, which is a University offence. For
example, the copying of significant parts of a document (or database) even if subsequently
modified, is plagiarism. Such academic dishonesty will be penalised in accordance with the
University’s rules and regulations. The assessment process may require some students to
attend an interview in order to explain aspects of their assignment.
Assignment 2 CSC72001 – Database Systems Page 4 of 4
C.4 Retain duplicate copy
You are strongly advised to retain a copy of original work, and progressive versions of your
work during the Session. In the event of any uncertainty regarding the submission of
assessment items, you may be requested to reproduce a final copy and/or any previous
versions of your work.
C.5 Penalty for late submission
A penalty of 10% per calendar day will be applied to all late assignments. An extension of time
will only be considered (not necessarily approved) when a written request is submitted with
appropriate supporting documentation and in accordance with University guidelines.
C.6 Marks and Feedback
Marks and feedback comments will be returned through your MySCU site for this unit.

The post Database Systems (CSC72001)  appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms