CI7300 Data Management And Governance Coursework ✓ Solved

CI7300 CI7300 Data Management And Governancecoursework Database Desi

Cleaned assignment instructions: Develop a conceptual class diagram that captures data and links supporting system requirements for a database supporting campaign management at Delphi Promotions. The design should address data about staff, clients, campaigns, meetings, adverts, components, placements, and related details, including constraints and assumptions. Then, implement the model in Oracle, populate it with sample data, test it with SQL queries, and produce a report with chapters covering introduction, class diagram with constraints and assumptions, implementation (table definitions), six system-relevant queries, and a critical evaluation of the final product and exercise.

Sample Paper For Above instruction

CI7300 CI7300 Data Management And Governancecoursework Database Desi

Database Design for Campaign Management at Delphi Promotions

Introduction

The aim of this project is to design and implement a comprehensive database system that efficiently manages the various aspects of campaigns at Delphi Promotions. This system aims to enhance accuracy, track campaign progress, and streamline operations related to staff, clients, campaigns, meetings, advertisements, components, and placements. The primary objective is to develop a flexible and robust data model that encapsulates all necessary details to support decision-making, planning, and execution of promotional campaigns, thereby reducing reliance on paper records and minimizing operational errors.

Class Diagram with Constraints and Assumptions

Conceptual Data Model

The core of the data model is built around the following major classes: Staff, Client, Campaign, Meeting, Advert, Component, Placement, and CampaignTeam. The class diagram is designed to encapsulate relationships and constraints as follows:

  • Staff class includes attributes such as staffNumber (primary key), name, role, salaryGrade, chargeRate, telephoneNumber, faxNumber, email. It is associated with Campaigns (as campaign manager or campaign team member) and Meetings (as attendee).
  • Client class has attributes like clientID, companyName, and contacts (with nested attributes for contactName, position, email, telephone). Each client has an AccountManager (a Staff member).
  • Campaign class includes campaignCode (PK), campaignName, campaignManager (Staff), client (linked to Client), and status. It is linked to multiple Meetings, Adverts, and CampaignTeams.
  • Meeting class records date, time, duration, purpose, location, and involved staff members.
  • Advert class with advertCode, description, audience, size, type, associated with a Campaign, and links to Components.
  • Component class is linked to Advert and records details such as componentType, description, completionStatus, and designCompletionDate.
  • Placement class includes placementCode, locationDetails, campaign, and advert, supporting multiple placements per advert.

Constraints and Assumptions

  • Staff numbers are unique; staff may fulfill multiple roles in different campaigns.
  • Campaigns are uniquely identified by campaignCode.
  • The campaign process involves multiple meetings and multiple adverts, each with components and placements.
  • Industry constraints such as referential integrity between classes, and data validation rules for dates, sizes, and types.
  • Assumption that all campaign documentation details are external and not stored within the database.
  • Meetings can occur at Delphi or client locations, recorded via location attribute but room details are not stored.

Implementation

SQL Table Definitions

CREATE TABLE Staff (

staffNumber INT PRIMARY KEY,


name VARCHAR(100),


role VARCHAR(50),


salaryGrade VARCHAR(20),


chargeRate DECIMAL(10,2),


telephoneNumber VARCHAR(20),


faxNumber VARCHAR(20),


email VARCHAR(100)


);


CREATE TABLE Client (


clientID INT PRIMARY KEY,


companyName VARCHAR(100),


-- Contacts are stored in a separate table since multiple contacts per client


);


CREATE TABLE ClientContact (


contactID INT PRIMARY KEY,


clientID INT REFERENCES Client(clientID),


contactName VARCHAR(100),


position VARCHAR(50),


email VARCHAR(100),


telephone VARCHAR(20)


);


CREATE TABLE Campaign (


campaignCode VARCHAR(10) PRIMARY KEY,


campaignName VARCHAR(100),


campaignManager INT REFERENCES Staff(staffNumber),


clientID INT REFERENCES Client(clientID),


status VARCHAR(20)


);


CREATE TABLE Meeting (


meetingID INT PRIMARY KEY,


campaignCode VARCHAR(10) REFERENCES Campaign(campaignCode),


date DATE,


time TIME,


duration DECIMAL(5,2),


purpose VARCHAR(255),


location VARCHAR(50),


meetingVenue VARCHAR(100)


);


CREATE TABLE Advert (


advertCode VARCHAR(10) PRIMARY KEY,


campaignCode VARCHAR(10) REFERENCES Campaign(campaignCode),


description VARCHAR(255),


audience VARCHAR(50),


size VARCHAR(20),


type VARCHAR(20)


);


CREATE TABLE Component (


componentID INT PRIMARY KEY,


advertCode VARCHAR(10) REFERENCES Advert(advertCode),


componentType VARCHAR(50),


description VARCHAR(255),


completionStatus BOOLEAN,


designCompletionDate DATE


);


CREATE TABLE Placement (


placementCode VARCHAR(10) PRIMARY KEY,


campaignCode VARCHAR(10) REFERENCES Campaign(campaignCode),


advertCode VARCHAR(10) REFERENCES Advert(advertCode),


locationDetails VARCHAR(255),


placementDate DATE


);

SQL Queries Demonstrating System Capabilities

1. Retrieve all campaigns with their campaign managers and clients

SELECT c.campaignCode, c.campaignName, s.name AS campaignManager, cl.companyName

FROM Campaign c


JOIN Staff s ON c.campaignManager = s.staffNumber


JOIN Client cl ON c.clientID = cl.clientID;

2. List all meetings for a specific campaign

SELECT m.meetingID, m.date, m.time, m.duration, m.purpose, m.meetingVenue

FROM Meeting m


WHERE m.campaignCode = 'C111';

3. Find all adverts and their components for a campaign

SELECT a.advertCode, a.description, c.componentType, c.description AS componentDescription

FROM Advert a


JOIN Component c ON a.advertCode = c.advertCode


WHERE a.campaignCode = 'C111';

4. List all placements for a specific advert

SELECT p.placementCode, p.locationDetails, p.placementDate

FROM Placement p


WHERE p.advertCode = 'A111';

5. Get staff involved in campaigns, including their roles

SELECT s.staffNumber, s.name, s.role

FROM Staff s


JOIN Campaign c ON c.campaignCode = 'C111' AND (s.staffNumber = c.campaignManager OR s.staffNumber IN (


SELECT staffNumber FROM CampaignTeam WHERE campaignCode = c.campaignCode


));

6. Count total number of campaigns and active campaigns

SELECT COUNT(*) AS TotalCampaigns,

(SELECT COUNT(*) FROM Campaign WHERE status = 'Active') AS ActiveCampaigns


FROM Campaign;

Conclusion

The designed database model captures all essential data and relationships required for effective campaign management at Delphi Promotions. It supports tracking staff involvement, client details, campaign progress, meetings, adverts, components, and placements, adhering to industry constraints and logical assumptions. Implementation using Oracle SQL demonstrates the system's feasibility, with sample data and queries validating functionality. This exercise highlights the importance of a well-structured data model in improving operational efficiencies, ensuring data integrity, and enabling comprehensive reporting for strategic decision-making. Critical evaluation reveals that while the model is comprehensive, future enhancements could include automation of alerts for incomplete components and integration with external documentation for a fully holistic campaign management system.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.

Related Assignments