d VIEWS in two different SELECT Statements.  One created VIEW per SELECT Statement.   Use the SHOW FULL TABLES Statement to list the stored VIEWS in the Database 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, re

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
icon
Related questions
Question

how can I write this SQL query using that code?

  • CREATE Two SQL CREATE VIEW Statements.  One of the CREATE VIEW Statements must use two joined tables.
  • Use the two created VIEWS in two different SELECT Statements.  One created VIEW per SELECT Statement.  
  • Use the SHOW FULL TABLES Statement to list the stored VIEWS in the Database

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(empIDstoreID, 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

 

email

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 DATABASE IF EXISTS RegionStore;

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)

 

);

 

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
steps

Step by step

Solved in 3 steps

Blurred answer
Knowledge Booster
SQL Query
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
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education