Project

Introduction

This blog is design to implement the our project named as hostel management database system.

Proposal

The basic purpose of designing this project is to get rid from manual entry and record system and try to give easy and simple database management system for hostels.

This project is designed to keep the record of the students living in hostel, allocation of rooms, their monthly or semester wise dues and many more things but we are trying to make this management system as much as simple and easy as we  can, but we well try to cover all the basic elements use for hostel management database.

If we talk in simple functionality of this project so our basic target is to keep database of students in hostels with different angles  such that their room number, fees status, their course name, semester and can be many more.

As mentioned earlier we will try to make our project easy and simple so it can be implement at any level and can be productive and useful for any hostel administration.

Members:

We are two members (Muhammad Fahad & Asad Ali), we will try to work together throughout the project to make it more effective and efficient.

Abstract View:

Just mention the screenshot to show the abstract view it can be extend and reduce according to requirement and thinking during the designing of project. 

Changes can be possible later on.....!

ER-Diagram And Business Rules:

Hostel management system is design so that our universities and colleges can easily manage the data of students and related things.

For the best understanding first we have to define the project scope or the scenario because different problem can be solve different design and more than one scenarios can be created for each problem. People design them according to their thinking.

We are also creating some type scenario so that our design can be bit specific for some kind of situation.

Our project is defined as;

As we can see that our university has the facility of hostel for boys and girls.

We will focus on the boy’s hostel only as almost all the things will be same in both hostel we will manage only boys so that it will be simple and easy to understand for everyone.

Obviously many students will be living in the boy’s hostel.

Boy’s hostel has many rooms for the accommodation of the students in which more than one student accommodate their self.

Each room has assigned different types of furniture for the students so they can easily spend the time in room.

There are many workers in the boy’s hostel who work in the mess and hostel as well (cleaning, washing  etc );

Every student must submit his fees in-time so they can get the every facility in hostel.

For the food service the hostel management is providing the facility of mess for students so students and easily get the meal in hostel.

It is also possible that visitors can come to meet students those are living in hostel.

                          Formal Specification

This document is explaining about the entities we are using in the project, reason for choosing the entities  their attributes how they will work ,data_type, size, primary key, foreign key  , constraints (if exists) and flow of data that how the data will move between the entities.

Basically this is designed to make it more easy and understandable for everyone.

Entities:

    1.      hostel

    2.      Students 

    3.      Fees

    4.      Room

    5.      Furniture 

    6.      Mess

    7.      Mess_employee/staff

    8.      Visitor

Hostel:

Many institutes provide the facility of hostel for boys and girls.

But we are mentioning only boys here , almost all the working is same in both hostels.

Reason:

We are taking the entity named as hostel  because from here data will move to other entities and we will manage the database.

This entity will manage the data of students in hostel.

Attributes:
 If we talk about the attributes, there can be many but we have mention only few those will be more  help full to make the project workable and simple.

Datatype And Size:

Each attribute have the suitable data-type and size according to the requirement.

 Primary Key:

Building_num because one institute can have more than one block or building so by assigning the num it will be easy to track the data.

 Constraints:

Primary key should be unique and cannot be null.

Data Flow:

Only administrator will get in the system and manage the database and check the status of students and will allocate the room to new students.

It will also do the check and balance of mess.

The data will move from here to different side like the branches of tree.

Students:

Students will be in hostel or hostel has students.

Reason:

The reason for choosing this entity is that ,student is the main entity in the hostel and we will keep the database of students and logically related things with the students.

Attributes:

Studend_id, Student_name , father_name,department, room_id, cell_no, age, DOB etc
These are selected so that hostel administration can easily manage the whole information about student and through different perspectives students can be accessed by hostel management.

Datatype And Size:

Each attribute have the suitable data-type and size according to the requirement.

Primary Key:

Student_id is the primary key and it can’t be null.

Constraints:

Primary key should be unique and cannot be null.

When primary key move to the other relation and join the tables the values should be same.

Data Flow:

The admin will enter the data of students (this entity), on the basis of different attributes like age, department the administrator will assign the room so that the students of same age fellow and alike department can share the room.

Room:

Students come in the hostel to get the room.

Reason:

Room will be allotted to different students, so the students can be accessed by the attributes of room as well.

Attributes:

There can be many attribute in this entity

No_of_students, Students_name, furniture_id ,student_id can be added as a foreign key to relate the both entities.

As we have composite foreign key this is coming from admin to student and then student to room.

Datatype And Size:

Each attribute have the suitable data-type and size according to the requirement.

Primary Key:

Room_id is the primary key and it can’t be null.

Constraints:

Primary key should be unique and cannot be null.

As we have composite foreign key in this table which is of boys_hostel_admin and student so there data-type size and values should be same.

Data Flow:

We can also see that there is a composite foreign key which is of boys_hostel_admin and student so data will flow from admin to student and student to room.
But in ER diagram we can see there is direct relation of admin and room so admin can retrieve the condition and data of room directly as well.

Fees:

Obviously when students will live in the hostel they will submit fee.

Reason:

This is weak entity here because if the student will exist then fee will be submitted other-wise not.

When students will live in hostel there will be fees charges on the monthly basis.

Attributes:

 There will be few attributes because it is the weak entity and just made to 

keep the fees status of student.

Datatype And Size:

Each attribute have the suitable data-type and size according to the requirement.

Primary Key:

student_id is the primary key and it can’t be null.

Constraints:

student_id is the primary key and  foreign key as well. it can’t be null.

Data Flow:

The data will follow the route, admin to student and then fees.

Admin will check the student status by getting in the student entity and fees is a weak entity attached to the student so admin will be able to check the fees status of student.

Furniture:

Reason:

The reason for adding this entity is to just track the either students are getting the facilities promised by the institute or not.

Attributes:

Furniture_type: chair, table etc

Datatype And Size:

Each attribute have the suitable datatype and size according to the requirement.

Primary Key:

Furniture_id

Constraints:

Primary key should be unique and cannot be null.

Foreign key (room_id) will be same as values in room relation

Data Flow:

The data come from room boys_hostel_admin will track through it.

Mess:

Students will get the facility of mess

Reason:

Students will get the facility of mess. It can be considered as marketing strategy so more students will come in this hostel because every hostel do not give this facility.

Attributes:

There will be mess incharge , who will handle the mess, there will be monthly expenses of each student, students name to know which have got their meal or not.

Mess timing can be added to make the environment much better.

Datatype And Size:

Each attribute have the suitable data-type and size according to the requirement.

Primary Key:

Mess_incharge will be the primary key

Constraints:

Primary key should be unique and cannot be null.

Data Flow:

Admin will check the mess staff and then staff will manage it.

Mess_employees:

Reason:

There will be staff in mess and hostel as well.
Some will work in mess and others like sweepers in hostel and many more.

Attributes:

Employee_name, address, salary, cell_no etc  are the attributes used in this entity.

Datatype And Size:

Each attribute have the suitable datatype and size according to the requirement.

Primary Key:

Employee_id is used as a primary key.

Constraints:

Primary key should be unique and cannot be null.

Mess_incharge will be foreign key in this table.

Data Flow:

The data will flow from admin to staff and then mess.

Visitors:

Reason:

This entity is added as the extra entity so that our project can be look complete from every aspect.

Attributes:

Student_id,visitor_name,time_in,time_out,date these are the attributes to check what time visitor came and when he/she will leave the hostel.

Datatype And Size:

Each attribute have the suitable datatype and size according to the requirement.

Primary Key:

CNIC is considered as primary key.

Constraints:

Primary key should be unique and cannot be null.

Student_id will be the foreign key in this.

Data Flow:

This is the optional entity and data will come from student and admin.
Admin can log in to see the student data in which visitor is also attached with the foreign key so admin can keep eye on the visitor position of every student.

•Meta-Data

Entity Names Attributes	Data-Type	Size	Key/Non-key
Hostel: Hostel_id No_of_room No_of_students Annual_expenses Location	int Int int Int Varchar	10 25 10 25 10	Primary key Non_key attribute Non_key attribute Non_key attribute Non_key attribute
Students: Student_id Student_name Student_fathername Department Cell_no Age DOB	Int Varchar Varchar Varchar Int Int int	10 40 40 30 20 5 15	Primary_key Non_key attribute Non_key attribute Non_key attribute Non_key attribute Non_key attribute Non_key attribute
Room: Room_id Capacity Name_of_students	Int Int varchar	10 10 40	Primary key Non_key attribute Non_key attribute
Mess: Mess_incharge Monthly_avg_expence Mess_Timing	Int Int TIME	20 30 By-default	Primary key Non_key attribute Non_key attribute

Staff: Emp_id Emp_name Address Emp_salary Cell_no	Int Varchar Varchar Int Int	20 40 50 20 20	Primary key Non_key attribute Non_key attribute Non_key attribute Non_key attribute
Furniture: Furniture_id Furniture_type	Int varchar	10 40	Primary key Non_key attribute
Fee: Fee_month Fee_status	Varchar varchar	45 45	Primary key Non_key attribute
Visitor: CNIC Student_id Visitor_name Time_in Time_out Date	int int varchar time time date	15 10 40 By_default By_default By_default	Primary key Non_key attribute Non_key attribute Non_key attribute Non_key attribute Non_key attribute

•Data Flow Diagram

                                            

Project Coding:

We have developed our project in the my-sql, my entering the raw data so we can identifies the errors and can make our project much better.

create database hostel;

use hostel;

show databases;

drop database hostel;

create table boys_hostel

(hostel_id int (10) not null,

no_of_rooms int (25) not null,

no_of_student int (22) not null,

annual_expences int (25) not null,

location varchar (10) not null,

primary key (hostel_id)

);

drop table boys_hostel;

insert into boys_hostel values

(1001,'25','50','2450000','attock'),

(1002,'30','60','2740000','attock'),

(1003,'31','62','2870000','attock'),

(1004,'22','44','2120000','attock');

select * from boys_hostel;

This is just showing that any institute can have many hostel but we will take the only one of them and will manage the data for them.

##################################################################################################################################

create table room

(room_id int (10) not null,

capacity int (10) not null,

hostel_id int (10) not null,

name_of_student varchar (40) not null,

room_status varchar(10) not null,

primary key (room_id),

foreign key (hostel_id) references boys_hostel (hostel_id)

);

drop table room;

insert into room values

(2001,'2','1001','asad and qayyum','full'),

(2002,'2','1001','hassan and junaid','full'),

(2003,'2','1001','amin and atiq khan','full'),

(2004,'2','1001','asif hussain','full'),

(2005,'2','1001','kashif ktk and bahadur khan','full'),

(2006,'2','1001','m.bilal','not_full'),

(2007,'2','1001','atiq-ur-rehman and sajawal','full'),

(2008,'2','1001','noman and m.ahsan','full'),

(2009,'2','1001','m.danish','not_full'),

(2010,'2','1001','khashif','not_full'),

(2011,'2','1001','faisal khan','not_full');

select * from room;

Let us take the example that we need the data about rooms having capacity mean those which are not full so new student can get the accommodation.  

select room_id,name_of_student from room where room_status='not_full';

##################################################################################################################################

create table student

(student_id int (10) not null,

student_name varchar (40) not null,

student_father_name varchar (40),

department varchar (30) not null,

address varchar (40) not null,

cell_no varchar (20) not null,

student_age int (5) not null,

student_DOB varchar (15) not null,

hostel_id int (10) not null,

room_id int (10) not null,

primary key (student_id),

foreign key (hostel_id) references boys_hostel (hostel_id),

foreign key (room_id) references room (room_id)

);

drop table student;

insert into student values

(101,'Asad','masood elahi','computer science','mardan cantt','03xx-xxxxxxx','20','15-june-93','1001',2001),

(102,'qayyum','momen shah','management','garm chashma chatral','03xx-xxxxxxx','23','13-july-90','1001',2001),

(103,'hassan','M.tariq','management','DI khan','03xx-xxxxxxx','21','13-feb-92','1001',2002),

(104,'amin','m.fazal','computer science','takht bai','03xx-xxxxxxx','22','1-may-91','1001',2003),

(105,'atiq khan','shamshat khan','computer science','hango','03xx-xxxxxxx','21','19-apri-92','1001',2003),

(106,'juniad','m.munir','management','fatih jung','03xx-xxxxxxx','23','28-may-90','1001',2002),

(107,'asif hussain','m.hussain','computer science','liyya gorak pur','03xx-xxxxxxx','22','19-april-91','1001',2004),

(108,'kashif ktk','m.ijaz','computer science','karak','03xx-xxxxxxx','20','1-sep-93','1001',2005),

(109,'bahadur khan','mazdoor khan','management','akorha khatak','03xx-xxxxxxx','21','20-sep--92','1001',2005),

(110,'bilal','m.younas','computer science','mardan cantt','03xx-xxxxxxx','22','1-may-91','1001',2006),

(111,'atiq-ur-rehman','saifullah','computer science','peshawer','03xx-xxxxxxx','22','1-march-91','1001',2007),

(112,'sajawal','m.shafiq','computer science','attock','03xx-xxxxxxx','23','20-march-90','1001',2007),

(113,'noman','m.shafiq','computer science','haripur','03xx-xxxxxxx','21','14-aug-92','1001',2008),

(114,'m.ahsan','ijaz ahmad','computer science','pendigape','03xx-xxxxxxx','21','20-april-92','1001',2008),

(115,'m.danish','jahanzeb khan','Electrical engineering','deer','03xx-xxxxxxx','22','2-sep-91','1001',2009),

(116,'kashif','masood','Electrical engineering','swat','03xx-xxxxxxx','23','2-jun-90','1001',2010),

(117,'faisal khan','allah dita','Electrical engineering','mardan cantt','03xx-xxxxxxx','21','2-sep-92','1001',2011);

select * from student;

select student_name,room_id,address,student_DOB from student where student_DOB='20-march-90';

##################################################################################################################################

create table mess

(

mess_incharge_Id varchar (20) not null,

monthly_avg_expence varchar (30) not null,

mess_bf_timing varchar (45) not null,

mess_dinner_timing varchar (45) not null,

sunday_bf_timing varchar (45) not null,

primary key ( mess_incharge_Id)

);

drop table mess;

insert into mess values

('9321','102400','7:00 AM to 8:45 AM','7:30 PM to 8:45 PM','9:00 AM to 11:00 AM'),

('9322','106400','7:30 AM to 8:45 AM','7:00 PM to 8:45 PM','9:00 AM to 11:00 AM');

 select * from mess;

##################################################################################################################################

create table staff

(emp_name varchar (40) not null,

emp_id int (20) not null,

emp_address varchar (50) not null,

emp_salary int (20) not null,

emp_cellno varchar (20) not null,

hostel_id int (10) not null,

emp_designation varchar (40) not null,

mess_incharge_Id varchar (20),

primary key (emp_id),

foreign key (hostel_id) references boys_hostel (hostel_id),

foreign key (mess_incharge_id) references mess (mess_incharge_id)

);

drop table staff;

insert into staff values

('habib','8321','sheen bagh attock','15000','03xx-xxxxxxx','1001','cook','9321')

('zahid','8322','awan sharif attock','15042','03xx-xxxxxxx','1001','sweeper','null'),

('abdul rehman','8323','sheen bagh attock','15042','03xx-xxxxxxx','1001','mess manager','9322'),

('waqar','8324','fowara chock attock','12050','03xx-xxxxxxx','1001','sweeper','null'),

('taimur','8325','madni chock,attock','18000','03xx-xxxxxxx','1001','technician','null'),

('zaheen','8326','awan sharif attock','18000','03xx-xxxxxxx','1001','technician','null'),

('mazhar','8327','sheen bagh attock','12050','03xx-xxxxxxx','1001','laundryman','null');

select * from staff;

##################################################################################################################################

 CREATE table visitor(

visitor_id int (34) not null,

student_id int (10) not null,

visitor_name varchar (40) not null,

visitor_time_in varchar (40) not null,

visitor_time_out varchar (40) not null,

student_name varchar (40) not null,

date_entring varchar (24) not null,

primary key (visitor_id),

foreign key (student_id) references student (student_id)

);

 drop table  visitor;

insert into visitor values

(4561,'101','fahad','8:45 PM','9:25 PM','asad','1-may-14'),

(4562,'101','zahid','8:45 PM','9:25 PM','asad','1-may-14'),

(4563,'105','tanveer','6:50 PM','7:10 PM','atiq khan','2-april-14'),

(4564,'106','fawad','2:00 PM','2:30 PM','junaid','2-april-14'),

(4565,'107','ali','3:24 Pm','4:45 Pm','asif husain','10-april-14'),

(4566,'107','noman','10:00 AM','11:00 AM','asif husain','10-april-14'),

(4567,'110','yasir','2:23 PM','3:08 PM','bilal','20-april-14'),

(4568,'112','umair','3:09 pM','4:43 PM','sajawal','3-may-14'),

(4569,'114','jawad','7:00 PM','8:45 PM','m.ahsan','4-may-14'),

(4560,'116','muazzam','8:00 AM','9:56 AM','kashif','23-april-14');

select * from visitor;

select * from visitor where student_name='asad';

##################################################################################################################################

create table furniture(

furniture_id varchar (34) not null,

room_id int (10) not null,

furniture_type varchar (40) not null,

primary key (furniture_id),

foreign key (room_id) references room (room_id)

);

drop table furniture;

insert into furniture values

('AK97','2001','bed'),

('AK98','2001','study chair'),

('AK99','2002','bed'),

('AK10','2002','study chair'),

('AK11','2002','study table'),

('AK12','2003','bed'),

('AK13','2004','study chair'),

('AK14','2004','study table'),

('AK15','2004','bed'),

('AK16','2005','bed'),

('AK17','2006','study chair'),

('AK18','2006','study table'),

('AK19','2006','bed'),

('AK20','2007','bed'),

('AK21','2007','study table'),

('AK22','2008','bed'),

('AK23','2008','study chair');

select * from furniture;

select room_id,furniture_id from furniture where furniture_type!='bed,study table,study chair';

##################################################################################################################################

create table Fee

(fee_month_year varchar (40) not null,

fee_status varchar (40) not null,

student_id int (10) not null,

student_name varchar (40) not null,

primary key (fee_month_year,student_id),

foreign key (student_id) references student (student_id)

);

 drop table Fee;

insert into Fee values

('january-2014','paid','101','asad'),

('february-2014','not paid','101','asad'),

('march-2014','paid','101','asad'),

('april-2014','not paid','101','asad'),

('may-2013','paid','101','asad'),

('january-2013','paid','101','asad'),

('march-2013','piad','101','asad'),

('dec-2014','paid','101','asad'),

('februay-2013','not paid','101','asad'),

('nov-2013','paid','101','asad'),

('april-2013','not paid','101','asad'),

('may-2014','paid','101','asad');

select * from fee;

#######################################################################################################################

Joins

•Select room.no_of_student,room.room_id,room.capacity,student.student_name from room inner join student on room.room_id=student.room_id where room.no_of_student<2;

Select student.student_name,visitor.visitor_id, visitor.visitor_name from student inner join visitor on student.student_id=visitor.student_id where visitor.student_id=101 or visitor.student_id=107;

Implementation:

hostel.web44.net

################################################################################################

Thanks