CIS2002 Semester 3 Assignment 2: Data Modeling, Normalization, and SQL

This document presents a comprehensive solution for CIS2002 Assignment 2, focusing on database design and implementation. The assignment begins with Section A, which involves data modeling, including the creation of an Entity Relationship Diagram (ERD) based on Clive Finkelstein's methods, a list of relations, and an Oracle SQL table create statement. Section B delves into database normalization, demonstrating the transformation of an un-normalized library relation through First, Second, and Third Normal Forms. Section C provides SQL queries, including examples for data retrieval and analysis, such as selecting book titles based on profit margins, calculating average category prices, and identifying categories based on book counts and profits. The assignment adheres to USQ's data modeling and normalization methodology and provides a strong foundation for exam preparation. The document concludes with a bibliography of relevant database design resources.

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

1CIS2002 – DATABASE
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

2CIS2002 – DATABASE
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

3CIS2002 – DATABASE
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

4CIS2002 – DATABASE
create table STUDENT
(
student_id_number number(10) primary key,
name varchar2(50),
phone number(10),
address varchar2(100)
);
Table - COURSE_ENROLMENT
create table COURSE_ENROLMENT
(
enrolment_no number(10) primary key,
student_id_number number(10),
course_code varchar2(10),
enrolment_date date,
foreign key(student_id_number) references
STUDENT(student_id_number),
foreign key(course_code) references COURSES(course_code)
);
Table – COURSES
create table COURSES
(
course_code varchar2(10) primary key,
title varchar2(100),
prerequisite_courses varchar2(200),
duration varchar2(50)
);

5CIS2002 – DATABASE
Section B – Normalization
Un-normalized Library relation:
LIBRARY (library name, address, opening hours, ((book id, title, category, genre, published
date, book entry date, ((author name, author contact)) )), total number of books, ((member id,
member name, member contact, join date)), main librarian name, main librarian office, main
librarian contact)
First Normal form:
Here the repeating groups in the following relation is denoted by the brackets ((…)) and
in the database, it is not controllable. It should be stored in the more than one relation to be able
to retrieve and store data efficiently. According to the first normal form, a key attribute is
required to be identified. Using the formation of a new key, concatenation the new keys will
describe the all attributes that are library name - book id and library name – member Id. It
creates new relational entities as BOOKS and MEMBERS, hence the results are:
LIBRARY (library name#, address, opening hours, total number of books, main librarian name,
main librarian office, main librarian contact)
BOOKS (book id#, library name#, title, category, genre, published date, book entry date,
((author name, author contact)))
MEMBERS (member id#, library name#, member name, member contact, join date)
Similarly for BOOKS, the new identified key relation is book id – author name. Hence
the resulting relation will be,

6CIS2002 – DATABASE
LIBRARY (library name#, address, opening hours, total number of books, main librarian name,
main librarian office, main librarian contact)
BOOKS (book id#, library name#, author name#, title, category, genre, published date, book
entry date)
AUTHOR (author name#, author contact)
MEMBERS (member id#, library name#, member name, member contact, join date)
Second normal form:
According to the third normal form, there should not be any partial decencies between the
attributes in an entity. The problem arises during the retrieval of the data. For example, main
librarian contact only needs the librarian name to determine the value. On other hand, library
name is not useful in finding the contact number of main librarian. This problem can be resolved
by removing the attributes to another set of entity who depends on the key attributes of the
current entity. The relation LIBRARY will give another entity named LIBRARIAN, hence; the
resultant relations will be,
LIBRARY (library name#, main librarian name#, address, opening hours, total number of
books)
LIBRARIAN (main librarian name#, main librarian office, main librarian contact)
BOOKS (book id#, library name#, author name#, title, category, genre, published date, book
entry date)
AUTHOR (author name#, author contact)
MEMBERS (member id#, library name#, member name, member contact, join date)

7CIS2002 – DATABASE
Third Normal Form:
The third normal form eliminates the transitive functional dependencies between the
attributes. In the current normalized relations there are still problems as if we retrieve data of
library which can be identified by the book id and it is not possible that same book cannot be
present on the different library in the system. The attribute here is transitively dependent on the
book id which is unwanted. Similarly in the book id – author name relationship the author is
transitively dependent on the book id where a book can have one or multiple authors. To resolve
this library name is removed from the BOOKS relation to a new entity and author name from
BOOKS to a different entity. The resultant relations will be;
LIBRARY (library name#, main librarian name#, address, opening hours, total number of
books)
LIBRARY HAS BOOKS (library name#, book id#)
LIBRARIAN (main librarian name#, main librarian office, main librarian contact)
BOOKS (book id#, title, category, genre, published date, book entry date)
AUTHOR HAS BOOOKS (author name#, book id#)
AUTHOR (author name#, author contact)
MEMBERS (member id#, library name#, member name, member contact, join date)
Section C – SQL
Query 1
select b.title,
count(oi.isbn) as "Number of books"

8CIS2002 – DATABASE
from orderitems oi
inner join books b
on b.isbn=oi.isbn
and ((b.retail-b.cost)/b.cost)*100>70
group by b.title
order by count(oi.isbn) desc;
Query 2
select b.category,
p.name,
TO_CHAR(avg(b.retail),'$99,999.00') as AVERAGE_CAT_PRICE
from books b
inner join publisher p
on p.pubid=b.pubid
and b.category in ('COMPUTER','CHILDREN')
group by b.category,
p.name
having avg(b.retail)>50
order by b.category asc,

9CIS2002 – DATABASE
TO_CHAR(avg(b.retail),'$99,999.00') desc ;
Query 3
select *
from (select category,
count(isbn) as count
from books
group by category)
where count > (select min(count)
from (select category,
count(isbn) as count
from books
group by category))
order by category asc;

10CIS2002 – DATABASE
Query 4
select isbn,
title,
TO_CHAR(cost,'$99,999.00') as COST
from books
where category
in (select category
from (select category,
count(isbn) as count
from books
group by category)
where count > (select min(count)
from (select category,
count(isbn) as count
from books

11CIS2002 – DATABASE
group by category)))
order by title asc;
Query 5
select b.category,
TO_CHAR(count(oi.isbn)*avg((retail)-cost),'$99,999') as
Category_Profit
from books b inner join orderitems oi
on oi.isbn=b.isbn

12CIS2002 – DATABASE
group by b.category
order by category_profit desc;
Query 6
select category,
title,
retail
from books
where retail < (select max(retail) as maximum
from books)
order by category asc,
retail desc;

13CIS2002 – DATABASE

14CIS2002 – DATABASE
Bibliography
Data, M. H. (2015). Database Design. Perancangan Basis Data) merupakan salah satu.
De Haan, L., Gorman, T., Jørgensen, I., & Caffrey, M. (2014). Beginning Oracle SQL. Apress.
Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems (Vol. 7). Pearson.
Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M., & Spinellis, D. (2018, May). Smelly
relations: measuring and understanding database schema quality. In Proceedings of the
40th International Conference on Software Engineering: Software Engineering in
Practice (pp. 55-64).