set A:
Consider the relations
Person (pnumber, pname, birthdate, income),
Area( aname,area_type).
An area can have one or more person living in it , but a person belongs to exactly one area. The
attribute ‘area_type’ can have values as either urban or rural.
Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).
Assume appropriate data types for all the attributes. Add any new attributes as required,
depending on the queries. Insert sufficient number of records in the relations / tables with
appropriate values as suggested by some of the queries.
Write select queries for following business tasks and execute them
Ans.
create table Area (
aname varchar(20) primary key,
area_type varchar check( area_type in('rural','urban'))
);
create table Person(
pnumber int Primary key,
pname varchar(20),
birthdate date,
income numeric(7,2),
aname varchar(20),
constraint fk foreign key(aname) references Area(aname) on delete cascade on update cascade
);
insert into Area values('Pune','urban');
insert into Area values('Satara','urban');
insert into Area values('Mumbai','urban');
insert into Area values('Washim','rural');
insert into Area values('Khed','rural');
insert into Person values(101,'Raj Hawaldar','08/24/1995',50000.00,'Pune');
insert into Person values(102,'Sanket Dewale','07/12/1996',20000.00,'Khed');
insert into Person values(103,'Mandar Athavle','05/02/1996',30000.00,'Washim');
insert into Person values(104,'Nilesh Bora','08/20/1995',40000,'Mumbai');
insert into Person values(105,'Prathmesh Gurav','08/12/1995',10000,'Satara');
insert into Person values(106,'Ram Sultan','05/02/1993',30000,'Pune');
insert into Person values(107,'Kiran Shinde','1/08/1994',40000,'Mumbai');
insert into Person values(108,'Swapnil Gore','2/05/1994',10000,'Satara');
insert into Person values(109,'Sunil Koli','7/04/1994',10000,'Satara');
Queries
Ans
Select * from Person where aname='Pune';