SQL Queries for Course and Student Data

This assignment presents a series of SQL queries designed to manipulate and retrieve information from a database containing courses and students. The queries cover fundamental concepts such as selecting specific data using `WHERE` clauses, joining tables using `JOIN`, and aggregating data with `COUNT`. Each query is accompanied by its expected output, illustrating the results obtained from executing the SQL statements.

Running head: DATABASE IMPLEMENATION
Database Implementation
Name of the Student:
Name of the University:
Author Note

1
DATABASE IMPLEMENATION
Table of Contents
Part 1: ER Model Implementation...................................................................................................2
ER model.....................................................................................................................................2
List of Entities and Attributes......................................................................................................2
List of Business Rules.................................................................................................................3
Assumptions and Justification.....................................................................................................3
Part 2: Relational Database Implementation...................................................................................4
Functional Dependencies.............................................................................................................4
3NF resolutions............................................................................................................................5
Data Integrity...............................................................................................................................5
Tables and Database Structure.....................................................................................................6
SQL Queries................................................................................................................................8
Bibliography..................................................................................................................................10

2
DATABASE IMPLEMENATION
Part 1: ER Model Implementation
ER model
Figure 1: ER Diagram
Source: (created by Author)
List of Entities and Attributes
Entities Attributes
Students StudentId (primary key), FamilyName,
GivenName, CourseId, Age
Staffs StaffId (primary key), StaffName, Salary,
CourseId, InstrumentId
Courses CourseId (primary key), CourseName, Cost,

3
DATABASE IMPLEMENATION
Duration, Schedule
Invoice InvoiceId (primary key), StaffId, StudentId,
StaementDate, PaymentDate, PaymentStatus,
StatementTotal
Instruments InstrucmentId (primary key),
InstrumentName, manufacturer, Quantity,
Stock
List of Business Rules
The list of Business Rules for the database are:
 Students are enrolled for the courses.
 The Staffs teaches a particular course.
 A staff can teach more than one course.
 The Staffs make use of one or more instruments.
 The staffs generate the invoice for the students.
 The students make the payment according to the invoice generated by the staffs.
Assumptions and Justification
For the implementation of the system the following assumptions have been taken into
consideration.

4
DATABASE IMPLEMENATION
 It can be assumed that the staffs make use of the equipment and the students bring their
own equipment and use them. Hence, only the relationship in between the staffs and the
equipment are maintained. This makes the database easier and reduces the further
complexities of the database.
 In addition to this it is also assumed that a student can enroll himself into a number of
courses and also the vice versa is also possible that is a number of student is enrolled into
a single course. Hence, a many to many relationships in between the students and the
courses are existent in the database. Hence for the system many number of student can
enroll themselves in many number of courses.
 It is also assumed that invoices are generated by the staffs and the student make their
payments in accordance with the invoice Ids. A separate table for the payment option
would be increasing the complexity of the database and hence the payment information
have been included in the invoice table.
 It is also assumed that the teaching staffs and the normal staffs that generate the invoices
generally are categorized into the same group. Hence no separate table for the teaching
staffs have been created. This would be helping to keep the database small and simple.
Part 2: Relational Database Implementation
Functional Dependencies
StudentId FamilyName, GivenName, Age
StaffId StaffName, Salary
CourseId CourseName, Cost, Duration, Schedule
InvoiceId StaffId, StaementDate, PaymentDate, PaymentStatus,

5
DATABASE IMPLEMENATION
StatementTotal
InstrucmentId InstrumentName, manufacturer, Quantity, Stock
3NF resolutions
The above provided functional dependencies are used for the normalization of the tables.
The tables have been normalized into 3NF and the following results have been obtained.
Entities Attributes
Students StudentId (primary key), FamilyName, GivenName, CourseId (forgien
key), Age
Staffs StaffId (primary key), StaffName, Salary, CourseId (forgien key),
InstrumentId (forgien key)
Courses CourseId (primary key), CourseName, Cost, Duration, Schedule
Invoice InvoiceId (primary key), StaffId (forgien key), StudentId (forgien key),
StaementDate, PaymentDate, PaymentStatus, StatementTotal
Instruments InstrucmentId (primary key), InstrumentName, manufacturer, Quantity,
Stock
Data Integrity
The data integrity that are maintained by the database are:
 Lesson duration cannot be less than 30 minutes (half hour).
 Cost cannot be negative (but it can be 0).
 Paid must be either yes or no.

6
DATABASE IMPLEMENATION
 Date paid cannot be in the future.
 Statement date must be supplied.
 Statement date cannot be in the future.
 Paid must be either yes or no, it cannot be null.
 Statement total must be supplied.
 Statement total must be greater than 0.
 The payment date must be on or later than the statement date.
Tables and Database Structure
Figure 2: Relationship in Access
Source: (created by Author)
The above relation shows the relationship in between the different tables in the database.
The different tables that are constructed for the database are provided below:
Student:

7
DATABASE IMPLEMENATION
Student
StudentId FamilyName GivenName CourseId Age
1 Rooney Wayne 4 17
2 rashford Marcus 2 15
3 martial Anthony 3 19
4 Young Ashley 5 18
Course
Course
CourseId CourseName Cost Duration Schedule
2 Singing $100.00 40 9/19/2017
3 Piano $75.00 45 9/20/2017
4 Violin $120.00 40 9/22/2017
5 Guitar $50.00 30 9/25/2017
Staff
Staff
StaffId StaffName Salary CourseId InstrumentId
1 James $100.00 2 1
2 David $20.00 3 2
3 Cameron $60.00 4 3
4 Steve $50.00 5 4
Invoice
Invoice
InvoiceId StaffId StudentId StatementDate PaymentDate PaymentStatus StatmentTotal
1 1 1 9/17/2017 9/19/2017 No $200.00
2 2 2 9/17/2017 9/19/2017 No $155.00
3 3 3 9/15/2017 9/20/2017 Yes $120.00
4 4 4 9/16/2017 9/23/2017 No $140.00

8
DATABASE IMPLEMENATION
Instrument
Instrument
InstrumentId intrumentName Manufacturer Quantity Stock
1 Mike Bargenza 2 5
2 Piano Casio 1 5
3 Violin Ripson 4 5
4 Guitar Ripson 3 10
SQL Queries
1.
SELECT *
FROM Student WHERE Age < 18;
Query1
StudentId FamilyName GivenName CourseId Age
1 Rooney Wayne 4 17
2 rashford Marcus 2 15
2.
SELECT Course.*
FROM Course
WHERE (((Course.Schedule) Between Now() And #9/25/2017#));
Query2
CourseId CourseName Cost Duration Schedule
2 Singing $100.00 40 9/19/2017
3 Piano $75.00 45 9/20/2017
4 Violin $120.00 40 9/22/2017

9
DATABASE IMPLEMENATION
Query2
CourseId CourseName Cost Duration Schedule
5 Guitar $50.00 30 9/25/2017
3.
SELECT Student.StudentId, Student.FamilyName, Student.GivenName,
Count(Student.CourseId) AS CountOfCourseId
FROM Student
GROUP BY Student.StudentId, Student.FamilyName, Student.GivenName;
Query3
StudentId FamilyName GivenName CountOfCourseId
1 Rooney Wayne 1
2 rashford Marcus 1
3 martial Anthony 1
4 Young Ashley 1
4.
SELECT Student.*, Course.CourseName
FROM Course INNER JOIN Student ON Course.CourseId = Student.CourseId
WHERE (((Course.CourseName) Like "piano"));
Query4
StudentId FamilyName GivenName CourseId Age CourseName
3 Martial Anthony 3 19 Piano

10
DATABASE IMPLEMENATION
Bibliography
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U.
and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream
Management (pp. 317-336). Springer Berlin Heidelberg.
Gouhar, A., 2017. Database Management System. International Journal of Engineering
Science, 11766.
Hababeh, I., Khalil, I. and Khreishah, A., 2015. Designing high performance web-based
computing services to promote telemedicine database management system. IEEE transactions on
services computing, 8(1), pp.47-64.
Nadkarni, U., Harrington Technologies Llc, 2013. Skills database management system and
method. U.S. Patent 8,554,754.
Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.