Assume that the following data definition statements for defining the COMPANY schema(CompanySchema.pdf) are given.CREATE TABLE EMPLOYEE (Fname VARCHAR(15), NOT NULL, Minit CHAR, Lname VARCHAR(15) NOTNULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DOUBLE, Super_ssnCHAR (9), Dno INT NOT NULL DEFAULT 1,PRIMARY KEY (Ssn),FOREIGN KEY (Super ssn) REFERENCES EMPLOYEE(Ssn)ON DELETE SET NULLFOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)ON DELETE SET DEFAULTON UPDATE CASCADE,ON UPDATE CASCADE);CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9)NOT NULL DEFAULT '888665555', Mgr_start_date DATE,PRIMARY KEY (Dnumber),FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)ON DELETE SET DEFAULTON UPDATE CASCADE);CREATE TABLE DEPT LOCATIONS (Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL,PRIMARY KEY (Dnumber, Dlocation),FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)ON DELETE CASCADEON UPDATE CASCADE);CREATE TABLE PROJECT (Pname VARCHAR(15) NOT NULL, Pnumber INT NOT NULL, PlocationVARCHAR(15), Dnum INT NOT NULL DEFAULT 1,PRIMARY KEY (Pnumber),FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)ON DELETE SET DEFAULTON UPDATE CASCADE);CREATE TABLE WORKS ON (Essn CHAR(9) NOT NULL, Pno INT NOT NULL DEFAULT 20, Hours DOUBLENOT NULL,PRIMARY KEY (Essn, Pno),FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)ON DELETE CASCADEFOREIGN KEY (Pno) REFERENCES EMPLOYEE(Pnumber)ON DELETE SET DEFAULTON UPDATE CASCADE);ON UPDATE CASCADE);CREATE TABLE DEPENDENT (Essn CHAR(9) NOT NULL,Dependent_name VARCHAR(15) NOT NULL, Sex CHAR, Bdate DATE, Relationship VARCHAR(8),PRIMARY KEY (Essn, Dependent_Name),FOREIGN KEY (Essn) REFERENCES EMPLOYEE(ssn)ON DELETE CASCADEON UPDATESuppose that each of the following update operations is applied independently to the database stategiven in CompanySchema.pdf. For each operation, write the name of all constraints if any. Also, writethe reason for the violation. Write the new state of each table after each operation if the operation isnot restricted. Show only the updated tables.a) Insert into PROJECTb) Insert into DEPARTMENTc) Insert into WORKS_ONd) Delete the PROJECT tuple with Pname%='ProductX'e) Delete the DEPARTMENT tuple with Dnumber=D4f) Modify the Dnumber attribute of the DEPARTMENT tuple with Dnumber=4 to 2. EMPLOYEEFrame Minit Lname Sen Bdate Address Sex Salary Super sen Dno+444DEPARTMENTDname DnumberMor an Mgr start dateDEPT LOCATIONSDnumber DlocationPROJECTPrame Prumber Plocation DnumWORKS ONEnnn Pno HoirHoursDEPENDENTEsen Dependent name Se Bdate RelationshipReforential integrity constraints displayedon the COMFWNY relational databaseschema.One possible database state for the COMPANY relational database schema.EMPLOYEEFname Minit LnameJohnFrankin TSex Salary30000 333445555 5M 40000 Jo00665555F 25000 Joe7e5432143000 J88Ge5556SanBdateAddressSuper en Dno123456789 1965-01-09 731 Fondren, Hounton, TX M333445555 1955-12-08 638 Voas, Houston, TXGo0087777 1960-01-19 3321 Castle, Spring. TX987654321 1941-06-20 291 Berry, Bellaire, TXNarayan 666884444 1962-09-15 975 Fire Oak, Humibile, TX MBSmithWongZelayaWallace5Alicia4JenniferF38000 33344565525000 333445555M 25000 007654321M 55000 NULLRameshEnglish453453453 1972-07-31 5631 Rice, Houston, TXFJoyceAhmadA9879879071969-03-29 980 Dallan, Houston, TXB80G05555 1937-11-10 450 Stone, Houston, TXJabbar4JamesEBorgDEPARTMENTDEPT LOCATIONSDnumberMar atart dateDnumberDlocationDnameMar enResearch3334455551988-06-22HoustonAdministration9876543211995-01-01StaffordHeadquarters1981-06-19BelaireSugarlandHoustonWORKS ONPROJECTEssnPnoHoursPramePnumberPlocationDnum123456709123456700325ProductXBellaireProductYProductz75Sugartand666884444340.03Houston545345345320.0Computeriation10Stafford445345345020.0Reorganization20Houston33344556510.04Newbenefite30Stafford333445566310.03334455653334455551010.0DEPENDENT20100EsanDependent naSexBdateRelationshin3030.0333445555Alice1986-04-05Daughter9098877771010.0333445556TheodoreM1983-10-25Son9879870871035.03334455551958-05-03SpouseSpouseJoy007007007987654321305.0987654321Abner1942-02-283020.0123456700Michael1988-01-04Son9876543212015.0123450700Alice1988-12-30DaughterB8866555520NULL123456789Ekrabeth1967-05-05Spoune

Question

Assume that the following data definition statements for defining the COMPANY schema(CompanySchema.pdf) are given.CREATE TABLE EMPLOYEE (Fname VARCHAR(15), NOT NULL, Minit CHAR, Lname VARCHAR(15) NOTNULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DOUBLE, Super_ssnCHAR (9), Dno INT NOT NULL DEFAULT 1,PRIMARY KEY (Ssn),FOREIGN KEY (Super ssn) REFERENCES EMPLOYEE(Ssn)ON DELETE SET NULLFOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)ON DELETE SET DEFAULTON UPDATE CASCADE,ON UPDATE CASCADE);CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9)NOT NULL DEFAULT '888665555', Mgr_start_date DATE,PRIMARY KEY (Dnumber),FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)ON DELETE SET DEFAULTON UPDATE CASCADE);CREATE TABLE DEPT LOCATIONS (Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL,PRIMARY KEY (Dnumber, Dlocation),FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)ON DELETE CASCADEON UPDATE CASCADE);CREATE TABLE PROJECT (Pname VARCHAR(15) NOT NULL, Pnumber INT NOT NULL, PlocationVARCHAR(15), Dnum INT NOT NULL DEFAULT 1,PRIMARY KEY (Pnumber),FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)ON DELETE SET DEFAULTON UPDATE CASCADE);CREATE TABLE WORKS ON (Essn CHAR(9) NOT NULL, Pno INT NOT NULL DEFAULT 20, Hours DOUBLENOT NULL,PRIMARY KEY (Essn, Pno),FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)ON DELETE CASCADEFOREIGN KEY (Pno) REFERENCES EMPLOYEE(Pnumber)ON DELETE SET DEFAULTON UPDATE CASCADE);ON UPDATE CASCADE);CREATE TABLE DEPENDENT (Essn CHAR(9) NOT NULL,Dependent_name VARCHAR(15) NOT NULL, Sex CHAR, Bdate DATE, Relationship VARCHAR(8),PRIMARY KEY (Essn, Dependent_Name),FOREIGN KEY (Essn) REFERENCES EMPLOYEE(ssn)ON DELETE CASCADEON UPDATESuppose that each of the following update operations is applied independently to the database stategiven in CompanySchema.pdf. For each operation, write the name of all constraints if any. Also, writethe reason for the violation. Write the new state of each table after each operation if the operation isnot restricted. Show only the updated tables.a) Insert <'ProductA',4,'Bellaire',2> into PROJECTb) Insert <'Production',4,'943775543','1998-10-01'> into DEPARTMENTc) Insert <'677678989',NULL,'40.0'> into WORKS_ONd) Delete the PROJECT tuple with Pname%='ProductX'e) Delete the DEPARTMENT tuple with Dnumber=D4f) Modify the Dnumber attribute of the DEPARTMENT tuple with Dnumber=4 to 2. EMPLOYEEFrame Minit Lname Sen Bdate Address Sex Salary Super sen Dno+444DEPARTMENTDname DnumberMor an Mgr start dateDEPT LOCATIONSDnumber DlocationPROJECTPrame Prumber Plocation DnumWORKS ONEnnn Pno HoirHoursDEPENDENTEsen Dependent name Se Bdate RelationshipReforential integrity constraints displayedon the COMFWNY relational databaseschema.One possible database state for the COMPANY relational database schema.EMPLOYEEFname Minit LnameJohnFrankin TSex Salary30000 333445555 5M 40000 Jo00665555F 25000 Joe7e5432143000 J88Ge5556SanBdateAddressSuper en Dno123456789 1965-01-09 731 Fondren, Hounton, TX M333445555 1955-12-08 638 Voas, Houston, TXGo0087777 1960-01-19 3321 Castle, Spring. TX987654321 1941-06-20 291 Berry, Bellaire, TXNarayan 666884444 1962-09-15 975 Fire Oak, Humibile, TX MBSmithWongZelayaWallace5Alicia4JenniferF38000 33344565525000 333445555M 25000 007654321M 55000 NULLRameshEnglish453453453 1972-07-31 5631 Rice, Houston, TXFJoyceAhmadA9879879071969-03-29 980 Dallan, Houston, TXB80G05555 1937-11-10 450 Stone, Houston, TXJabbar4JamesEBorgDEPARTMENTDEPT LOCATIONSDnumberMar atart dateDnumberDlocationDnameMar enResearch3334455551988-06-22HoustonAdministration9876543211995-01-01StaffordHeadquarters1981-06-19BelaireSugarlandHoustonWORKS ONPROJECTEssnPnoHoursPramePnumberPlocationDnum123456709123456700325ProductXBellaireProductYProductz75Sugartand666884444340.03Houston545345345320.0Computeriation10Stafford445345345020.0Reorganization20Houston33344556510.04Newbenefite30Stafford333445566310.03334455653334455551010.0DEPENDENT20100EsanDependent naSexBdateRelationshin3030.0333445555Alice1986-04-05Daughter9098877771010.0333445556TheodoreM1983-10-25Son9879870871035.03334455551958-05-03SpouseSpouseJoy007007007987654321305.0987654321Abner1942-02-283020.0123456700Michael1988-01-04Son9876543212015.0123450700Alice1988-12-30DaughterB8866555520NULL123456789Ekrabeth1967-05-05Spoune

Accepted Answer

The problem is related to the company database where the tables and constraints on the tables are…