ProductsLogo
LogoStudy Documents
LogoAI Grader
LogoAI Answer
LogoAI Code Checker
LogoPlagiarism Checker
LogoAI Paraphraser
LogoAI Quiz
LogoAI Detector
PricingBlogAbout Us
logo

Database Management System

Verified

Added 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.
Document Page
Running head: DATABASE
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.
Document Page
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
Document Page
2
DATABASE
ScheduleNUMBER
Term
DayOfWeek
BranchNumber
StartTime
EndTime
RoomNumber
DanceStyle
Enrolment
EnrolmentNUMBER
ScheduleNUMBER
StudentNUMBER
Payment
PaymentNumber
Mode
EnrolmentNUMBER
Amount
Document Page
3
DATABASE
ER Diagram

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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;
Document Page
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
Document Page
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 |
+----------------+---------------------+

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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);
Document Page
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
Document Page
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.

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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.
1 out of 11
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]