Create a relational database to meet the client needs

Assignment Help Database Management System
Reference no: EM132355145

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 and Mastersdegrees 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 Master of IT), units (such as DTB91001), 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.

B. Assessment Requirements

B.1 Assignment 1 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 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
Create a MySQL database using phpMyAdmin. You must name this database as your username followed by A1. For example: eyuwon10A1.
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
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
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
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.

B.6 SQL Queries

B.6.1 Workshop enrolment list
Create a query to display the workshop enrolment lists for all students enrolled in Session 2, 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
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

Create a query to display the information required for a Student Transcript. The result should include the student number, student name, year, session, and unit.

B.6.4 Student Assessment Totals

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.

Attachment:- Data Management Systems.rar

Verified Expert

This is MySQL assignment which was supposed to be completed in phmyy admin.We created the database, inserted the data and then executed 4 queries mentioned in the requirements file.

Reference no: EM132355145

Questions Cloud

Which of the theories do you find the easiest to apply : Facebook is a social media networking service that currently boasts over 1 billion users worldwide. For this assignment, you will analyze the Facebook page.
Explain workplace spirituality and identify a spiritual : Explain workplace spirituality and identify a spiritual organization. What are the characteristics of this spiritual organization?
Manager delegated the task of receiving : Here is a scenario to analyze: A manager delegated the task of receiving, organizing, and posting invoices.
Does supervision play a role in any of the ways you describe : Describe three to five different ways in which a psychologist or counselor may acquire or maintain competence in a specific area of practice.
Create a relational database to meet the client needs : DTB91001 - Data Management Systems - Southern Cross University - Create a relational database to meet the client needs. You will also need to add sample data
How would you incorporate social media into a company : How would you incorporate social media into a company's Search Engine Optimization (SEO) strategy?
How would you use the divisions to continue your education : Explore the divisions of APA. Which would you join? How would you use those divisions to continue your education/training in the field of psychology?
Security manager of a company : What parts of Organizational Behavior do you feel would be the most beneficial as a security manager of a company and why?
Describe how your manager delegates or if you manage : Describe how your manager delegates or if you manage, how you delegate.Book Fundamentals of Management 10th Edition

Reviews

len2355145

8/9/2019 9:58:03 PM

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_A1.doc or username_A1.docx, (e.g.,aalaei10_A1.docx). Your SQL script should be named as your username_A1.sql (e.g., aalaei10_A1.sql).

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd