how to Create three or more SQL Data Control Language (DCL) Statements using the Homework Database: EMPLOYEE, STORE, REGION, and SUPPLIER Database.
-- creating the RegionStore DataBase
DROP DATABASE IF EXISTS RegionStore;
CREATE DATABASE RegionStore;
USE RegionStore;
-- creating the Region table
CREATE TABLE Region
(
regionID VARCHAR(10),
regionName VARCHAR(50),
supervisor VARCHAR(50),
CONSTRAINT PRIMARY KEY (regionID)
);
-- creating the store table
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)
);
-- creating the Employee table
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)
);
-- creating the Supplier table
CREATE TABLE Supplier
(
supplierID VARCHAR (10),
supplierName VARCHAR (50),
contact VARCHAR (50),
phone CHAR (10),
email VARCHAR (50),
CONSTRAINT PRIMARY KEY (supplierID)
);
-- creating the Supplies table
CREATE TABLE Supplies
(
supplierID_FK VARCHAR (10),
storeID_FK VARCHAR (10),
startDate DATE,
CONSTRAINT PRIMARY KEY (supplierID_FK,storeID_FK )
);
-- creating the StateOfRegion table
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)
);
--INSERTING information INTO the Region table
INSERT INTO region (regionID,regionName,supervisor)
VALUES ('001','Alabama','Henry');
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('003','wellington','katty');
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('004','hamilton','ross');
INSERT INTO Region (regionID,regionName,supervisor)
VALUES ('005','auckland','robert');