Database Management System

This document explains the entities, attributes, ER diagram, and queries of a database management system. It also includes personal reflection and bibliography. The subject and course code are not mentioned.

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.