DB-Mini-Project

BookDB 5785

Authors: Avi Parshan and Leib Blam

Requirements

PostgreSQL 17

PGAdmin 4 version 8.11

Disclaimer

This library book database system was created solely for educational and demonstration purposes. All data contained within is entirely fabricated and does not represent any real-world information. Any resemblance to actual book titles, authors, or other entities is purely coincidental and unintentional.

Description

README files are included in each stage folder to provide a detailed explanation of the project’s progress and the steps taken to achieve the final product

Stages

Stage 1 + 2

Stage 3

Stage 4

Dumps

Gitlab for LFS

Dumps are also available in the Github Releases section.

Stage 1 and Stage 2

Stage 1

Project Proposal

Build a database system to manage books in a library.

Features and Functionalities

  1. Coding Systems
    • Internal Code: Unique identifiers for internal processes.
    • International Code: Integration with global standards like ISBN.
  2. Location Tracking
    • Make it easier for librarians and patrons to find books based on floor and shelf.
  3. Condition Monitoring
    • Track each book’s condition assists librarians with inventory management and overall quality.
  4. Copies Management
    • Total copies available per title.

Design

Main Entities:

Screenshots and erdplus json files are here

Data Generation

Run the SQL files in the following order:

  1. Schema definition CreateTables.sql

  2. Data:

    Country

    • Independent table.
    • Script: random_countries.sql
    • Rows: 24

    Publisher

    • Depends on Country for Is_In table.
    • Script: random_publishers.sql
    • Rows: 30,000

    Author

    • Independent table.
    • Script: random_authors.sql
    • Rows: 5,000

    Language

    • Independent table.
    • Script: random_languages.sql
    • Rows: 63

    Genre

    • Independent table.
    • Script: random_genres.sql
    • Rows: 77

    Book

    • Independent table but referenced by several others.
    • Script: random_books.sql
    • Rows: 100,000

    Location

    • Depends on Book.
    • Script: random_locations.sql
    • Rows: 70,000

    Written_By

    • Depends on Book and Author.
    • Script: written_by.sql
    • Rows: 132,820

    Published_By

    • Depends on Book and Publisher.
    • Script: published_by.sql
    • Rows: 124,848

    Written_In

    • Depends on Book and Language.
    • Script: written_in.sql
    • Rows: 125,087

    Type_of

    • Depends on Book and Genre.
    • Script: type_of.sql
    • Rows: 124,741

    Is_In

    • Depends on Publisher and Country.
    • Script: is_in.sql
    • Rows: 37,134

Screenshot 2024-12-03 155347

Stage 2

Create Tables in PostgreSQL

Click Query Tool

Now open the CreateTables.sql script via Open File image

And click execute script

image

Log for table creation, dump

Load data

In a similar fashion to the CreateData.sql script, we now bring in each sql file and execute them in the order listed above

Log for loading data

Dump Data

Via command line, we can dump the data from the database into a file. –clean to first drop tables –if-exists to avoid errors if tables do not exist

Restore Data

Queries

Basic Queries

SELECT:

UPDATE:

DELETE:

Logs with timings

Parameterized Queries

Logs with timings

Indexing

To optimize the performance of queries on the database, we created indexes on the columns that are frequently used. Additionally, all foreign keys for each table are also used as indexes (This helps for referential integrity, as it prevents data being deleted when its referenced elsewhere).

Timing

(1 to 8 are basic queries, 9 to 13 are parameterized)

Query Number Normal Runtime (ms) Runtime With Indexes (ms)
1 84.207 80.004
2 32.147 5.371
3 31.784 11.209
4 25.110 13.830
5 11.120 12.255
6 321.113 7.711
7 53.818 10.199
8 113.304 28.650
9 55.366 11.174
10 16.055 6.270
11 8.349 8.538
12 12.886 2.713

Constraints

To make our database system more robust, we enforced the following rules during table creation phase in CreateTables.sql:

Testing constraints

To test these constraints, we designed and executed invalid scenarios that should produce errors. These include:

INSERTS

UPDATES

DELETES

Testing Constraint error log

Copies

DB Dumps for Stage 2

Stage 3

Things worth knowing
Extract Extra DB Info * In PSQL Shell Enter: * `\di` to get general info * `\d` to get relation info... can do `\d Author` to get specific information about the Author table for example * `\df` to get user created function info * `\dS Book` to get trigger info for the Book table * `VACUUM;` to clear up old space in the database (and also works on indexes)
Restore DB with Indexes * Restore backupPSQL (binary format) with Indexes ```bash pg_restore -U postgres -d postgres -v --clean --if-exists --disable-triggers --no-owner --no-privileges --format=c "backupPSQLIndex.sql" ``` Ensure you are in the [Stage2 Folder](https://gitlab.com/avipars/db-lfs/-/tree/main/Stage2)
Dropping tables * Use [DropTables.sql](https://github.com/avipars/DB-Mini-Project/blob/main/Stage3/Commands/DropItems.sql) first to drop the Stage 3 additions. Afterwards, you can run the [DropTables.sql](https://github.com/avipars/DB-Mini-Project/blob/main/Stage1/Commands/DropTables.sql) from Stage 1 to remove all tables.

Queries

Join Queries

  1. This query joins the Book, Written_By, and Author tables to get the first and last name of the author of a book with a specific ID

     SELECT 
     Author.First_Name, 
     Author.Last_Name
 FROM Book
 JOIN 
     Written_By ON Book.ID = Written_By.ID
 JOIN 
     Author ON Written_By.Author_ID = Author.Author_ID
 WHERE Book.ID = 2;

  2. This query updates all the books published by Murray-Jenkins to Good Condition

     UPDATE Location
 SET Condition = 'Good'
 WHERE ID IN (
     SELECT B.ID
     FROM Book B
     JOIN 
         Written_By WB ON B.ID = WB.ID
     JOIN 
         Author A ON WB.Author_ID = A.Author_ID
     JOIN 
         Published_By PB ON B.ID = PB.ID
     JOIN 
         Publisher P ON PB.Publisher_ID = P.Publisher_ID
     WHERE P.Name = 'Murray-Jenkins');

  3. This query joins the Publisher, Is_In, and Country tables to get the name of the country where a specific publisher is located

     SELECT Country.Name
 FROM Publisher
 JOIN 
     Is_In ON Publisher.Publisher_ID = Is_In.Publisher_ID
 JOIN 
     Country ON Is_In.Country_ID = Country.Country_ID
 WHERE Publisher.Publisher_ID = 1;

  4. This query selects all books with more than 10 pages and where the book was released within 10 years of the author being born, taking the first 5 results

     SELECT
     b.ID AS Book_ID, 
     b.Release_Date, 
     a.Date_of_Birth
 FROM Book b
 JOIN 
     Written_By wb ON b.ID = wb.ID
 JOIN 
     Author a ON wb.Author_ID = a.Author_ID
 WHERE 
     b.Release_Date < (a.Date_of_Birth + INTERVAL '10 years') 
     AND b.Page_Count > 10
 LIMIT 5;

Timings

Query Number Normal Runtime (ms) Runtime With Indexes (ms)
1 4.033 2.521
2 29.940 6.404
3 2.936 1.332
4 2.416 1.083

Logs for Queries

Logs for Queries with Indexing

Views

Creating Views

Querying via Views

Visualizations

I selected the following views to create visualizations of:

View 3: Most popular birth month among authors

Pie Chart (Sorted by Month Number)

SELECT 
    TO_CHAR(a.Date_of_Birth, 'Month') AS Birth_Month, -- month name 
    COUNT(DISTINCT a.Author_ID) AS Author_Count, -- # of unique authors per month
    TO_CHAR(a.Date_of_Birth, 'MM') AS Month_Number -- Jan = '01'
FROM Author_Books_View a
WHERE 
    a.Date_of_Birth IS NOT NULL
GROUP BY 
    Birth_Month, Month_Number -- connect 2 columns
ORDER BY Month_Number; -- sort by month #

months

View 4: Number of distinct books in each genre

Bar Graph (Sorted from least to most unique titles)

SELECT 
    Genre_Name, 
    Total_Copies_Available, -- total copies of books in genre
    Unique_Titles  -- distinct titles in genre
From Genre_Location_Popularity_View 
ORDER BY Unique_Titles;

unique_titles

Functions

  1. GetAuthorNameByBookID(book_id INT) - The first function returns the full name, first and last, of the author for a specific book, given by its book id. When we select the first name and last name attribute, we cast them into text and returning a Query back. We accept an integer into our parameters and return all full names inside the query.

     CREATE OR REPLACE FUNCTION GetAuthorNameByBookID(book_id INT)
 RETURNS TABLE (first_name text, last_name text) AS $$
 BEGIN
     RETURN QUERY
         SELECT 
             CAST(a.First_Name AS text) AS first_name,
             CAST(a.Last_Name AS text) AS last_name
         FROM Book b
         JOIN Written_By w ON b.ID = w.ID
         JOIN Author a ON w.Author_ID = a.Author_ID
         WHERE b.ID = book_id;
 END;
 $$ LANGUAGE plpgsql;

  2. UpdateBooksConditionForPublisher(publisher_name VARCHAR, cond_name VARCHAR) - The second function is a PROCEDURE, it updates the condition of all books written by an Publisher to a certain condition. The function accepts 2 parameters, publisher name as VARCHAR and condition name as VARCHAR.

     CREATE OR REPLACE PROCEDURE UpdateBooksConditionForPublisher(publisher_name VARCHAR, cond_name VARCHAR)
 LANGUAGE plpgsql
 AS $$
 BEGIN
     UPDATE Location
     SET condition = cond_name
     WHERE ID IN (
         SELECT B.ID
         FROM Book B
         JOIN Written_By WB ON B.ID = WB.ID
         JOIN Published_By PB ON B.ID = PB.ID
         JOIN Publisher P ON PB.Publisher_ID = P.Publisher_ID
         WHERE P.Name = publisher_name
     );
 END;
 $$;

  3. GetCountryByPublisherID(p_id INT) - The third function returns the name of the country where a specific publisher is located. It accepts 1 parameter, publisher id as an integer and returns a table of all the names of the countries. The result is returned as a table of countries

     CREATE OR REPLACE FUNCTION GetCountryByPublisherID(p_id INT)
 RETURNS TABLE (name VARCHAR) AS $$
 BEGIN
     RETURN QUERY
     SELECT c.Name
     FROM Publisher p
     JOIN Is_In ii ON p.Publisher_ID = ii.Publisher_ID
     JOIN Country c ON ii.Country_ID = c.Country_ID
     WHERE p.Publisher_ID = p_id;
 END;
 $$ LANGUAGE plpgsql;

  4. GetBooksReleasedWithin10YearsOfBirth(p_count INT, limit_count INT) - The fourth function returns the first limit_count books with more than a p_count number of pages and released within 10 years of the author being born. It accepts two integers as its parameters and returns a Table with Book id as integer, Release Date, and Date of Birth of the Author.

     CREATE OR REPLACE FUNCTION GetBooksReleasedWithin10YearsOfBirth(p_count INT, limit_count INT DEFAULT 5)
 RETURNS TABLE (
     Book_ID INT,
     Release_Date DATE,
     Date_of_Birth DATE
 ) AS $$
 BEGIN
     RETURN QUERY
     SELECT
         b.ID AS Book_ID, 
         b.Release_Date, 
         a.Date_of_Birth
     FROM Book b
     JOIN Written_By wb ON b.ID = wb.ID
     JOIN Author a ON wb.Author_ID = a.Author_ID
     WHERE 
         b.Release_Date < (a.Date_of_Birth + INTERVAL '10 years') 
         AND b.Page_Count > p_count
     LIMIT limit_count;
 END;
 $$ LANGUAGE plpgsql;

Logs for Functions

DB Dump for Functions

Timing Functions

Function Number Runtime With Functions (ms) Runtime With Functions and Indexing (ms)
1 3.375 0.73
2 27.598 5.506
3 2.28 0.505
4 1.398 1.001

Logs for running Functions

Logs for running Functions with Indexing

Overall Timing

Query/Function Number Query Runtime (ms) Query Runtime With Indexing (ms) Runtime With Functions (ms) Runtime With Functions and Indexing (ms)
1 4.033 2.521 3.375 0.73
2 29.940 6.404 27.598 5.506
3 2.936 1.332 2.28 0.505
4 2.416 1.083 1.398 1.001

Triggers

To enhance logging capability and functionality of the database, we created 2 useful triggers:

  1. If a book gets deleted from the Book table, Log it in the Book_Log Table (new table created via Trigger.sql)

    • Functions: log_book_deletion()

    • Trigger Name: book_delete_trigger

    • Activated: After DELETE on Book Table

    • Tables Affected: Book_Log

     CREATE TABLE IF NOT EXISTS Book_Log
 (
 Log_ID SERIAL PRIMARY KEY,
 Book_ID INT NOT NULL,
 Title VARCHAR(1000) NOT NULL,
 Deleted_At TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- deletion time
 );

 CREATE OR REPLACE FUNCTION log_book_deletion()
 RETURNS TRIGGER AS $$
 BEGIN
 INSERT INTO Book_Log (Book_ID, Title, Deleted_At)
 VALUES (OLD.ID, OLD.Title, CURRENT_TIMESTAMP); --logs id , title and deletion time 

 RETURN OLD; 
 END;
 $$ LANGUAGE plpgsql;

 DROP TRIGGER IF EXISTS book_delete_trigger ON Book; -- remove former trigger

 CREATE TRIGGER book_delete_trigger -- re-create
 AFTER DELETE ON Book
 FOR EACH ROW
 EXECUTE FUNCTION log_book_deletion(); -- run function whenever there is a deletion

  2. If a book is classified as an Ebook, automate location changes (Set condition, floor, shelf, quantity)

    • Functions: update_condition_for_ebook()

    • Trigger Name: update_condition_on_ebook_format, insert_condition_on_new_ebook

    • Activated: After a Book format changes on UPDATE in Book Table, or After a Book is INSERTED to the Book Table

    • Tables Affected: Book, Location

    • Note: If run via insert_condition_on_new_ebook, Location ID for these eBooks is the same as BookID for simplicity

     CREATE OR REPLACE FUNCTION update_condition_for_ebook()
 RETURNS TRIGGER AS $$
 BEGIN
 -- check if book format is changed to 'eBook'
 IF TG_OP = 'UPDATE' AND NEW.Format = 'Ebook' AND OLD.Format != 'Ebook' THEN
     -- update condition to 'NEW' in Location table for book
     UPDATE Location
     SET Condition = 'New', Floor = 'E-Library Section', Shelf = 1
     WHERE ID = NEW.ID;
 END IF;
     -- handle when new eBook is inserted
 IF TG_OP = 'INSERT' AND NEW.Format = 'Ebook' THEN
     -- automatically add eBook to Location table with 'New' condition and 'E-Library Section' floor
     INSERT INTO Location (Quantity, Floor, Shelf, Condition, ID, Location_ID)
     VALUES (1, 'E-Library Section', 1, 'New', NEW.ID, NEW.ID);
 END IF;

 RETURN NEW;
 END;
 $$ LANGUAGE plpgsql;

 DROP TRIGGER IF EXISTS update_condition_on_ebook_format ON Book; -- remove former trigger

 CREATE TRIGGER update_condition_on_ebook_format  -- re-create
 AFTER UPDATE ON Book
 FOR EACH ROW
 WHEN (OLD.Format != NEW.Format AND NEW.Format = 'Ebook') -- format changed of existing book
 EXECUTE FUNCTION update_condition_for_ebook();

 DROP TRIGGER IF EXISTS insert_condition_on_new_ebook ON Book; -- remove former trigger

 CREATE TRIGGER insert_condition_on_new_ebook   -- re-create
 AFTER INSERT ON Book
 FOR EACH ROW
 WHEN (NEW.Format = 'Ebook') -- inserted ebook
 EXECUTE FUNCTION update_condition_for_ebook();

Logs for Triggers

DB Dump for Triggers

Testing Trigger

Trigger 1:

Trigger 2:

Logs for Testing Triggers

DB Dumps for Triggers

DB Dumps for Stage 3

Stage 4

This is the final stage of project involving an integration with another team’s project.

Things worth knowing
Extract Extra DB Info * In PSQL Shell Enter: ```\conninfo``` to get username, database name, & port number used
Other Group's Github https://github.com/Ravioli246/Database-Project-2024-Semester-Spring

Integration

As a part of stage 4, we merged our Book Database System with the Store Database System. The store is responsible for managing shelving codes, rare books, archive, disposal, rehabilitation and preservation of books.

Design

Our original ERD

image

Their original ERD

image

Combined ERD

image

Our original DSD

image

Their original DSD

image

Combined DSD

image

Their Original Diagrams and JSON

Our Original Diagrams and JSON

Combined Diagrams and JSON

Design Changes

To ensure compatibility with the other group’s database schema, we updated our Book IDs from the INT data type to BIGINT, aligning with their schema. This required modifying our original database schema and addressing all dependent views that relied on the Book IDs. Running the script BookBigInt.sql applies these changes, enabling seamless integration between the two systems. image. Following this update, we created a new database dump reflecting the altered schema.

Next, we created a new merged database called MergedDB via this command:

CREATE DATABASE MergedDB;

We then connected to the new database and ran CreateTables to create the tables from both systems. The order of table creation is crucial to avoid foreign key constraint violations.

Data Generation

We improved the data generation script as part of the integration process. By doing so, we ensured that the data was consistent with the updated schema in a way that would facilitate the integration of the two systems.

Data Integration

To successfully integrate the two systems, we ordered the table data insertion commands. This ensured that foreign key constraints were respected and no violations occurred during the data integration process.

Insertion Order:

  1. Country
  2. Publisher
  3. Archive
  4. Author
  5. Language
  6. Genre
  7. Book
  8. Location
  9. Written By
  10. Published By
  11. Written In
  12. Type Of
  13. Is In
  14. Employee
  15. Disposal
  16. Upkeep
  17. Archive Assignment

image

After completing the data insertion stage, our database looks like this:

image

We also ran a sample query to ensure that the new database is working as expected

image

SELECT 
    B.Title, 
    B.Release_Date, 
    B.Rarity, 
    A.Book_Type, 
    L.Floor, 
    L.Shelf, 
    L.Condition,
    A.Archive_Number
FROM 
    Book B
JOIN 
    Location L ON B.ID = L.ID
JOIN 
    Archive A ON L.Archive_Number = A.Archive_Number
LIMIT 5;

Insertion Scripts

Insertion Logs

Views

After incorporating both systems, we created two new views to utilize the additional functionality seamlessly.

  1. Find_Archived_Books_View

This view displays all books that have been archived, including their respective archive ID, book ID, and the date they were archived.

CREATE OR REPLACE VIEW Find_Archived_Books_View AS
SELECT 
    B.Title, 
    B.Release_Date, 
    B.Rarity, 
    A.Book_Type, 
    L.Floor, 
    L.Shelf, 
    L.Condition,
    A.Archive_Number
FROM 
    Book B
JOIN 
    Location L ON B.ID = L.ID
JOIN 
    Archive A ON L.Archive_Number = A.Archive_Number;
  1. Disposed_Books_Employees

This view shows all books that have been disposed of, along with the employee responsible for the disposal.

CREATE OR REPLACE VIEW  Disposed_Books_Employees AS
SELECT 
    E.Employee_ID, 
    E.Name, 
    E.Role, 
    E.Age, 
    E.Salary, 
    D.Date AS Disposal_Date, 
    D.Method, 
    D.Material_of_Book, 
    B.Title AS Book_Title, 
    B.ID AS Book_ID
FROM 
    Employee E
JOIN 
    Disposal D ON E.Employee_ID = D.Employee_ID
JOIN 
    Book B ON D.ID = B.ID;

Logs for Views

View Queries

View 1 Find_Archived_Books_View

Query 1: Get all archived books that are legendary, academic, and in the study area between 2000 and 2020

SELECT * FROM Find_Archived_Books_View WHERE release_date BETWEEN '2000-01-01' AND '2020-12-31' AND rarity = 'Legendary' AND book_type = 'Academic' AND floor = 'Study Area';

Query 2: Delete archived legendary academic books that were stolen but were last in the returns section (fails due to table joins)

DELETE FROM Find_Archived_Books_View WHERE  book_type = 'Academic' AND rarity = 'Legendary' AND floor = 'Returns' and condition = 'Stolen';

This fails as the view is based upon several tables (using joins).

View 2 Disposed_Books_Employees

Query 3: Get all disposed books that were buried and made of synthetic material, and were disposed by disposal workers over 60 years old

SELECT name, role, disposal_date, book_title, age FROM Disposed_Books_Employees WHERE method = 'Burial' AND material_of_book = 'Synthetic' AND role = 'Disposal Worker' AND age > 60;

Query 4: Give the employee who disposed of the most books a salary increase of 10%

UPDATE 
    Disposed_Books_Employees
SET 
    Salary = Salary * 1.10 
WHERE 
    Employee_ID = (
        SELECT 
            E.Employee_ID
        FROM 
            Employee E
        JOIN 
            Disposal D ON E.Employee_ID = D.Employee_ID
        GROUP BY 
            E.Employee_ID, E.Name
        ORDER BY 
            COUNT(*) DESC
        LIMIT 1
    );

This fails as the view is based upon several tables (using joins).

Logs for View Queries

Queries

Additionally, we added new queries which take advantage of the views that have been created.

View 1 Find_Archived_Books_View

Query 5: A book was declared missing but we found it. Update the condition status to match this in the view

UPDATE Find_Archived_Books_View
SET condition = 'Found'
WHERE archive_number = (
    SELECT archive_number
    FROM Find_Archived_Books_View
    WHERE condition = 'Missing'
    AND shelf = 99
    AND archive_number = 2
)
AND title = 'Yard job stop court computer beautiful enough such.';

Query 6: Delete all books from the archive with the title that contains the words ‘car TV’ from the view

DELETE FROM Find_Archived_Books_View WHERE Title LIKE '%car TV%' AND Archive_Number = 46;

View 2 Disposed_Books_Employees

Query 7: Insert a record saying that Sonya (Existing Employee) disposed of a specific book today

INSERT INTO Disposed_Books_Employees (employee_id, name, role, age, salary, disposal_date, method, material_of_book, book_title, book_id)
VALUES (97, 'Sonya West', 'Archivist', 20, 67252, '2025-01-26', 'Burial', 'Paper', 'Federal hundred sure country.', 99999);

Query 8: Update the disposal method of the book with the title contains the words ‘floor plan’ to ‘Recycling’ (from ‘Burial’) in the Disposal table (query fails due to view using joins)

UPDATE Disposed_Books_Employees
SET Method = 'Recycling'
WHERE Book_ID = (SELECT Book_ID FROM Disposed_Books_Employees WHERE Book_Title LIKE '%floor plan%');

These queries fail to execute as both views are based upon several tables (using joins) and are therefore not modifiable.

Logs for Queries

Timing Queries

Using the timings, via the \timing command in the PSQL shell, we were able to determine the time. The timing logs are also found in this file

View Number Query Number Query Runtime (ms) Note
1 1 138.118  
1 2 4.434 Query Fails
2 3 196.596  
2 4 109.425 Query Fails
       
1 5 3.771 Query Fails
1 6 92.362 Query Fails
2 7 7.094 Query Fails
2 8 3.805 Query Fails

Query Fails - Indicates that the query failed to execute due to this ERROR: cannot update view "disposed_books_employees" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

DB Dumps for Stage 4