Database Systems and Design (IMAT5103): EER Modelling and SQL Report

This report details the design and implementation of a database system for a school scenario. It begins with a conceptual database design, outlining business rules and entity relationships. The subsequent stage involves logical database design, including the creation of tables for students, courses, faculty, and enrollment, using Oracle DBMS. The report also covers the creation of indexes and data population within the tables. Furthermore, it provides a series of SQL queries designed to retrieve and manipulate data, demonstrating the functionality of the database system. The document concludes with a bibliography of relevant sources. This assignment showcases the student's understanding of database design principles and SQL implementation.

Running head: DATABASE SYSTSEM AND DESIGN
Database systems and design
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design Task
1.1: Selection of the case upon which the database design and implementation is to be
based
The report is used for the description of the school database has been used in this
report.
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled
Business Rules

2
DATABASE SYSTEM AND DESIGN
The business rules on which the development of the database is done are described
below:
 The database should be able to store the details of the students identified by their
unique id.
 The database should be storing the details of the faculty in the system.
 The database should be able to store the details of the course in the system.
 The database should be able to store the details of the Enrolment of the students in the
system.
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Student (StudentID (pk), StudentName, StudentAddress, StudentContact)
Course (CourseID (pk), CourseName, CourseDuration)
Faculty (FacultyID (pk), FacultyName, FacultyAddress)
Enrollment (EnrollmnetID (pk), FacultyID (fk), StudentID (fk), CourseID (fk))
Task 2.2 Create the tables using Oracle DBMS
Database tables
Student

3
DATABASE SYSTEM AND DESIGN
Course
Faculty

4
DATABASE SYSTEM AND DESIGN
Enrolment
Task 2.3: Create the four most useful indexes on your tables
The list of constraints is provided below:
Students
Courses
Faculty

5
DATABASE SYSTEM AND DESIGN
Enrolment
Task 2.4: Data Population
Students
Courses

6
DATABASE SYSTEM AND DESIGN
Faculty
Enrolment

7
DATABASE SYSTEM AND DESIGN
Task 2.5: SQL Query writing
Query 1
Select P17242819STUDENTS.STUDENTNAME, P17242819COURSES.COURSENAME
From P17242819STUDENTS
Inner Join P17242819ENROLLMENT On P17242819ENROLLMENT.STUDENTID =
P17242819STUDENTS.STUDENTID
Inner Join P17242819COURSES On P17242819COURSES.COURSEID =
P17242819ENROLLMENT.COURSEID;

8
DATABASE SYSTEM AND DESIGN
Query 2
Select P17242819STUDENTS.STUDENTNAME
From P17242819STUDENTS
Inner Join P17242819ENROLLMENT On P17242819ENROLLMENT.STUDENTID =
P17242819STUDENTS.STUDENTID
Inner Join P17242819COURSES On P17242819COURSES.COURSEID =
P17242819ENROLLMENT.COURSEID
Where P17242819COURSES.COURSENAME = 'English';
Query 3
Select COUNT(P17242819STUDENTS.STUDENTNAME)
From P17242819STUDENTS
Inner Join P17242819ENROLLMENT On P17242819ENROLLMENT.STUDENTID =
P17242819STUDENTS.STUDENTID
Inner Join P17242819COURSES On P17242819COURSES.COURSEID =
P17242819ENROLLMENT.COURSEID
Where P17242819COURSES.COURSENAME = 'Spanish';

9
DATABASE SYSTEM AND DESIGN
Query 4
Select P17242819COURSES.COURSENAME
From P17242819FACULTY
Inner Join P17242819ENROLLMENT On P17242819ENROLLMENT.FACULTYID =
P17242819FACULTY.FACULTYID
Inner Join P17242819COURSES On P17242819COURSES.COURSEID =
P17242819ENROLLMENT.COURSEID
Where P17242819FACULTY.FACULTYNAME = 'Derick Springs';
Query 5
Select COUNT(P17242819COURSES.COURSENAME)
From P17242819FACULTY
Inner Join P17242819ENROLLMENT On P17242819ENROLLMENT.FACULTYID =
P17242819FACULTY.FACULTYID
Inner Join P17242819COURSES On P17242819COURSES.COURSEID =
P17242819ENROLLMENT.COURSEID
Where P17242819FACULTY.FACULTYNAME = 'Elvin Stones';

10
DATABASE SYSTEM AND DESIGN
Query 6
Select COUNT(P17242819STUDENTS.STUDENTNAME)
From P17242819FACULTY
Inner Join P17242819ENROLLMENT On P17242819ENROLLMENT.FACULTYID =
P17242819FACULTY.FACULTYID
Inner Join P17242819STUDENTS On P17242819STUDENTS.STUDENTID =
P17242819ENROLLMENT.STUDENTID
Where P17242819FACULTY.FACULTYNAME = 'Elvin Stones';

11
DATABASE SYSTEM AND DESIGN
Bibliography
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).