Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need some help PLESASE in SQL Question 2. Consider the following relations: Faculty ID Campuses name campus state Offices building room# 1 DANA 110
I need some help PLESASE in SQL
Question 2. Consider the following relations: Faculty ID Campuses name campus state Offices building room# 1 DANA 110 2 EUC 110 3 EUC 202 4 VECS 110 5 EME 202 campus PULLM BRE EVERE VANCO PULLM 1 111-11-1111 2 222-11-1111 31 333-11-1111 4 444-11-1111 5 555-11-1111 Pat Paul John Carl Mel lastname Pedrow Pitre Schneider Hauser Netzhammer 1 2 3 4 PULLM EVERE BRE TRICI VANCO city Pullman Everett Bremerton Richland Vancouver WA WA WA WA WA room# ID CampusChancellors campus ID EVERE 222-11-1111 VANCO 555-11-1111 1 2 3 1 2 yearAppointed 2016 2012 Faculty Offices building DANA DANA EUC EUC VECS EME EME 110 110 110 202 110 202 202 4 dateAssigned 1/5/2012 10/1/2016 1/1/2016 5/10/2020 1/10/2014 8/16/2018 20/1/2010 111-11-1111 222-11-1111 222-11-1111 222-11-1111 444-11-1111 444-11-1111 555-11-1111 5 6 Primary keys (refer to these as PK1 through PKS): 1. Relation Offices: building, room# 2. Relation Faculty: ID 3. Relation Faculty Offices: building, room#, ID 4. Relation CampusChancellors: campus, ID 5. Relation Campuses: campus The following foreign key constraints are given for relations Offices, Faculty, Faculty Offices, CampusChancellors, and Campuses: (refer to these as FK1 through FK2) 1. FacultyOffices (building, room#) references Offices (building, room#) 2. Faculty Offices (ID) references Faculty (ID) 3. Offices (campus) references Campuses (campus) 4. CampusChancellors (campus) references Campuses (campus) 5. CampusChancellors (ID) references Faculty (ID) For all foreign keys: assume "CASCADE" policy for delete operations, and assume "SET NULL" policy for update operations. a) (10pts) For the operations given below, indicate whether execution of the operation would violate some "primary key" or "foreign key constraints". If your answer is yes, specify the constraints (from the above list) that would be violated (e.g. violates FK1) i) Insert tuple ("VECS', '202', 'VANCO") into Offices. ii) Insert tuple l' TRICI', '666-11-1111', 2021) into Campus Chancellors. iii) Insert tuple ('DANA', '110', '222-11-1111', '1/30/2021') into Faculty Offices. iv) Insert tuple ('VECS', '202', '444-11-1111', '12/15/2018') into FacultyOffices. b) (20pts) For the operations given below, indicate whether execution of the operation would violate any primary key" or "foreign key constraints". If your answer is yes: a. specify the constraints (from the above list) that would be violated, and b. if there is a violation, apply "CASCADE" policy for delete operations, and apply "SET NULL" policy for update operations. Update the tables after applying those policies. (For deletions, give the relation name and row-number(s) of the tuple(s) that will be deleted. For updates, if the update is possible rewrite the changed tables. Otherwise explain why update can't be performed.) Assume that each operation is applied on the original tables (i.e. the operations are not applied in a cumulative way.) i) Delete tuple ('EVERE', 'Everett', 'WA') from Campuses. ii) Delete tuple ('DANA', '110', 'PULLM") from Offices. iii) Delete tuple ('333-11-1111', 'John', 'Schneider') from Faculty. iv) Update tuple ('PULLM', 'Pullman', 'WA') in Campuses with values ('PULL', 'Pullman', 'WA") v) Update tuple ('EME', '202', 'PULLM') in Offices with values ('DANA', 202', 'PULLM")Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started