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.