A library database made in MySQL with books stock, authors, customers and loans.
SQL project for Coderhouse.
- Entity-relationship diagram
- Tables creation
- Data entry
- Views
- Functions
- Stored procedures
- Triggers
- Users creation
- TCL statements
- Database backup
🔑: Primary Key
🗝: Foreign Key
Just run the tables creation script.
Match id_country with country name.
Column | Type | Description |
---|---|---|
id_country | tinyint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a Country |
country | varchar(30) NOT NULL | Country name |
Match id_category with category name.
Column | Type | Description |
---|---|---|
id_category | tinyint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a category |
category | varchar(30) NOT NULL | Category name |
Match id_publisher with publisher name.
Column | Type | Description |
---|---|---|
id_publisher | smallint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a publisher |
publisher | varchar(30) NOT NULL | Publisher name |
Match id_book_language with language name.
Column | Type | Description |
---|---|---|
id_book_language | smallint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a language |
book_language | varchar(30) NOT NULL | Book language name |
Match id_book_status with book status. Book status can be borrowed, lost, destroyed, etc.
Column | Type | Description |
---|---|---|
id_book_status | tinyint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a book status |
book_status | varchar(30) NOT NULL | Book status name |
Library customers info.
Column | Type | Description |
---|---|---|
id_customer | smallint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a customer |
first_name | varchar(30) NOT NULL | Customer first name |
last_name | varchar(30) NOT NULL | Customer last name |
dni | int unsigned NOT NULL | Customer DNI |
phone | varchar(20) NOT NULL | Customer phone number |
varchar(50) NOT NULL | Customer email | |
birth_date | date NOT NULL | Customer birth date |
Book info.
Column | Type | Description |
---|---|---|
id_book | smallint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a book |
id_publisher | smallint unsigned NOT NULL | Foreign Key to publisher table |
title | varchar(128) NOT NULL | Book title |
book_description | text NULL | Book description |
publication_date | date NULL | Book publication date |
id_book_language | smallint unsigned NOT NULL | Foreign Key to book_language table |
Match book copies with id_book from book table and its status from book_status table.
Column | Type | Description |
---|---|---|
id_book_inventory | smallint unsigned NOT NULL AUTO_INCREMENT | Primary Key for a book copy |
id_book | smallint unsigned NOT NULL | Foreign Key to book table |
id_book_status | tinyint unsigned NOT NULL | Foreign Key to book status table |
Book copies storage location. With id_book_inventory (from book_inventory table), shows its shelf number and shelf_section number.
Column | Type | Description |
---|---|---|
id_book_inventory | smallint unsigned NOT NULL | Foreign Key to book inventory table |
shelf | tinyint unsigned NOT NULL | Shelf number |
shelf_section | tinyint unsigned NOT NULL | Shelf section number |
Describes library loans.
Column | Type | Description |
---|---|---|
id_loan | int unsigned NOT NULL AUTO_INCREMENT | Primary Key for a loan |
id_customer | smallint unsigned NOT NULL | Foreign Key to customer table |
id_book_inventory | smallint unsigned NOT NULL | Foreign Key to book inventory table |
loan_date | datetime NOT NULL | Loan date time |
due_date | datetime NOT NULL | Loan due date time |
Book authors info.
Column | Type | Description |
---|---|---|
id_author | smallint unsigned NOT NULL AUTO_INCREMENT | Primary Key for an author |
first_name | varchar(30) NOT NULL | Author's first name |
last_name | varchar(30) NOT NULL | Author's last name |
id_country | tinyint unsigned NOT NULL | Foreign Key to country table |
Books by author. Match id_author from author table with id_book from book table.
Column | Type | Description |
---|---|---|
id_author | smallint unsigned NOT NULL | Foreign Key to author table |
id_book | smallint unsigned NOT NULL | Foreign Key to book table |
Books by category. Match id_category from category table with id_book from book table.
Column | Type | Description |
---|---|---|
id_category | tinyint unsigned NOT NULL | Foreign Key to category table |
id_book | smallint unsigned NOT NULL | Foreign Key to book table |
Books returns. Describes loans (from loan table) returned.
Column | Type | Description |
---|---|---|
id_book_return | int unsigned NOT NULL AUTO_INCREMENT | Primary Key for a book return |
id_loan | int unsigned NOT NULL | Foreign Key to loan table |
return_date | datetime NOT NULL | Loan return date time |
Tables explained in Triggers section.
- Log statement table
- Log loan table
- Log deleted book return table
Just run data_entry_script.sql for an example of data entry.
Fake data from Mockaroo:
- Book titles are actually movie titles.
- Books description are sentences chosen randomly from lorem ipsum.
Just run views.sql for an example of views.
Show books info order by id_book.
Column | Description |
---|---|
id_book | Book id |
title | Book title |
book_description | Book description |
publication_date | Publication date |
publisher | Publisher |
book_language | Book language |
Show authors info order by id_author.
Column | Description |
---|---|
id_author | Author id |
complete_name | Author complete name |
nationality | Author nationality |
Show lost books order by id_book.
Column | Description |
---|---|
id_book_inventory | Book inventory id |
id_book | Book id |
title | Book title |
Show loans by book order by times_borrowed.
Column | Description |
---|---|
id_book | Book id |
title | Book title |
times_borrowed | Times borrowed |
Show overdue loans order by due date.
Column | Description |
---|---|
id_loan | Loan id |
id_book_inventory | Book inventory id |
title | Book title |
id_customer | Customer id |
customer | Customer complete name |
phone | Customer phone |
Customer email | |
due_date | Due date |
Show borrowed books order by expected return.
Column | Description |
---|---|
id_book_inventory | Book inventory id |
id_book | Book id |
title | Book title |
loan_date | Loan date |
expected_return | Due date |
Show available books and its quantity in stock order by id_book.
Column | Description |
---|---|
id_book | Book id |
title | Book title |
quantity_in_stock | Quantity in stock |
In functions.sql:
- get_available_stock_book: Returns available stock book.
- get_book_times_borrowed: Returns book times borrowed.
- get_id_book_status: Returns
id_book_status
frombook_status
table, dependingbook_status
parameter.
- order_books_by: Orders
book
table bycolumn_to_order_by
parameter (inorder_way
ASC/DESC). This stored procedure will return an error if the column_to_order_by doesn't exist in book table or if the order_way is not "ASC"/"DESC"/"" - insert_or_delete_book_category: inserts or deletes an
id_book-id_category
match frombook_category
table depending onstatement_in
("INSERT"
/"DELETE"
). - insert_book_category: Inserts an
id_book-id_category
match frombook_category
table (usinginsert_or_delete_book_category
sp). - delete_book_category: Deletes an
id_book-id_category
match frombook_category
table (usinginsert_or_delete_book_category
sp). - update_book_status: Updates
book_status
inbook_inventory
table. - update_book_status_from_id_book_return: Updates
book_status
inbook_inventory
table, dependingid_book_return
frombook_return
table. - log_into_log_loan_table: Logs into
log_loan_table
, depending arguments.
See examples.
In triggers.sql:
Log statements table (for 'INSERT', 'DELETE'):
Column | Type | Description |
---|---|---|
id_statement | tinyint unsigned AUTO_INCREMENT | Primary Key for a statement |
statement | varchar(6) NOT NULL | Statement |
Note: we need to run the data entry script first, because we need the registers INSERT
and DELETE
.
Log loan table for loan table triggers:
Column | Type | Description |
---|---|---|
id_log | int unsigned AUTO_INCREMENT | Primary Key for a log |
id_statement | tinyint unsigned | Foreign Key to statement table |
id_loan | int unsigned | id_loan from loan table |
id_customer | smallint unsigned | id_customer from loan table |
id_book_inventory | smallint unsigned | id_book_inventory from loan table |
user_running_query | varchar(80) | User running the query |
log_date | date | Log date |
log_time | time | Log time |
- loan_AFTER_INSERT: Updates log loan table.
- loan_AFTER_DELETE: Updates log loan table.
- loan_BEFORE_INSERT: Updates book status to borrowed in book_inventory table.
Log deleted book return table for book return table triggers:
Column | Type | Description |
---|---|---|
id_log | int unsigned AUTO_INCREMENT | Primary Key for a log |
id_loan | int unsigned | id_loan from book_return table |
id_book_return | int unsigned | id_book_return from book_return table |
return_date | datetime | return_date from book_return table |
user_running_query | varchar(80) | User running the query |
log_date | date | Log date |
log_time | time | Log time |
- book_return_AFTER_DELETE: Updates log deleted book return table.
- book_return_BEFORE_DELETE: Updates book status to borrowed in book_inventory table.
- book_return_AFTER_INSERT: Updates book status to stock in book_inventory table.
Simple users creation examples:
- 'user_with_reading_grants'@'localhost': An user with only reading grants.
- 'user_with_more_grants'@'localhost': An user with reading, insertion and modification grants.
Simple tcl statements examples:
DELETE
statements on book return_table: Deleting random registers from book_return table withROLLBACK
andCOMMIT
example.INSERT
statements on publisher table: Inserting registers on publisher table withSAVEPOINT
s examples.
Data backup realized in backup_20220529.sql. (Date: 2022-05-29)
The backup is only data from all tables excepting log tables (log_statement, log_loan, log_deleted_book_return tables).
Usage:
$ mysql -u 'user' -p library < backup_20220529.sql