Creating and Querying a Database Using Oracle SQL

Introduction

As part of a course project, our team of four developed a database system for a university library. We began by designing the database schema, resolving relations between entities, and performing normalization. Using Oracle SQL, a widely used industrial DBMS, we implemented the database and incorporated constraints, views, and sample queries to demonstrate functionality.

Conceptual Schema

We chose five entities to represent the library database system:

• Member: Stores all members, both student and staff. It tracks the member name, member type, number of active loans, payments received, and suspension status. Members create reservations, make loans, and pay fines.
• Resource: Keeps a record of all resources in the library. It tracks the resource name, type of resource, its allowed loan period, and its class number. Resources can be a name of a book.
• Copy: A separate entity where each copy is associated with a resource. It tracks the copy number, location, and availability. Copies are the physical copy for a particular book name.
• Reservation: Stores reservations, the date and time the reservation was made, whether the reservation is active or archived, and the remaining chances a member has to pick up the resource. Reservations reserve resources and notify members of available resources.
• Loan: An entity which tracks loans and fines. It stores the checkout date and time, status of the loan, and fine amount. Loans loan out copies.

Resolving a Relational Database Schema

Step 1: One to Many Relation

• Moved the PK of Resource (Resource_ID) into Copy as a FK
• Since Copy is a weak entity, Resource_ID is used as part of the composite PK

Step 2: One to Many Relation

• Moved the PK of Member (Member_ID) into Reservation as a FK

Step 3: One to Many Relation

• Moved the PK of Resource (Resource_ID) into Reservation as a FK

Step 4: One to Many Relation

• Moved the PK of Member (Member_ID) into Loan as a FK

Step 5: One to One Relation

• Moved the PKs of Copy (Resource_ID, Copy_No) into Loan as FKs

Normalization

1NF - Identify and remove repeating groups (multi-valued attributes)
Divided the following attributes into their atomic attributes:

• Member Name into Member_First_Name and Member_Last_Name
• Location into Floor_No and Shelf_No
• Note that Resource_ID, Resource_ID_Copy_No, Reservation_ID, and Loan_ID are tracked as candidate keys

2NF - Every other attribute must be functionally dependent on one primary key
Established four relations which ensure each attribute is functionally dependent on only one primary key

• From Member_ID, we can determine the member's first name, last name, membership type, total payment received, and suspension status
• From Reservation_ID, we can determine the reservation date and time, reservation status, and how many remaining chances the member has left to pick up the book
• From Loan_ID, we can determine the checkout date and time, loan status, and the fine amount
• From Resource_ID, Copy_ID, we can determine the resource name, type, maximum loan period, class number and name, its floor and shelf, and availability

3NF - Eliminate transitive dependencies
Separated the transitive dependencies Resource_Name, Type, Loan_Period, Class_No, and Class_Name, which are directly dependent on Resource_ID but only transitively dependent on Resource_ID, Copy_ID

• We do not need information about the copy number to determine the name, type, maximum loand period, and class details about a resource
  Separated the transitive dependency Class_Name which is directly dependent on Class_No and only transitively dependent on Resource_ID
• Note that Class_Name is not a part of our relational schema because it is not a part of the coursework requirements to track Class_Name - shown here to illustrate 3NF

Example Queries

-- 1. List all resources contained in the library. Output its class number, how many copies of it are held by the library, and the location of the resource.

SELECT LR.RESOURCE_NAME,
LR.CLASS_NO,
COUNT(C.COPY_NO) AS COPIES_HELD,
C.FLOOR,
C.SHELF
FROM LIB_RESOURCE LR
LEFT JOIN COPY C ON LR.RESOURCE_ID = C.RESOURCE_ID
GROUP BY LR.RESOURCE_NAME, LR.CLASS_NO, C.FLOOR, C.SHELF
ORDER BY LR.RESOURCE_NAME;

-- 2. List all the student and staff members of the library.

SELECT LAST_NAME, FIRST_NAME, MEMBER_TYPE
FROM MEMBER
ORDER BY MEMBER_TYPE, LAST_NAME;

-- 3. List all current reservations. Check if the reserved item is passed on to the next person when the reservation becomes unavailable.

SELECT *
FROM RESERVATION;

-- Select all members who made a reservation for the resource '345678901C':

SELECT *
FROM RESERVATION
WHERE
RESOURCE_ID = '345678901C';

-- Select only the member first in line for the reservation

SELECT *

FROM RESERVATION
WHERE
RESOURCE_ID = '345678901C' AND
RESERVATION_DATE_TIME = (
SELECT MIN(RESERVATION_DATE_TIME)
FROM RESERVATION
WHERE RESOURCE_ID = '345678901C' AND RESERVATION_STATUS != 0
);

-- 4. List all active loans and their overdue status.

SELECT LOAN_ID, RESOURCE_ID, COPY_NO, MEMBER_ID, LOAN_STATUS, FINE_AMOUNT,
CASE
WHEN LOAN_STATUS = 1 AND FINE_AMOUNT > 0
THEN 'Overdue'
ELSE 'Not Overdue'
END AS OVERDUE_STATUS
FROM LOAN_ACTIVE
ORDER BY FINE_AMOUNT DESC;

-- 5. List all previous loans by order of popularity.

SELECT LR.RESOURCE_NAME, COUNT(L.RESOURCE_ID)
FROM LOAN_ARCHIVE L
LEFT JOIN LIB_RESOURCE LR
ON L.RESOURCE_ID = LR.RESOURCE_ID
GROUP BY LR.RESOURCE_NAME
ORDER BY COUNT(L.RESOURCE_ID) DESC;

Full project download: