Graveyard management System

Introduction :

A database management system (DBMS) is a system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data. Our project Graveyard management system was created by the concept of DBMS.
The main point that I want to highlight is what the work system of Graveyard management system is At first we gathered some information about this topic from internet .Then we wrote a case study. This case study used to develop ERDiagram. Then we solved the Normalization. Constraints are implemented to the exact position. We have tried to implement all of the constraints we have learned
Then we inserted 5 data’s into each table. Finally we wrote 3 query from group function.3 query from sub query ,and 3 from join and 1 from single row.

Case :

The Graveyard management System (GMS) is one of the most important service areas offered by the local governments in our country to citizens. GMS has name, Address and registration number.
In this respect, many municipalities are striving to provide more efficient and effective service by trying to provide these services. Municipalities has name, address and reg.no for identify. They have some team for this management. Team has A manager and some worker. Manager and worker also have name id, age phone.no and amount of salary. Municipalities keep each dead body information (name, address, birth date) and make a map. With this work, citizens will be able to access the information and find the grave of their dead relatives.

ER Diagram :

Data constrains :

Table Name: Graveyard
Column Name Data type Constraint G-Reg.no Number(12) Primary key G-Name Varchar2(20) Not null G-Address Varchar2(20)

Table Name: Municipality
Column Name Data type Constraint Mi-Reg.no Number(12) Primary key Mi-Name Varchar2(20) Not null Mi- Address Varchar2(20) G-Reg.no Number(12) foreign key from graveyard table

Table Name: Manager
Column Name Data type Constraint M-ID Number(10) Primary key M-Name Varchar2(20) Not null M-Salary Number(7,3) M-Age Number(3) M-phone Number(12) Mi-Reg.No Number(12) foreign key from Municipality table

Table Name: Worker
Column Name Data type Constraint W-ID Number(10) Primary key W-Name Varchar2(20) Not null W-Salary Number(7,3) W-Age Number(3) W-phone Number(12) M-ID Number(12) foreign key from Manager table

Table Name: Dead-Body
Column Name Data type Constraint D-ID Number(10) Primary key D-Name Varchar2(20) Not null Address Varchar2(20) Birth-date Varchar2(20)

Normalization :

Service (G-REG.NO, G-Name, G-Address, Mi-REG.NO, Mi-Name, Mi-address)
1NF: This table is already in first normal from because no multivalued
          Attributes.
2NF:
          G-REG.NO, G-Name, G-Address,
           Mi-REG.NO, Mi-Name, Mi-Address, G-REG.NO,
3NF:
           No Transetive dependency
Table:
          G-REG.NO, G-Name, G-Address,
           Mi-REG.NO, Mi-Name, Mi-Address, G-REG.NO,

Provide (Mi-REG.NO, Mi-Name, Mi-Address, M-ID, M-Salary, Phone No, M-Age, M-Name)
1NF: Phone No multivalued attributes.
2NF:
Mi-REG.NO, Mi-Name, Mi-Address,
M-ID, M-Salary, Phone No, M-Age, M-Name, Mi-REG.NO
3NF:
        No Transetive dependency

Table:
Mi-REG.NO, Mi-Name, Mi-Address,
M-ID, M-Salary, Phone No, M-Age, M-Name, Mi-REG.NO

Manage ( M-ID, M-Salary, M-Phone No, M-Age, M-Name   W- ID, W- Salary, W-Phone No, W-Age, WName)
1NF: Phone No multivalued attributes:
2NF:
   M-ID, M-Salary, M-Phone No, M-Age, M-Name
   W- ID, W- Salary, W-Phone No, W-Age, W-Name,M-ID

3NF:
No Transetive dependency
Table:
   M-ID, M-Salary, M-Phone No, M-Age, M-Name
   W- ID, W- Salary, W-Phone No, W-Age, W-Name,M-ID

Keep Information (Mi-REG.NO, Mi-Name, Mi-Address, D-Name, D-ID, Address, Birth-date)

1NF: This table is already in first normal from because no multivalued     Attributes
2NF:
                 Mi-REG.NO, Mi-Name, Mi-Address D-ID
                   D-ID, D-Name, Address, Birth-date

3NF:
          No Transetive dependency
Table:
                 Mi-REG.NO, Mi-Name, Mi-Address D-ID
                   D-ID, D-Name, Address, Birth-date

Total Table:

G-REG.NO, G-Name, G-Address,
Mi-REG.NO, Mi-Name, Mi-Address, G-REG.NO,
Mi-REG.NO, Mi-Name, Mi-Address,
M-ID, M-Salary, Phone No, M-Age, M-Name, Mi-REG.NO
M-ID, M-Salary, M-Phone No, M-Age, M-Name
W- ID, W- Salary, W-Phone No, W-Age, W-Name,M-ID
    Mi-REG.NO, Mi-Name, Mi-Address D-ID
     D-ID, D-Name, Address, Birth-date

Final Table:   G-REG.NO, G-Name, G-Address,
Mi-REG.NO, Mi-Name, Mi-Address, G-REG.NO
M-ID, M-Salary, Phone No, M-Age, M-Name, Mi-REG.NO
W- ID, W- Salary, W-Phone No, W-Age, W-Name,M-ID
Mi-REG.NO, Mi-Name, Mi-Address D-ID
D-ID, D-Name, Address, Birth-date

Table Creating :

Query Writing :

Single Row

select M_salary from manager where M_salary BETWEEN 2500 And 3500;

Sub Query:

SELECT M_ID,M_Name,M_Age,M_Phone from manager where M_salary=(select Max(M_salary)from manager)

select W_ID,W_Name,W_Salary,W_Age,M_ID from worker where M_ID IN (select M_ID from manager where Mi_regNo=2341)

select W_ID,W_Name,W_salary,M_ID from worker where(W_salary)IN(Select MIN(W_salary)from worker);

Group Function

select count(G_regNo),G_Name from Graveyard group by G_Name

select Min(M_salary),Avg(M_salary),Max(M_salary) from manager

select Max(M_salary),M_Name from manager group by M_name Having Max(M_salary)>2400

Join:

select M.M_ID,M.M_Name,M.Mi_regNo,Mi.Mi_regNo,Mi_Address from Manager M,Municipality Mi where M.Mi_regNo=Mi.Mi_regNo;

select Mi.Mi_Name,Mi.G_regNo,G_Name from Municipality Mi,Graveyard G where Mi.G_regNo(+)=G.G_regNo;

select worker.M_Name,worker.M_ID||' works for'|| director.M_Name from Manager worker,Manager director where worker.M_ID=director.M_ID;

select A.G_Name,B.G_Name,A.G_Address from graveyard A,graveyard B where A.G_regNO<>B.G_regNo and A.G_Address=B.G_Address order by A.G_Address

CONCLUSION :

The Graveyard management system is developed to ease the manager of graveyard in graveyard management in term of location of the grave, the number of empty grave, and total number of grave in one graveyard. This will ease the manager when the graveyard is full and manager can make an early step to avoid the problem such as family of the dead must wait in long time to find the new grave. This system will used by manager of the graveyard and community.

Get this Project

Header Ads

Graveyard management System