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