Hostel-Database-System

SQL based database management project

Description:-

This project is based on HOSTEL MANAGEMENT!! Hostel management system is the database system to maintain the records related to hostel, it’s information, it’s management, staff and students living in it so that anyone can enquire or access the information about everything related to the hostel. Our model consists of information of following Entity sets:-

1.HOSTEL :-

it has attributes:-

1. Hostel number (primary key)
2. Capacity
3. Hostel Name
4. Vacant Rooms and
Some facility attributes like:-
5. Mess ID (Foreign Key)
6. Sports
7. Newspaper

 

2.SKILLD EMPLOYEE:-

it has attributes:-

1. Name (Composite attribute)
2. Employee Id (primary key)
3. Sex
4. Salary
5. Age
6. Contact number ( multivalued attribute)
7. Mail_id
8. Post

It is related to entity set hostel (hostel number) via relation manages .

3.Non Skilled EMPLOYEE:-

It consist of attribute:-

1. Name (Composite attribute)
2. Employee Id (primary key)
3. Sex
4. Salary/ wage
5. Age
6. Contact number ( multivalued attribute)
7. Company/Contractor’s name
8. Working as

It is related to entity set hostel (hostel number) via relation REPAIRS . 

4.Visitor record.:-

It is divided into attributes:-

1. Record number (Primary Key)
2. Timestamp of arrival
3. Timestamp of departure
4. Contact no. (Multivalued attribute)
5. Visitor Name
6. Hostel number(Visited)

It is related to entity set hostel (hostel number) via relation VISITED .

5. Student in-out record:-

It has attributes:-

1. Record number(Primary Key)
2. Timestamp of sign out
3. Timestamp of sign in
4. Roll number(foreign key)

It is related to entity set STUDENT via relation MAINTAINED FOR .

 

6.STUDENT:-

It has attributes:-

Roll no.(primary key)

Name (Composite attributes)

Date of birth

Age (derived from dob)

Room no.

Contact no. (multivalued attribute)

Mail ID

Address (Composite attributes)

It is related to entity set Hostel via many to one relationship lives in.

7.Internet:-

It consists of attributes:-

1. (Hostel number, Room number)(Primary Key)
2. IP Address

It is related to entity set hostel (hostel number) via relation FACILITATES . To reduce redundancy mess is reduced into 2 normalized tables:-Mess-admin and Mess-menu.

8.Mess-Admin:-

It consists of attributes:-

1. Mess ID(Primary Key)
2. Catarer’s name
3. Representative

9.Mess-Menu:-

It consists of attributes:-

1. (Mess Id,Day,Time)(Primary Key)
2. Menu

Both of the sets are related to entity set hostel (hostel number) via relation facilitates .

THIS DATABASE CONSISTS OF 3 TRANSACTION TABLES:-

1. SKILLED Employee Transaction:-

It consists of attributes:-

1. ID_No(Primary Key)
2. Attendence
3. Working Days
4. Hostel (working at Present)

It is related to entity sets skilled employee via relation update .

2. SKILLED Employee Transaction:-

It consists of attributes:-

1. ID_No(Primary Key)
2. Attendence
3. Working Days
4. Hostel (working at Present)

It is related to entity sets unskilled employee via relation update .

3.Student transaction:-

It consists of attributes:-

1. Roll number(Primary Key)
2. Attendence
3. Days in Hostel
4. Hostel(living at present)

It is related to entity set student via relation update.  

Composite attributes description:-

1. 1.Name:- it is used in many entity sets and has the following attributes:- 1.First name 2.Last name
2. 2.Address:- it has following attributes:- 1.House no. 2.Street no.
3. 3.Pin code:- It has following attributes:- 1.City 2.State 3.Country 4.ZIP Code

 

Stakeholders who have to access the Database and their needs:-

As the name specifies “HOSTEL MANAGEMENT SYSTEM” is a database developed for managing various activities in the hostel. The stakeholders for this database are :-

1. Warden of hostels:To keep the record of personal details of each employee and students of the hostel.
2. Supervisiors of hostels : Same as Warden’s role.
3. Security Officer : To keep the record of the visitors visiting the hostel, arrival and departure time of a student from the hostel.

All these people are also the administrator of the database.

The Administrator can :
1. Allot different students to the different hostels.
2. Control the status of the payment of salaries and other expenses.
3. Edit the details of the students & modify the student records.
4. If any administrative authority comes to investigate the hostel’s record (like Director etc.), then the administrator can make them see the proper records.