Database Management System
VerifiedAdded on 2023/04/23
|11
|1413
|497
AI Summary
This document explains the entities, attributes, ER diagram, and queries related to Database Management System. It also includes personal reflection and bibliography. Subject: Computer Science, Course Code: CS101, Course Name: Introduction to Databases, College/University: Not mentioned.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
Running head: DATABASE
Database Management System
Name of the student:
Name of the University:
Author Note
Database Management System
Name of the student:
Name of the University:
Author Note
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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
DATABASE
Part A
List of entities and attributes
The list of entities and their attributes have been described below:
Students
StudentNumber
LastName
FirstName
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
DATABASE
ScheduleNUMBER
Term
DayOfWeek
BranchNumber
StartTime
EndTime
RoomNumber
DanceStyle
Enrolment
EnrolmentNUMBER
ScheduleNUMBER
StudentNUMBER
Payment
PaymentNumber
Mode
EnrolmentNUMBER
Amount
3
DATABASE
ER Diagram
DATABASE
ER Diagram
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
4
DATABASE
Part C
Query 1
Select CONCAT (FirstName, " ",LastName) As StudentName, Email From student;
+----------------+------------------+
| StudentName | Email |
+----------------+------------------+
| Sam Billing | Sam@gmail.com |
| Barry Richards | Richie@gmail.com |
| Kevin Arnolds | arnie@gmail.com |
+----------------+------------------+
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;
DATABASE
Part C
Query 1
Select CONCAT (FirstName, " ",LastName) As StudentName, Email From student;
+----------------+------------------+
| StudentName | Email |
+----------------+------------------+
| Sam Billing | Sam@gmail.com |
| Barry Richards | Richie@gmail.com |
| Kevin Arnolds | arnie@gmail.com |
+----------------+------------------+
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 | Sam@gmail.com | NULL |
| 2 | Barry | Richards | Richie@gmail.com | 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
DATABASE
+---------------+-----------+----------+------------------+--------+
| StudentNumber | FirstName | LastName | Email | Number |
+---------------+-----------+----------+------------------+--------+
| 1 | Sam | Billing | Sam@gmail.com | NULL |
| 2 | Barry | Richards | Richie@gmail.com | 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 |
+----------------+---------------------+
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 |
+----------------+---------------------+
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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);
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 | arnie@gmail.com | 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
DATABASE
+---------------+-----------+----------+-----------------+----------+
| StudentNumber | FirstName | LastName | Email | Number |
+---------------+-----------+----------+-----------------+----------+
| 3 | Kevin | Arnolds | arnie@gmail.com | 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.
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.
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
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.
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.
1 out of 11
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
Unlock your academic potential
© 2024 | Zucol Services PVT LTD | All rights reserved.