Entity Relationship Diagram (ERD) represents the real-world objects as Entities and relationship between them.

Running head: CIS2002 - DATABASE
CIS2002 – DATABASE
Name of the Student
Name of the University
Author Note

CIS2002 – DATABASE
1
Table of Contents
Section A – Data Modelling............................................................................................................2
a) ER Diagram.............................................................................................................................2
b) A list of Relations....................................................................................................................3
c) An Oracle SQL table create statement....................................................................................3
Section B – Normalization...............................................................................................................5
Un-normalized Library relation:..................................................................................................5
First Normal form:.......................................................................................................................5
Second normal form:...................................................................................................................6
Third Normal Form:....................................................................................................................7
Section C – SQL..............................................................................................................................7
Query 1........................................................................................................................................7
Query 2........................................................................................................................................8
Query 3........................................................................................................................................9
Query 4......................................................................................................................................10
Query 5......................................................................................................................................11
Query 6......................................................................................................................................12
Bibliography..................................................................................................................................14

CIS2002 – DATABASE
2
Section A – Data Modelling
a) ER Diagram
Entity Relationship Diagram (ERD) represents the real-world objects as Entities and
relationship between them. The below ERD shown in Figure 1. is developed according to the
Clive Finkelstein methods of data modelling.
Figure 1: Entity Relationship Diagram for Pomona Extension
Source: Created by author

CIS2002 – DATABASE
3
b) A list of Relations
List of relations represents the entities, attributes with clear foreign keys and primary
keys. The underlined attributes with # represents primary key and the attributes with # which are
not underlined are the foreign keys of their belonging tables.
STUDENT (student_id_number#, name, phone, address)
COURSES (course_code#, title, prerequisite_courses, duration)
COURSE_ENROLEMENT (enrolment_no#, student_id_number#, course_code#,
enrolment_date)
COURSE_OFFERING (offering_id#, instructor_id#, year, class_time, class_room,ess
enrolment_capacity)
INSTRUCTORS (instructor_id#, name, address, phone, skills, certifications)
CAMPUS_STORE (item_name#, price, description)
SUPPLIER (supplier’s_company_name#, phone, address)
STORE_HAS_SUPPLIER (supplier’s_company_name#, item_name#)
PURCHASE_ITEM (item_name#, course_code#, quantity)
c) An Oracle SQL table create statement
The most critical relation of the system is the enrolment of a student into any courses.
The system’s main functionality is to enroll student and it is the major part of the system. This
relation is consist of three tables named STUDENT, COURSE_ENROLMENT and COUSES.
Table – STUDENT