ISY1002 Database Management Project: Sydney Dance Academy System

This project outlines the development of a database management system for the Sydney Dance Academy, addressing their needs for managing registrations, enrollments, timetabling, room allocations, and payments across multiple Sydney locations. The project includes an ER diagram illustrating the entities (Students, Branches, Dance Styles, Instructors, Rooms, Time Table, Enrollment, Payment) and their attributes, followed by the implementation of SQL queries to retrieve and manipulate data within the database. These queries demonstrate various functionalities such as selecting student information, filtering dance styles based on rates, identifying students without phone numbers, joining tables to retrieve related data, grouping and aggregating data for reporting, and querying based on date ranges. A personal reflection discusses the development process, challenges faced, and resources utilized. Desklib provides access to similar assignments and past papers for students.

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 entities and the attributes of the Sydney Dance Academy database has been listed
below:
Students
Student_Number
Last_Name
First_Name
Email
Phone_Number
Branch
Branch_Number
Branch_Location
Dance Style
Style_Name
Rate
Instructors
Instructor_Number
Instructor_Name
Rooms

2
DATABASE
RoomNumber
Time Table
Class_ID
Term
Week_Day
Branch_Number
Start_Time
End_Time
Instructor_Number
Style_Name
Room_Number
Enrolment
Enrolment_Number
Class_ID
Student_Number
Payment
Payment_Number
Payment_Mode
Enrolment_Number
Amount

3
DATABASE
Payment_Date
ER Diagram
Part C
Query 1
Select CONCAT (First_Name, " ",Last_Name) As StudentName, Email From student;
+-----------------+-----------------+
| StudentName | Email |

4
DATABASE
+-----------------+-----------------+
| Steve Richard | [e-mail] |
| Owen Hargreaves | [e-mail] |
| Kelly Baines | [e-mail] |
+-----------------+-----------------+
3 rows in set (0.00 sec)
Query 2
SELECT * FROM dance_style WHERE rate > 12;
+-----------------+------+
| Style_Name | rate |
+-----------------+------+
| Bachata | 16 |
| Ball Room Dance | 19 |
| Tango | 19 |
+-----------------+------+
3 rows in set (0.00 sec)
Query 3
Select * from Student where Phone_Number IS NULL;
+----------------+------------+------------+-----------------+--------------+
| Student_Number | First_Name | Last_Name | Email | Phone_Number |

5
DATABASE
+----------------+------------+------------+-----------------+--------------+
| 1 | Steve | Richard | [e-mail] | NULL |
| 2 | Owen | Hargreaves | [e-mail] | NULL |
| 3 | Kelly | Baines | [e-mail] | NULL |
+----------------+------------+------------+-----------------+--------------+
3 rows in set (0.00 sec)
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';
+---------------+
| StudentName |
+---------------+
| Steve Richard |
+---------------+
1 row in set (0.00 sec)
Query 5
SELECT instructor.Instructor_Name FROM
instructor INNER JOIN schedule ON instructor.Instructor_number = schedule.Instructor_number
HAVING COUNT(schedule.class_ID)> 5;

6
DATABASE
+-------------------+
| Instructor_Name |
+-------------------+
| Henry Springfield |
+-------------------+
1 row in set (0.00 sec)
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;
+-----------------+---------------------+
| StudentName | SUM(payment.Amount) |
+-----------------+---------------------+
| Kelly Baines | 75 |
| Owen Hargreaves | 50 |
| Steve Richard | 35 |
+-----------------+---------------------+
3 rows in set (0.00 sec)

7
DATABASE
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;
+------------+---------------------------------+
| Style_Name | COUNT(enrolment.Student_NUMBER) |
+------------+---------------------------------+
| Bachata | 2 |
| Tango | 1 |
+------------+---------------------------------+
2 rows in set (0.00 sec)
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';
+----------+------+-----------+---------------+------------+----------+-------------+-------------------+-----------------+
| Class_ID | Term | Week_Day | Branch_Number | Start_Time | End_Time | Room_Number |
Instructor_Number | Style_Name |

8
DATABASE
+----------+------+-----------+---------------+------------+----------+-------------+-------------------+-----------------+
| 1 | 1 | Monday | 1 | 15:00:00 | 17:00:00 | 1 | 2 | Bachata |
| 2 | 1 | Wednesday | 3 | 08:00:00 | 12:00:00 | 3 | 1 | Tango |
| 3 | 2 | Thursday | 2 | 13:00:00 | 17:00:00 | 1 | 2 | Bachata |
| 4 | 2 | Tuesday | 3 | 15:00:00 | 17:00:00 | 3 | 2 | Tango |
| 5 | 1 | Thursday | 2 | 10:00:00 | 12:00:00 | 1 | 2 | Ball Room Dance |
| 6 | 2 | Tuesday | 1 | 16:00:00 | 17:00:00 | 1 | 2 | Ball Room Dance |
| 8 | 1 | Tuesday | 1 | 09:00:00 | 10:00:00 | 1 | 2 | Tango |
| 9 | 1 | Wednesday | 3 | 06:00:00 | 09:00:00 | 1 | 3 | Bachata |
+----------+------+-----------+---------------+------------+----------+-------------+-------------------+-----------------+
8 rows in set (0.00 sec)
Query 9
SELECT * FROM student WHERE Student_NUMBER NOT IN (SELECT Student_NUMBER FROM
enrolment);
Empty set (0.09 sec)
Query 10
SELECT * FROM payment where Payment_date > ‘2017-07-01’;
+----------------+------------------+--------------+--------+--------------+
| Payment_Number | Enrolment_Number | Payment_Mode | Amount | Payment_date |
+----------------+------------------+--------------+--------+--------------+
| 2 | 2 | Online | 50 | 2018-12-27 |

9
DATABASE
| 3 | 3 | Cash | 75 | 2018-12-15 |
+----------------+------------------+--------------+--------+--------------+
2 rows in set (0.00 sec)
Personal reflection that describes your experience building the database
The development of the database has been started from the logical aspect. For the
development of the database the logical diagram was designed. The ER diagram was helpful in the
realisation process of the entities and the attributes of the system. The ER Diagram has been
developed on the MS Visio tool. The relationships and the attributes for the system has been
identified efficiently by the diagram. However some difficulties were faced during the deployment of
the database resulting from the ER diagram for the database. However the tutorials from the
internet were referred for the foreign key constrains and the problem was solved efficiently.

10
DATABASE
Bibliography
Kopaczyk, J., Molineaux, B., Karaiskos, V., Alcorn, R., Los, B. and Maguire, W., 2018. Towards a
grapho-phonologically parsed corpus of medieval Scots: Database design and technical solutions1.
Corpora.
Pejathaya, K., Talluri, G. and Bhide, A.S., Tata Consultancy Services Ltd, 2017. Foreign key
identification in database management systems. U.S. Patent 9,552,379.