a) Insert <'ProductA',4,'Bellaire',2> into PROJECT b) Insert <'Production',4,'943775543','1998-10-01'> into DEPARTMENT c) Insert <'677678989',NULL,'40.0'> into WORKS ON
a) Insert <'ProductA',4,'Bellaire',2> into PROJECT b) Insert <'Production',4,'943775543','1998-10-01'> into DEPARTMENT c) Insert <'677678989',NULL,'40.0'> into WORKS ON
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
Transcribed Image Text:Assume that the following data definition statements for defining the COMPANY schema
(CompanySchema.pdf) are given.
CREATE TABLE EMPLOYEE (Fname VARCHAR(15), NOT NULL, Minit CHAR, Lname VARCHAR(15) NOT
NULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DOUBLE, Super_ssn
CHAR (9), Dno INT NOT NULL DEFAULT 1,
PRIMARY KEY (Ssn),
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET NULL
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
ON DELETE SET DEFAULT
ON UPDATE CASCADE,
ON UPDATE CASCADE);
CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9)
NOT NULL DEFAULT '888665555', Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE DEPT_LOCATIONS (Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)
ON DELETE CASCADE
ON UPDATE CASCADE);
CREATE TABLE PROJECT (Pname VARCHAR(15) NOT NULL, Pnumber INT NOT NULL, Plocation
VARCHAR(15), Dnum INT NOT NULL DEFAULT 1,
PRIMARY KEY (Pnumber),
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
CREATE TABLE WORKS_ON (Essn CHAR(9) NOT NULL, Pno INT NOT NULL DEFAULT 20, Hours DOUBLE
NOT NULL,
PRIMARY KEY (Essn, Pno),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
ON DELETE CASCADE
FOREIGN KEY (Pno) REFERENCES EMPLOYEE(Pnumber)
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
ON UPDATE CASCADE);
CREATE TABLE DEPENDENT (Essn CHAR(9) NOT NULL,
Dependent_name VARCHAR(15) NOT NULL, Sex CHAR, Bdate DATE, Relationship VARCHAR(8),
PRIMARY KEY (Essn, Dependent_Name),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(ssn)
ON DELETE CASCADE
ON UPDATE
Suppose that each of the following update operations is applied independently to the database state
given in CompanySchema.pdf. For each operation, write the name of all constraints if any. Also, write
the reason for the violation. Write the new state of each table after each operation if the operation is
not restricted. Show only the updated tables.
a) Insert <'ProductA',4,'Bellaire',2> into PROJECT
b) Insert <'Production',4,'943775543','1998-10-01'> into DEPARTMENT
c) Insert <'677678989',NULL,'40.0'> into WORKS_ON
d) Delete the PROJECT tuple with Pname='ProductX'
e) Delete the DEPARTMENT tuple with Dnumber=D4
f) Modify the Dnumber attribute of the DEPARTMENT tuple with Dnumber=4 to 2.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 3 images
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY