EER Modelling and SQL Implementation for Exam Database System Design

This project focuses on the design and implementation of an exam database using EER modeling and Oracle SQL. The project includes conceptual database design with an EER diagram illustrating the relationships between entities such as Student, Department, Grade, and Subject, along with defined enterprise rules for data integrity and security. The logical database design is presented, followed by the creation of tables in Oracle DBMS, the implementation of indexes for optimization, and data population with sample SQL statements. The project concludes with SQL queries demonstrating various functionalities, including counting records, selecting specific columns, performing inner joins, sorting data, and applying conditions using operators and constraints, showcasing a comprehensive approach to database development.

Running head: EER MODELLING AND SQL
Database Systems and Design
Name of the Student
Name of the University
Author’s Note

1
EER MODELLING AND SQL
Table of Contents
Stage 1: Scenario and Conceptual Database Design..................................................................2
Task 1.1: Case selection for designing and implementing the database upon it....................2
Task 1.2: Conceptual design of the database on the selected case and modelling of
enterprise rules.......................................................................................................................2
Stage 2: Oracle SQL Implementation and Logical Database Design........................................4
Task 2.1: Logical Database Design........................................................................................4
Task 2.2: Create tables using Oracle DBMS.........................................................................6
Task 2.3: Creation of four most useful indexes for the tables...............................................6
Task 2.4: Data Population......................................................................................................6
Task 2.5: Writing of SQL querry.........................................................................................10
Bibliography.............................................................................................................................15

2
EER MODELLING AND SQL
Stage 1: Scenario and Conceptual Database Design
Task 1.1: Case selection for designing and implementing the database upon it
An exam database is selected for the management of the academic activity of the
student and management of the grades provided to the student in each of the exams. For the
development of the database the dependency between the table is identified and they are
normalized such that no data is redundantly stored in the database. The development of the
database would reduce the time needed to search the result and can be used by the colleges
for automating the system. The grades can be added to the different subjects such that the
student can view the result and for the development of the database oracle 12c is used.
Task 1.2: Conceptual design of the database on the selected case and modelling of
enterprise rules
The Enterprise architecture diagram is created for the demonstration of the
relationship between the tables needed for the development of the exam database system. For
the development of the exam database four entities are considered i.e. Student, Department,
Grade and Subject. The diagram represents relationship between the entities and the primary
key allocated for eliminating the redundancy of data and increase the efficiency of the
database.

3
EER MODELLING AND SQL
EER Diagram
Enterprise Rules
 An unique key should be provided for every student, subject and department such that
it can be used for identifying the entity.
 The student would have only the view permission such that they cannot modify the
grade table
 The admin would have the permission to add new department and subject such that it
can be used in future and support the growth of the educational institution.
 There should be no data redundantly stored in the database and the tables can be
joined to provide accurate result to the querry.

4
EER MODELLING AND SQL
 The details of the student are needed to be stored in the student table of the database.
 The details of the marks and GPA should be stored in the grade table of the database
 The details of the depart and the location details should be stored in the department
table such that it can be used by the student for getting information about the exam.
Stage 2: Oracle SQL Implementation and Logical Database Design
Task 2.1: Logical Database Design
P2414265STUDENT
P2414265DEPARTMENT

5
EER MODELLING AND SQL
P2414265GRADE
P2414265SUBJECT

6
EER MODELLING AND SQL
Task 2.2: Create tables using Oracle DBMS
Task 2.3: Creation of four most useful indexes for the tables
Task 2.4: Data Population

7
EER MODELLING AND SQL
P2414265STUDENT
Sample SQL Statement for inserting Values into the table:
INSERT INTO P2414265student (Student_id, Student_name, Student_address,
Student_email, Student_phone, Dept_ID )
VALUES ('S_5', 'Ava Leach', '44 Marloo Street', '[e-mail]', '8294 1715', 'D_5');
P2414265DEPARTMENT

8
EER MODELLING AND SQL
Sample SQL Statement for inserting Values into the table:
INSERT INTO p2414265department (Dept_ID, dept_name, dept_location )
VALUES ('D_5', 'Science', '3rd Block');
P2414265GRADE

9
EER MODELLING AND SQL
Sample SQL Statement for inserting Values into the table:
INSERT INTO p2414265grade (GRADE_ID, sub_code, exam_name, marks, gpa )
VALUES ('G_5', 'S5', 'MID TERM', 90, 9);
P2414265SUBJECT

10
EER MODELLING AND SQL
Sample SQL Statement for inserting Values into the table:
INSERT INTO p2414265subject (SUB_CODE, sub_name, crd_hrs)
VALUES ('S5', 'POL Science', 26);
Task 2.5: Writing of SQL querry
Use of count and/or another similar mathematical expression
INPUT:
Select count (student_name)

11
EER MODELLING AND SQL
From p2414265STUDENT;
OUTPUT:
COUNT(STUDENT_NAME)
-------------------
5
Selection of particular table columns
INPUT:
SELECT DEPT_NAME FROM p2414265department;
OUTPUT:
DEPT_NAME
--------------------
CNC programmer
IT
Commerce
Arts
Science
Inner Join of at least 2 tables
INPUT:
Select p2414265Student.Student_Name , p2414265Student.Student_ID,
p2414265Department.dept_Name

12
EER MODELLING AND SQL
FROM p2414265Student, p2414265Department
WHERE
p2414265Student.Dept_ID = p2414265Department.DEPT_ID;
OUTPUT:
25 rows selected
STUDENT_NAME STUDENT_ID DEPT_NAME
-------------------- -------------------- --------------------
George Thomas S_1 CNC programmer
Emily Jamison S_2 IT
Sienna Hoddle S_3 Commerce
Kayla Clark S_4 Arts
Ava Leach S_5 Science
Use of a sorting/ordering facility
INPUT:
SELECT *
FROM p2414265grade
ORDER BY marks;
OUTPUT:
GRADE_ID SUB_CODE EXAM_NAME MARKS GPA
-------------------- -------------------- -------------------- -------------------- --------------------

13
EER MODELLING AND SQL
G_1 S1 MID TERM 50 5
G_2 S2 MID TERM 75 7.5
G_4 S4 MID TERM 80 8
G_3 S3 MID TERM 85 8.5
G_5 S5 MID TERM 90 9
A condition using “<”, “>”
INPUT:
SELECT *
FROM p2414265subject
WHERE
CRD_HRS > 20;
OUTPUT:
SUB_CODE SUB_NAME CRD_HRS
-------------------- -------------------- ----------
S1 Coding 25
S3 Accountancy 28
S2 Database 28
S4 History 28
S5 POL Science 26
A condition using IN, NOT NULL, or similar

14
EER MODELLING AND SQL
INPUT:
Alter table p2414265STUDENT
Modify STUDENT_PHONE NOT NULL;
OUTPUT:
table P2414265STUDENT altered.

15
EER MODELLING AND SQL
Bibliography
Aldahdooh, R. and Naser, S.S.A., 2017. Development and Evaluation of the Oracle
Intelligent Tutoring System (OITS).
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Farooq, T., Avantsa, S. and Sharman, P., 2016. Building Database Clouds in Oracle 12c.
Addison-Wesley Professional.
Foster, E.C. and Godbole, S., 2016. Overview of Oracle. In Database Systems (pp. 435-442).
Apress, Berkeley, CA.
Islam, K., Ahsan, K., Bari, S.A.K., Saeed, M. and Ali, S.A., 2017. Huge and Real-Time
Database Systems: A Comparative Study and Review for SQL Server 2016, Oracle 12c &
MySQL 5.7 for Personal Computer. Journal of Basic and Applied Sciences, 13, pp.481-490.
McGee, P., 2015. C# 5.0: A Beginner's Guide. McGraw-Hill Education.
Zhang, P., 2017. Practical Guide for Oracle SQL, T-SQL and MySQL. CRC Press.