Anumaan Standalone-0.2 Release

Dear all,

We are very happy to announce the new release of our open source predictive text entry system - Anumaan. This release includes new version of standalone flavour of Anumaan i.e. Anumaan-standalone-0.2. This version has been released as part of the activities of project "Enhancing Accessibility for FOSS Desktops" under NRCFOSS-Phase II being carried out at CDAC, Mumbai.

Anumaan gives predictions based on preceding text/words used by the user in his/her text and user can use these predictions, while composing text. By incorporating predictions, user can improve his/her rate of text entry to a great extent.

Anumaan is mainly intended to help persons with motor disabilities, specially ones facing problems in hand and finger movement. Such persons face difficulty in using regular input devices like keyboard for text entry related tasks. Anumaan can help such persons immensely in their text entry related tasks by way of predictions. However, It is equally useful for common users also and can support them in creating mails, letters, documents etc. in less time.

Salient features of current version include:

• More user-friendly and simplified interface for easy interaction and navigation.
• Re-oriented layout of text entry area and prediction display in order to reduce visual and cognitive overhead.
• A single comprehensive prediction list combining predictions from all relevant grams for easy selection.
• Keyboard support for selecting predictions from prediction list. It reduces time taken in selecting and committing predictions to text.
• Larger text entry area compared to previous version for easy text entry.
• Integrated context-sensitive help.
• Re-oriented menu bar for easy access and navigation.

All these features are intended to improve user interaction and experience for motor disabled people. Anumaan is developed on java platform and supports UTF-8, so it can effectively be run on any platform.

Newly released version of Anumaan can be downloaded HERE.

We will appreciate if you can use the application and send us your valuable feedback. Feedback, bug reports or feature enhancement requests about the application can be sent to ossd@cdac.in or predictanumaan@gmail.com

Thanks & Regards
Anumaan Team,
Open Source Software Division,
CDAC Mumbai.
Kharghar, Navi Mumbai

DBMS Sample Project Part-3

******************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

DBMS Sample Project Part-2

 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

-->

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.