Consider the E-R diagram depicted in the figure. Write down the corresponding relational schema using SQL. Include all applicable constraints.
Solution:
CREATE TABLE Department (
DeptId CHAR(6),
DeptName CHAR(30) NOT NULL,
Address CHAR(50),
PRIMARY KEY DeptId )
CREATE TABLE Employee (
Name CHAR(20),
Phone CHAR(10),
PRIMARY KEY (Name, Phone) )
CREATE TABLE WorksFor (
EName CHAR(20),
DeptId CHAR(6),
FOREIGN KEY (EName) REFERENCES Employee(Name),
FOREIGN KEY (DeptId) REFERENCES Department(DeptId)
The participation constraint cannot be represented using foreign keys, since DeptId is not
a key in WorksFor. It can be represented as an assertion, however, which states that every
DeptId in Department occurs somewhere in WorksFor:
CREATE ASSERTION DeptMustHaveEmployees
CHECK (
NOT EXISTS ( SELECT D.DeptId
FROM Department D
WHERE
D.DeptId NOT IN ( SELECT W.DeptId
FROM WorksFor W) )
)
You might also like to view...
What is the purpose of the swap space in CentOS 7?
What will be an ideal response?
Which of the following is a task handled at the Network layer?
A. recognizes and uses multiple routes B. manages point-to-point transmission across the networking medium C. ensures reliable end-to-end transmission of PDUs D. maintains ongoing communications between a sender and a receiver
A predesigned document to which you can turn for help with wording or design is a ________
Fill in the blank(s) with correct word
A flaw in the protection system of many operating systems is argument passing. Often, a common shared stack is used by all nested routines for arguments as well as for the remainder of the context of each calling process. (a) Explain what vulnerabilities this flaw presents. (b) Explain how the flaw can be controlled. The shared stack is still to be used for passing arguments and storing context
What will be an ideal response?