1. Assume that the following data definition statements for defining the COMPANY schema given in page 4 are given. CREATE TABLE EMPLOYEE (Fname VARCHAR(15), NOT CREATE TABLE PROJECT (Pname VARCHAR(15) NOT NULL, NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn Pnumber INT NOT NULL, Plocation VARCHAR(15), Dnum CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), INT NOT NULL DEFAULT 1, Sex CHAR, Salary DOUBLE, Super_ssn CHAR (9), Dno INT PRIMARY KEY (Pnumber), NOT NULL DEFAULT 1, FOREIGN KEY (Dnum) REFERENCES DEPARTMENT PRIMARY KEY (Ssn), (Dnumber) FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE); ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) CREATE TABLE WORKS_ON (Essn CHAR(9) NOT NULL, Pno ON DELETE SET DEFAULT ON UPDATE CASCADE); INT NOT NULL DEFAULT 10, Hours DOUBLE NOT NULL, PRIMARY KEY (Essn, Pno), CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT ON DELETE CASCADE ON UPDATE CASCADE); NULL DEFAULT '888665555', Mgr_start_date DATE, FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber) PRIMARY KEY (Dnumber), ON DELETE SET DEFAULT ON UPDATE CASCADE); FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) ON DELETE SET DEFAULT 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), CREATE TABLE DEPT_LOCATIONS (Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL, PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) FOREIGN KEY (Dnumber) REFERENCES ON DELETE CASCADE ON UPDATE CASCADE); DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE); Suppose that each of the following update operations is applied directly to the database state given in page 4. Write all constraints violated by each operation if any. Write new state of each table after each operation if the operation is not restricted. Show only the updated tables. 1. Insert <'ProjectX',NULL,'Bellaire',10> into PROJECT 2. Insert <'Production',1,'666884444','1998-10-01'> into DEPARTMENT 3. Insert <'677988889',15,'40.0'> into WORKS_ON 4. Delete the PROJECT tuple with Pname='ProductX' 5. Delete the DEPARTMENT tuple with Dnumber=4 6. Modify the Pnumber attribute of the PROJECT tuple with Pnumber=2 to 8.
1. Assume that the following data definition statements for defining the COMPANY schema given in page 4 are given. CREATE TABLE EMPLOYEE (Fname VARCHAR(15), NOT CREATE TABLE PROJECT (Pname VARCHAR(15) NOT NULL, NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn Pnumber INT NOT NULL, Plocation VARCHAR(15), Dnum CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), INT NOT NULL DEFAULT 1, Sex CHAR, Salary DOUBLE, Super_ssn CHAR (9), Dno INT PRIMARY KEY (Pnumber), NOT NULL DEFAULT 1, FOREIGN KEY (Dnum) REFERENCES DEPARTMENT PRIMARY KEY (Ssn), (Dnumber) FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE); ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) CREATE TABLE WORKS_ON (Essn CHAR(9) NOT NULL, Pno ON DELETE SET DEFAULT ON UPDATE CASCADE); INT NOT NULL DEFAULT 10, Hours DOUBLE NOT NULL, PRIMARY KEY (Essn, Pno), CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT ON DELETE CASCADE ON UPDATE CASCADE); NULL DEFAULT '888665555', Mgr_start_date DATE, FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber) PRIMARY KEY (Dnumber), ON DELETE SET DEFAULT ON UPDATE CASCADE); FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn) ON DELETE SET DEFAULT 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), CREATE TABLE DEPT_LOCATIONS (Dnumber INT NOT NULL, Dlocation VARCHAR(15) NOT NULL, PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) FOREIGN KEY (Dnumber) REFERENCES ON DELETE CASCADE ON UPDATE CASCADE); DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE); Suppose that each of the following update operations is applied directly to the database state given in page 4. Write all constraints violated by each operation if any. Write new state of each table after each operation if the operation is not restricted. Show only the updated tables. 1. Insert <'ProjectX',NULL,'Bellaire',10> into PROJECT 2. Insert <'Production',1,'666884444','1998-10-01'> into DEPARTMENT 3. Insert <'677988889',15,'40.0'> into WORKS_ON 4. Delete the PROJECT tuple with Pname='ProductX' 5. Delete the DEPARTMENT tuple with Dnumber=4 6. Modify the Pnumber attribute of the PROJECT tuple with Pnumber=2 to 8.
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
![Q: [ slide-15 .png ] Q1. Please write SQL codes to create below member database table based on…A: A relational database contains tables. Tables are also known as relations and they contain rows and…](/questions-and-answers/slide-15-.png-q1.-please-write-sql-codes-to-create-below-member-database-table-based-on-the-previous/3b4446fb-5348-4dbb-944d-a99326b9259f){kind=link}
Question
Transcribed Image Text:1.
Assume that the following data definition statements for defining the COMPANY schema given in page 4 are
given.
CREATE TABLE EMPLOYEE (Fname VARCHAR(15), NOT
CREATE TABLE PROJECT (Pname VARCHAR(15) NOT NULL,
NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn
Pnumber INT NOT NULL, Plocation VARCHAR(15), Dnum
CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30),
INT NOT NULL DEFAULT 1,
Sex CHAR, Salary DOUBLE, Super_ssn CHAR (9), Dno INT
PRIMARY KEY (Pnumber),
NOT NULL DEFAULT 1,
FOREIGN KEY (Dnum) REFERENCES DEPARTMENT
PRIMARY KEY (Ssn),
(Dnumber)
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn)
ON DELETE SET DEFAULT ON UPDATE CASCADE);
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
CREATE TABLE WORKS_ON (Essn CHAR(9) NOT NULL, Pno
ON DELETE SET DEFAULT
ON UPDATE CASCADE);
INT NOT NULL DEFAULT 10, Hours DOUBLE NOT NULL,
PRIMARY KEY (Essn, Pno),
CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT
ON DELETE CASCADE ON UPDATE CASCADE);
NULL DEFAULT '888665555', Mgr_start_date DATE,
FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber)
PRIMARY KEY (Dnumber),
ON DELETE SET DEFAULT ON UPDATE CASCADE);
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn)
ON DELETE SET DEFAULT 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),
CREATE TABLE DEPT_LOCATIONS (Dnumber INT NOT NULL,
Dlocation VARCHAR(15) NOT NULL,
PRIMARY KEY (Dnumber, Dlocation),
FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)
FOREIGN KEY (Dnumber) REFERENCES
ON DELETE CASCADE ON UPDATE CASCADE);
DEPARTMENT(Dnumber)
ON DELETE CASCADE ON UPDATE CASCADE);
Suppose that each of the following update operations is applied directly to the database state given in page 4. Write
all constraints violated by each operation if any. Write new state of each table after each operation if the operation is
not restricted. Show only the updated tables.
1. Insert <'ProjectX',NULL,'Bellaire',10> into PROJECT
2. Insert <'Production',1,'666884444','1998-10-01'> into DEPARTMENT
3. Insert <'677988889',15,'40.0'> into WORKS_ON
4. Delete the PROJECT tuple with Pname='ProductX'
5. Delete the DEPARTMENT tuple with Dnumber=4
6. Modify the Pnumber attribute of the PROJECT tuple with Pnumber=2 to 8.
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 2 steps
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