ABV - INDIAN INSTITUTE OF INFORMATION
TECHNOLOGY
AND MANAGEMENT, GWALIOR
Database Management System Project
HOSTEL MANAGEMENT SYSTEM
Subject Code – ITIT-2107
Submitted to: Submitted by:
Dr. Debanjan Sadhya Ishita Choudhary (2019IMT-042)
Kumar Lakshya (2019IMT-051)
Kushagra Pathak (2019IMT-054)
Shambhavi Shandilya (2019IMT-089)
Shashwat Agrawal (2019IMT-091)
Somya Surendra Singh (2019IMT-101)
Pawan Kumar Lanjiwar (2018IMT-065)
INTRODUCTION
The purpose of our assignment is to design a sample database of a Hostel Management which
will help students as well as supervisors to get the required information regarding the rooms, the
furniture and electrical appliances embedded in it, complaints filed by a particular resident and
reviews about the room.
ENTITIES AND ENTITY SETS
Entity is an object which is distinguishable from others. It is a real-world thing which can be
distinctly identified like a person, place or a concept.
Entity Set is a collection of entities of the same entity type.
ATTRIBUTES
An attribute refers to a database component such as a table. It also may refer to a database
field. Attributes describe the instances in the column of a database.
1. Student : This entity set provides data regarding the student’s roll number,
name, phone number and email address. Note that roll number here is the
primary key attribute.
2. Room : This entity set contains information regarding the room number allotted
to a particular student. The attributes here are RoomNo and RoomName .
3. Furniture : This entity set provides information regarding the type of furniture in a
room. The attributes here are FurID, FurType .
4. ElectricalAppliances : This entity set provides the list of electrical appliances
being used in a room. The attributes here are AppID, AppType .
5. Complaints : This entity set shows the information regarding the complaints filed
by the resident due to various problems faced by him/her. Its attributes are
CompID, Description .
6. Reviews : This entity set consists of the reviews given by students about the
condition of the rooms and furniture in the hostel. The attributes here are
ReviewID, Description .
RELATIONSHIP SETS
The association among entities is called a relationship. A set of relationships of similar type is
called a relationship set.
1. Have_EA : This is the relationship between the entity sets “Room” and
“ElectricalAppliances” and have “many to many relationship” between them as a single
room can contain many appliances while the same appliance can also be contained by
many rooms . The attributes here are “RoomNo” and “AppID” .
2. Have_Fur : This is the relationship between the entity sets “Room” and “Furniture” and
have “many to many relationship” between them as a single room can be associated
with many furnitures while the same furniture can also be contained by many rooms .
The attributes here are “RoomNo” and “FurID” .
3. Files : This is the relationship between the entity sets “Student” and “Complaints” and
have “one to many relationship” between them as a single student can file many
complaints while a complaint is unique for a particular student . The attributes here are
“RollNo” and “CompID” .
4. Gives : This is the relationship between the entity sets “Student” and “Reviews” and
have “one to many relationship” between them as a single student can give many
reviews while a review is unique for a particular student . The attributes here are
“RollNo” and “ReviewID” .
5. Occupy : This is the relationship between the entity sets “Student” and “Room” and have
“one to one relationship” between them as a single student can occupy a particular
room. Also, a particular room must be associated with a single student . The attributes
here are “RollNo” and “RoomNo” .
SCHEMA
Student (RollNo, FirstName, LastName, PhnNo, Email)
Room (RoomNo , FloorNo)
Furniture (FurID , FurType)
ElectricalAppliances (AppID , AppType)
Reviews (ReviewID , Description )
Complaints (CompID , Description )
Have_EA (RoomNo , AppID )
Have_Fur (RoomNo , FurID )
Files (RollNo , CompID)
Gives (RollNo , ReviewID)
Occupy(RollNo , RoomNo)
ER DIAGRAM
RELATIONSHIP SCHEMA
NORMALISATION
1. All the relations are in 1NF as all the attributes in the relations have atomic domains.
2. All relations are in 2NF as they are already in 1NF and every non-prime attribute is fully
functionally dependent on the prime key attribute i.e there is no partial dependency.
3. All relations are in 3NF as they’re in 2NF and there is no transitive dependency i.e. no
non-prime attribute is transitively dependent on prime key attribute.
4. All the relations are in BCNF which is the stricter form of 3NF according to which LHS of
all functional dependencies must contain only superkey.
QUERIES IN RELATIONAL ALGEBRA
Q1 ) Find The Name Of Students Occupying Rooms On The Second Floor.
Q2 ) Find The Email Address Of The Student Residing in Room 201
Q3 ) Find The Review Given By A Student For Room No 150
Q4 ) Find The Room No Associated With Complain Id 2
Q5 ) Find The No. Of Furniture Items In Each Room.
Q6 ) Get The List Of Electronic Appliances In The Room Of RollNo 7.
QUERIES IN SQL
Q1 ) Find The Name Of Students Occupying Rooms On The Second Floor.
Q2 ) Find The Email Address Of The Student Residing in Room 201.
Q3 ) Find The Review Given By A Student For Room No 150
Q4 ) Find The Room No Associated With Complain Id 2
Q5 ) Find The No. Of Furniture Items In Each Room.
Q6 ) Get The List Of Electronic Appliances In The Room Of RollNo 7.
