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;

12
EER MODELLING AND SQL
Use of a sorting/ordering facility
SELECT rank
FROM P17238602FACULTY
ORDER BY rank;
A condition using “<”, “>”
SELECT *
FROM P17238602ATTENDANCE
Where Hours > 4;

13
EER MODELLING AND SQL
A condition using IN, NOT NULL, or similar
Alter table P17238602DEPARTMENT
Modify Contactphone number NOT NULL;

14
EER MODELLING AND SQL
Bibliography
Aref, M., ten Cate, B., Green, T.J., Kimelfeld, B., Olteanu, D., Pasalic, E., Veldhuizen, T.L.
and Washburn, G., 2015, May. Design and implementation of the LogicBlox system.
In Proceedings of the 2015 ACM SIGMOD International Conference on Management of
Data (pp. 1371-1382). ACM.
Chapman, W., 2018. Engineering modeling and design. Routledge.
Chu, S., Balazinska, M. and Suciu, D., 2015, May. From theory to practice: Efficient join
query evaluation in a parallel database system. In Proceedings of the 2015 ACM SIGMOD
International Conference on Management of Data (pp. 63-78). ACM.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Davie, B., Koponen, T., Pettit, J., Pfaff, B., Casado, M., Gude, N., Padmanabhan, A., Petty,
T., Duda, K. and Chanda, A., 2017. A database approach to sdn control plane design. ACM
SIGCOMM Computer Communication Review, 47(1), pp.15-26.
Davie, B., Koponen, T., Pettit, J., Pfaff, B., Casado, M., Gude, N., Padmanabhan, A., Petty,
T., Duda, K. and Chanda, A., 2017. A database approach to sdn control plane design. ACM
SIGCOMM Computer Communication Review, 47(1), pp.15-26.
Eastman, C.M., 2018. Building product models: computer environments, supporting design
and construction. CRC press.
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.

15
EER MODELLING AND SQL
Liu, X., Li, Y., Zheng, Y., Li, J. and Li, B., 2017. Database System Design and Analysis
Model Study of Drug Resistance of Animal Origin Pathogens. In 2017 ASABE Annual
International Meeting (p. 1). American Society of Agricultural and Biological Engineers.
Sharma, M., Singh, G. and Singh, R., 2016. Design and analysis of stochastic DSS query
optimizers in a distributed database system. Egyptian informatics journal, 17(2), pp.161-173.
Siewiorek, D. and Swarz, R., 2017. Reliable Computer Systems: Design and Evaluatuion.
Digital Press.
Wang, J. and Zhang, D.S., 2015, June. Research and Design of Distributed Database
Synchronization System Based on Middleware. In 2015 8th International Conference on
Intelligent Computation Technology and Automation (ICICTA)(pp. 685-688). IEEE.
Wu, J., 2017. Distributed system design. CRC press.
Zhuang, H., Lu, K., Li, C., Sun, M., Chen, H. and Zhou, X., 2015, May. Design of a more
scalable database system. In Cluster, Cloud and Grid Computing (CCGrid), 2015 15th
IEEE/ACM International Symposium on (pp. 1213-1216). IEEE.