EER Modelling and SQL

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.