Mini project: college library tables

50 min · Beginner

A college library runs on three lists: the books it owns, the people who may borrow them, and the loans that connect the two. Each list becomes one table, and every column gets the type that matches its data.

What you'll build

The college library still keeps a paper register. It wants a database with three tables: books (ISBN, title, author, category, price, copies, date added), members (students and staff, with roll number, branch, mobile and a blocked flag, and card numbers that start at 1001) and loans (which book went to which member, when it was issued, when it is due, when it came back and any fine).

You write one SQL script, queries.sql, that drops and creates the three tables, inserts sample rows, and ends with three SELECTs that show the data. Run it as often as you like: it always gives the same result. Linking the tables with foreign keys and joining them come in the next modules; here every type and id must be right.

Output

+---------+--------------------------------+-----------+---------+--------+
| book_id | title                          | category  | price   | copies |
+---------+--------------------------------+-----------+---------+--------+
|       1 | Let Us C                       | TEXTBOOK  |  450.00 |      4 |
|       2 | Database System Concepts       | TEXTBOOK  | 1095.00 |      6 |
|       3 | Introduction to Algorithms     | REFERENCE | 4999.00 |      2 |
|       4 | Malgudi Days                   | FICTION   |  299.00 |      3 |
|       5 | Competition Success Review Jan | MAGAZINE  |   90.00 |      1 |
+---------+--------------------------------+-----------+---------+--------+
+-----------+-----------------+-------------+--------+------------+
| member_id | name            | member_type | branch | is_blocked |
+-----------+-----------------+-------------+--------+------------+
|      1001 | Aditya Joshi    | STUDENT     | CSE    |          0 |
|      1002 | Bhavana Reddy   | STUDENT     | CSE    |          0 |
|      1003 | Chirag Patel    | STUDENT     | ECE    |          0 |
|      1004 | Dr. Kavitha Rao | STAFF       | CSE    |          0 |
+-----------+-----------------+-------------+--------+------------+
+---------+---------+-----------+---------------------+------------+-------------+-------+
| loan_id | book_id | member_id | issued_at           | due_on     | returned_on | fine  |
+---------+---------+-----------+---------------------+------------+-------------+-------+
|       1 |       1 |      1001 | 2025-01-06 10:15:00 | 2025-01-20 | 2025-01-18  |  0.00 |
|       2 |       3 |      1002 | 2025-01-10 14:40:00 | 2025-01-24 | 2025-01-29  | 25.00 |
|       3 |       4 |      1003 | 2025-02-03 11:05:00 | 2025-02-17 | NULL        |  0.00 |
|       4 |       2 |      1004 | 2025-02-05 09:30:00 | 2025-03-07 | NULL        |  0.00 |
+---------+---------+-----------+---------------------+------------+-------------+-------+

Requirements

Your program should do all of these.

The script starts with DROP TABLE IF EXISTS for all three tables, so it can be run again and again. Each table has an INT AUTO_INCREMENT PRIMARY KEY id; member card numbers start at 1001.
Codes are text: ISBN CHAR(13), roll number and mobile VARCHAR; money (price, fine) is DECIMAL. Fixed lists use ENUM: the book category and the member type (STUDENT or STAFF).
Dates use DATE, the issue moment uses DATETIME, and the blocked flag is a BOOLEAN that defaults to FALSE. A loan that is not back yet has returned_on empty (NULL), and the fine defaults to 0.00.
The tables are named library_books, library_members and library_loans, with these columns in this order (every column not marked as the key allows NULL):
library_books: book_id INT AUTO_INCREMENT PRIMARY KEY, isbn CHAR(13), title VARCHAR(150), author VARCHAR(80), category ENUM('TEXTBOOK', 'REFERENCE', 'FICTION', 'MAGAZINE'), price DECIMAL(8,2), copies TINYINT UNSIGNED, added_on DATE.
library_members: member_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60), roll_no VARCHAR(12), member_type ENUM('STUDENT', 'STAFF'), branch VARCHAR(10), mobile VARCHAR(15), joined_on DATE, is_blocked BOOLEAN DEFAULT FALSE.
library_loans: loan_id INT AUTO_INCREMENT PRIMARY KEY, book_id INT, member_id INT, issued_at DATETIME, due_on DATE, returned_on DATE, fine DECIMAL(6,2) DEFAULT 0.00.
The script inserts 5 books, 4 members and 4 loans (the loans are exactly the four rows of the sample output) and ends with three SELECTs, each sorted by its id: books show book_id, title, category, price, copies; members show member_id, name, member_type, branch, is_blocked; loans show every column.

Build it step by step in the Build tab (4 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Creating Tables & Data Types

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

4 milestones

What you'll build

Output

+---------+--------------------------------+-----------+---------+--------+
| book_id | title                          | category  | price   | copies |
+---------+--------------------------------+-----------+---------+--------+
|       1 | Let Us C                       | TEXTBOOK  |  450.00 |      4 |
|       2 | Database System Concepts       | TEXTBOOK  | 1095.00 |      6 |
|       3 | Introduction to Algorithms     | REFERENCE | 4999.00 |      2 |
|       4 | Malgudi Days                   | FICTION   |  299.00 |      3 |
|       5 | Competition Success Review Jan | MAGAZINE  |   90.00 |      1 |
+---------+--------------------------------+-----------+---------+--------+
+-----------+-----------------+-------------+--------+------------+
| member_id | name            | member_type | branch | is_blocked |
+-----------+-----------------+-------------+--------+------------+
|      1001 | Aditya Joshi    | STUDENT     | CSE    |          0 |
|      1002 | Bhavana Reddy   | STUDENT     | CSE    |          0 |
|      1003 | Chirag Patel    | STUDENT     | ECE    |          0 |
|      1004 | Dr. Kavitha Rao | STAFF       | CSE    |          0 |
+-----------+-----------------+-------------+--------+------------+
+---------+---------+-----------+---------------------+------------+-------------+-------+
| loan_id | book_id | member_id | issued_at           | due_on     | returned_on | fine  |
+---------+---------+-----------+---------------------+------------+-------------+-------+
|       1 |       1 |      1001 | 2025-01-06 10:15:00 | 2025-01-20 | 2025-01-18  |  0.00 |
|       2 |       3 |      1002 | 2025-01-10 14:40:00 | 2025-01-24 | 2025-01-29  | 25.00 |
|       3 |       4 |      1003 | 2025-02-03 11:05:00 | 2025-02-17 | NULL        |  0.00 |
|       4 |       2 |      1004 | 2025-02-05 09:30:00 | 2025-03-07 | NULL        |  0.00 |
+---------+---------+-----------+---------------------+------------+-------------+-------+

Requirements

Your program should do all of these.

The script starts with DROP TABLE IF EXISTS for all three tables, so it can be run again and again. Each table has an INT AUTO_INCREMENT PRIMARY KEY id; member card numbers start at 1001.

Codes are text: ISBN CHAR(13), roll number and mobile VARCHAR; money (price, fine) is DECIMAL. Fixed lists use ENUM: the book category and the member type (STUDENT or STAFF).

Dates use DATE, the issue moment uses DATETIME, and the blocked flag is a BOOLEAN that defaults to FALSE. A loan that is not back yet has returned_on empty (NULL), and the fine defaults to 0.00.

The tables are named library_books, library_members and library_loans, with these columns in this order (every column not marked as the key allows NULL):

library_books: book_id INT AUTO_INCREMENT PRIMARY KEY, isbn CHAR(13), title VARCHAR(150), author VARCHAR(80), category ENUM('TEXTBOOK', 'REFERENCE', 'FICTION', 'MAGAZINE'), price DECIMAL(8,2), copies TINYINT UNSIGNED, added_on DATE.

library_members: member_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60), roll_no VARCHAR(12), member_type ENUM('STUDENT', 'STAFF'), branch VARCHAR(10), mobile VARCHAR(15), joined_on DATE, is_blocked BOOLEAN DEFAULT FALSE.

library_loans: loan_id INT AUTO_INCREMENT PRIMARY KEY, book_id INT, member_id INT, issued_at DATETIME, due_on DATE, returned_on DATE, fine DECIMAL(6,2) DEFAULT 0.00.

The script inserts 5 books, 4 members and 4 loans (the loans are exactly the four rows of the sample output) and ends with three SELECTs, each sorted by its id: books show book_id, title, category, price, copies; members show member_id, name, member_type, branch, is_blocked; loans show every column.