how can I Create 5 Functional UPDATE Statements? 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 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

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 can I Create 5 Functional UPDATE Statements?

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

regionID
statecode
M
stateName
regionName
Region
in
State OfRegion
statePopulation
director
1
RegionID_Fk
contain
storelD FK
Store ID
M
empID
phone
1
M
store
employ
employee
empName
manager
hireDate
address
1
birthDate
RegionID_FK
manages
M
socsecNum
storelD FK
supplierlD FK
Supplies
startDate
supplierlD
supplierName
contact
Supplier
email
phone
0000

Expert 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