Database Management Project: ISY1002 - Sydney Dance Academy

This database project report details the design and implementation of a database system for the Sydney Dance Academy (SDA). The report begins by listing the entities and attributes, followed by an ER diagram illustrating the relationships between the entities. Part B showcases the database tables with their respective attributes. Part C provides various SQL queries demonstrating data retrieval and manipulation, including queries to select student names and addresses, filter dance styles based on rate, and perform joins to retrieve specific data. The report also includes a personal reflection on the challenges and experiences encountered during the database development process, such as creating complex queries and using inner joins. Finally, a bibliography of the references used is provided.

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

1
DATABASE
Part A
List of entities and attributes
The list of entities for the database are provided below:
 Student
 Branch
 Dance Style
 Instructors
 Time Table
 Rooms
 Enrolment
 Payment
The list of attributes of entities for tables in the database are provided below:
Students
StudentID
LastName
FirstName
Address
Branch
BranchID
BranchLocation
Dance Style

2
DATABASE
StyleID
StyleName
Instructors
InstructorID
InstructorName
Rooms
RoomNumber
Time Table
TimeID
Term
DayOfWeek
BranchID
StartTime
EndTime
RoomNumber
Enrolment
EnrolmentID
TimeID
StudentID
Payment

3
DATABASE
PaymentID
EnrolmentID
Amount
ER Diagram
Part B
Database Tables
Student

4
DATABASE
Branch
Dance Style
Instructors
Time Table

5
DATABASE
Rooms
Enrolment
Payment

6
DATABASE
Table Data
Student
Branch
Dance Style
Instructors

7
DATABASE
Time Table
Rooms
Enrolment

8
DATABASE
Payment
Part C
Query 1
Select CONCAT (FirstName, “ ”,LastName) As StudentName, Address From student;
+----------------+------------------+
| StudentName | Address |
+----------------+------------------+
| Garry Neville | 34 Baylon Street |
| Freddy Perez | 23 Southend Road |
+----------------+------------------+

9
DATABASE
2 rows in set (0.00 sec)
Query 2
SELECT * FROM dancestyle WHERE rate > 12;
MariaDB [danceclass]> SELECT * FROM dancestyle WHERE rate > 12;
+---------+-----------+------+
| StyleID | StyleName | rate |
+---------+-----------+------+
| 1 | Salsa | 600 |
| 2 | Tango | 900 |
+---------+-----------+------+
2 rows in set (0.00 sec)
Query 3
Select * from Student where Number IS NULL;
MariaDB [danceclass]> Select * from Student where Number IS NULL;
+-----------+---------------+------------------+--------+
| StudentID | StudentName | Address | Number |
+-----------+---------------+------------------+--------+
| 1 | Garry Neville | 34 Baylon Street | NULL |
| 2 | Freddy Perez | 23 Southend Road | NULL |
+-----------+---------------+------------------+--------+

10
DATABASE
2 rows in set (0.00 sec)
Query 4
SELECT student.StudentName FROM student INNER JOIN enrolmentid ON enrolmentid.StudentID =
student.StudentID INNER JOIN timetable ON timetable.TimeID = enrolmentid.TimeID INNER JOIN
branch ON branch.BranchID = timetable.BranchID WHERE branch.BranchLocation LIKE '%Hill'
MariaDB [danceclass]> SELECT student.StudentName FROM student INNER JOIN enrolmentid ON
enrolmentid.StudentID = student.StudentID INNER JOIN timetable ON timetable.TimeID =
enrolmentid.TimeID INNER JOIN branch ON branch.BranchID = timetable.BranchID WHERE
branch.BranchLocation LIKE '%Hill';
+---------------+
| StudentName |
+---------------+
| Garry Neville |
+---------------+
1 row in set (0.00 sec)
Query 5
SELECT instructor.InstructorName FROM
instructor INNER JOIN timetable ON instructor.InstructorID = timetable.InstructorID
HAVING COUNT(timetable.TimeID)> 5;
MariaDB [danceclass]> SELECT instructor.InstructorName FROM
-> instructor INNER JOIN timetable ON instructor.InstructorID = timetable.InstructorID

11
DATABASE
-> HAVING COUNT(timetable.TimeID)> 5;
Empty set (0.00 sec)
Query 6
SELECT student.StudentName, SUM(payment.Amount) from student
INNER JOIN enrolmentid ON enrolmentid.StudentID = student.StudentID
INNER JOIN payment ON payment.EnrolmentID = enrolmentid.EnrolmentID
GROUP BY student.StudentName;
+---------------+---------------------+
| StudentName | SUM(payment.Amount) |
+---------------+---------------------+
| Freddy Perez | 750 |
| Garry Neville | 650 |
+---------------+---------------------+
2 rows in set (0.00 sec)
Query 7
SELECT dancestyle.StyleName, COUNT(enrolmentid.StudentID) from student INNER JOIN
enrolmentid ON enrolmentid.StudentID = student.StudentID INNER JOIN timetable ON
timetable.TimeID = enrolmentid.TimeID INNER JOIN dancestyle ON dancestyle.StyleID =
timetable.StyleID
GROUP BY dancestyle.StyleName;
+-----------+------------------------------+