The Library Checkouts Database is a fictitious and simulated SQLite database made for learning and practicing SQL. The database aims to model how and what a library keeps track of when lending books.
Below are the data definitions of the tables and columns in the Library Checkouts Database.
| column | description | data type |
|---|---|---|
| id | unique id of the user | integer |
| first_name | first name of the user | text |
| last_name | last name of the user | text |
| birth_date | birth date of the user | date |
| phone | cell phone of the user | text |
| email address of the user | text | |
| address | address where the the user lives | text |
| city | city where the user lives | text |
| state | state where the user lives | text |
| zip_code | zip code where the user lives | integer |
| column | description | data type |
|---|---|---|
| id | unique id of the book checkout | integer |
| user_id | id of the user who checked out a book | integer |
| book_id | id of the book that was checked out | integer |
| checkout_date | date the book was checked out by the user | date |
| days_checking_out | number of days the user will check out the book for | integer |
| due_date | date the book is due based off days_checking_out | date |
| return_date | date the book was returned by the user | date |
| days_checked_out | number of days the book was checked out for | integer |
| returned_with_damage | a number to distinguish whether the book was returned with damage 0 = the book was not returned with damage 1 = the book was returned with damage |
integer |
| column | description | data type |
|---|---|---|
| id | unique id of the book | integer |
| title | title of the book | text |
| author | name of the author | text |
| isbn | isbn of the book | text |
| date_published | date the book was published | date |
| publisher | publisher of the book | text |
| format | the format of the book Ex) Hardcover | text |
| pages | the number of pages the book has | integer |
| column | description | data type |
|---|---|---|
| book_id | id of the book | integer |
| genre_id | id of the genre | integer |
| column | description | data type |
|---|---|---|
| id | unique id of the genre | integer |
| name | name of the genre | text |
In the latest release, download the assets
- lcdb.db: the SQLite database
- library-checkouts-erd.png: the entity relationship diagram of the database
The database was created using R and Python files.
lcdb_ddl.sql
A SQL script to create the database table definitions.
initiliaze.py
A Python file to execute
lcdb-ddl.sql.
users.py
A Python file to create the users table.
books.R
An R file to create the books, genres, and book_genre_link tables.
checkouts.R
An R file to create the checkouts for users and books.
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License
