ISY1002 Database Project: Sydney Dance Academy Management System

This project provides a database management system solution for Sydney Dance Academy (SDA), addressing their needs for managing registrations, enrollments, timetabling, room allocations, and payments across multiple locations. The solution includes an ER diagram developed using MS Visio, representing the logical model of the database, along with SQL queries to retrieve and manipulate data. The project identifies entities such as Students, Branches, Dance Styles, Instructors, Rooms, Schedules, Enrollments, and Payments, defining their respective attributes. The SQL queries demonstrate data retrieval, filtering, and aggregation, addressing specific requirements such as retrieving student names and emails, finding dance styles with rates above a certain threshold, and calculating payment sums. The document also includes a personal reflection on the experience of building the database, highlighting challenges faced and solutions found using online resources. The project concludes with a bibliography referencing database systems design and structural database resources.

Running head: DATABASE
Database Management System
Name of the student:
Name of the University:
Author Note

1
DATABASE
Part A
List of entities and attributes
The list of entities and their attributes have been described below:
Students
StudentNumber
LastName
FirstName
Email
Branch
BranchNumber
BranchLocation
Dance Style
StyleName
Rate
Instructors
InstructorNUMBER
InstructorName
Rooms
RoomNumber
Schedule

2
DATABASE
ScheduleNUMBER
Term
DayOfWeek
BranchNumber
StartTime
EndTime
RoomNumber
DanceStyle
Enrolment
EnrolmentNUMBER
ScheduleNUMBER
StudentNUMBER
Payment
PaymentNumber
Mode
EnrolmentNUMBER
Amount

3
DATABASE
ER Diagram

4
DATABASE
Part C
Query 1
Select CONCAT (FirstName, " ",LastName) As StudentName, Email From student;
+----------------+------------------+
| StudentName | Email |
+----------------+------------------+
| Sam Billing | [e-mail] |
| Barry Richards | [e-mail] |
| Kevin Arnolds | [e-mail] |
+----------------+------------------+
3 rows in set (0.00 sec)
Query 2
SELECT * FROM dancestyle WHERE rate > 12;
+-----------+------+
| StyleName | rate |
+-----------+------+
| Salsa | 16 |
| Tango | 19 |
+-----------+------+
2 rows in set (0.00 sec)
Query 3
Select * from Student where Number IS NULL;

5
DATABASE
+---------------+-----------+----------+------------------+--------+
| StudentNumber | FirstName | LastName | Email | Number |
+---------------+-----------+----------+------------------+--------+
| 1 | Sam | Billing | [e-mail] | NULL |
| 2 | Barry | Richards | [e-mail] | NULL |
+---------------+-----------+----------+------------------+--------+
2 rows in set (0.00 sec)
Query 4
SELECT CONCAT (student.FirstName, " ",student.LastName) As StudentName FROM student INNER
JOIN enrolment ON enrolment.StudentNUMBER = student.StudentNUMBER INNER JOIN schedule
ON Schedule.classNUMBER = enrolment.classNUMBER INNER JOIN branch ON
branch.BranchNUMBER = schedule.BranchNUMBER WHERE branch.BranchLocation LIKE '%Hill';
+----------------+
| StudentName |
+----------------+
| Sam Billing |
| Barry Richards |
+----------------+
2 rows in set (0.00 sec)
Query 5
SELECT instructor.InstructorName FROM

6
DATABASE
instructor INNER JOIN schedule ON instructor.InstructorNUMBER = schedule.InstructorNUMBER
HAVING COUNT(schedule.classNUMBER)> 5;
+----------------+
| InstructorName |
+----------------+
| Kevin Spacey |
+----------------+
1 row in set (0.00 sec)
Query 6
SELECT CONCAT (student.FirstName, " ",student.LastName) As StudentName,
SUM(payment.Amount) from student
INNER JOIN enrolment ON enrolment.StudentNUMBER = student.StudentNUMBER
INNER JOIN payment ON payment.EnrolmentNUMBER = enrolment.EnrolmentNUMBER
GROUP BY StudentName;
+----------------+---------------------+
| StudentName | SUM(payment.Amount) |
+----------------+---------------------+
| Barry Richards | 50 |
| Sam Billing | 65 |
+----------------+---------------------+

7
DATABASE
2 rows in set (0.04 sec)
Query 7
SELECT dancestyle.StyleName, COUNT(enrolment.StudentNUMBER) from student INNER JOIN
enrolment ON enrolment.StudentNUMBER = student.StudentNUMBER INNER JOIN schedule ON
schedule.classNUMBER = enrolment.classNUMBER INNER JOIN dancestyle ON dancestyle.StyleName
= schedule.StyleName
GROUP BY dancestyle.StyleName;
+-----------+--------------------------------+
| StyleName | COUNT(enrolment.StudentNUMBER) |
+-----------+--------------------------------+
| Salsa | 2 |
+-----------+--------------------------------+
1 row in set (0.00 sec)
Query 8
SELECT schedule.* from schedule
Inner join branch on schedule.branchNumber = branch. branchNumber
Where schedule.roomnumber = 1 Or schedule.roomnumber = 3 and branch.branchLocation =
‘Chatswood’;
0 row in set (0.00 sec)
Query 9
SELECT * FROM student WHERE StudentNUMBER NOT IN (SELECT StudentNUMBER FROM
enrolment);

8
DATABASE
+---------------+-----------+----------+-----------------+----------+
| StudentNumber | FirstName | LastName | Email | Number |
+---------------+-----------+----------+-----------------+----------+
| 3 | Kevin | Arnolds | [e-mail] | 34568347 |
+---------------+-----------+----------+-----------------+----------+
1 row in set (0.07 sec)
Query 10
SELECT * FROM payment where Pdate > ‘2017-07-01’;
MariaDB [sydneydanceacademy]> SELECT * FROM payment where Pdate > 2017-07-01;
+---------------+-----------------+------+--------+------------+
| PaymentNumber | EnrolmentNumber | Mode | Amount | Pdate |
+---------------+-----------------+------+--------+------------+
| 1 | 1 | Cash | 65 | 2018-06-13 |
| 2 | 2 | Cash | 50 | 2018-07-17 |
+---------------+-----------------+------+--------+------------+
2 rows in set, 1 warning (0.02 sec)
Personal reflection that describes your experience building the database
The ER diagram was developed using the tool MS vision tool so that the logical model of the
database. We faced some difficulties in deriving the queries, which involved the dates. These were
solved with the solution from the internet. The date functions were used for the retrieving the query
for the database. Additionally, the entities and the attributes of the database were identified and the

9
DATABASE
ER diagram were created using the list of entities and attributes. After the databases was created it
was populated with a couple of data for each tables and the query were completed.

10
DATABASE
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Groom, C.R., Bruno, I.J., Lightfoot, M.P. and Ward, S.C., 2016. The CambrNumberge structural
database. Acta Crystallographica Section B: Structural Science, Crystal Engineering and
Materials, 72(2), pp.171-179.