5 Functional DELETE Statements,Each of the DELETE Statements must be preceded by a SELECT Statement that lists the rows/tuples that are going to be deleted based on the criteria in the DELETE Statement. After each DELETE Statement executes, the result set of DELETE Statement should indicate the number of items deleted. The number of items deleted should match the number of items listed in the Resultset created by the SELECT Statement that proceeds each DELETE Statement.?
how can I create
5 Functional DELETE Statements,Each of the DELETE Statements must be preceded by a SELECT Statement that lists the rows/tuples that are going to be deleted based on the criteria in the DELETE Statement.
After each DELETE Statement executes, the result set of DELETE Statement should indicate the number of items deleted. The number of items deleted should match the number of items listed in the Resultset created by the SELECT Statement that proceeds each DELETE Statement.?
- Relational schema
The primary key is bold and the foreign key is italic
Region(regionID, regionName, director)
StateOfRegion(stateCode, stateName, statePopulation, RegionID)
Store(StoreID, phone, manager, address, regionID)
Employee(empID, storeID, empName, hireDate, birthDate, socSecNum)
Supplier(supplierID, SupplierName, contact, email, phone)
StoreSupplier(storeID, supplierID, startDate)
- Meta dataThe metdata for each table from the given ER diagram is as below
|
Table Name |
Attribute |
Data Type |
Constraint |
|
Region |
|
|
|
|
|
regionID |
INT |
PK |
|
|
regionName |
VARCHAR |
NOT NULL |
|
|
director |
VARCHAR |
|
|
|
|
|
|
|
StateOfRegion |
|
|
|
|
|
stateCode |
CHAR(2) |
PK |
|
|
stateName |
VARCHAR |
Not NULL |
|
|
statePopulation |
double |
|
|
|
regionID |
int |
FK Region(RegionID) |
|
|
|
|
|
|
Store |
|
|
|
|
|
storeID |
int |
PK |
|
|
phone |
VARCHAR |
Not Null |
|
|
manager |
int |
Not Null |
|
|
address |
VARCHAR |
Not Null |
|
|
regionID |
int |
FK Region(RegionID) |
|
|
|
|
|
|
Employee |
|
|
|
|
|
empID |
Int |
PK |
|
|
storeID |
int |
FK Store(StoreID) |
|
|
empName |
VARCHAR |
Not Null |
|
|
hireDate |
Date |
Not Null |
|
|
birthDate |
Date |
|
|
|
socSecNum |
Int |
|
|
|
|
|
|
|
Supplier |
|
|
|
|
|
supplierID |
INT |
PK |
|
|
supplierName |
VARCHAR |
Not null |
|
|
contact |
VARCHAR |
Not null |
|
|
|
VARCHAR |
Not null |
|
|
phone |
VARCHAR |
Not null |
|
|
|
|
|
|
StoreSupplier |
|
|
|
|
|
storeID |
Int |
PK, FK store(StoreID) |
|
|
supplierID |
int |
PK, FK Supplier(SupplierID) |
|
|
startDate |
Date |
Not null |
- The create table script:
DROP
CREATE DATABASE RegionStore;
USE RegionStore;
CREATE TABLE Region
(
regionID VARCHAR(10),
regionName VARCHAR(50),
supervisor VARCHAR(50),
CONSTRAINT PRIMARY KEY (regionID)
);
CREATE TABLE Store
(
storeID VARCHAR (10),
storeAddress VARCHAR (50),
phone VARCHAR (10),
Manager VARCHAR (10),
regionID_FK VARCHAR (10),
CONSTRAINT PRIMARY KEY (storeID),
CONSTRAINT FOREIGN KEY (regionID_FK)
REFERENCES REGION (regionID)
);
CREATE TABLE Employee
(
empNum VARCHAR (10),
empName VARCHAR (50),
hireDate DATE,
birthDate Date,
socSecNum CHAR(9),
storeID_FK VARCHAR(10),
CONSTRAINT PRIMARY KEY (empNum),
CONSTRAINT FOREIGN KEY (storeID_FK)
REFERENCES Store(storeID)
);
CREATE TABLE Supplier
(
supplierID VARCHAR (10),
supplierName VARCHAR (50),
contact VARCHAR (50),
phone CHAR (10),
email VARCHAR (50),
CONSTRAINT PRIMARY KEY (supplierID)
);
CREATE TABLE Supplies
(
supplierID_FK VARCHAR (10),
storeID_FK VARCHAR (10),
startDate DATE,
CONSTRAINT PRIMARY KEY (supplierID_FK,storeID_FK )
);
CREATE TABLE StateOfRegion
(
stateCode CHAR(2),
stateName VARCHAR(50),
regionID_FK VARCHAR(10),
statePopulation INT,
CONSTRAINT PRIMARY KEY (stateCode),
CONSTRAINT FOREIGN KEY (regionID_FK )
REFERENCES REGION(regionID)
);
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 4 images
