Answered: how to Create three or more SQL Data…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

how to Create three or more SQL Data Control Language (DCL) Statements using the Homework Database: EMPLOYEE, STORE, REGION, and SUPPLIER Database.

Create a new user statement for the database. The user name should be based on yourself containing your name.
Statements that grant privileges to the new user. Statements granting privileges to at least 2 of the tables in the assigned database
Statements that revoke privileges to the new user. Statements revoking privileges to at least 2 of the tables in the assigned Database

the home work data base:

-- 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');

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions