C++ Programming Database System Assessment Guide Assessment 2 - Case Study Database System – Assessment Guide 1|Page This project is the main assessment of this subject. It is worth 20% of your total...

/*--------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------*/
/*Data Definition Language Script:
The tables are created using following code
*/
create table series(
SeriesID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
SeriesName    varchar(50),
SeriesPlatform    varchar(50),
SeriesReleaseDate    date,
SeriesGenre    varchar(50),
SeriesPrice    float,
SeriesPublisher    varchar(50)
);
create table developer(
DeveloperID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
Contact varchar(20),
);
create table game(
GameID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
GameName varchar(50),
Platform varchar(50),
ReleaseDate date,
Genre varchar(50),
SubGenre varchar(50),
Price float,
GamePublisher varchar(50),
DeveloperID varchar(20),
foreign key (DeveloperID) references Developer(DeveloperID)
);
create table gameSeries(
GameSeriesID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
GameID integer NOT NULL,
SeriesID integer NOT NULL,
foreign key (GameID) references Game(GameID),
foreign key (SeriesID) references Series(SeriesID)
);
create table ProductionStudio(
PSTeamID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
PSTeamName varchar(50)
);
create table devProduction (
DevProductionID    integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
PSTeamID    int not null,
DeveloperID    int not null,
foreign key (PSTeamID) references ProductionStudio(PSTeamID),
foreign key (DeveloperID) references Developer(DeveloperID)
);
create table advideo(
AdVideoID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
Director varchar(50),
AdPlatform varchar(50),
VideoLength integer,
Channel varchar(50),
GameID integer not null,
FOREIGN KEY (GameID) REFERENCES Game(GameID)
);
create table campaign(
CampaignID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
TimePeriodInMonths integer,
CampaignName varchar(50),
CampaignDetail varchar(50),
CampaignType varchar(50)
);
create table CampaignActivity(
CampActivityID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
StartDate date,
Validity integer,
CampaignID integer not null,
FOREIGN KEY (CampaignID) REFERENCES Campaign(CampaignID)
);
create table CampaignPackDetails(
PackID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
CampaignID integer not null,
GameID integer not null,
FOREIGN KEY (CampaignID) REFERENCES Campaign(CampaignID),
FOREIGN KEY (GameID) REFERENCES Game(GameID)
);
create table Stock(
StockID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
StockDescription varchar(50),
Quantity integer,
GameID integer not null,
StockAlertStatus char(1),
FOREIGN KEY (GameID) REFERENCES Game(GameID)
);
create table Store(
StoreID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
StoreLocation varchar(50),
StoreContact varchar(20),
StoreEmail varchar(50),
StockID integer not null,
FOREIGN KEY (StockID) REFERENCES Stock(StockID)
);
create table employee(
EmployeeID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
EmployeeName varchar(50),
Phone varchar(20),
JobTitle varchar(50),
Salary float,
CACertificateStatus char(1),
ParkingSpace char(1)
);
create table StoreEmployee(
StoreEmployeeID integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
StoreID integer not null,
EmployeeID integer not null,
FOREIGN KEY (StoreID) REFERENCES Store(StoreID),
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);
/*---------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------*/
/*Data Manipulation Language Script:
The tables are populated with the following data
*/
INSERT INTO developer(FirstName,LastName,Contact)
VALUES ('David','Dawney',7064453456);
INSERT INTO developer(FirstName,LastName,Contact)
VALUES ('Robert','Smith',7064454567);
INSERT INTO developer(FirstName,LastName,Contact)
VALUES ('Kelly','Torres',7064455678);
INSERT INTO developer(FirstName,LastName,Contact)
VALUES ('Elizabeth','Hurley',7064456789);
INSERT INTO developer(FirstName,LastName,Contact)
VALUES ('chin','Yang',7064457890);
INSERT INTO series(SeriesName,SeriesPlatform,SeriesReleaseDate,SeriesGenre,SeriesPrice,SeriesPublisher)
Values('WiFi Family','Cinematic','2019/7/26','Puzzle',10,'Midway Games');
INSERT INTO series(SeriesName,SeriesPlatform,SeriesReleaseDate,SeriesGenre,SeriesPrice,SeriesPublisher)
Values('Sports Pack','Adventure','2019/7/20','Run and Gun',15,'Electronic Arts');
INSERT INTO series(SeriesName,SeriesPlatform,SeriesReleaseDate,SeriesGenre,SeriesPrice,SeriesPublisher)
Values('English learning pack','educational','2019/7/21','Puzzle',10,'Warner Bros');
INSERT INTO...