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
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
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;
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 ONSchedule.classNUMBER=enrolment.classNUMBERINNERJOINbranchON 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 SELECTCONCAT(student.FirstName,"",student.LastName)AsStudentName, 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 SELECTdancestyle.StyleName,COUNT(enrolment.StudentNUMBER)fromstudentINNERJOIN 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*FROMstudentWHEREStudentNUMBERNOTIN(SELECTStudentNUMBERFROM 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
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