> For the complete documentation index, see [llms.txt](https://pycoders-nl.gitbook.io/pycoders-handbook/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://pycoders-nl.gitbook.io/pycoders-handbook/~/changes/YTi8eWfJe4r0epZkSSSg/database/module-project.md).

# Module Project

March 2023

<figure><img src="https://lh5.googleusercontent.com/leJDoPGKGJ620bL2-v6eux917WlFh1ktpIyIDMjcxGs2I_XrrXggW7FMJ-RG6PZqNCPLuMrgUnemJpN_O7CnJQZp50uoMBFyqjrQAVWbNIVDtLSRn97x0jScpvsTzX2cumIoJx64Mfhl2ge0b35o7zQOmLY08d-bqGjeM8NPaNXWX1vC5hrS4FLqj9V_IQ" alt=""><figcaption></figcaption></figure>

## Library CLI Application

### <mark style="color:red;">Overview</mark>

This project aims to create a library system. Let’s imagine a library:

* You can give books to this library.
* You can register to this library and keep track of the books you read/liked.
* You can find the books by their genre/author.
* You can see the top books/authors.&#x20;

With this project, you will be able to have all these functionalities in your Library Command-line application.

### <mark style="color:red;">Sample Scenarios</mark>

#### <mark style="color:green;">**Scenario 1**</mark>

To illustrate, a person who wants to borrow the “Harry Potter” book from the library needs to first register at the library. Thus he will enter the command **sign\_up**, only username is required to sign up. After signing up, he will enter the command **borrow\_book** and will give the “Book ID” together with this command. After running the **borrow\_book** command if this book is available in the library he will be able to borrow the book. Later he can run the command **return\_book** to return this book. A user can also mark books as read by running **mark\_read** command or add this book to his/her favorites by running **fav\_book** command. Both commands will require “Username” and “Book ID”.

#### <mark style="color:green;">**Scenario 2**</mark>

To illustrate,  everyone can add books to the library by running the **add\_book** command. It is possible to search books by their name or their genre which is described in more detail in the [Command Details](#command-details) section. By executing these commands, you can also see detailed information about the books including their “Book ID”.

### <mark style="color:red;">Typer</mark>

You will use the [Typer](https://typer.tiangolo.com/) library in the implementation of this project. Typer is a library for building CLI applications that users will love using and developers will love creating. You can follow [this tutorial](https://typer.tiangolo.com/tutorial/) to get familiar with the library.

Example repository we created for you → <https://github.com/iremugurlu/sample-library-cli-app>

### <mark style="color:red;">**Command Details**</mark>

The commands are split into 2 categories: the commands do not require a sign-in (available to all users) and the commands require sign-in.

#### Commands Do Not Require Sign-in

#### <mark style="color:blue;">**1. start**</mark>

This command doesn’t take any arguments. It creates the database if not created. If it is created, it connects to it. Finally, it logs the following output.

<figure><img src="https://lh5.googleusercontent.com/KdSVVBM34Zpaq6-8GEivye2cjavo4aFxuphHlfBYlvIEcx4ziaM9r-Rcj5nMYnA0EtpVU64Ru_LjtEP0YorLsW-vDihuE-BDCY0UjVz0xOHAKYKnELbiA_WghyLUFzdpzz2FY5pOJbSkkd9Q3hozTeaWb00jNkVr1HXxToKOoT9KdjfzZwyUAhfEuYLPqg" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**2. sign\_up**</mark>

This command takes ‘NAME’ and ‘PASSWORD’ as an argument. It adds a user to the database with his username and password. If the username is already in use, logs an error message and asks for another username.

<figure><img src="https://lh6.googleusercontent.com/rHZzOL0g3plnsx2aA2ao_5k4F96xTiE1bcoOvuUk3giDzfOct9s_y5leL_KGZ00iYXIOU2wYJ7kszXLf7lVLt2GyCcGxp6yZzGK3qJTKJTUXUbzFal5RW6aPK1q5BX4XhMLiDmxQ8-nTIlLfNzEjEjasw2G4Ni-O7EsGRcCcQpiO23PEx4JJmDC6-oGT6A" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**3. sign\_in**</mark>

This command takes ‘NAME’ and ‘PASSWORD’ as an argument. It logs in the user into the system if the username and password combination exists in the database (a user needs to sign up before signing in). Logs an error message when the username doesn’t exist or the password is incorrect. After a successful login, [the commands require a sign-in](#commands-require-sign-in) will be available to this user.

*Successful*

<figure><img src="https://lh6.googleusercontent.com/e6E37pr_-65vup8-7PIIVuE0pD3Misr_nh2s3IVf6XfU3OZaFqrWVwNpWjqkP6ocWOX3HYrPbXy5e8ZQouHuqBoKhCae2cEFvCP3lb6tOM6apAEoctzrboSFK-MDRAR6UGSulQsGOzkGvNrXIqMa85E" alt=""><figcaption></figcaption></figure>

*Error*

<figure><img src="https://lh5.googleusercontent.com/hopp3PaU6UBuvlD-qPXIL_FJrLrXsxdcmSSoPQVsA1wh_wdO9M_t4GhAx-Juw76Eu6LLGcY-jGS9MuQb4RHjVkos0fnI4Q2tyxsMgk5iYLI5ox6T1eZF5tzrjlj_ckC2D1x7L7HQQpcL89sQTrKvgvs" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**4. search\_by\_name**</mark>

This command takes ‘NAME’ as an argument. It displays books with this name in the table view. **Note:** You can show ‘True/False’ or the number of available books in the ‘Availability’ column.

<figure><img src="https://lh6.googleusercontent.com/vzP0LH9iIxCqyV9yDcJChUCB03JnYYrLciPzC4Ef6BYKkifz6Vc0EYcsMgpVDC3NsHzq6p1x3iDKNRj0Z8QBqXNQ18J98VHGEwt0HjOQz_kI-VAuijg5OOTzp7LbXW974pzYx9kHlxUac1cGTMdw-xyDVSCF8iP7JNi6-npg3WWwZ79xFvAeuMz4o5OMKw" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**5. search\_by\_author**</mark>

This command takes ‘AUTHOR’ as an argument. It displays books written by this author in the table view. **Note:** You can show ‘True/False’ or the number of available books in the ‘Availability’ column.

<figure><img src="https://lh4.googleusercontent.com/0zdeq343bvwLBEaaHTZvFYa11WLsXdIYVzk9ayeS3FUZ3Ru6ot-9-PBlkN3wpbA18WoKmS0BjpvXhZ85aaiLZgklDe3_PdE0xsQ-VL5tbs94DiiH-YRJ9eEokJczZtijZRJypmmLe9vPOqZoNMOjDHejAZr4MhPoamrmQbohR2aWHefHxf1tuMqefCyS2Q" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**6. recently\_added**</mark>

This command takes ‘GENRE’ as an **optional** argument. If it is provided, it displays the 5 most recent books added in a given genre. If not, it displays the 5 most recent books added. Keep the “date\_added” information when adding a book so you can identify the most recent ones. Also, keep the “username” who added this book for the “Added By” column. **Note:** You can show ‘True/False’ or the number of available books in the ‘Availability’ column.

*Without genre*

<figure><img src="https://lh4.googleusercontent.com/23PEStstdN168JynSA2bLs1C5zQX4w3ofcPoTYYq4kIklnnYPGdpmM0xaF0jVJbJd_hbgfzPwverQJKu6OcbR3a963-N2ldUrG7UYlYYfP_Tu1ePjRq2g240lfn6Da97fb51va628_R8j53EKpHpc-JEdxFRFD0ygucHYVVvIZV-17_zZsK0HgFZlryx9g" alt=""><figcaption></figcaption></figure>

*With genre*

<figure><img src="https://lh5.googleusercontent.com/kH6fzpzB_u3OIfrmRSG0HaDp7NGYKMPg19NXQnMJJO8_UjZj5KoO4R21bnxiZ84VD2t82FsRGcWIAjN0axKGRLXxMEKXOvcHLXvGDlAu0a8iZKVgnPeMsfApXJCwgnj4WOpUw9FhD3wvFsXL97C0ikBx8t7AFYhQwns8cJ2Ly8dmXDtBXySGpp14pgyWvg" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**7. most\_read\_books**</mark>

This command takes ‘GENRE’ as an optional argument. If it is provided, it displays the 10 most-read books in a given genre. If not, it displays the 10 most-read books.

*Without genre*

<figure><img src="https://lh3.googleusercontent.com/MfgiLM-uUnkuUZZpSYMRhdxXiNADPTAcTqb5KAIMsc-wDmm3Hndifx5I-F1Wpy6NNVIQFD9IQGDoSrKG2HPr_MldO1YtEGTz15ZzNy-dCQVrnm8nx0SBPuhGn5HnzqJXpQts9k0YOB31VcTAxJQkjMUnYfYLaAsZXttGoQh85fwPqcR7HNU5vlC-Cp5o3g" alt=""><figcaption></figcaption></figure>

*With genre*

<figure><img src="https://lh6.googleusercontent.com/ehPSsNKJfvVD8caBMZa9EeeqK4H7-Z-NSUlVrDI3Io6uJsUrR3JY55YFFrFgpE3YSt9-rDbsLPwEUXVUyB5Uv_Ch_RU3dX9iLlQYdLmkDoOpMlTCSqJLLo2pPOhdPcGJhdjJYfGgoD6ADeIumJgMa9pbjpU56CUEqIho1mRHRe_OdaApzQQ_ZPvTvmFDWw" alt=""><figcaption></figcaption></figure>

**Note:** You need to have an ‘Added By’ column as well. These screenshots for the ‘recently\_added’ command are not up to date.

<mark style="color:blue;">**8. most\_favorite\_books**</mark>

This command takes ‘GENRE’ as an **optional** argument. If it is provided, it displays the 10 most favorite books in a given genre. If not, it displays the 10 most favorite books.&#x20;

*Without genre*

<figure><img src="https://lh6.googleusercontent.com/k1i_8AWKrDdYEazDk0GSNs_qX_9k2tl1Oqc7uv6PI2-clttD3QPOQWaoVXFw-ksuouHV_yFqxhnUNWaP4H7Q3xN_O36vNt-jlsUUAx3B-ZCK2d2LiJqvxXoZcXPVEGetiTiDdeDyTntFvr2J3HNaGj22K21hVJ_p2eUzHTEolSug4ptGzpzA66XLdnxlsQ" alt=""><figcaption></figcaption></figure>

*With genre*

<figure><img src="https://lh6.googleusercontent.com/YcsYY8AiGPyvounBW2NmacetM2-mVKoPol1pB_U4V6XYbM65z-W-adtzGl13Vrt5Qagj5WCQuifudUAb7TmV6V7IhUVN7_cXMo8Uaglf2N_32R7g7lKAu3788XQNeylUjZ40fciA1iZCopGdwGzFdVTkAs3ImSIMOhHYBcZEfHbc45v4DCgaetjdPyYmMA" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**9. most\_read\_genres**</mark>

This command doesn’t take any arguments. It displays the 5 most-read genres.

<figure><img src="https://lh5.googleusercontent.com/r-yUefnEhtE7TZlVSm1HKZpvMHMj2-Km8i6F8Sv4tJLGW6vGDWfU-1gF3msXWt3xRPr4Zojd-rJcLo-tNmDGn4PWQpRAZpfHL0umM3N4craYPXsQIMedxWIAuEnJzJuTfezdZtebtc3s5rroEWKtIwDp8f2UQJC4ieRJoin-gLvhkiW1CNVnCN0r7-K1FA" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**10. most\_read\_authors**</mark>

This command doesn’t take any arguments. It displays the 3 most-read authors.

<figure><img src="https://lh3.googleusercontent.com/mCwuPkUTjMnpHN2RwKyVjK5XBeTn-u1HCVshwmhbA3okCT5DSXm-2OB4KgLAzkyxcW4hOR1aflYuUSrhmkH7i_7tfV2sbW6Y_UUYg52zrtYPIHgBX4xHZY1W6YbffEdrC1rmH10vEwPpKGd9OFypBf069n7J3IqRBty1Cm7R6s8Gcp-RvNBORnnXbnCZgQ" alt=""><figcaption></figcaption></figure>

#### Commands Require Sign-in

The following commands are not available for users who are not signed in. If the user tries to run these commands before signing in, log an error message saying that they need to log in.

<mark style="color:blue;">**11. add\_book**</mark>

This command doesn’t take any arguments. It asks for the details of the book: name, author, # pages, and genre. Then it adds the book to the database. The books are identical by the combination of their name and author. If another book with the same name and author exists, increment the quantity of the existing book instead of adding a new one.

<figure><img src="https://lh3.googleusercontent.com/-prGzDRjP_FyhQPTYSLSXT5qhRikzMVI7Hjo2dszVGawjybk8NdcWGoHtJ5mnJkR6J3TWnEY3yd2Pf3av08epN7_KakBa7yZb8XdeTWLLUdnV_4tD8j-kEql89LxjJuwvTjgEVSwQlD2EotBH6z1BbjkN18jFVEtE4Qd8SzbNhJQRi-Hmp9AAHPVOvXQeA" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**12. borrow\_book**</mark>

This command takes ‘BOOK ID’ as an argument. If this book is available, saves the data as the signed-in user borrowed the book and reduces the available amount of the book. If not available, then logs an error message saying this book is not available.

*Available book*

<figure><img src="https://lh5.googleusercontent.com/-ivwekRK-0bB3TYT3wjsmspne62qno6lgrlrLCXH9I2Px4NSRBNRmD5-i1aHmdE_5Ge6NVzSnF5n6y2TWi3-43DBXwPBJZZo02-ykzpoH_scIvkFfTOI1SIkxO_SDoQEolS6EVHM_zMbMNBaJ66KOa0IdX4PE5GzmC3eoxy1ywccH-4qtv54tNuydIFCag" alt=""><figcaption></figcaption></figure>

*Unavailable book*

<figure><img src="https://lh3.googleusercontent.com/wGqjvjauVi_vONk3hsGnHq2fe8CukuNRRlvD6m2JYWfzxZqWXql8ZB-eTEZUb2s9wqB0hjSIM5SvBQfRAzasHmIpZG99r7YNPbEA5C0nhYyVG86XgWxy4UKDVfQdqhYx7c5xVaPxVhjjyZdITX13jQriAzbJkTua7bAuKBqkB5LCNtiU5dQigogxE1g58Q" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**13. return\_book**</mark>

This command takes ‘BOOK ID’ as an argument. If the signed-in user borrowed the book previously, it saves the data as this user returned the book and increments the available amount of the book. If this user did not borrow the book, then logs an error message saying this book is not borrowed by him.

*Borrowed book*

<figure><img src="https://lh3.googleusercontent.com/s1UQnsPxsGkLu_wvBjBcTSKBDAjRpwGjGGuDkh-5pUsV0FkwiJGuDbYHTWsEg8J95-_f5tXiDHNdc-c_D6UvFQspKJ_EG53dkqJhL1FoAxUJ3-FINH8oAjhHNIMSLvyyaZijySav_xkKrocXg7hepwRU88-yHxJjly-rToHUKqCEEeLUyn1eFP_zgMiJsg" alt=""><figcaption></figcaption></figure>

*Not borrowed book*

<figure><img src="https://lh5.googleusercontent.com/FN7cYIiAVuQYT3-kz4HEFWgBcz2KpJVzZCh1rAHjLziP6Y4KxLxOz2F45b9dMlh-aM_8pOOpEjBRYLw_qTk7zP73kCIpgdd_crXEe9LgFXEsOUp0XSuSd6idh_1iwH_7cYjVabM4UASp1e50hKukSbaFbmg7BpjMF_9rY_ViNNq-BFGMUu2-Ecn2mZXahw" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**14. mark\_read**</mark>

This command takes ‘BOOK ID’ as an argument. It marks this book as “read” for the signed-in user.

<figure><img src="https://lh5.googleusercontent.com/khd_0-tsl-zFnQ_DTXQVhbt_w2XZkuJOVaKPJvePoOibnMnf8gUvqZmQTbiEbi5-tUW8gCdWvzP9PSceD81w0tKYh7k9iiIKskAa3heZOp-UtRCdBNHk4I2UrJ261ny4EYi81tlYChRlabq3yRlIR5_VyGu7_IA8sZoOgFJPYE9tVwc54Hu3ruA_892ZCQ" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**15. fav\_book**</mark>

This command takes ‘BOOK ID’ as an argument. It adds this book to signed-in user’s favorites

<figure><img src="https://lh5.googleusercontent.com/4L3o_B9_Xv0KMWHGq9IU1UY2lxdif94SmVTu_Hh09fuL3HtpQiVaXlG6iTn0Ir9WtHt5WKKOCpV1GbCwChFEg1NHjdIvoNUBeMOORTvt8D7nKxMmPzEpe1co6a68Lq5TDotF0wEdkUyC9727oOjkcRCFqt6vYFPVAK3SLOVXQp_ISS4jipYkfWQpBARQaQ" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**16. my\_books**</mark>

This command doesn’t take any arguments. It displays the books read and favorited by the signed-in user.<br>

<figure><img src="https://lh4.googleusercontent.com/J45bRjspzU-U64iwdwrkuvKfYQ6qrSClu3Lw7hCClkzkvGLIs7vX6IucDbPZfciiApdwatVurGrIrMB0Qd43fdVSj-3Z2I5tIh-CmVwWWXXWi50XEJg3k1BfwVVBnEtu05O6AXxNk-IMVH1jPZyLDqHH556Xs7d3y2FhHwN4qWd1i-XyTSwNRIW5rZ38Tw" alt=""><figcaption></figcaption></figure>

<mark style="color:blue;">**17. statistics**</mark>

This command doesn’t take any arguments. It displays the following statistics for the signed-in user in a table: number of books you read, number of authors you read, number of genres you read, and number of total pages you read.

<figure><img src="https://lh5.googleusercontent.com/FBqipRrUnqdvjj5FfmYRX3XOjvD09c4Z8L6hGQ8JxbVtQB6k97mcY0xOEWLtuqh_USZePwxtAhZgg83CPSXzDNrRAmxvY7MuDoNzdO0ivtM8EXPz6QUltL-eph-Yp0yKtepyIfjafUOE6P-qsqaxJUiadmjs6OPxmPwDQJZLLndq4TyJKv4L6ConYkgGpQ" alt=""><figcaption></figcaption></figure>

### <mark style="color:red;">**Definition of Done**</mark>

* Full attendance at mentor meetings
* ERD Diagram
* Python CLI application
* Project presentation&#x20;
* GitHub repository with a README

### <mark style="color:red;">**General Requirements**</mark>

* [**PostgreSQL** ](https://www.postgresql.org/)will be used as a database management system.
* [**GitHub** ](https://github.com/)will be used in the project.&#x20;
  * Each team will have a GitHub repository and each team member will be added as a collaborator.&#x20;
* [**Trello** ](https://trello.com/)board will be used in the project.
  * Team mentors will be added to the board and they will check if the team uses Trello actively.&#x20;
* **Meetings**&#x20;
  * A minimum of 30 minutes of meetings will be held with teammates every day. The content of the daily meeting is generally as follows: what each teammate has done, the general direction of the project and task sharing until tomorrow.
  * Each team will have a mentor. A meeting will be held with the team mentor and team members on the specified dates (once in 2 - 3 days). Each team can determine the time of the meetings.
* **Presentation** at the end of the project. To complete the project, all members have to show up in the presentation and present the program.

### <mark style="color:red;">Instruction Steps</mark>

You have to stick to the schedule. You will have a progress meeting with your mentor once in 2-3 days. You have to complete related steps before the next meeting.

#### <mark style="color:blue;">**Step 1**</mark>

* Project Kick-off Meeting
* Reading instructions
* Understanding and discussing the requirements of the project with your teammates&#x20;

<mark style="color:blue;">**Step 2**</mark>

<mark style="color:orange;">Database & ERD Design</mark>&#x20;

* Design the database according to the requirements of the project.
* You can use the [pgAdmin ERD tool](https://www.pgadmin.org/docs/pgadmin4/6.8/erd_tool.html) to draw your ERD diagram.

<mark style="color:green;">**Note:**</mark> Present your DB design and ERD diagram to your mentor so you can get feedback about it!&#x20;

#### <mark style="color:blue;">Step 3</mark>

* Extract the SQL query from your ERD design to create the necessary tables.
* Use this SQL file in your python application to create tables in your database upon the application start.

<mark style="color:green;">**Note:**</mark> [Step 4](#step-4) and [Step 5](#step-5) can be done in parallel by different teammates!

#### <mark style="color:blue;">Step 4</mark>

Write SQL queries to get/add the required information from/to the database.

<mark style="color:green;">**Tip:**</mark> Having a separate `database.py` file to connect to database and execute queries would ease your work!

#### <mark style="color:blue;">Step 5</mark>

Implement all commands described in the [Command Details](#command-details) section.

#### <mark style="color:blue;">Step 6</mark>

Connect commands and database functions to have a running app.

#### <mark style="color:blue;">Step 7</mark>

<mark style="color:orange;">Test Your Program</mark>

* Test your program and try to find the bugs.
* Execute all of the commands your program has and verify that they work correctly.
* Try different scenarios to discover new bugs.
* Improve the exception handling in your program. Show useful error messages to users when undesired behaviour occurs.