Mini project: hostel room allocation

60 min · Intermediate

A hostel office allocates beds to students every academic year, and every rule it cares about (one bed per student, no shared beds, sensible dates) can live inside the database as a constraint.

What you'll build

Write one SQL file, queries.sql, that creates four tables for a college hostel office: hostels, rooms, students and allocations. Every rule from the requirement becomes a primary key, NOT NULL, DEFAULT, UNIQUE, CHECK or foreign key, each with a readable name. The database is empty when your file runs, so the file only creates the tables.

The checker then fills your tables with its own rows and tries bad rows too: each one must be refused with the constraint name from the list below, so the names matter.

queries.sql only creates the tables; this check of their columns shows the result

SELECT table_name AS table_name, column_name AS column_name, column_type AS column_type, is_nullable AS is_nullable, column_default AS column_default, column_key AS column_key FROM information_schema.columns WHERE table_schema = 'shop' ORDER BY table_name, ordinal_position;
SELECT table_name AS table_name, constraint_name AS constraint_name, constraint_type AS constraint_type FROM information_schema.table_constraints WHERE table_schema = 'shop' ORDER BY table_name, constraint_name;
SELECT constraint_name AS constraint_name, table_name AS table_name, referenced_table_name AS referenced_table_name, delete_rule AS delete_rule FROM information_schema.referential_constraints WHERE constraint_schema = 'shop' ORDER BY constraint_name;

+-----------------+---------------+--------------+-------------+----------------+------------+
| table_name      | column_name   | column_type  | is_nullable | column_default | column_key |
+-----------------+---------------+--------------+-------------+----------------+------------+
| allocations     | allocation_id | int          | NO          | NULL           | PRI        |
| allocations     | student_id    | int          | NO          | NULL           | MUL        |
| allocations     | room_id       | int          | NO          | NULL           | MUL        |
| allocations     | bed_no        | tinyint      | NO          | NULL           |            |
| allocations     | academic_year | char(7)      | NO          | NULL           |            |
| allocations     | check_in      | date         | NO          | NULL           |            |
| allocations     | check_out     | date         | NO          | NULL           |            |
| allocations     | status        | varchar(10)  | NO          | ACTIVE         |            |
| hostel_students | student_id    | int          | NO          | NULL           | PRI        |
| hostel_students | roll_no       | char(8)      | NO          | NULL           | UNI        |
| hostel_students | name          | varchar(40)  | NO          | NULL           |            |
| hostel_students | gender        | char(1)      | NO          | NULL           |            |
| hostel_students | year          | tinyint      | NO          | NULL           |            |
| hostels         | hostel_id     | int          | NO          | NULL           | PRI        |
| hostels         | name          | varchar(40)  | NO          | NULL           | UNI        |
| hostels         | gender        | char(1)      | NO          | NULL           |            |
| hostels         | warden        | varchar(40)  | NO          | NULL           |            |
| rooms           | room_id       | int          | NO          | NULL           | PRI        |
| rooms           | hostel_id     | int          | NO          | NULL           | MUL        |
| rooms           | room_no       | varchar(5)   | NO          | NULL           |            |
| rooms           | capacity      | tinyint      | NO          | NULL           |            |
| rooms           | monthly_rent  | decimal(8,2) | NO          | NULL           |            |
+-----------------+---------------+--------------+-------------+----------------+------------+
+-----------------+-----------------------------+-----------------+
| table_name      | constraint_name             | constraint_type |
+-----------------+-----------------------------+-----------------+
| allocations     | chk_allocations_bed         | CHECK           |
| allocations     | chk_allocations_dates       | CHECK           |
| allocations     | chk_allocations_status      | CHECK           |
| allocations     | fk_allocations_room         | FOREIGN KEY     |
| allocations     | fk_allocations_student      | FOREIGN KEY     |
| allocations     | PRIMARY                     | PRIMARY KEY     |
| allocations     | uq_allocations_bed          | UNIQUE          |
| allocations     | uq_allocations_student_year | UNIQUE          |
| hostel_students | chk_hostel_students_gender  | CHECK           |
| hostel_students | chk_hostel_students_year    | CHECK           |
| hostel_students | PRIMARY                     | PRIMARY KEY     |
| hostel_students | uq_hostel_students_roll     | UNIQUE          |
| hostels         | chk_hostels_gender          | CHECK           |
| hostels         | PRIMARY                     | PRIMARY KEY     |
| hostels         | uq_hostels_name             | UNIQUE          |
| rooms           | chk_rooms_capacity          | CHECK           |
| rooms           | chk_rooms_rent              | CHECK           |
| rooms           | fk_rooms_hostel             | FOREIGN KEY     |
| rooms           | PRIMARY                     | PRIMARY KEY     |
| rooms           | uq_rooms_hostel_room        | UNIQUE          |
+-----------------+-----------------------------+-----------------+
+------------------------+-------------+-----------------------+-------------+
| constraint_name        | table_name  | referenced_table_name | delete_rule |
+------------------------+-------------+-----------------------+-------------+
| fk_allocations_room    | allocations | rooms                 | RESTRICT    |
| fk_allocations_student | allocations | hostel_students       | RESTRICT    |
| fk_rooms_hostel        | rooms       | hostels               | RESTRICT    |
+------------------------+-------------+-----------------------+-------------+

Requirements

Your program should do all of these.

Table hostels: hostel_id INT AUTO_INCREMENT primary key, name VARCHAR(40), gender CHAR(1), warden VARCHAR(40). A hostel has a unique name (uq_hostels_name) and a gender of M or F (chk_hostels_gender).
Table rooms: room_id INT AUTO_INCREMENT primary key, hostel_id INT, room_no VARCHAR(5), capacity TINYINT, monthly_rent DECIMAL(8,2). The room number is unique inside a hostel (uq_rooms_hostel_room), capacity is 1 to 4 (chk_rooms_capacity) and rent is above zero (chk_rooms_rent). Foreign key fk_rooms_hostel to hostels.
Table hostel_students: student_id INT AUTO_INCREMENT primary key, roll_no CHAR(8), name VARCHAR(40), gender CHAR(1), year TINYINT. The roll number is unique (uq_hostel_students_roll), gender is M or F (chk_hostel_students_gender) and year is 1 to 4 (chk_hostel_students_year).
Table allocations: allocation_id INT AUTO_INCREMENT primary key, student_id INT, room_id INT, bed_no TINYINT, academic_year CHAR(7) such as 2025-26, check_in DATE, check_out DATE, status VARCHAR(10) with default ACTIVE. Foreign keys fk_allocations_student and fk_allocations_room.
A bed can be given only once per academic year (uq_allocations_bed), and a student gets only one bed per academic year (uq_allocations_student_year).
The bed number is 1 to 4 (chk_allocations_bed), check-out must be after check-in (chk_allocations_dates) and the status is ACTIVE or LEFT (chk_allocations_status).
Every column is NOT NULL. Deleting a hostel, room or student that is still in use is refused (every foreign key uses ON DELETE RESTRICT).

Build it step by step in the Build tab (5 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 Keys & Constraints

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

5 milestones

What you'll build

The checker then fills your tables with its own rows and tries bad rows too: each one must be refused with the constraint name from the list below, so the names matter.

queries.sql only creates the tables; this check of their columns shows the result

SELECT table_name AS table_name, column_name AS column_name, column_type AS column_type, is_nullable AS is_nullable, column_default AS column_default, column_key AS column_key FROM information_schema.columns WHERE table_schema = 'shop' ORDER BY table_name, ordinal_position;
SELECT table_name AS table_name, constraint_name AS constraint_name, constraint_type AS constraint_type FROM information_schema.table_constraints WHERE table_schema = 'shop' ORDER BY table_name, constraint_name;
SELECT constraint_name AS constraint_name, table_name AS table_name, referenced_table_name AS referenced_table_name, delete_rule AS delete_rule FROM information_schema.referential_constraints WHERE constraint_schema = 'shop' ORDER BY constraint_name;

+-----------------+---------------+--------------+-------------+----------------+------------+
| table_name      | column_name   | column_type  | is_nullable | column_default | column_key |
+-----------------+---------------+--------------+-------------+----------------+------------+
| allocations     | allocation_id | int          | NO          | NULL           | PRI        |
| allocations     | student_id    | int          | NO          | NULL           | MUL        |
| allocations     | room_id       | int          | NO          | NULL           | MUL        |
| allocations     | bed_no        | tinyint      | NO          | NULL           |            |
| allocations     | academic_year | char(7)      | NO          | NULL           |            |
| allocations     | check_in      | date         | NO          | NULL           |            |
| allocations     | check_out     | date         | NO          | NULL           |            |
| allocations     | status        | varchar(10)  | NO          | ACTIVE         |            |
| hostel_students | student_id    | int          | NO          | NULL           | PRI        |
| hostel_students | roll_no       | char(8)      | NO          | NULL           | UNI        |
| hostel_students | name          | varchar(40)  | NO          | NULL           |            |
| hostel_students | gender        | char(1)      | NO          | NULL           |            |
| hostel_students | year          | tinyint      | NO          | NULL           |            |
| hostels         | hostel_id     | int          | NO          | NULL           | PRI        |
| hostels         | name          | varchar(40)  | NO          | NULL           | UNI        |
| hostels         | gender        | char(1)      | NO          | NULL           |            |
| hostels         | warden        | varchar(40)  | NO          | NULL           |            |
| rooms           | room_id       | int          | NO          | NULL           | PRI        |
| rooms           | hostel_id     | int          | NO          | NULL           | MUL        |
| rooms           | room_no       | varchar(5)   | NO          | NULL           |            |
| rooms           | capacity      | tinyint      | NO          | NULL           |            |
| rooms           | monthly_rent  | decimal(8,2) | NO          | NULL           |            |
+-----------------+---------------+--------------+-------------+----------------+------------+
+-----------------+-----------------------------+-----------------+
| table_name      | constraint_name             | constraint_type |
+-----------------+-----------------------------+-----------------+
| allocations     | chk_allocations_bed         | CHECK           |
| allocations     | chk_allocations_dates       | CHECK           |
| allocations     | chk_allocations_status      | CHECK           |
| allocations     | fk_allocations_room         | FOREIGN KEY     |
| allocations     | fk_allocations_student      | FOREIGN KEY     |
| allocations     | PRIMARY                     | PRIMARY KEY     |
| allocations     | uq_allocations_bed          | UNIQUE          |
| allocations     | uq_allocations_student_year | UNIQUE          |
| hostel_students | chk_hostel_students_gender  | CHECK           |
| hostel_students | chk_hostel_students_year    | CHECK           |
| hostel_students | PRIMARY                     | PRIMARY KEY     |
| hostel_students | uq_hostel_students_roll     | UNIQUE          |
| hostels         | chk_hostels_gender          | CHECK           |
| hostels         | PRIMARY                     | PRIMARY KEY     |
| hostels         | uq_hostels_name             | UNIQUE          |
| rooms           | chk_rooms_capacity          | CHECK           |
| rooms           | chk_rooms_rent              | CHECK           |
| rooms           | fk_rooms_hostel             | FOREIGN KEY     |
| rooms           | PRIMARY                     | PRIMARY KEY     |
| rooms           | uq_rooms_hostel_room        | UNIQUE          |
+-----------------+-----------------------------+-----------------+
+------------------------+-------------+-----------------------+-------------+
| constraint_name        | table_name  | referenced_table_name | delete_rule |
+------------------------+-------------+-----------------------+-------------+
| fk_allocations_room    | allocations | rooms                 | RESTRICT    |
| fk_allocations_student | allocations | hostel_students       | RESTRICT    |
| fk_rooms_hostel        | rooms       | hostels               | RESTRICT    |
+------------------------+-------------+-----------------------+-------------+

Requirements

Your program should do all of these.

Table hostels: hostel_id INT AUTO_INCREMENT primary key, name VARCHAR(40), gender CHAR(1), warden VARCHAR(40). A hostel has a unique name (uq_hostels_name) and a gender of M or F (chk_hostels_gender).

Table rooms: room_id INT AUTO_INCREMENT primary key, hostel_id INT, room_no VARCHAR(5), capacity TINYINT, monthly_rent DECIMAL(8,2). The room number is unique inside a hostel (uq_rooms_hostel_room), capacity is 1 to 4 (chk_rooms_capacity) and rent is above zero (chk_rooms_rent). Foreign key fk_rooms_hostel to hostels.

Table hostel_students: student_id INT AUTO_INCREMENT primary key, roll_no CHAR(8), name VARCHAR(40), gender CHAR(1), year TINYINT. The roll number is unique (uq_hostel_students_roll), gender is M or F (chk_hostel_students_gender) and year is 1 to 4 (chk_hostel_students_year).

Table allocations: allocation_id INT AUTO_INCREMENT primary key, student_id INT, room_id INT, bed_no TINYINT, academic_year CHAR(7) such as 2025-26, check_in DATE, check_out DATE, status VARCHAR(10) with default ACTIVE. Foreign keys fk_allocations_student and fk_allocations_room.

A bed can be given only once per academic year (uq_allocations_bed), and a student gets only one bed per academic year (uq_allocations_student_year).

The bed number is 1 to 4 (chk_allocations_bed), check-out must be after check-in (chk_allocations_dates) and the status is ACTIVE or LEFT (chk_allocations_status).

Every column is NOT NULL. Deleting a hostel, room or student that is still in use is refused (every foreign key uses ON DELETE RESTRICT).