Database for Desklib - Entities, Attributes, ER Diagram, Tables, Queries

This report describes the development of a database for Desklib, an online library for study material. It includes entities, attributes, ER diagram, tables, and queries. The report also includes personal reflection and bibliography.

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;
+-----------+------------------------------+

12
DATABASE
| StyleName | COUNT(enrolmentid.StudentID) |
+-----------+------------------------------+
| Salsa | 1 |
| Tango | 1 |
+-----------+------------------------------+
2 rows in set (0.01 sec)
Query 8
SELECT dancestyle.StyleName, COUNT(enrolmentid.StudentID) from student INNER JOIN
enrolmentid ON enrolmentid.StudentID = student.StudentID INNER JOIN timetable ON
timetable.TimeID = enrolmentid.EnrolmentID INNER JOIN dancestyle ON dancestyle.StyleID =
timetable.StyleID GROUP BY dancestyle.StyleName;
+-----------+------------------------------+
| StyleName | COUNT(enrolmentid.StudentID) |
+-----------+------------------------------+
| Salsa | 1 |
| Tango | 1 |
+-----------+------------------------------+
2 rows in set (0.00 sec)
Query 9
SELECT * FROM student WHERE StudentID NOT IN (SELECT StudentID FROM enrolmentid);
Empty set (0.00 sec)

13
DATABASE
Query 10
SELECT * FROM payment INNER JOIN enrolmentid ON enrolmentid.EnrolmentID =
payment.EnrolmentID INNER JOIN timetable ON timetable.TimeID = enrolmentid.TimeID WHERE
timetable.StartTime > '2019-07-01';
Empty set (0.00 sec)
Personal reflection that describes your experience building the database
In order to implement the database system I have developed an ER diagram for creating a
logical model of the database. There were some difficulties in creating the queries that involved
more than two tables. Hence for the solution we looked it over in the internet and found the
appropriate solution. The Inner join queries were used so that the two tables could be joined and
the product of the intersection of the two tables resulted as solution to the problem. In addition to
this, at first, the requirements of the database were noted down and the list of entities and
attributes for the system were formed. In addition to this, the logical model is then developed into a
physical model and all the tables in the database were created. In addition to this, the data were
entered into the system. The references described in the earlier part of this report is also
implemented in the database accordingly.

14
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 Cambridge structural
database. Acta Crystallographica Section B: Structural Science, Crystal Engineering and
Materials, 72(2), pp.171-179.