Desklib - Online Library for Study Material with Solved Assignments, Essays, Dissertations
Verified
Added on  2023/06/12
|10
|1328
|366
AI Summary
This document contains a class diagram, design decisions, relational model, implementation, and queries for a campaign management system. The system allows clients to order campaigns from an advertising firm, and staff members to manage and execute those campaigns. The document includes SQL queries for retrieving information from the system's database.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
COVER PAGE
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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 EntityattributeData typeconstraint staffstaffIDNUMBERPrimary key roleVARCHAR2(25) salaryGradeVARCHAR(25) chargeOutRateNUMBER firstNameVARCHAR2(50) lastNameVARCHAR(50) telVARCHAR(25) FaxVARCHAR(25) emailVARCHAR(255) clientclientIDNUMBERPrimary key firstNameVARCHAR2(50) lastNameVARCHAR2(50) PositionVARCHAR2(50) EmailVARCHAR2(250) TelVARCHAR2(25) managerNUMBERForeign key references staff (staffID) campaigncampaignCodeVARCHAR(10) clientIDNUMBERForeign key references client (clientID) ProductVARCHAR2(50) campaignNameVARCHAR2(50) managerNUMBERForeign key references staff (staffID) campaignContactscampaignCodeVARCHAR(10)Primary key,’ Foreign key references campign (CampaignCode) staffIDNUMBERPrimary key, Foreign key references staff (staffID) meetingmeetingIDNUMBERPrimary key DateDATE TimeTIME
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
constraint meetingattendees_fk2foreignkey(meetingID) references meeting(meetingID) ); 7.Advert table create table advert( advertcode varchar2(10)notnullprimarykey, adverttype varchar2(50)notnull, content varchar2(100)notnull, audience varchar2(100)notnull, advertsize varchar2(100)notnull, startdate date not null, enddate date not null, campaigncode varchar2(10)notnull, constraint advert_fkforeignkey(campaigncode)references campaign (campaigncode) ); 8.Component table create table component( componentCode numbernotnullprimarykey, componenttype varchar2(50)notnull, status varchar2(50)notnull, enddate date not null, advertcode varchar2(10)notnull, constraint component_fkforeignkey(advertcode)references advert (advertcode) ); 9.Placement table create table placement( placementcode varchar(2)notnullprimarykey, location varchar2(50)notnull, notes varchar2(100)notnull, startdate date not null, enddate date not null, advertcode varchar2(10)notnull,
constraint placement_fkforeignkey(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 joinstaffsonc.clientmanager=s.staffID; 2.Adverts for campaign one select * from advert wherecampaigncode='c001'; 3.All staff names who attended meeting one select s.firstname,s.lastnamefrommeetingattendeesmainnerjoin staff s on s.staffid=ma.staffidinnerjoinmeetingmon m.meetingid=ma.meetingidwherem.meetingid=1; 4.Count of all adverts done select count(advertcode)fromadvert; 5.The number of campaigns each staff has managed select s.firstname,s.lastname,count(campaigncode)fromcampaignc inner join staff s on s.staffID=c.managergroupby s.firstname,s.lastname;