A. Task Description
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 enro ments. You are provided with 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 reporing.
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 distance education. Student enrolments and their progress in units need to be stored in a database. A systems analyst has designed this database (see Entity Relationship Diagram provided). You have been brought nto 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 a sessment. The client has 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 (that is, 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 CSC00228), staff and workshops, you must allow for the following:
Record student enrolment in a course, including enrolment date;
Record student enrolment in a unit, including the Session and the type of enrolment (internal or external);
Record student enrolment in a workshop;
Record student assessment submission, with date submitted and marks given.
Assignment 2 Report
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 2 report. Some suggested headings for the first part of this report:
* Client Business Rules
* Assumptions Made
* Naming Conventions
* Data types chosen
MySQL database
Create a MySQL database using phpMyAdmin. You must name this database as your username followed by A2. For example: rmason10A2.
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.
Test Data
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.
Export Script
You must create an export script (.sql) to create a backup of your database, including all database structures, table definitions and data.
Proof of Testing
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.
SQL Queries:
Workshop enrolment list
Create a query to display the workshop enrolment lists for all students enrolled in Session 1, 2015. 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.
Workshop count list
Create a query to display the number of students who were enrolled in workshops in all sessions for 2014. 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.
Student Transcripts
Create a query to display the information required for a Student Transcript. The result should include the student number, student name, year, session, unit
code, unit name and final grade. The result should be sorted by the student's last name, first name, by year, session and then by unit code.
Student Assessment
Create a query to display the total marks for each student's assessments in all sessions for 2014. 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.
