SCHOOL OF COMPUTING &ELECTRICAL

ENGINEERING
Advanced data base
Title=dormitory management system
Prepared by:-
NO NAME ID NUMBER
.
1. Genet Worku 0503193
2. Eshetu Getachew 0503168
3. Kinnent Ayalew 0503253
4. Elias Alebachew 0503154
5. Betehlium Belay 0503106

SUBMMITED TO:- Adamu .

[Link]/09/2006

1
Table of content
1. Title of the project……………………………………………………..2

2. Back ground……………………………………………………………….3

3. Introduction…………………………………………………………………3

4. Objective………………………………………………………………………3

4.1. General Objective………………………………………………..3

4.2 Specific objective………………………………………………….3

5. Scope of the project………………………………………………………..4

6. Project deliverables………………………………………………………..4

7. List of table used in database…………………………………………4

8. ER Diagram…………………………………………………………………….6

9. Description of E-R Diagrams ………………………………………..6

10. Schema Diagram of the project………………………………………7

11. Normalized table relationships………………………………………..10

12. Mapping of ER to relational database tables……………………11

13. Implementation……………………………………………………………………..13

2
Back ground

This dormitory management system is online few years ago, students can see their
dormitory placement on line .students across Faculty of business and organization are
increasingly empowered with new authorities to order their placement ,to control
students who is absent of his/her dorm.

Introduction
This document is mainly focused on Dormitory management system for Bahir Dar
University specifically for faculty of business and economics
It also describes the function and performance of online pharmacy management
system.
Our system is System ability to record, display, store, and exchange students’
specific information in a manner that optimizes workflow within university. System
ability to record, display, store, and exchange information’s, using T_SQL Server
Functions.
Objective
General objective:- The system will do the dormitory placement of students for dormitory
management system.
Specific objective:-
 To easily display full information about the placement of
students.
 To update students information
 To repels the purpose of stock card by simplified method
 Ensure data accuracy’s.
 Administrator controls the entire system.
 Minimize manual data entry.
 Greater efficiency.
 User friendly and interactive.
 Minimum time required.

Scope of the project

3
  The scope of this project is restricted on faculty of business and
economics dormitory management system.
 When the completion of this project the system will have:
 ordered students, add students
 delete students information who are in withdrwal,students
permanently leave out of the university
 update information’s about the system,
 Efficient reuse of code and programming abstraction. Enhanced
security controls. You can grant users permission to execute a stored
procedure
Project deliverables

 After this system is completed students can easily know there dorm
placement,
 Proctors can manage students effectively.
 The user can update, delete and retrieve tables.

LIST OF TABLES USED IN THE DATABASE

Field Name Data Type size Key value Description

manID int -------------- PRIMARY Manger id

KEY
Name varchar 30 --------------- Name
Sex varchar 1 --------------- Sex
Salary varchar 20 --------------- Salary
Office int ------------- --------------- Office
number number
Age int ------------- -------------- Age
Table manager

Field Name Data Type size Key value Description

4
 Pro_id int ------- Primary Proctor id
key
name varchar 20 ------- Name
Sex varchar 1 -------- Sex
salary int ------ -------- salary
Block int ------ -------- Block
number number
Table proctor
Table block

Field Name Data Type size Key value Description

Block int ---------- ------------------ Block

number number
Block name varchar 2 --------------------- Block
name

Table student
Field Name Data Type size Key value Description

studID int --------- ----------- Student id

name varchar 30 ----------- Name
dep varchar 30 ------------ department
sex varchar 30 ----------- Sex
age int ---------- ----------- Age

Block_numbe int ---------- ------------- Block_numb

r - er
Table dorm
Field Name Data Type size Key value Description

Dorm_number int -------- --------- Dorm number

Dorm_name varchar 2 ---------- Dorm name
ERDiagram

5
Description of E-R Diagrams

6
Entity name: student
Attributes: are studid , name ,sex, age, department , bed _number ,block
number.
 studid is a primary key. It represented by
 Age is a derived attribute. Because it is derived from current
year minus year of birth. It is represented by Dotted circle and
broken arrow.
 Block number: is foreign key that references entity proctor.
Entity name: Manager
Attributes are : manid, name, sex, salary, office_number,age.
 Manid is a primary key. It represented by
 Age is a derived attribute. Because it is derived from current
year minus year of birth. It is represented by Dotted circle and
broken arrow.
Entity name : proctor
Attributes are: pro_id, name, sex, block number, salary.
 Pro_id a foreign key manager.
 Block number is primary key
Entity name : Block
Attributes are: block number and block name.
Block number-is foreign key references the entity manager.
Entity name: Dorm-This entity is weak entity among other entities.
Attributes are: dorm number, dorm name.
5

7
Schema Diagram
manager
Man_id Name Salary Office number Sex

Student
stud ID Name Sex Age Department Bed_number

Proctor

Pro_Id Name Salary Block number Sex

Block
Bname Bnumber

2.3. Mapping in to relational database

Mapping of entity
Manager
There is no any composite attribute or multivalued
attribute. So there is no other table to map.
Man_id Name Salary Office number Sex

8
 Proctor
There is no any composite attribute or multivalued
attribute. So there is no other table to map.

Pro_Id Name Salary Block number Sex

Student.
The same to the above there is no any composite
attribute or multivalued attribute. So there is no other
table to map.
stud ID Name Sex Age Department Bed_number

Block
There is no any composite attribute or multivalued
attribute. So there is no other table to map.
Bname Bnumber

Mapping relation ship

Proctor controls student
Block number is foreign from proctor to student

Pro_id Stud_id

Manger manages proctor

9
ProID is foreign key references manager

Man_id pro_id

Proctor works on Block

Block number is foreign key references proctor

Pro_id Block_number

General mapping ER-diagram to relational database tables

Man_id Name Salary Office number Sex

stud ID Name Sex Age Department Bed_number

Block name Block __number

Pro_Id Name Salary Block number Sex

Normalized table relationships,

10
TABLE FOR MANAGER
manID name Sex Salary age Office
number
10 Alazar male 1000 31 18
11 Eeven female 2000 18 19
12 Abeanazer male 8000 40 20
13 Rich female 3000 20 21
14 Abraham male 4000 19 22

1st and 2nd Normalization is fulfill: there is no composite attribute in this table,
there is no any dependency .So it is normalized table.
3rd normalization
manID Salary Age
10 1000 31
11 2000 18
12 8000 40
13 3000 20
14 4000 19
Un_Normalized for proctor Table
Proi d name Block man id
number sex Salary
050342 Asmar 18 M 05876
2000
050343 Kebede 19 M 03489
4500
050344 Aster 20 F 6000 04336
05055 Eden 18 F 4500 02346

1st and 2nd Normalization is fulfill: there is no composite attribute in this table,
there is no any dependency .So it is normalized table.
11
 3rd normalization
Pro_id Man_id
050342 05876
050343 03489
050344 04336
05055 02346
Un_normalized student table
studID Name Sex Department Bed- Block- Age
number number
100 genet f cs 1 179 20
101 kinnent m cse 4 180 43
102 beti f cs 3 185 21
103 elias m it 5 183 56
104 esubie m is 7 189 65
105 derebe m is 8 200 70

1st and 2nd Normalization is fulfill: there is no composite attribute in this table,
there is no any dependency .So it is normalized table.
studID Bed-number Block-number Age
100 1 179 20
101 4 180 43
102 3 185 21
103 5 183 56
104 7 189 65
105 8 200 70

Mapping of ER to relational database tables

10
Table Manager
Stud-id name sex Salary Officenumbe age

12
 r

Table student
stud ID name sex department Bed number block
number

Table block
Block number Block name

Table proctor

Pro id Name sex Salary Block number

Table dorm
Dorm _number Dorm_name
Implementation
create database dormitory_sytem
use dormitory_sytem

create table manager

(
manID int primary key,
name varchar(20),
sex varchar(15),
salary varchar(20),
office_number int
age int,
)
insert into manager values (10,'alazar','male',1000,31,18)

13
, (11,'feven','femae',2000,18,19),
(12,'abnazer','male',8000,40,20)
,(13,'Rich','female',3000,20,21)
,(14,'abrham','male',4000,19,22)
select *from manager
----------drop table manager

create table proctor

(
proID int foreign key references [Link],
name varchar(20),
sex varchar(15),
salary int,
Block_number int primary key,
)
----drop table proctor
insert into proctor values(10,'abebe','male',600,178)
,(11,'selam','female',900,179)
,(12,'helen','female',300,180)
,(13,'abel','female',700,181)
,(10,'eyob','male',10,182)
select *from proctor

create table student

(
studID int primary key,
name varchar(20),
sex varchar(15),
Department varchar(20),
Bed_number int,
Block_number int foreign key references [Link],
age int,
)
--drop table student
insert into student values
(100,'Genet','female','comp',2,178,18),
(101,'Eshetu','male','madicine',3,179,20),11
(102,'Embetu','female','biology',4,180,22)

14
,(103,'Kinnent','male','comp',2,181,50)
,(104,'nardos','female','civics',6,182,12)
select *from student
create table dorm
(
dnumber int,
dname varchar(20),

)
-----drop table dorm
insert into dorm values(1,'A'),(2,'B'),(1,'C'),(5,'D'),(6,'A'),(6,'D')
select*from dorm
create table block(
bname varchar(12),
Block_number int foreign key references [Link]

)
----drop table block
insert into block values('taytu',178),('lalibela',179),('rasdejen',181),
('belay',180)
select*from block
--------selection implimenaion---------
select name,sex
from student
where name like '%a%'
select top(2) *from proctor where salary>=900
select name,salary from manager where salary=1000 and name='alazar'
select sum(salary )
from proctor
select sex,COUNT(*)from student group by sex
select COUNT(salary) as total_salary from proctor where sex='female'
--------delete implimentation---------
delete from student where studID=102
delete from block
delete from proctor where proID in(select manID from manager where
manID=10)
---------update implimentaion-------------
update proctor
set salary=12345

15
where proID=11
update proctor
set salary=salary*2
where proID in(select proID where proID=11)
update student set
studID=123 , name='genet' where sex='f'
------------------------index syntax-------------------------
-----------Single-Column Indexes---------------------
create index yourindex
ON proctor (sex);
--------create index Unique Indexes------------
create unique index data
on student (studID)
----------------to drop index-----
DROP INDEX data

-------------------Composite Indexes-------------------
create index data1
on manager (salary, manID);

-----------------create view---------------
use dormitory_sytem
go
create view myview as select name
from student where studID=100;
select *from myview
---------------------------------------
create view [manager above average salary] as
select name,sex,manID from manager where salary>1000
---------------------------------------------------
create view myview2 AS
SELECT name
from proctor
where proID=11
select *from myview2
------SQL DROP VIEW Syntax
Drop view myview2
16
Create Functions, Stored procedures and
database triggers
Create Function
------------Advanced database implimentaion--------------------
--scalar implemantation
create function [Link](@studID int)
returns table
as
return select *from [Link] where studID=@studID
select name,studID from [Link]
select count(*) from [Link] where name like 'e%u'

create function [Link](@name varchar(20))

returns table
as
return select *from [Link] where name=@name
select name,salary from [Link]
select name,salary from [Link] where name!='selam'
create function [Link](@salary int)
returns table
as
return select *from [Link] where salary=@salary
select name from [Link] where name='alazar'
select name from [Link] where name like '%e%'
--inline functions---
create function manag(@maneID int)
returns float
as
begin
declare @manID float
set @manID=18
return @manID
end
select [Link](11)
select*from manager

17
---------------- Stored procedures-----------
use dormitory_sytem
go
alter procedure stud1(@studID int)
as
begin
select [Link] , [Link]
from student s ,manager m-------------joining condition
where [Link]=[Link] and [Link]=@studID
end

EXECUTE stud1 100

use dormitory_sytem
go
create procedure stud2
as
begin
select name,sex
from proctor where name like ('e%')
end

EXECUTE stud2
use dormitory_sytem
go
alter procedure stud3(@studID int)
as
begin

select count(*)
from proctor where proID>10
end

EXECUTE stud3 102

Database Trigger
------using trigger-----------
use dormitory_sytem
go
alter trigger triggerproject ON student
after insert,delete
18
as
print 'genet'
go
insert into student values (107,'eshetu','m','ce',6,180,44)
delete from student where studID=104
select*from student
--update implimantation
use dormitory_sytem
go
create trigger triggerpro ON proctor
after update
as
begin
if(update(name))
print'name is successfully updated'
else
print'you can not touch this'
end
update proctor
set name='brukti'
where name='selam'
select*from proctor
---------------------------------------------

use dormitory_sytem
go
create trigger manage on manager
for update
as if (update(salary))
begin
Raiserror ('Slary information should not be modified',16,1)
rollback transaction
end
update manager

19
set salary=5000
where manID=11
---------------------------------------------------------------
USE dormitory_sytem
go
CREATE TRIGGER b1 ON block
INSTEAD OF Insert
AS
PRINT 'beti'
go
insert into block values('esubie',181)
select *from block
----------------------------------------------------------
USE dormitory_sytem
go
create trigger dorm5 on dorm
for delete as
if (select count(*) from deleted )>2
begin
Raiserror('you can not delete more than 2 dorm at once',16,1)
Rollback transaction
End
insert into dorm values(1,'A'),(2,'B'),(1,'C'),(5,'D'),(6,'A'),(6,'D')
--drop table dorm
delete from dorm where dnumber=104
select *from dorm
-----------------------------------------------------------------------------
Database security and authorization (logins,
users, roles, schemas, permissions)

------------------login-----------
create login tigi with password='adm123'
20
create login netsi with password='ad345'
create login admin1 with password='adm12'
create login admin2 with password='ad34'
----------create users---------
CREATE USER kinninet FOR LOGIN eshetu
CREATE USER kebede FOR LOGIN netsi
CREATE USER seble FOR LOGIN admin2
CREATE USER nigist FOR LOGIN admin1
grant select on student to kebede
grant delete on student to kebede with grant
option
grant insert on manager to alemu
grant update on manager to alemu with grant
option
grant select on manager to alemu with grant
option
grant delete on manager to alemu with grant
option

21