U Me N CS: May 2012

Tuesday, May 29, 2012

DBMS Sample Project Part-3

Posted by umencs

******************QUERIES************************

1.'AND' OPERATOR:

QUERY: find the employees details whose salary is
between 20000 and 22000.

select* from EMPLOYEE1 where esal>20000 and esal<22000;

ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
------- ----- ----- ------ ---- ---- ----------- ----- -----
bhagya e18 emp 2 21000 17-APR-81 banglore b33 b3

2.'OR' OPERATOR:

QUERY: find the employees details whose salary is
equal to 20000 or 22000.

select* from EMPLOYEE1 where esal=20000 or esal=22000;

ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
--------- ----- ----- ------ ---------- --------- ----------
thirupati e2 emp 2 22000 06-MAY-79 delhi b11 b1
raghu e1 crm 2 22000 06-MAY-79 delhi b11 b1
saurab e10 emp 3 22000 30-JAN-87 bombay b22 b2
lalitha e20 emp 4 22000 06-SEP-81 calcutta b41 b4
krishna e26 emp 6 22000 23-AUG-67 hyderabadb51 b5

3.'NOT' OPERATOR:

QUERY: find the employees details whose experience in not 2 years.

select* from EMPLOYEE1 where eexyr!=2 and eexyr!=3 and eexyr!=4
and eexyr!=5;

ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
------- ---- ----- ----- ---- ------- --------- ---- -----
safi e3 fm 7 25000 12-NOV-71 delhi b12 b1
sandyae4 emp 1 35000 12-FEB-81 delhi b12 b1
divya e5 cd 6 32000 24-MAR-85 delhi b13 b1
amith e8 emp 6 35000 12-FEB-81 bombay b21 b2
sandeepe11 cd 7 35000 14-OCT-81 bombay b23 b2
susheel e24 emp 6 23000 03-MAR-71 calcutta b43 b4
krishna e26 emp 6 22000 23-AUG-67 hyderabadb51 b5
harry e42 mgr 1 35000 05-OCT-71 chennai b6 b6

4.'LIKE(%)' OPERATOR:

QUERY:find the details of employee whose name starts with letter 'm'
or ends with letter 'n'.

select* from EMPLOYEE1 where ename like 'm%' or ename like '%n';

ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
------ ---- ----- ----- ---- -------- --------- ----- ----
mukesh e14 emp 3 23000 29-SEP-78 banglore b31 b3
nareen e17 cd 5 35000 06-JUN-72 banglore b33 b3
kiran e34 emp 4 26000 22-FEB-72 chennai b62 b6
john e37 mgr 4 40000 01-JAN-71 delhi b1 b1
mathew e38 mgr 4 45000 02-FEB-72 bombay b2 b2
martin e39 mgr 2 30000 03-MAR-76 banglore b3 b3

5'LIKE(_)' OPERATOR:

QUERY:find the details of employee whose name has letter 'a'
as third letter.

select* from EMPLOYEE1 where ename like '__a%';

ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
----- ----- ----- ----- ---- ------ -------- ---- -----
bhagya e18 emp 2 21000 17-APR-81 banglore b33 b3
chakri e33 fm 5 25000 22-FEB-81 chennai b62 b6

6.'IN' OPERATOR:

QUERY:find the details of clients whose establish years are
1983or1984.

select* from COMPANY_CLIENT where cesyr in(1983,1984);

CNAME CREGNO
--------------------------------------------------
CESYR CPHNO# CFNO
---------- ---------- ----------
CEMAIL
-------------------------
CLOCAT PCODE
-------------------------------- ------------------
bajaj c501
1983 4412345678 4412345679
bajaj@yahoo.com
banglore s19

herohonda c601
1984 4412345680 4412345681
honda@yahoo.com
banglore s19

7.'BETWEEN_AND' OPERATOR:

QUERY:find the details of products where the cost of products
between 20000 and 40000.

select* from PRODUCT where ppriceapproxrs between 20000 and 40000;

PNAME PCODE PPRICEAPPROXRS PWEIGHT
-------- ----- -------------- ----------
scooter s19 40000 100

8.'CONCATENATION(||)' OPERATOR:

QUERY:use concate operator on ad_branch table.

select bcode||'branch is in'||blocat from AD_BRANCH;

BCODE||'BRANCHISIN'||BLOCAT
---------------------------
b1branch is indelhi
b2branch is inbombay
b3branch is inbanglore
b4branch is incalcutta
b5branch is inhyderabad
b6branch is inchennai

9.'ORDER BY' OPERATOR:

QUERY:list the all products order by its code.

select* from PRODUCT order by pcode;

PNAME PCODE PPRICEAPPROXRS PWEIGHT
------- ----- -------------- --------
biscut b2 20 .25
car c3 300000 250
drinks d4 20 .6
laptop l12 45000 3
pen p16 15 .1
scooter s19 40000 100

QUERY:list the all products in descending order by its code.

select* from PRODUCT order by pcode desc;

PNAME PCODE PPRICEAPPROXRS PWEIGHT
------- ----- -------------- ----------
scooter s19 40000 100
pen p16 15 .1
laptop l12 45000 3
drinks d4 20 .6
car c3 300000 250
biscut b2 20 .25

10.'MONTHS_BETWEEN' OPERATOR:

QUERY:find the employee whose id is e12 is younger or elder than
employee whose id is e14.

select months_between((select edob from EMPLOYEE1 where eid='e12'),
(select edob from EMPLOYEE1 where eid='e14')) as YOUNG_OLD from dual;

YOUNG_OLD
----------
48.1290323

11.'SUM' FUNCTION:

QUERY:find the the total balances of clients of ad_company.

select sum(balan) from ACCOUNT_SEC;

SUM(BALAN)
---------
9400000

13.'MAX' FUNCTION:

QUERY:find the details of clients of ad_company whose balance
is maximum.

select* from COMPANY_CLIENT
where cregno in(select cregno from ACCOUNT_SEC
where balan in(select max(balan) from ACCOUNT_SEC));

CNAME CREGNO
------------------------------ --------------------
CESYR CPHNO# CFNO
---------- ---------- ----------
CEMAIL
------------------------------
CLOCAT PCODE
-------------------------------- --------------------
maruthi c101
1987 1112345678 1112345679
maruthi@yahoo.com
delhi c3

zen c201
1980 1112345680 1112345681
zen@yahoo.com
delhi c3

14.'MIN' FUNCTION:

QUERY:find the details of clients of ad_company whose balance
is minimum.

select* from COMPANY_CLIENT
where cregno in(select cregno from ACCOUNT_SEC
where balan in(select min(balan) from ACCOUNT_SEC));

CNAME CREGNO
---------------------------------- --------------------
CESYR CPHNO# CFNO
---------- ---------- ----------
CEMAIL
----------------------------
CLOCAT PCODE
---------------------------------- --------------------
cello c111
19985 4012345680 4012345681
cello@yahoo.com
hyderabad p16

reynolds c901
1994 4012345678 4012345679
ren@yahoo.com
hyderabad p16

15.'AVG' FUNCTION:

QUERY:find the average balance of clients of ad_company.

select avg(balan) from ACCOUNT_SEC;

AVG(BALAN)
----------
783333.333

16.'COUNT' FUNCTION:

QUERY:find the total number of employees in ad_company.

select count(eid) as TOT_EMP from EMPLOYEE1;

TOT_EMP
--------
42

17.'UPDATE' COMMAND:

QUERY:update emplotee salary to 30000 whose eid is e23.

update EMPLOYEE1 set esal=30000 where eid='e23';

select* from EMPLOYEE1 where eid='e23';

ENAME EID EDESI EEXYR ESAL EDOB ELOCAT DCODE BCODE
-------------- --------------------------------------------
sunil e23 cd 3 30000 14-NOV-81 calcutta b43 b4

18.'EQUI_JOIN' OPERATOR:

QUERY:find adtype and adduration of clients of ad_company.

select cname,adtype,adduram from COMPANY_CLIENT,ADS_ON
where COMPANY_CLIENT.pcode=ADS_ON.pcode;

CNAME ADTYPE ADDURAM
-------------- ---------- ----------
parle tvad 1month
britania tvad 1month
maruthi tvad 2months
zen tvad 2months
pepsi internetad 1month
cocacola internetad 1month
pepsi internetad 1month
cocacola ternetad 1month
hcl tvad 3months
dell tvad 3months
bajaj paperad 11months
herohonda paperad 11months

19.'CROSS_JOIN' OPERATOR:

QUERY:use cross join on finance and creative departmets.

select* from FINANCE_DEPT,CREATIVE_DEPT;

FDNAME FDCOD FDPHNO# FDFNO ACODE CDNAME CDCOD CDPHNO# CDFNO
------- ----- ------- --------- ----- -------- ----- ------- ------
finance1b12 1112345694 1112345695 a1 creative1 b13 1112345696 1112345697
finance2b22 2212345694 2212345695 a2 creative1 b13 1112345696 1112345697
finance3b32 4412345696 4412345697 a3 creative1 b13 1112345696 1112345697

20.'UNION' OPERATOR:

QUERY:use union on product and ads_on relations.

select pcode from PRODUCT
union
select pcode from ADS_ON;

PCODE
-----
b2
c3
d4
l12
p16
s19

21.'INTERSECT' OPERATOR:

QUERY:use intersect on department and finance_dept relations.

select dname from DEPARTMENT
intersect
select fdname from FINANCE_DEPT;

DNAME
----------
finance1
finance2
finance3
finance4
finance5
finance6

22.'VIEW' CREATING:

create view STUDIO(stname,stcity)
as select stname,stcity from PRODUCTION_STUDIO
where stcity='hyderabad';

View created.

23.'GROUP BY' OPERATOR:

QUERY:find the total amount paid to different branches of
ad_company as salary.

select bcode as branch,sum(esal) from EMPLOYEE1 group by bcode;

BRANC SUM(ESAL)
----- ----------
b1 199000
b2 210000
b3 186000
b4 201000
b5 197000
b6 192000

QUERY:find the number of employees in each branch.

select bcode as branch,count(*) as noofemp from EMPLOYEE1
group by bcode;

BRANC NOOFEMP
----- ----------
b1 7
b2 7
b3 7
b4 7
b5 7
b6 7

QUERY:find the maximum salary of each designation.

select edesig,max(esal) from EMPLOYEE1 group by edesig;

EDESI MAX(ESAL)
----- ----------
cd 35000
crm 29000
emp 35000
fm 29000
mgr 45000

24.'GROUP BY'with 'HAVING'CLAUSE:

select bcode,count(*) as nofemp,sum(esal) as salofbranch
from EMPLOYEE1 group by bcode having count(*)=7;

BCODE NOFEMP SALOFBRANCH
----- ------ -----------
b1 7 199000
b2 7 210000
b3 7 186000
b4 7 201000
b5 7 197000
b6 7 192000

Sunday, May 27, 2012

DBMS Sample Project Part-2

Posted by umencs

Creating Tables and Inserting Records

create table COMPANY_CLIENT(cname varchar2(50),
   cregno varchar2(20) primary key,
   cesyr int,
   cphno# int,
   cfno int,
   cemail varchar2(50),
   clocat varchar2(50),
   pcode varchar2(20));
   insert into COMPANY_CLIENT values('maruthi',
   'c101',
   1987,
   01112345678,
   01112345679,
   'maruthi@yahoo.com',
   'delhi',
   'c3');
    insert into COMPANY_CLIENT values('zen',
   'c201',
   1980,
   01112345680,
   01112345681,
   'zen@yahoo.com',
   'delhi',
   'c3');
   insert into COMPANY_CLIENT values('hcl',
   'c301',
   1972,
   02212345678,
   02212345679,
   'hcl@yahoo.com',
   'bombay',
   'l12');
   insert into COMPANY_CLIENT values('dell',
   'c401',
   1982,
   02212345680,
   02212345681,
   'dell@yahoo.com',
   'bombay',
   'l12');
   insert into COMPANY_CLIENT values('bajaj',
   'c501',
   1983,
   04412345678,
   04412345679,
   'bajaj@yahoo.com',
   'banglore',
   's19');
   insert into COMPANY_CLIENT values('herohonda',
   'c601',
   1984,
   04412345680,
   04412345681,
   'honda@yahoo.com',
   'banglore',
   's19');
       insert into COMPANY_CLIENT values('pepsi',
   'c701',
   1989,
   05512345678,
   05512345679,
   'pepsi@yahoo.com',
   'calcutta',
   'd4');
    insert into COMPANY_CLIENT values('cocacola',
   'c801',
   1979,
   05512345680,
   05512345681,
   'cola@yahoo.com',
   'calcutta',
   'd4');
    insert into COMPANY_CLIENT values('reynolds',
   'c901',
   1994,
   04012345678,
   04012345679,
   'ren@yahoo.com',
   'hyderabad',
   'p16');
   insert into COMPANY_CLIENT values('cello',
   'c111',
   19985,
   04012345680,
   04012345681,
   'cello@yahoo.com',
   'hyderabad',
   'p16');
   insert into COMPANY_CLIENT values('parle',
   'c222',
   1988,
   05412345678,
   05412345679,
   'parle@yahoo.com',
   'chennai',
   'b2');
   insert into COMPANY_CLIENT values('britania',
   'c333',
   2001,
   05412345680,
   05412345681,
   'bri@yahoo.com',
   'chennai',
   'b2');

---------------------------------------------------

create table PRODUCT(pname varchar2(10),
pcode varchar2(5),
ppriceapproxrs int,
pweight numeric(5,2));
insert into PRODUCT values('car',
'c3',
300000,
250.00);
insert into PRODUCT values('laptop',
'l12',
45000,
003.00);
insert into PRODUCT values('scooter',
's19',
40000,
100.00);
insert into PRODUCT values('drinks',
'd4',
20,
000.60);
insert into PRODUCT values('pen',
'p16',
15,
000.10);

insert into PRODUCT values('biscut',
'b2',
20,
000.25);

--------------------------------------------------------

create table AD_COMPANY(aname varchar2(20),
aregno varchar2(5) primary key,
aowner varchar2(10),
aesyr int,
aphno# int,
afno int,
aemail varchar2(20),
alocat varchar2(10));
insert into AD_COMPANY values('mother_ad_ageccy',
'A007',
'reniguntla',
1980,
05598765432,
05598765433,
'mother@yahoo,com',
'calcutta');
create table AD_BRANCH(bcode varchar2(5) primary key,
besyr int,
bphno# int,
bfno int,
blocat varchar2(10),
aregno varchar2(5));
insert into AD_BRANCH values('b1',
1981,
01112345690,
01112345691,
'delhi',
'A007');
insert into AD_BRANCH values('b2',
1983,
02212345690,
02212345691,
'bombay',
'A007');
insert into AD_BRANCH values('b3',
1986,
04412345690,
04412345691,
'banglore',
'A007');
insert into AD_BRANCH values('b4',
1989,
05512345690,
05512345691,
'calcutta',
'A007');
insert into AD_BRANCH values('b5',
1999,
04012345690,
04012345691,
'hyderabad',
'A007');
insert into AD_BRANCH values('b6',
2001,
05412345690,
05412345691,
'chennai',
'A007');
------------------------------------------------------------

create table DEPARTMENT(dname varchar2(10),
dcode varchar2(5) primary key,
dphno# int,
dfno int);
insert into DEPARTMENT values('cusreldep1',
'b11',
01112345692,
01112345693);
insert into DEPARTMENT values('finance1',
'b12',
01112345694,
01112345695);
insert into DEPARTMENT values('creative1',
'b13',
01112345696,
01112345697);
insert into DEPARTMENT values('cusreldep2',
'b21',
02212345692,
02212345693);
insert into DEPARTMENT values('finance2',
'b22',
02212345694,
02212345695);
insert into DEPARTMENT values('creative2',
'b23',
02212345696,
02212345697);
insert into DEPARTMENT values('cusreldep3',
'b31',
04412345694,
04412345695);
insert into DEPARTMENT values('finance3',
'b32',
04412345696,
04412345697);
insert into DEPARTMENT values('creative3',
'b33',
04412345692,
04412345693);
insert into DEPARTMENT values('cusreldep4',
'b41',
05512345692,
05512345693);
insert into DEPARTMENT values('finance4',
'b42',
05512345694,
05512345695);
insert into DEPARTMENT values('creative4',
'b43',
05512345696,
05512345697);
insert into DEPARTMENT values('cusreldep5',
'b51',
04012345692,
04012345693);
insert into DEPARTMENT values('finance5',
'b52',
04012345694,
04012345695);
insert into DEPARTMENT values('creative5',
'b53',
04012345696,
04012345697);
insert into DEPARTMENT values('cusreldep6',
'b61',
05412345692,
05412345693);
insert into DEPARTMENT values('finance6',
'b62',
05412345694,
05412345695);
insert into DEPARTMENT values('creative6',
'b63',
05412345696,
05412345697);

----------------------------------------------------------

create table HAVE(bcode varchar2(5),
dcode varchar2(5),
primary key(bcode, dcode));
insert into HAVE values('b1','b11');
insert into HAVE values('b1','b12');
insert into HAVE values('b1','b13');
insert into HAVE values('b2','b21');
insert into HAVE values('b2','b22');
insert into HAVE values('b2','b23');
insert into HAVE values('b3','b31');
insert into HAVE values('b3','b32');
insert into HAVE values('b3','b33');
insert into HAVE values('b4','b41');
insert into HAVE values('b4','b42');
insert into HAVE values('b4','b43');
insert into HAVE values('b5','b51');
insert into HAVE values('b5','b52');
insert into HAVE values('b5','b53');
insert into HAVE values('b6','b61');
insert into HAVE values('b6','b62');
insert into HAVE values('b6','b63');

-------------------------------------------------------------

create table CUS_REL_DEPT(cusdname varchar2(10),
cusdcode varchar2(5) primary key,
cusdphno# int,
cusdfno int);
insert into CUS_REL_DEPT values('cusreldep1',
'b11',
01112345692,
01112345693);
insert into CUS_REL_DEPT values('cusreldep2',
'b21',
02212345692,
02212345693);
insert into CUS_REL_DEPT values('cusreldep3',
'b31',
04412345694,
04412345695);
insert into CUS_REL_DEPT values('cusreldep4',
'b41',
05512345692,
05512345693);
insert into CUS_REL_DEPT values('cusreldep5',
'b51',
04012345692,
04012345693);
insert into CUS_REL_DEPT values('cusreldep6',
'b61',
05412345692,
05412345693);

------------------------------------------------------

create table CONSULT(cusdcode varchar2(5),
cregno varchar2(5),
primary key(cusdcode,cregno));
insert into CONSULT values('b11','c101');
insert into CONSULT values('b11','c333');
insert into CONSULT values('b21','c201');
insert into CONSULT values('b21','c222');
insert into CONSULT values('b31','c301');
insert into CONSULT values('b31','c111');
insert into CONSULT values('b41','c401');
insert into CONSULT values('b41','c901');
insert into CONSULT values('b51','c501');
insert into CONSULT values('b51','c801');
insert into CONSULT values('b61','c601');
insert into CONSULT values('b61','c701');
create table FINANCE_DEPT(fdname varchar2(10),
fdcode varchar2(5) primary key,
fdphno# int,
fdfno int,
acode varchar2(5));
insert into FINANCE_DEPT values('finance1',
'b12',
01112345694,
01112345695,
'a1');
insert into FINANCE_DEPT values('finance2',
'b22',
02212345694,
02212345695,
'a2');
insert into FINANCE_DEPT values('finance3',
'b32',
04412345696,
04412345697,
'a3');
insert into FINANCE_DEPT values('finance4',
'b42',
05512345694,
05512345695,
'a4');
insert into FINANCE_DEPT values('finance5',
'b52',
04012345694,
04012345695,
'a5');
   insert into FINANCE_DEPT values('finance6',
'b62',
05412345694,
05412345695,
'a6');
create table CREATIVE_DEPT(cdname varchar2(10),
   cdcode varchar2(5) primary key,
   cdphno# int,
   cdfno int);
    insert into CREATIVE_DEPT values('creative1',
   'b13',
   01112345696,
   01112345697);
   insert into CREATIVE_DEPT values('creative2',
   'b23',
   02212345696,
   02212345697);
    insert into CREATIVE_DEPT values('creative3',
   'b33',
   04412345692,
   04412345693);
   insert into CREATIVE_DEPT values('creative4', 'b4
   05512345696,
   05512345697);
   insert into CREATIVE_DEPT values('creative5',
   'b53',
   04012345696,
   04012345697);
    insert into CREATIVE_DEPT values('creative6',
   'b63',
   05412345696,
   05412345697);

-----------------------------------------------------------------

   create table ACCOUNT_SEC(acode varchar2(5),
   adcost int,
   balan int,
   ldate date,
   cregno varchar2(5),
   primary key(acode,cregno));
   insert into ACCOUNT_SEC values('a1',
   3000000,
   1500000,
   '1-jan-08',
   'c101');
   insert into ACCOUNT_SEC values('a1',
   600000,
   300000,
   '25-dec-07',
   'c333');
    insert into ACCOUNT_SEC values('a2',
   3000000,
   1500000,
   '1-dec-07',
   'c201');
    insert into ACCOUNT_SEC values('a2',
   600000,
   300000,
   '5-dec-07',
   'c222');
    insert into ACCOUNT_SEC values('a3',
   2500000,
   1200000,
   '25-dec-07',
   'c301');
   insert into ACCOUNT_SEC values('a3',
   500000,
   200000,
   '28-nov-07',
   'c111');
    insert into ACCOUNT_SEC values('a4',
   2500000,
   1200000,
   '25-nov-07',
   'c401');
   insert into ACCOUNT_SEC values('a4',
   500000,
   200000,
   '2-feb-08',
   'c901');
   insert into ACCOUNT_SEC values('a5',
   2000000,
   1000000,
   '1-feb-08',
   'c501');
   insert into ACCOUNT_SEC values('a5',
1000000,
500000,
'14-feb-08',
'c801');
insert into ACCOUNT_SEC values('a6',
2000000,
1000000,
'6-jan-08',
'c601');
insert into ACCOUNT_SEC values('a6',
1000000,
500000,
'24-mar-08',
'c701');

------------------------------------------------------------------

   create table PRODUCTION_STUDIO(stname varchar2(15),
   stregno varchar2(5) primary key,
   stphno# int,
   ststreet varchar2(15),
   stcity varchar2(10),
   stpin int,
   cdcode varchar2(5));
   insert into PRODUCTION_STUDIO values('konika_studio',
   'st101',
   01198765432,
   'cnnaughtplace',
   'delhi',
   110023,
   'b13');
   insert into PRODUCTION_STUDIO values('komal__studio',
   'st102',
   01198765433,
   'nehruplace',
   'delhi',
   110024,'b13');
   insert into PRODUCTION_STUDIO values('shivaji__studio',
   'st103',
   02298765432,
   'shivajinagar',
   'bombay',
   220037,
   'b23');
    insert into PRODUCTION_STUDIO values('chatrapathi_std',
   'st104',
   02298765433,
   'bandra',
   'bombay',
   220038,
   'b23');
    insert into PRODUCTION_STUDIO values('bang_studio',
   'st105',
   04498765433,
   'karunanagar',
   'banglore',
   220038,
   'b33');
   insert into PRODUCTION_STUDIO values('padmakshi_std',
   'st106',
   04498765434,
   'mahanagar',
   'banglore',
   220049,
   'b33');
   insert into PRODUCTION_STUDIO values('netaji_studio',
   'st107',
   05598765434,
   'therisanagar',
   'calcutta',
   550666,
   'b43');
    insert into PRODUCTION_STUDIO values('subhash_studio',
   'st108',
   05598765435,
   'netajinagar',
   'calcutta',
   550777,
   'b43');
    insert into PRODUCTION_STUDIO values('annapurna_std',
   'st109',
   04098765435,
   'banjarahills',
   'hyderabad',
   800056,
   'b53');
   insert into PRODUCTION_STUDIO values('ramoji_studio',
   'st110',
   04098765436,
   'jublihills',
   'hyderabad',
   800057,
   'b53');
   insert into PRODUCTION_STUDIO values('padmalaya_std',
   'st111',
   05498765436,
   'nugambakkam',
   'chennai',
   054678,
   'b63');
   insert into PRODUCTION_STUDIO values('chennai_std',
   'st112',
   05498765437,
   'madhuranagar',
   'chennai',
   054679,
   'b63');

----------------------------------------------------------------------

create table EMPLOYEE1(ename varchar2(10),
eid varchar2(5) primary key,
edesig varchar2(5),
eexyr int,
esal int,
edob date,
elocat varchar2(10),
dcode varchar2(5),
bcode varchar2(5));
insert into EMPLOYEE1 values('raghu',
'e1',
'crm',
2,
22000,
'6-may-79',
'delhi',
'b11',
'b1');
insert into EMPLOYEE1 values('thirupati',
'e2',
'emp',
2,
22000,
'6-may-79',
'delhi',
'b11',
'b1');
   insert into EMPLOYEE1 values('safi',
'e3',
'fm',
7,
25000,
'12-nov-71',
'delhi',
'b12',
'b1');
insert into EMPLOYEE1 values('sandya',
'e4',
'emp',
1,
35000,
'12-feb-81',
'delhi',
'b12',
'b1');
insert into EMPLOYEE1 values('divya',
'e5',
'cd',
6,
32000,
'24-mar-1985',
'delhi',
'b13',
'b1');
insert into EMPLOYEE1 values('laxmi',
'e6',
'emp',
3,
23000,
'6-may-81',
'delhi',
'b13',
'b1');
insert into EMPLOYEE1 values('rama',
'e7',
'crm',
2,
16000,
'1-aug-71',
'bombay',
'b21',
'b2');
insert into EMPLOYEE1 values('amith',
'e8',
'emp',
6,
35000,
'12-feb-81',
'bombay',
'b21',
'b2');
insert into EMPLOYEE1 values('neema',
'e9',
'fm',
5,
25000,
'23-apr-79',
'bombay',
'b22',
'b2');
insert into EMPLOYEE1 values('saurab',
'e10',
'emp',
3,
22000,
'30-jan-87',
'bombay',
'b22',
'b2');
insert into EMPLOYEE1 values('sandeep',
'e11',
'cd',
7,
35000,
'14-oct-81',
'bombay',
'b23',
'b2');
insert into EMPLOYEE1 values('vivek',
'e12',
'emp',
3,
32000,
'2-oct-82',
'bombay',
'b23',
'b2');
insert into EMPLOYEE1 values('rahul',
'e13',
'crm',
5,
23000,
'23-mar-83',
'banglore',
'b31',
'b3');
insert into EMPLOYEE1 values('mukesh',
'e14',
'emp',
3,
23000,
'29-sep-78',
'banglore',
'b31',
'b3');
insert into EMPLOYEE1 values('satya',
'e15',
'fm',
5,
29000,
'19-mar-79',
'banglore',
'b32',
'b3');
insert into EMPLOYEE1 values('naresh',
'e16',
'emp',
3,
25000,
'12-mar-79',
'banglore',
'b32',
'b3');
insert into EMPLOYEE1 values('nareen',
'e17',
'cd',
5,
35000,
'6-jun-72',
'banglore',
'b33',
'b3');
insert into EMPLOYEE1 values('bhagya',
'e18',
'emp',
2,
21000,
'17-apr-81',
'banglore',
'b33',
'b3');
insert into EMPLOYEE1 values('roja',
'e19',
'crm',
3,
27000,
'5-sep-79',
'calcutta',
'b41',
'b4');
insert into EMPLOYEE1 values('lalitha',
'e20',
'emp',
4,
22000,
'6-sep-81',
'calcutta',
'b41',
'b4');
insert into EMPLOYEE1 values('latha',
'e21',
'fm',
3,
29000,
'7-may-82',
'calcutta',
'b42',
'b4');
insert into EMPLOYEE1 values('amul',
'e22',
'emp',
4,
30000,
'15-aug-77',
'calcutta',
'b42',
'b4');
insert into EMPLOYEE1 values('sunil',
'e23',
'cd',
3,
40000,
'14-nov-81',
'calcutta',
'b43',
'b4');
insert into EMPLOYEE1 values('susheel',
'e24',
'emp',
6,
23000,
'3-mar-71',
'calcutta',
'b43',
'b4');
insert into EMPLOYEE1 values('ram',
'e25',
'crm',
2,
29000,
'25-dec-60',
'hyderabad',
'b51',
'b5');
insert into EMPLOYEE1 values('krishna',
'e26',
'emp',
6,
22000,
'23-aug-67',
'hyderabad',
'b51',
'b5');
insert into EMPLOYEE1 values('anir',
'e27',
'fm',
4,
26000,
'11-aug-78',
'hyderabad',
'b52',
'b5');
insert into EMPLOYEE1 values('rajini',
'e28',
'emp',
4,
25000,
'22-feb-79',
'hyderabad',
'b52',
'b5');
insert into EMPLOYEE1 values('suresh',
'e29',
'cd',
4,
32000,
'14-feb-72',
'hyderabad',
'b53',
'b5');
insert into EMPLOYEE1 values('sreenu',
'e30',
'emp',
3,
23000,
'30-jan-75',
'hyderabad',
'b53',
'b5');
insert into EMPLOYEE1 values('venky',
'e31',
'crm',
3,
23000,
'23-dec-73',
'chennai',
'b61',
'b6');
insert into EMPLOYEE1 values('deva',
'e32',
'emp',
4,
24000,
'23-nov-73',
'chennai',
'b61',
'b6');
insert into EMPLOYEE1 values('chakri',
'e33',
'fm',
5,
25000,
'22-feb-81',
'chennai',
'b62',
'b6');
insert into EMPLOYEE1 values('kiran',
'e34',
'emp',
4,
26000,
'22-feb-72',
'chennai',
'b62',
'b6');
insert into EMPLOYEE1 values('sagar',
'e35',
'cd',
3,
29000,
'29-mar-71',
'chennai',
'b63',
'b6');
insert into EMPLOYEE1 values('nikhil',
'e36',
'emp',
4,
30000,
'15-nov-76',
'chennai',
'b63',
'b6');
insert into EMPLOYEE1 values('john',
'e37',
'mgr',
4,
40000,
'1-jan-71',
'delhi',
'b1',
'b1');
insert into EMPLOYEE1 values('mathew',
'e38',
'mgr',
4,
45000,
'2-feb-72',
'bombay',
'b2',
'b2');
   insert into EMPLOYEE1 values('martin',
'e39',
'mgr',
2,
30000,
'3-mar-76',
'banglore',
'b3',
'b3');
insert into EMPLOYEE1 values('dick',
'e40',
'mgr',
2,
40000,
'3-apr-76',
'calcutta',
'b4',
'b4');
    insert into EMPLOYEE1 values('tom',
'e41',
'mgr',
3,
40000,
'4-apr-71',
'hyderabad',
'b5',
'b5');
insert into EMPLOYEE1 values('harry',
'e42',
'mgr',
1,
35000,
'5-oct-71',
'chennai',
'b6',
'b6');

-----------------------------------------------------------------

   create table EMPLOYEE2(eid varchar2(5) primary key,
   esex varchar2(5));
   insert into EMPLOYEE2 values('e1','m');
   insert into EMPLOYEE2 values('e2','m');
   insert into EMPLOYEE2 values('e3','m');
   insert into EMPLOYEE2 values('e4','f');
   insert into EMPLOYEE2 values('e5','f');
   insert into EMPLOYEE2 values('e6','f');
   insert into EMPLOYEE2 values('e7','f');
   insert into EMPLOYEE2 values('e8','m');
   insert into EMPLOYEE2 values('e9','f');
   insert into EMPLOYEE2 values('e10','m');
   insert into EMPLOYEE2 values('e11','m');
   insert into EMPLOYEE2 values('e12','m');
   insert into EMPLOYEE2 values('e13','m');
   insert into EMPLOYEE2 values('e14','m');
   insert into EMPLOYEE2 values('e15','m');
   insert into EMPLOYEE2 values('e16','m');
   insert into EMPLOYEE2 values('e17','m');
   insert into EMPLOYEE2 values('e18','f');
   insert into EMPLOYEE2 values('e19','f');
   insert into EMPLOYEE2 values('e20','f');
   insert into EMPLOYEE2 values('e21','f');
   insert into EMPLOYEE2 values('e22','m');
   insert into EMPLOYEE2 values('e23','m');
   insert into EMPLOYEE2 values('e24','m');
   insert into EMPLOYEE2 values('e25','m');
   insert into EMPLOYEE2 values('e26','m');
   insert into EMPLOYEE2 values('e27','m');
   insert into EMPLOYEE2 values('e28','f');
   insert into EMPLOYEE2 values('e29','m');
   insert into EMPLOYEE2 values('e30','m');
   insert into EMPLOYEE2 values('e31','m');
   insert into EMPLOYEE2 values('e32','m');
   insert into EMPLOYEE2 values('e33','m');
   insert into EMPLOYEE2 values('e34','m');
   insert into EMPLOYEE2 values('e35','m');
   insert into EMPLOYEE2 values('e36','m');
   insert into EMPLOYEE2 values('e37','m');
   insert into EMPLOYEE2 values('e38','m');
   insert into EMPLOYEE2 values('e39','m');
   insert into EMPLOYEE2 values('e40','m');
   insert into EMPLOYEE2 values('e41','m');
   insert into EMPLOYEE2 values('e42','m');

---------------------------------------------------------

   create table DEPENDENT1(name varchar2(10),
   age int,
   relation varchar2(10),
   eid varchar2(5),
   primary key(name,eid));
   insert into DEPENDENT1 values('daniel',
   22,
   'brother',
   'e1');
   insert into DEPENDENT1 values('chaitanya',
   21,
   'sisterinla',
   'e2');
   insert into DEPENDENT1 values('anees',
   25,
   'brother',
   'e3');
insert into DEPENDENT1 values('bindya',
   19,
   'sister',
   'e4');
   insert into DEPENDENT1 values('dheeraj',
   19,
   'brother',
   'e5');
   insert into DEPENDENT1 values('prasad',
   23,
   'husband',
   'e6');
   insert into DEPENDENT1 values('naag',
   25,
   'husband',
   'e7');
   insert into DEPENDENT1 values('aparna',
   29,
   'sister',
   'e8');
   insert into DEPENDENT1 values('shivam',
   49,
   'father',
   'e9');
   insert into DEPENDENT1 values('sandy',
   29,
   'brother',
   'e10');
   insert into DEPENDENT1 values('ekka',
   49,
   'uncle',
   'e11');
   insert into DEPENDENT1 values('suchi',
   21,
   'sister',
   'e12');
   insert into DEPENDENT1 values('umesh',
   21,
   'brother',
   'e13');
   insert into DEPENDENT1 values('raj',
   23,
   'uncle',
   'e14');
   insert into DEPENDENT1 values('narayana',
   22,
   'brother',
   'e15');
   insert into DEPENDENT1 values('sidharth',
   41,
   'father',
   'e16');
   insert into DEPENDENT1 values('reema',
   41,
   'mother',
   'e17');
   insert into DEPENDENT1 values('nasaiah',
   48,
   'father',
   'e18');
   insert into DEPENDENT1 values('livingstan',
   31,
   'husband',
   'e19');
   insert into DEPENDENT1 values('gopal',
   31,
   'husband',
   'e20');
   insert into DEPENDENT1 values('moses',
   30,
   'brother',
   'e21');
   insert into DEPENDENT1 values('kullu',
   30,
   'brother',
   'e22');
   insert into DEPENDENT1 values('venu',
   29,
   'uncle',
   'e23');
   insert into DEPENDENT1 values('sneha',
   21,
   'wife',
   'e24');
   insert into DEPENDENT1 values('sita',
   22,
   'wife',
   'e25');
   insert into DEPENDENT1 values('gopika',
   22,
   'wife',
   'e26');
   insert into DEPENDENT1 values('ananya',
   21,
   'wife',
   'e27');
     insert into DEPENDENT1 values('anil',
   25,
   'husband',
   'e28');
   insert into DEPENDENT1 values('swathi',
   21,
   'wife',
   'e29');
   insert into DEPENDENT1 values('shreya',
   22,
   'wife',
   'e30');
   insert into DEPENDENT1 values('geneliya',
   19,
   'sister',
   'e31');
   insert into DEPENDENT1 values('rani',
   21,
   'sister',
   'e32');
   insert into DEPENDENT1 values('chandra',
   19,
   'brother',
   'e33');
   insert into DEPENDENT1 values('anupam',
   22,
   'uncle',
   'e34');
   insert into DEPENDENT1 values('vidya',
   21,
   'sister',
   'e35');
   insert into DEPENDENT1 values('akshya',
   16,
   'sister',
   'e36');
   insert into DEPENDENT1 values('stella',
   16,
   'sister',
   'e37');
   insert into DEPENDENT1 values('hepsibah',
   19,
   'wife',
   'e38');
   insert into DEPENDENT1 values('jessica',
   16,
   'sister',
   'e39');
   insert into DEPENDENT1 values('buelah',
   21,
   'wife',
   'e40');
   insert into DEPENDENT1 values('jashuva',
   16,
   'brother',
   'e41');
   insert into DEPENDENT1 values('ratnam',
   16,
   'brother',
   'e42');

------------------------------------------------------------------

   create table DEPENDENT2(name varchar2(10) primary key,
   sex varchar2(5));
   insert into DEPENDENT2 values('daniel','m');
   insert into DEPENDENT2 values('chaitanya','f');
   insert into DEPENDENT2 values('anees','m');
   insert into DEPENDENT2 values('bindya','f');
   insert into DEPENDENT2 values('dheeraj','m');
   insert into DEPENDENT2 values('prasad','m');
   insert into DEPENDENT2 values('naag','m');
   insert into DEPENDENT2 values('aparna','f');
   insert into DEPENDENT2 values('shivam','m');
   insert into DEPENDENT2 values('sandy','m');
   insert into DEPENDENT2 values('ekka','m');
   insert into DEPENDENT2 values('suchi','f');
   insert into DEPENDENT2 values('umesh','m');
   insert into DEPENDENT2 values('raj','m');
   insert into DEPENDENT2 values('narayana','m');
   insert into DEPENDENT2 values('sidharth','m');
   insert into DEPENDENT2 values('reema','f');
   insert into DEPENDENT2 values('narsaiah','m');
   insert into DEPENDENT2 values('livingstan','m');
   insert into DEPENDENT2 values('gopal','m');
   insert into DEPENDENT2 values('moses','m');
   insert into DEPENDENT2 values('kullu','m');
   insert into DEPENDENT2 values('venu','m');
   insert into DEPENDENT2 values('sneha','f');
   insert into DEPENDENT2 values('sita','f');
   insert into DEPENDENT2 values('gopika','f');
   insert into DEPENDENT2 values('ananya','f');
   insert into DEPENDENT2 values('anil','m');
   insert into DEPENDENT2 values('swathi','f');
   insert into DEPENDENT2 values('shreya','f');
   insert into DEPENDENT2 values('geneliya','f');
   insert into DEPENDENT2 values('rani','f');
   insert into DEPENDENT2 values('chandra','m');
   insert into DEPENDENT2 values('anupam','m');
   insert into DEPENDENT2 values('vidya','f');
   insert into DEPENDENT2 values('akshya','f');
   insert into DEPENDENT2 values('stella','f');
   insert into DEPENDENT2 values('hepsibah','f');
   insert into DEPENDENT2 values('jessica','f');
   insert into DEPENDENT2 values('buelah','f');
   insert into DEPENDENT2 values('jashuva','m');
   insert into DEPENDENT2 values('ratnam','m');

----------------------------------------------------------------

   create table ADS_ON(adtype varchar2(10),
   adduram varchar2(10),
   pcode varchar2(5),
   aregno varchar2(5));
   insert into ADS_ON values('tvad',
   '2months',
   'c3',
   'a007');
   insert into ADS_ON values('tvad',
   '3months',
   'l12',
   'a007');
   insert into ADS_ON values('paperad',
   '11months',
   's19',
   'a007');
   insert into ADS_ON values('internetad',
   '1month',
   'd4',
   'a007');
   insert into ADS_ON values('tvad',
   '2months',
   'p16',
   'a007');
   insert into ADS_ON values('tvad',
   '1month',
   'b2',
   'a007');

Sample DBMS Project Part-1

Posted by umencs

-->

PROBLEM DEFINITION OF ADVERTISING AGENCY

An “ Advertising agency ” is a service business dedicated to creating, planning, and handling advertising for it’s “ clients or customers ” Here clients are “ companies ” and that companies produce “ products ” . These products needs print or television or Internet ads in order to increase their sales in the market. An Advertising agency independent from the client and provides an outside point of view to the effort of selling the client’s products or services. An agency can also handle overall marketing and branding strategies and sales promotions for it’s clients. Inside Advertising agency mainly three departments are there. They are

1.CUSTOMER RELATIONSHIP DEPARTMENT

This department takes the orders from clients in order to give ads on their products.

2.FINANCE DEPARTMENT

“ Finance department ” deals with the finance matters between clients and Advertising agency just like how much money client paid at initial time and what is the balance amount and what is the last date to pay balance etc..

3.CREATIVE DEPARTMENT

Creative department ” is a department where “ employees ” creates the actual ads from the core of an advertising agency. Creative department frequently work with “ production studios ” to develop and implement their ideas.

In Ad agency large number of “employees” exists. For example (i) Creative, drawing artists & copy writers work in creative department. (ii) Account manager handles finance department. (iii) .Branch manager handle ad branch etc.. for these employees “dependents” may also exists.

E-R Diagram

Will upload soon...!!!

-->

Expansion of Entity Attribute Names

1.COMPANY_CLIENT

cname=company name,cregno=compay registration number,cesyr=company establish year, cphno#=company phone number,cfno=company fax number,cemail=company e-mail,clocat= company location;

2.PRODUCT

pname=product name,pcode=product code,ppriceapproxrs=product price approximately in rupees,pweight=product weight;

3.AD_COMPANY

aname=ad company name,aregno=ad company registration number,aowner=ad company owner,aesyr=ad company establish year,aphno#=ad company phone number,afno=ad company fax number,aemail=ad company e-mail,alocat=ad company location;

4.AD_BRANCH

bcode=branch code,besyr=branch establish year,bphno#=branch phone number, bfno= branch fax number,blocat=branch location;

5.DEPARTMENT

dname=department name,dcode=department code,dphno#=department phone number, dfno=department fax number;

6.CREATIVE_DEPT

dname=department name,dcode=department code,dphno#=department phone number, dfno=department fax number;

7.FINANCE_DEPT

dname=department name,dcode=department code,dphno#=department phone number, dfno=department fax number;

8.CUS_REL_DEPT

dname=department name,dcode=department code,dphno#=department phone number, dfno=department fax number;

9.PRODUCTION_STUDIO

stname=studio name,stregno=studio registration number,stphno#= studio phone number,ststreet=studio street,stcity=studio city,stpin=studio pincode;

10.ACCOUNT_SEC

acode=account section code,adcost=advertising cost in rupees,balan=balance in rupees, ldate=last date to pay balance;

11.EMPLOYEE

ename=employee name,esex=employee sex,eid=employee identity number, edesig=employee designation, eexpe=employee experience,esal=employee salary,edob=employee date of birth,elocat=employee location;

12.DEPENDENT

name=dependent name,age=dependent age,sex=dependent sex,relation=dependent relationship with employee;

13.ADS_ON

Adtype=advertising type,adduram=advertising duration in months;

Relations

-->

1.COMPANY_CLIENT

cname cregno cesyr cphno# cfno cemail clocat pcode

ATTRIBUTE_NAME TYPE

cname varchar

cregno varchar

Cesyr int

cphno# int

cfno int

cemail varchar

clocat varchar

“cregno” is primary key in COMPANY_CLIENT relation.
Foreign key “pcode” refers to pcode in PRODUCT relation.

2.PRODUCT
pname pcode ppriceapproxrs pweight

ATTRIBUTE_NAME TYPE

pname varchar

pcode varchar

ppriceapproxrs int

pweight numeric

“pcode” is primary key in PRODUCT relation.

No foreign keys in PRODUCT relation.

3.AD_COMPANY

aname aregno aowner aesyr aphno# afno aemail alocat

ATTRIBUTE_NAME TYPE

aname varchar

aregno varchar

aowner varchar

aesyr int

aphno# int

afno int

aemail varchar

alocat varchar

“aregno” is primary key in AD_COMPANY relation.
No foreign keys in COMPANY_CLIENT relation.

4.AD_BRANCH
bcode besyr bphno# bfno blocat aregno

ATTRIBUTE_NAME TYPE

bcode varchar

besyr int

bphno# int

bfno int

blocat varchar

“bcode” is primary key in AD_BRANCH relation.
Foreign key “aregno” refers to aregno in AD_AGENCY relation.

5.DEPARTMENT
dname dcode dphno# dfno

ATTRIBUTE_NAME TYPE

dname varchar

dcode varchar

dphno# int

dfno int

“dcode” is primary key in DEPARTMENT relation.
No foreign keys in DEPARTMENT relation.

6.CUS_REL_DEPT

cudname cudcode cudphno# cudfno

ATTRIBUTE_NAME TYPE

cudname varchar

cudcode varchar

cudphno# int

cudfno int

“cudcode” is primary key in CREATIVE_DEPT relation.
No foreign keys in CUS_REL_DEPT relation.

7.FINANCE_DEPT

fdname fdcode fdphno# fdfno acode

ATTRIBUTE_NAME TYPE

fdname varchar

fdcode varchar

fdphno# int

fdfno int

“fdcode” is primary key in FINANCE_DEPT relation.
Foreign key “acode” refers to acode in ACCOUNT_SEC relation.

8.CREATIVE_DEPT
cdname cdcode cdphno# cdfno

ATTRIBUTE_NAME TYPE

cdname varchar

cdcode varchar

cdphno# int

cdfno int

“cdcode” is primary key in CREATIVE_DEPT relation.
No foreign keys in CREATIVE_DEPT relation.

9.ACCOUNT_SEC
acode adcost balan ldate cregno

ATTRIBUTE_NAME TYPE

acode varchar

adcost int

balan int

ldate date

“acode” is primary key in ACCOUNT_SEC relation.
Foreign key “cregno” refers to cregno in COMPANY_CLIENT relation.

10.PRODUCTION_STUDIO
stname stregno stphno# ststreet stcity stpin cdcode

ATTRIBUTE_NAME TYPE

stname varchar

strehno int

stphno# int

ststreet varchar

stcity varchar

stpin int

“stregno” is primary key in PRODUCTION_STUDIO relation.
Foreign key “cdcode” refers to cdcode in CREATIVE_DEPT relation.

11.EMPLOYEE
ename eid edesig eexyr esal edob elocat esex bcode dcode

ATTRIBUTE_NAME TYPE

ename varchar

eid varchar

edesig varchar

eexyr varchar

esal int

edob date

elocat varchar

esex varchar

“eid” is primary key in EMPLOYEE relation.
Foreign key “bcode” refers to bcode in AD_BRANCH relation.
Foreign key “dcode” refers to dcode in DEPARTMENT relation.

12.DEPENDENT

name age sex dob relation eid

ATTRIBUTE_NAME TYPE

name varchar

age int

sex varchar

dob date

relation varchar

“name” is partial primary key in DEPENDENT relation.

Foreign key “eid” refers to eid in EMPLOYEE relation.

13.ADS_ON

adtype adduram pcode aregno

ATTRIBUTE_NAME TYPE

adtype varchar

adduram varchar

Foreign key “pcode” refers to pcode in PRODUCT relation.
Foreign key “aregno” refers to aregno in AD_COMPANY relation.

Normalizing Relations

1.COMPANY_CLIENT

COMPANY_CLIENT(cname,cregno,cesyr,cphno#,cfno,cemail,clocat,pcode);

In the above COMPANY_CLIENT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

2.PRODUCT

PRODUCT(pname,pcode,ppriceapproxrs,pweight,aregno);

In the above PRODUCT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

3.AD_COMPANY

AD_COMPANY(aname,aregno,aowner,aesyr,aphno#,afno,aemail,alocat);

In the above AD_COMPANY relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

4.AD_BRANCH

AD_BRANCH(bcode,besyr,bphno#,bfno,blocat,aregno);

In the above AD_BRANCH relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

5.DEPARTMENT

DEPARTMENT(dname,dcode,dphno#,dfno);

In the above DEPARTMENT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.

There is no transitive dependency so it is in 3NF.

6.CUS_REL_DEPT

CUS_REL_DEPT(cudname,cudcode,cudphno#,cudfno);

In the above CUS_REL_DEPT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

7.FINANCE_DEPT

FINANCE_DEPT(fdname,fdcode,fdphno#,fdfno,acode);

In the above FINANCE_DEPT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

8.CREATIVE_DEPT

CREATIVE_DEPT(cdname,cdcode,cdphno#,cdfno);

In the above CREATIVE_DEPT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

9.ACCOUNT_SEC

ACCOUNT_SEC(acode,adcost,balan,ldate,cregno);

In the above ACCOUNT_SEC relation
There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

10.PRODUCTION_STUDIO

PRODUCTION_STUDIO(stname,stregno,stphno#,ststreet,stcity,stpin,cdcode);

In the above CREATIVE_DEPT relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.

11.EMPLOYEE

EMPLOYEE(ename,esex,eid,edesig,eexyr,esal,edob,elocat,bcode,dcode);

In the above EMPLOYEE relation

There are all atomic attributes so it is in 1NF.
There is a partial dependency so it is not in 2NF.

eid,ename esex

eid sex

after decomposition

EMPLOYEE1(ename,eid,edesig,eexpe,esal,edob,street,ecity,epin);

EMPLOYEE2(eid,esex);

Now EMPLOYEE relation is in 2NF.

There is no transitive dependency so it is in 3NF.

12.DEPENDENT

DEPENDENT(name,age,sex,relation,eid);

In the above DEPENDENT relation

There are all atomic attributes so it is in 1NF.
There is a partial dependency so it is not in 2NF.

Eid,name sex

name sex

after decomposition

DEPENDENT1(name,age,dob,relation,eid);

DEPENDENT2(name,sex);

Now DEPENDENT relation is in 2NF.

There is no transitive dependency so it is in 3NF.

13.ADS_ON

ADS_ON(adtype,adduram,pcode,aregno);

In the above ADS_ON relation

There are all atomic attributes so it is in 1NF.
There is no partial dependency so it is in 2NF.
There is no transitive dependency so it is in 3NF.