A relational database for managing a student dormitory: residents, room assignments, billing, staff, maintenance tasks, kitchen and cleaning supplies, and visitors. Built in Oracle SQL / PL/SQL.
Final project for CENG315 (Database Management) at Izmir Institute of Technology, by Group 17. The project was tested and presented to the course instructor.
| User | Access |
|---|---|
| Dormitory manager | Full access: rooms, billing, staff |
| Staff member | Tracks assigned tasks (cleaning, repair, etc.) |
| Kitchen manager | Manages menus and kitchen inventory |
| Student | Views their room assignment and bills |
- 18 tables covering students, rooms, staff, menus, supplies, tasks, visitors and all financial transactions
- Bridge tables for many-to-many relationships (e.g. tasks ↔ staff, tasks ↔ supplies, menus ↔ supplies)
- Primary keys, foreign keys and CHECK constraints to keep the data consistent (valid roles, payment methods, non-negative stock, visitor exit after entry, etc.)
- 400+ rows of sample data, so every table can be queried right away
- 5 triggers that keep business rules inside the database (below)
| Trigger | When | What it does |
|---|---|---|
trg_room_assignment_validator |
Before a student is assigned or moved | Blocks the assignment if the room is already at full capacity |
trg_room_assignment_sync |
After an assignment changes | Updates the room's current capacity and marks it Full or Available |
trg_update_stock_menu |
When a supply is used for a menu | Deducts the used quantity from stock |
trg_update_stock_task |
When a supply is used for a task | Deducts the used quantity from stock |
trg_supply_inventory_check |
Before stock is updated | Rejects any change that would make stock negative |
Together, the room triggers mean a room can never be over-assigned and its status is always in sync with who actually lives there.
| Area | Tables |
|---|---|
| Residents | STUDENT, STUDENT_PHONE, ROOM, ROOM_ASSIGNMENT |
| Visitors | VISITOR, STUDENT_VISITOR |
| Finance | DORM_TRANSACTION, ROOM_BILLING, MENU_BILLING, STUDENT_TRANSACTION, STAFF_TRANSACTION |
| Kitchen | MENU, MENU_SUPPLY |
| Operations | STAFF, TASK, TASK_STAFF, SUPPLY, TASK_SUPPLY |
Every payment goes through DORM_TRANSACTION (income or expense); specialised tables link each transaction to a room bill, a meal, a student or a staff salary.
The full design, with every entity, relationship and business rule, is in the design report.
- Open an Oracle database (Oracle Live SQL, SQL Developer or Oracle XE all work).
- Run
script.sql. It drops any existing tables, creates the schema, adds constraints, inserts the sample data and creates the triggers. - Try the example queries in
04_Queries/example_queries.sql.
Example: current residents with their block, floor and phone number, run in Oracle Live SQL.
script.sql is the complete, final version of the project. The numbered folders (01_Schema, 02_Relationships, 03_Constraints, 07_Triggers) contain earlier drafts of individual modules.
Group 17: Mehmet Zahid Alpay, Hüsna Bosun, Yağmur Başoral, Mete Cem Bulut, Mert Çelik
My contributions: designed the ER diagram and the primary/foreign key structure, co-wrote the triggers, helped create the sample data and the test script, and tested and presented the project.