A group of PostgreSQL scripts that setup a database for a fictional library. Having worked with SQL before, I wanted to learn PostgreSQL, and I decided that designing a database for a fictional library would be a fun challenge. Below I describe the features, motivate the design choices I made, and describe the specific tables and columns I included.

This script drops all tables and recreates them. This is useful during development, but dangerous in production!

This script creates two callable procedures that automate the processes of loaning and returning a piece of media. The user only needs to specify the id of the media and the user that is making the loan.

This script creates a couple of views for the database that are useful. For example a view of all currently active loans, the number of books in various subjects, employees per department, etc.

I have included a python script that generates data for development purposes.

For this project, I wanted a compromise between database complexity and employee usability. All tables use automatically incremented integers as primary key which allows for convenient references, and does not require storing long convoluted UUID strings. As an additional challenge, I wanted this library to reflect the large infrastructure of a national library. I have therefore included "departments", which are essentially different branches of the library organization. A department could be a specific library such as in a city, but also a specific sub-organization such as "management", or "transportation". This allows the database to describe an internal hierarchy, where deparments report to other departments, and different types of departments can be distinguished. In addition, one department may have several "locations", which are specific buildings, or annexes with individual addresses. This allows us to distinguish between different locations that are part of the same department, for example two buildings in a city that function as the same library in the organizational structure.

In addition, I have in some tables included columns for "codenames", i.e. abbreviations which may be useful when departments and locations have long names. For example "East Oregon Contemporary Library of the Culinary Mind" (EO-CLCM).

Each instance represents a "serial" of media, for example a trilogy of books.