CI7300 Data Management: Database Design & Development - GreenCress

This project details the design and development of a database for GreenCress Promotions, an advertising company seeking to improve its campaign management. The project includes a class diagram illustrating the entities and relationships within the system, followed by key design decisions that shaped the database structure. A relational model is presented, outlining the tables, attributes, data types, and primary/foreign key constraints for entities like staff, clients, campaigns, meetings, adverts, components and placements. The implementation section provides the SQL code for creating these tables. Finally, the project showcases SQL queries used to retrieve relevant information, such as client-manager relationships, campaign-specific adverts, meeting attendees, and campaign statistics. Desklib offers more solved assignments and study resources for students.

COVER PAGE

Contents
Task 1: Class diagram...................................................................................................................................3
Task 2: Design decisions..............................................................................................................................3
Task 3 Relational model...............................................................................................................................4
Task 4 : Implementation..............................................................................................................................6
Task 5: queries.............................................................................................................................................9

Task 1: Class diagram
Task 2: Design decisions
The major design decisions are;
 A client can order one or more campaigns from the advertising firm.
 When a client is registered as a customer he or she is assigned a manager who is a staff of the
firm. The manager is supposed to keep contact with the staff even after the campaign is over for
purposes of marketing the advertising firm.
 When a staff orders a campaign, the campaign is assigned two contacts who are staff of the
firm. One of the staff assigned to the campaign is the manager of the campaign.

 One or more meetings are held for a particular campaign. A record of the meetings and the staff
who attended the meeting is supposed to be kept. A meeting can either be held at the client’s
premises or the firm’s premises.
 A campaign results into one or more adverts where by each advert has a start date and an end
date. An advert is of a particular type which determines the type of placement
 An advert has one or more components that are placed in the advertising space.
 An advert results into one or more placements. Each placement has a starting date and an end
date for example for a newspaper placement, the placement is supposed to happen between
specified dates. A staff may record notes regarding a placement.
Task 3 Relational model
Entity attribute Data type constraint
staff staffID NUMBER Primary key
role VARCHAR2(25)
salaryGrade VARCHAR(25)
chargeOutRate NUMBER
firstName VARCHAR2(50)
lastName VARCHAR(50)
tel VARCHAR(25)
Fax VARCHAR(25)
email VARCHAR(255)
client clientID NUMBER Primary key
firstName VARCHAR2(50)
lastName VARCHAR2(50)
Position VARCHAR2(50)
Email VARCHAR2(250)
Tel VARCHAR2(25)
manager NUMBER Foreign key references
staff (staffID)
campaign campaignCode VARCHAR(10)
clientID NUMBER Foreign key references
client (clientID)
Product VARCHAR2(50)
campaignName VARCHAR2(50)
manager NUMBER Foreign key references
staff (staffID)
campaignContacts campaignCode VARCHAR(10) Primary key,’
Foreign key references
campign
(CampaignCode)
staffID NUMBER Primary key,
Foreign key references
staff (staffID)
meeting meetingID NUMBER Primary key
Date DATE
Time TIME

Duration VARCHAR2(10)
Location VARCHAR2(50)
campaignCode VARCHAR(10) Foreign key references
campaign
(campaignCode)
Meeting_attendees meetingID NUMBER Primary key,
Foreign key references
meeting (meetingID)
staffID NUMBER Primary key,
Foreign key references
staff (staffID)
advert advertCode VARCHAR2(10) Primary key
Type VARCHAR2(50)
Content VARCHAR2(500)
Audience VARCHAR2(50)
Size VARCHAR2(50)
startDate DATE
endDate DATE
campaignCode Varchar2(10) Foreign key references
campaign
(campaigncode)
components componentID NUMBER Primary key
Type VARCHAR2(50)
advertCode VARCHAR(10) Foreign key references
advert (AdvertCode)
Status VARCHAR2(50)
completionDate DATE
placement placementCode VARCHAR2(10) Primary key
advertCode VARCHAR2(10) Foreign key references
advert (advertCode)
startDate DATE
endDate DATE
Location VARCHAR2(50)
notes VARCHAR2(100)

Task 4 : Implementation
1. Staff table
create table staff(
staffID number not null primary key,
role varchar2(50) not null,
salaryGrade varChar2(50) not null,
chargedOutRate number not null,
firstname varchar2(50) not null,
lastname varchar2(50) not null,
tel varchar2(25) not null,
fax varchar2(25) not null,
email varchar2(250) not null
);
2. Client table
create table client(
clientID number not null primary key,
firstname varchar2(50) not null,
lastname varchar2(50) not null,
tel varchar2(25) not null,
email varchar2(250) not null,
clientmanager number not null,
constraint client_fk foreign key (clientmanager) references
staff (staffID)
);
3. Campaign table
create table campaign(
campaignCode varchar2(10) not null primary key,
campaignName varchar2(50) not null,
clientID number not null,
manager number not null,
tel varchar2(25) not null,
constraint campaign_fk foreign key (manager) references staff
(staffID)
);

4. Campaigncontacts table
create table campaigncontacts(
campaignCode varchar2(10) not null,
staffID number not null,
constraint campaigncontacts_pk primary key
(campaigncode,staffID),
constraint campaigncontacts_fk foreign key (staffID)
references staff (staffID),
constraint campaigncontacts_fk2 foreign key (campaigncode)
references campaign(campaignCode)
);
5. Meeting table
create table meeting(
meetingID number not null primary key,
meetingDate DATE not null,
meetingTime varchar2(6) not null,
duration varchar2(10) not null,
location varchar2(25) not null,
campaignCode varchar2(10) not null,
constraint meeting_fk foreign key (campaigncode) references
campaign(campaignCode)
);
6. MeetingAttendess table
create table meetingAttendees(
meetingID number not null,
staffID number not null,
constraint meetingattendees_pk primary key
(meetingID,staffID),
constraint meetingattendees_fk foreign key (staffID)
references staff (staffID),

constraint meetingattendees_fk2 foreign key (meetingID)
references meeting(meetingID)
);
7. Advert table
create table advert(
advertcode varchar2(10) not null primary key,
adverttype varchar2(50) not null,
content varchar2(100) not null,
audience varchar2(100) not null,
advertsize varchar2(100) not null,
startdate date not null,
enddate date not null,
campaigncode varchar2(10) not null,
constraint advert_fk foreign key (campaigncode) references
campaign (campaigncode)
);
8. Component table
create table component(
componentCode number not null primary key,
componenttype varchar2(50) not null,
status varchar2(50) not null,
enddate date not null,
advertcode varchar2(10) not null,
constraint component_fk foreign key (advertcode) references
advert (advertcode)
);
9. Placement table
create table placement(
placementcode varchar(2) not null primary key,
location varchar2(50) not null,
notes varchar2(100) not null,
startdate date not null,
enddate date not null,
advertcode varchar2(10) not null,

constraint placement_fk foreign key (advertcode) references
advert (advertcode)
);
Task 5: queries
1. Clients and their managers
select
c.clientID,c.firstname,c.lastname,s.staffID,s.firstname,s.lastnam
e from client c inner join staff s on c.clientmanager=s.staffID;
2. Adverts for campaign one
select * from advert where campaigncode='c001';
3. All staff names who attended meeting one
select s.firstname,s.lastname from meetingattendees ma inner join
staff s on s.staffid=ma.staffid inner join meeting m on
m.meetingid=ma.meetingid where m.meetingid=1;
4. Count of all adverts done
select count(advertcode) from advert;
5. The number of campaigns each staff has managed
select s.firstname,s.lastname,count(campaigncode) from campaign c
inner join staff s on s.staffID=c.manager group by
s.firstname,s.lastname;