Assignment 2 CSC72001 – Database Systems Page 1 of 4School of Business and TourismDatabase Systems (CSC72001)
Assignment 2: Student Enrolment DatabaseDue Date: 11.30pm Friday Week 7Submission: Upload report (including query results) and SQL script via MySCUWeight: 15% of overall unit assessment
CookMyProjectA. Task DescriptionA.1. OverviewYour task is to create and test a database in MySQL using PhpMyAdmin. You are providedwith a scenario and supporting documents, describing the requirements that Southern CrossUniversity may have for a database to handle their student enrolments. You are providedwith most of the design for such a database and will need to create a relational database tomeet the client needs. You will also need to add sample data and create SQL queries toprovide results suitable for reporting.A.2 ScenarioSouthern Cross University provides a variety of Bachelor degrees to students studyinginternally across campuses at Coffs Harbour, Lismore and the Gold Coast, as well as by onlineeducation. 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 Diagramprovided). You have been brought into the project to finish creating and testing the databaseto support SCU needs. Additional to the ERD, SCU has provided you with some lists anddetails about what information needs to be stored for enrolment and recording of studentresults. Some of the data for each of these lists has been provided in the fileSCUStudentEnrolment.xlsx, included with this assessment. The client had provided you withas much as they know about the data requirements. As with most client-provided data, muchinformation is duplicated in the sample data (the data is not normalised), and thespreadsheets 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 ofthe database. In addition to storage of information about students, courses (such as Bachelorof 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 4B. Assessment RequirementsB.1 Assignment 2 Report 2 marksUsing the ERD supplied and the data requirements provided in the Excel file, you mustanalyse the database needs of the client. You should provide an explanation of yourdatabase decisions or data you feel is relevant in your Assignment 1 report. Some suggestedheadings for this report are included in the report template (available in thisassignment folder): Client Business Rules Assumptions Made Naming Conventions Data types chosenB.2 MySQL database 5 marksCreate a MySQL database using phpMyAdmin. You must name this database as yourusername followed by A2. For example: eyuwon10A2.You will build the required tables, columns, data types and relationships based on youranalysis. 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 maychoose to add additional data columns to store other information about students, teachers,etc if you wish to do so.B.3 Test Data 1 markYou must provide enough valid data in your database to run the SQL queries belowsuccessfully with at least 5-10 resulting rows. In particular, association tables will need to besufficiently populated to give meaningful test results. You may refer to the provided sampledata for references on the data format. However, you need to analyse and transform thesample data to be suitable for your database, e.g. you will be required to use yourassumption on some fields that are not available in the sample data.B.4 Export Script 1 markYou must create an export script (.sql) to create a backup of all database structures, includingtable definitions and data.B.5 Proof of Testing 1 markThe 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 bereadable. You should paste each query into your report, then the results of that query.Assignment 2 CSC72001 – Database Systems Page 3 of 4B.6 SQL Queries:B.6.1 Workshop enrolment list 1 markCreate a query to display the workshop enrolment lists for all students enrolled inSession 3, 2019. The result should include the session, unit code, unit name, campuslocation, workshop day & time, tutor name, student number and student name. Theresult should be sorted by the unit code, campus location, workshop day and timethen student last name and first name.B.6.2 Workshop count list 1 markCreate a query to display the number of students who were enrolled in workshops inall 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 resultshould be sorted by the Session, unit code, campus location and workshop day & time.B.6.3 Student Transcripts 1 markCreate a query to display the information required for a Student Transcript. The resultshould include the student number, student name, year, session, unitB.6.4 Student Assessment Totals 2 marksCreate a query to display the total marks for each student’s assessments in all sessionsfor 2019. The result should include the student names, unit code, the sum of marksgiven for their assessments and the final grade given. It should be sorted by thestudent’s last name and first name, session, and unit code. Note: the final grade isentered by the unit assessor into the database, not calculated automatically from thetotal.C. Other NotesC.1 Marking CriteriaMarking Criteria will be made available via a rubric on the MySCU website.C.2 Submission FormatYou will be required to submit your assignment materials both on the Infotech server, and viathe MySCU unit site. Your assignment submission should be in the form of a Word documentand a SQL script text file. Your Word document should be named as your username_A2.docor username_A2.docx. (eg: eyuwon10_A2.docx) Your SQL script should be named as yourusername_A2.sql (eg: eyuwon10_A2.sql).C.3 Original workThis assignment must be completed individually and must be your own original work.Exchange of ideas with other people can be considered educationally valuable howeverexcessive collaboration will be regarded as plagiarism, which is a University offence. Forexample, the copying of significant parts of a document (or database) even if subsequentlymodified, is plagiarism. Such academic dishonesty will be penalised in accordance with theUniversity’s rules and regulations. The assessment process may require some students toattend an interview in order to explain aspects of their assignment.Assignment 2 CSC72001 – Database Systems Page 4 of 4C.4 Retain duplicate copyYou are strongly advised to retain a copy of original work, and progressive versions of yourwork during the Session. In the event of any uncertainty regarding the submission ofassessment items, you may be requested to reproduce a final copy and/or any previousversions of your work.C.5 Penalty for late submissionA penalty of 10% per calendar day will be applied to all late assignments. An extension of timewill only be considered (not necessarily approved) when a written request is submitted withappropriate supporting documentation and in accordance with University guidelines.C.6 Marks and FeedbackMarks and feedback comments will be returned through your MySCU site for this unit.
Let’s block ads! (Why?)