CI7300: Data Management and Governance Assignment Sample 2023
Introduction
The Delphi promotion company has decided that to develop the advertising campaigns for their clients. The client first approach the company with the product then the company shows the information of various proposals related to the campaigns type. Then the client chose the campaign for their product advertisement along with the type of various types of process. The company has to decide the accurate monitoring of the record for the analysis of the current process going status.
The company has the permanent staffs that are nearly 40 members and also the company has the role of company manager, graphic designer and account manager like that. The main aim of this company is to deploy the customer along with the necessary information and maintain the customer along with the advertisement details.
Campaigns consist of adverts then each advertisement is consisting with the advertisement code and campaign information. The meeting is conducted for the every process along with the customer and staff for discussing about the advertisement type and mode of advertisement is executed.
The placement details is used for the analysis of the placement process every product along with campaigns details all the information is considered and developed the database using SQL and performed some manipulation operation regarding company processes.
Implementation with SQL Tables
Staff Details
CREATE TABLE STAFFDETAILS (STAFFDEID VARCHAR2 (20) PRIMARY KEY, STNAME VARCHAR2 (25) NOT NULL, PNO NUMBER NOT NULL, EMAILVARCHAR2 (25) NOT NULL, POSITIONVARCHAR2 (25) NOT NULL);
ClientInformation
CREATE TABLE CLIENTINF (CLIIDVARCHAR (25) PRIMARY KEY, CLINAME VARCHAR2 (25) NOT NULL, PNO NUMBER NOT NULL, EMAILIDVARCHAR2 (25) NOT NULL);
CampaignsInfo
CREATE TABLE CAMPAIGSNDE (CAMPAIGSNDEID VARCHAR (25) PRIMARY KEY, PRODUCTDETAILSVARCHAR2 (25) NOT NULL, SUPERVISORVARCHAR2 (25) NOT NULL, STAFFDEID VARCHAR2 (25), CONSTRAINT FK_STAFFDEF FOREIGN KEY (STAFFDEID) REFERENCES STAFFDETAILS (STAFFDEID), CLIID VARCHAR (25), CONSTRAINT FK_CLIID FOREIGN KEY (CLIID) REFERENCES CLIENTINF (CLIID));
Meeting Details
CREATE TABLE MEETINGDETAILS (MDEID VARCHAR(25) PRIMARY KEY, STDATE DATE NOT NULL, DURATION NUMBER, STAFFDEID VARCHAR2(25), CONSTRAINT FK_STAFFDEME FOREIGN KEY (STAFFDEID) REFERENCES STAFFDETAILS(STAFFDEID), CLIID VARCHAR(25), CONSTRAINT FK_CLIME FOREIGN KEY (CLIID) REFERENCES CLIENTINF(CLIID), CAMPAIGSNDEID VARCHAR(25), CONSTRAINT FK_CAMPAIGSNDEME FOREIGN KEY (CAMPAIGSNDEID) REFERENCES CAMPAIGSNDE(CAMPAIGSNDEID));
Component Information
CREATE TABLE COMPONENTINF (COMPONENTINFID VARCHAR (25) PRIMARY KEY, COMPONENTNAME VARCHAR2 (25) NOT NULL);
Advertisement Information
CREATE TABLE ADVERTISEMENTINF (ADVINFID VARCHAR(25) PRIMARY KEY, TYPEVARCHAR(25) NOT NULL , DETAILS VARCHAR(25) NOT NULL, STDATE DATE NOT NULL, EDDATE DATE NOT NULL, SIZEOFINT VARCHAR(25) NOT NULL , COMPONENTINFID VARCHAR(25), CONSTRAINT FK_COMPONENTINF FOREIGN KEY (COMPONENTINFID) REFERENCES COMPONENTINF(COMPONENTINFID) );
PlacementInformation
CREATE TABLE PLACEMENTINF (PLACEMENTINFID VARCHAR (25) PRIMARY KEY, PLACMENTNAME VARCHAR (25) NOT NULL);
Sharing of Placement
CREATE TABLE PLACEMENTALLOCATION (PALLID VARCHAR(25) PRIMARY KEY, ADVINFID VARCHAR(25), CONSTRAINT FK_ADVINFIDP FOREIGN KEY (ADVINFID) REFERENCES ADVERTISEMENTINF(ADVINFID),CAMPAIGSNDEID VARCHAR(25), CONSTRAINT FK_CAMPAIGSNPLA FOREIGN KEY (CAMPAIGSNDEID) REFERENCES CAMPAIGSNDE(CAMPAIGSNDEID), PLACEMENTINFID VARCHAR(25), CONSTRAINT FK_PLACEMENTINFIDFOREIGN KEY (PLACEMENTINFID) REFERENCES PLACEMENTINF (PLACEMENTINFID) );
Queries Demonstration
- INSERT INTO STAFFDETAILS VALUES (‘S101’,’REKA’,678432445,’[email protected]’,’CEO’);
SELECT *FROM STAFFDETAILS
- INSERT INTO CLIENTINF VALUES(‘CLI101′,’RAJI’,56783455,’[email protected]’);
SELECT *FROM CLIENTINF
- INSERT INTO CAMPAIGSNDE VALUES (‘CAMP021′,’MAGAZINE’,’KARTHI’,S101,’CLI101′);
SELECT *FROM CAMPAIGSNDE
- INSERT INTO MEETINGDETAILS VALUES (‘MEDEC101′,’JUL-16-2019′,18,’S101’,’CLI101′,’CAMP021’);
Select MDEID, STDATE, DURATION, STAFFDEID, CLIID, CAMPAIGSNDEID from MEETINGDETAILS
- INSERT INTO COMPONENTINF VALUES (‘COM101′,’VIDEO’);
Select COMPONENTINFID, COMPONENTNAME from COMPONENTINF
- INSERT INTO ADVERTISEMENTINF VALUES (‘ADVINF101′,’RADIO’,’AUDIO’,’FEB-10-2020′,’NOV-10-2020′,’A2′,’COM101′);
select ADVINFID, TYPE, DETAILS, STDATE, EDDATE, SIZEOFINT, COMPONENTINFID from ADVERTISEMENTINF
- INSERT INTO PLACEMENTINF VALUES(‘PL1001′,’NEWS PAPER’);
select PLACEMENTINFID, PLACMENTNAME from PLACEMENTINF
- INSERT INTO PLACMENTALLOCATION VALUES(‘PDAL101′,’ADVINF101′,’CAMP021′,’PL1001’);
select PALLID, ADVINFID, CAMPAIGSNDEID, PLACEMENTINFID from PLACEMENTALLOCATION
Conclusion
The above information shows that the database creation of the Delphi Company using the SQL and performed the some manipulation operations related to the company requirement.
The company has to decide the accurate monitoring of the record for the analysis of the current process going status. The company has the permanent staffs that are nearly 40 members and also the company has the role of company manager, graphic designer and account manager like that. The main aim of this company is to deploy the customer along with the necessary information and maintain the customer along with the advertisement details.
The Delphi database to support the activities involve in the campaign include with the staff details along with the campaign information and client information along with the contact information, campaign details and meeting details of the process. Then consider with the advertisement information along with the type and placement information along with the location.
Like that the database is created for the Delphi Company and developed application for their customer.
References
- Jing Han, Meina Song, and Junde Song,2016. “A Novel Solution of Distributed Memory NoSQL Database for Cloud Computing”, In ICIS 2011, 10th IEEE/ACIS International Conference on Computer and Information Science.
- Kai Fan,2017. “Suvey on Nosql”, Programmer, : pp.76-78
- Eessaar, E,2014. On applying normalized systems theory to thebusiness architectures of information systems. Baltic J. Modern Computing.
- Date, C. J., Darwen, H., & Lorentzos, N. A. ,2016. Temporal Data and the Relational Model: A Detailed Investigation into the Application of Interval and Relation Theory to the Problem of Temporal Database Management. Amsterdam: Elsevier Science Ltd.
- Hoffer, J. A., Prescott, M. B., & McFadden, F. R., 2017. Modern Database Management, 8th ed. Upper Saddle River, NJ: Pearson Prentice Hall.
Know more about UniqueSubmission’s other writing services:
