MILITARY DATABASE MANAGEMENT SYSTEM

A PROJECT REPORT

Submitted in partial fulfillment of the requirements for the award of the degree of
Bachelor of Technology
In
INFORMATION TECHNOLOGY
Submitted by

KONDETI VINAY
20331A1260

Under the Esteemed Guidance of

Mrs.D.Gayatri
Mrs.M.Swarna

DEPARTMENT OF INFORMATION TECHNOLOGY

MAHARAJ VIJAYARAM GAJAPATHI RAJ COLLEGE OF ENGINEERING

(AUTONOMOUS)
VIZIANAGARAM-535005, AP (INDIA)
Case study:
To design a database for army management system in which we can
store the details of soldier,Armory,Living location details of soldier.In
case of any sudden attack or any war happens ,with the use of this army
management system we can retrieve the possible information about the
soldier count,no of equipments ,and we can assign a soldier to the war
based on experience ,weapons availability,etc…so that the completion
of mission is very easy and have been successful.

Every soldier contains name ,id,address,age.every soildier liven in

barracks.

Every barracks conations barracks id,location id,capacity,name ,area,

Every barracks is managed by an officer.

Officer have officer name ,id,rank.and officer manages the soldier and
armory.

Armory have weight,idno,no of times weapons used license no,serial no.

Armory damages are maintained by a engineer and engineer contains the

id,speciality,name.

Design er model for given cse and convert into r and normalize it.
ER –Diagram:
Schemas:
Soldier(ssn id int,address char,name char,age int,experience int)
Barracks(barracks id int,capacity int,area char,name char,location id int)
Armory(Serial no int,idno int,no_of_times_used int, weight float,license
no int)
Officer(officer name char,office id int,rank int)

Relational Schemas:
Livesin(ssnid int,barracksid int,no of days lives int)
Armony_mangement(officerid int,armory id int)
Armory maintainance(armory_idno int,enginner_id
int,dateofmaintainance date);
assign_armory(ssnid int,officer_id int,dateofassignment date)
1.Table creation:

Database creation:

create database Military;

use military;

Soldier table creation:

create table soldier ( ssnid int primary key,name char(80),height float,age

int,experience int,address char(100));

Describe Soldier:

desc soldier;

Output:

Insertion into soldier table:

insert into soldier values(1201,'arjun',5.6,27,6,'vishakapatnam');

insert into soldier values(1202,'varun',5.8,26,5,'chattisghar');

insert into soldier values(1203,'ravi',5.9,29,8,'mumbai');

Output:
Table Creation for Barracks :

create table barracks (barracks_id int primary key,bname char(80),capacity

float,area char(100),location_id int);

desc barracks;

Output:

Insertion of values into the Barracks:

insert into barracks values(101,'redstone',650,'jammu',53002);

insert into barracks

values(102,'blackkingdom',100,'northkashmir',53022),(103,'rockers',240,'gurezvall
ey',59034);

select * from barracks;

Output:

Table creation for officer:

create table officer(officer_id int primary key,officer_name char(50),rank_no int);

desc officer;
Output:

Insertion of the values of officer:

insert into officer values(301,'navven',3),(302,'vinay',1),(303,'sunny',4);

select *from officer;

Output:

Table creation of armory:

create table armory (armory_idno int primary key,license_no int,weight

float,serial_no int,no_of_times_used int);

desc armory;

Output:

Insertion the values of armory:

insert into armory values(1000,14321,34.45,5,7):

insert into armory values(1001,13451,54.45,6,10),(1002,14532,46.43,6,4);

select *from armory;

Output:

Creation of engineer table:

create table engineer(enginner_id int primary key,name char(50),speciality

char(200));

desc engineer;

Output:

Insertion the values of Engineer:

insert into engineer values(10,'prasad','armaory adjustment');

select *from engineer;

Output:
Creation of assign_armory table:

create table assign_armory(ssnid int,officer_id int,dateofassignment date,primary

key(ssnid,officer_id,dateofassignment),foreign key(ssnid) references

soldier(ssnid),foreign key(officer_id) references officer(officer_id));

Insertion the values of assign_armory:

insert into assign_armory values(1201,301,'1998-08-13');

select *from assign_armory;

Output:

Creation of livesin table:

create table livesin(ssnid int,barracks_id int,noofdays int,primary

key(ssnid,barracks_id),foreign key(ssnid) references soldier(ssnid),foreign

key(barracks_id) references barracks(barracks_id));

Insertion the values of livesin :

insert into livesin values (1201,101,5);

select *from livesin;

Output:
Creation of armory_maintainance table:

create table armory_maintainance(armory_idno int,enginner_id

int,dateofmaintainance date,primary key(armory_idno ,enginner_id),foreign

key(armory_idno) references armory(armory_idno),foreign key(enginner_id)

references engineer(enginner_id));

Insertion the values of armory_maintainance:

insert into armory_maintainance values (1000,10,'2022-09-24');

select *from armory_maintainance;

Output:

Creation of table barracks_management:

create table barracks_management(officer_id int,barracks_id int,primary

key(officer_id,barracks_id),foreign key(officer_id) references

officer(officer_id),foreign key(barracks_id) references barracks(barracks_id));

Insertion the values of barracks_management:

insert into barracks_management values(301,102);

select *from barracks_management;

Output:
Creation of armory_management table:

create table armory_management(officer_id int,armory_idno int,primary

key(officer_id,armory_idno),foreign key(officer_id) references

officer(officer_id),foreign key(armory_idno) references armory(armory_idno));

Insertion the values of barracks_management:

insert into armory_management values(301,1001);

select *from armory_management;

Output:
Queries:

1.find the id of the soidier whose name is arjun?

Query:

select ssnid from soldier where name='arjun';

Output:

2.find the count of how many are living in barracks?

Query:

select count(ssnid) from livesin;

Output:

3.Get the details of the all soldiers;

Query:

select *from soldier;

output:
4.Identify the soldier with maximum experience?

Query:

select s.name from soldier s where s.experience>=all(select s2.experience from

soldier s2;

output:

5.find the officer id who manages the barracks whose barracks_id is 102

Query:

select officer_id from barracks_management where barracks_id=102;

Output:

6.find the armory which is mostly used by soildiers?

Query:

select armory_idno,max(no_of_times_used) from armory;

Output:

7.Find the soidier details who lives in barracksid=103?

Query:

select *from soldier s inner join livesin l on s.ssnid=l.ssnid where

l.barracks_id=101;

Output:

8.find the armory which are in engineer for maintainance

Query:select armory_idno from armory_maintainance;

Output:

9.Find the total no of armory or weapons?

Query: select armory_idno from armory_maintainance;

Output:

10.Find the date of assignment of armory for soidier id 1201

Query: select *from assign_armory where ssnid=1201;

Output:
Normalization:

ssnid Name height age experience Address

1201 Arjun 5.6 27 6 Vzg

1202 varun 5.8 26 5 Chattighar

1203 Ravi 5.9 29 6 mumbai

1204 Sai 6.9 26 8 up

1NF:

From above table there is no multiple values attributes so it is 1nf.

2NF:

From above table the functional dependencies are

Ssnid →name

Ssnid →height

Ssnid→age

Ssnid→experience

Ssnid→address

After finding closures (ssnid)+=(name,height,age,experience,address,ssnid)

We get ssnid is a candidate key.

Therefore the The set of key attributes are: { ssnid }

ssnid → name height age experience

but ssnid does not determine address because two soldiers can have same address

so we have to divide the table into two tables

ssnid name height age experience

1201 Arjun 5.6 27 6

1202 varun 5.8 26 5

1203 Ravi 5.9 29 6

1204 Sai 6.9 26 8

ssnid address

1201 Vzg

1202 Chattisghar

So that we have all functional dependencies

And every non key attributes are partially dependent on key attribute(ssnid)

There it is 2 NF

3NF:

From the above table we don’t have any transitive dependency exists

So that LHS is superkey or the RHS are all key attributes

Therefore it is 3Nf .
For officer table:

Officer id Officername rankno

301 navven 3
302 vinay 1
303 sunny 4

1NF:

From above table there is no multiple values attributes so it is 1nf.

2NF:

From the above table we have functional dependencies are

Officer_id→Officername

Officer_id→rankno

Officer_id→(officername,rankno)

Closures are:

(Officerid)+ →(officer_id,officername,rankno)

Therefore the candidate key is officer id

The candiates keys are { officerid}, The set of key attributes are: { officerid}

Therefore key attributes is determines every non key attribute so there is no partial

dependency

So it is in 2NF.

3NF:

It is 3nf because the table is in 2nf and there no Transitive dependency .Therefore

it is in 3NF
For Armory table:

Armory id License_no Weight Serial no No of times

used

1000 14321 5 7
34.45

1001 13451 6 10
54.45

1002 14532 6 4
46.43

For the above table there is all atomic values so table is in 1NF

2NF:

The functional dependencies are:

Armory_id→(license_no)

Armory_id→(Weight)

License_no→(Serial_no)

Licecse_no→(No of times used)

Closures:(armory_id)+=(armory_id,weight,license no,serialno,no of times used)

Therefore it is a candidate key.

Therefore the candidate key is Armory id

The candidates keys are { Armory id}, The set of key attributes are: { Armoryid}

Fd={armory_id} →( lno , weight)

L_no → ( serialno , nofotimesused)

Therefore key attributes is determines every non key attribute so there is no partial

dependency

So it is in 2NF.

3NF:

Initially rel[1] is the original table with the original functional dependencies.

In each round we check the FDs one by one to see if there is a violation of 3NF

(there is a partial or transitive dependency where the RHS includes non-key

attributes). If yes, we decompose the table into two.

lno Serialno No of times used

14321 5 7
13451 6 10
14532 6 4

idno Lno Weight

1000 14321 34.45

1001 13451 54.45
1002 14532 46.43

rel[2] = (lno,serialno,nofotimesused), with FDs:

lno --> serialno,nofotimesused

rel[3] = (id,lno,weight), with FDs:

id --> lno,weight

so that from 2 ,3 table we does not have any transitive dependency.

Therefore it is 3NF.
For Barracks table:

barracks_id bname capacity area location_id

101 redstone 650 jammu 53002

102 blackkingdom 100 northkashmir 53022

103 rockers 240 gurezvalley 59034

From the above table there is all are in atomic values so there is 1NF

2NF:

The functional dependencies are

Barracks_id→bname

Barracks_id→capacity

Location_id→barracks_id

Location_id→area

bname→capacity

from the above the closures are

(location_id)+={bid,capacity,area,bname}

Therefore candidate key is Location_id

The candidates keys are { Armory id}, The set of key attributes are: { Armoryid}

Therefore key attributes is determines every non key attribute so there is no partial

dependency.So it is in 2NF.
3NF:

Initially rel[1] is the original table with the original functional dependencies.

In each round we check the FDs one by one to see if there is a violation of 3NF

(there is a partial or transitive dependency where the RHS includes non-key

attributes). If yes, we decompose the table into two.

checking table rel[1]

The table is not in 3NF.

rel[2] = (barracks_id,bname,capacity), with FDs:

barracks_id --> bname

bname --> capacity

rel[3] = (barracks_id,area,location_id), with FDs:

location_id --> barracks_id,area

checking table rel[2]

The table is not in 3NF.

rel[4] = (bname,capacity), with FDs:

bname --> capacity

rel[5] = (barracks_id,bname), with FDs:

barracks_id --> bname

there all the tables are in 3NF so there is no Transitive dependencies so it is in 3NF
Conclusion:

Our project is only a humble venture to satisfy the needs to manage their project

Work .several user friendly coding have also been adopted.with this project we

can or the any military management can know the details about the order

soldiers,barracks .using this concept we can implement extension model to this

project i.e Military management System