IMAT5103 - Database Systems and Design: EER Modelling & SQL Report

This report details the design and implementation of a database system for a college management system (CMS) using EER modeling and SQL. The initial stage involves conceptual database design, including defining enterprise rules for managing students, courses, and faculty. The subsequent stage focuses on logical database design, creating tables using Oracle DBMS, implementing indexes, populating data, and writing SQL queries for data retrieval and manipulation. The queries demonstrate the use of count functions, column selection, inner joins, sorting, and conditional statements. The database aims to streamline academic and non-academic activities within the college, providing a centralized platform for administration, staff, and student management. Desklib offers a variety of solved assignments and past papers to aid students in their studies.

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: Selection of the case upon which the database design and implementation is to be
based.......................................................................................................................................2
Task 1.2: Conceptual database design for the scenario and the list of enterprise rules being
modelled.................................................................................................................................2
Stage 2: Logical Database Design and Oracle SQL Implementation........................................3
Task 2.1: Logical Database Design for the scenario..............................................................3
Task 2.2: Create tables using Oracle DBMS.........................................................................6
Task 2.3: Create four most useful indexes on the tables........................................................6
Task 2.4: Data Population......................................................................................................8
Task 2.5: SQL Query writing...............................................................................................10
Bibliography.............................................................................................................................14

2
EER MODELLING AND SQL
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
For the development of the database college management system is selected where the
system is designed for handling academic and non-academic activities. The system is create
for providing detailed structure of the college and synchronizing the working of each of the
department. It provides a joint integrated platform for the management of the administration,
staff details, attendance and other modules. For the development of CMS the user are needed
to be identified and the system is aligned according to the requirement and the database is
developed using oracle.
Task 1.2: Conceptual database design for the scenario and the list of enterprise rules
being modelled
EER Diagram

3
EER MODELLING AND SQL
Enterprise Rules
 The college administrator has the functionality to register new students and courses
 The administrator have the right to create new department and allocate new courses,
create facility and allow subject to the faculty.
 The faculty have the right to enter the marks and attendance of the student and the
students are provided with the access to check their attendance and marks but they are
not provided rights to modification.
Stage 2: Logical Database Design and Oracle SQL Implementation
Task 2.1: Logical Database Design for the scenario

4
EER MODELLING AND SQL
P17238602ATTENDANCE
P17238602COURSE
P17238602DEPARTMENT
P17238602ENROLLMENT

5
EER MODELLING AND SQL
P17238602FACULTY
P17238602SCHEDULE
P17238602SECTION

6
EER MODELLING AND SQL
P17238602STUDENT
Task 2.2: Create tables using Oracle DBMS
Task 2.3: Create four most useful indexes on the tables
P17238602ATTENDANCE_IDX1

7
EER MODELLING AND SQL
P17238602DEPARTMENT_IDX1
P17238602STUDENT_IDX1

8
EER MODELLING AND SQL
Task 2.4: Data Population
P17238602COURSE
P17238602STUDENT
P17238602SECTION

9
EER MODELLING AND SQL
P17238602FACULTY
P17238602ENROLLMENT
P17238602DEPARTMENT

10
EER MODELLING AND SQL
P17238602ATTENDANCE
P17238602SCHEDULE
Task 2.5: SQL Query writing
Use of count and/or another similar mathematical expression

11
EER MODELLING AND SQL
Select count (name)
From P17238602Department;
Selection of particular table columns
Select name
from P17238602Department;
Inner Join of at least 2 tables
Select P17238602Faculty.FirstName , P17238602Faculty.LastName,
P17238602Department.Name
from P17238602Faculty, P17238602Department
where
P17238602Faculty.DepartmentID = P17238602Department.ID;