ISY1002: Database Management System Report - Sydney Dance Academy

This report details the development and testing of a database for the Sydney Dance Academy (SDA). The assignment focuses on creating and executing a series of SQL queries designed to retrieve specific information from the database. The queries cover various aspects of the SDA's operations, including student data retrieval (names, emails, phone numbers), dance style information, enrolment details, schedule management, instructor data, and payment records. The report presents each query along with its corresponding results, demonstrating the functionality and capabilities of the database system. The queries involve operations like selecting, joining, filtering, grouping, and aggregating data to extract meaningful insights. The provided bibliography includes relevant research on database operators and injection molding processes.

DATABASE
MANAGEMENT
SYSTEM
Sydney Dance Academy

Introduction
• The database has been developed for
the Sydney Dance Academy.
• For testing the database a series of
queries has been designed.
• The queries and the results of the
queries have been described in the
report.

Query 1
• Select CONCAT (First_Name, " ",Last_Name) As StudentName, Email
From student;

Query 2
• SELECT * FROM dance_style WHERE rate > 12;

Query 3
• Select * from Student where Phone_Number IS NULL;

Query 4
• SELECT CONCAT (student.First_Name, " ",student.Last_Name) As
StudentName FROM student INNER JOIN enrolment ON
enrolment.Student_number = student.Student_number INNER JOIN
schedule ON Schedule.class_ID = enrolment.class_ID INNER JOIN
branch ON branch.Branch_number = schedule.Branch_Number WHERE
branch.Branch_Location LIKE '%Hill';

Query 5
• SELECT instructor.Instructor_Name FROM instructor INNER JOIN schedule ON
instructor.Instructor_number = schedule.Instructor_number HAVING
COUNT(schedule.class_ID)> 5;

Query 6
• SELECT CONCAT (student.First_Name, " ",student.Last_Name) As
StudentName, SUM(payment.Amount) from student
• INNER JOIN enrolment ON enrolment.Student_number=
student.Student_Number
• INNER JOIN payment ON payment.Enrolment_Number =
enrolment.Enrolment_Number
• GROUP BY StudentName;

Query 7
• SELECT dancestyle.Style_Name, COUNT(enrolment.Student_NUMBER)
from student INNER JOIN enrolment ON enrolment.Student_NUMBER =
student.Student_NUMBER INNER JOIN schedule ON schedule.class_ID
= enrolment.class_ID INNER JOIN dancestyle ON
dancestyle.Style_Name = schedule.Style_Name
• GROUP BY dancestyle.Style_Name;

Query 8
• SELECT schedule.* from schedule
• Inner join branch on schedule.branch_Number = branch.
Branch_Number
• Where schedule.room_number = 1 OR schedule.room_number = 3
AND branch.branch_Location = 'Chatswood';

Query 9
• SELECT * FROM student WHERE Student_NUMBER NOT IN (SELECT
Student_NUMBER FROM enrolment);
• The query returns an empty set as there are no student who have not been
enrolled in the system.

Query 10
• SELECT * FROM payment where Payment_date > ‘2017-07-01’;