In SQLSever perform the following queries: 8. a. Insert a new row in the Locatio
ID: 3757289 • Letter: I
Question
In SQLSever perform the following queries:
8. a. Insert a new row in the Location table related to the Facility row. The new row should have “Locker Room” for the location name.
b. Change the location name for all locations of the swimming pool facility. The new location name should be “Door”. You must not use the facility number of the swimming pool in your UPDATE statement. You also cannot reference specific location numbers in your SQL statement. c. Insert a new row in the Location table related to the Facility row in modification problem 1. The new row should have “Gate” for the location name.
c. Delete the swimming pool facility and all related locations. When deleting the locations, you must not use the facility number of the swimming pool in your DELETE statement. Pretend that the user knows only the facility name, not the facility number. In addition, your solution should not rely on changing the design of the ICA database. In the design, the relationship from Facility to Location has the RESTRICT option for referenced rows. You cannot change the option to CASCADE.
/*-------------------- IADEMPLOYEE --------------------------------*/
CREATE TABLE IADEMPLOYEE
(EmpNo VARCHAR(8) NOT NULL,
EmpName VARCHAR(35) NOT NULL,
Department VARCHAR(25) NOT NULL,
Email VARCHAR(30) NOT NULL,
Phone VARCHAR(10) NOT NULL,
CONSTRAINT IADEMPLOYEE_PK PRIMARY KEY (EmpNo) ) ;
-------------------- IADCUSTOMER --------------------------------
CREATE TABLE IADCUSTOMER
(CustNo VARCHAR(8) NOT NULL,
CustName VARCHAR(30) NOT NULL,
Address VARCHAR(50) NOT NULL,
Internal CHAR(1) DEFAULT 'Y' NOT NULL,
Contact VARCHAR(35) NOT NULL,
Phone VARCHAR(11) NOT NULL,
City VARCHAR(30) NOT NULL,
State VARCHAR(2) NOT NULL,
Zip VARCHAR(10) DEFAULT '80217' NOT NULL,
CONSTRAINT IADCUSTOMER_PK PRIMARY KEY (CustNo) ) ;
-------------------- IADRESOURCE --------------------------------
CREATE TABLE IADRESOURCE
(ResNo VARCHAR(8) NOT NULL,
ResName VARCHAR(30)NOT NULL,
Rate NUMERIC(15,4) NOT NULL,
CONSTRAINT RatePositive CHECK (Rate > 0),
CONSTRAINT IADRESOURCE_PK PRIMARY KEY (ResNo) );
-------------------- IADFACILITY --------------------------------
CREATE TABLE IADFACILITY
(FacNo VARCHAR(8)NOT NULL,
FacName VARCHAR(30) NOT NULL,
CONSTRAINT IADFACILITY_PK PRIMARY KEY (FacNo) );
-------------------- IADLOCATION --------------------------------
CREATE TABLE IADLOCATION
(LocNo VARCHAR(8) NOT NULL,
FacNo VARCHAR(8) NOT NULL,
LocName VARCHAR(30) NOT NULL,
CONSTRAINT IADLOCATION_PK PRIMARY KEY (LocNo),
CONSTRAINT IADFACNO_PK FOREIGN KEY (FacNo) REFERENCES IADFACILITY (FacNo) );
-------------------- IADEVENTREQUEST --------------------------------
CREATE TABLE IADEVENTREQUEST
(EventNo VARCHAR(8) NOT NULL,
DateHeld DATE NOT NULL,
DateReq DATE DEFAULT GETDATE() NOT NULL,
CustNo VARCHAR(8) NOT NULL,
FacNo VARCHAR(8) NOT NULL,
DateAuth DATE,
Status VARCHAR(20) DEFAULT 'Pending' NOT NULL,
EstCost NUMERIC(15,4)NOT NULL,
EstAudience NUMERIC(11,0) NOT NULL,
BudNo VARCHAR(8),
CONSTRAINT ValidStatus CHECK (Status IN ('Pending', 'Denied', 'Approved')),
CONSTRAINT EstAudiencePositive CHECK (EstAudience > 0),
CONSTRAINT DateReqAuthRelationship CHECK (DateReq < DateAuth OR DateAuth IS NULL),
CONSTRAINT IADEVENTREQUEST_PK PRIMARY KEY (EventNo),
CONSTRAINT EVENT_FACNO_FK FOREIGN KEY (FacNo) REFERENCES IADFACILITY (FacNo),
CONSTRAINT IADCUSTNO_FK FOREIGN KEY (CustNo) REFERENCES IADCUSTOMER (CustNo) );
-------------------- IADEVENTPLAN --------------------------------
CREATE TABLE IADEVENTPLAN
(PlanNo VARCHAR(8) NOT NULL,
EventNo VARCHAR(8) NOT NULL,
WorkDate DATE NOT NULL,
Notes VARCHAR(50),
Activity VARCHAR(50) NOT NULL,
EmpNo VARCHAR(8),
CONSTRAINT IADEVENTPLAN_PK PRIMARY KEY (PlanNo),
CONSTRAINT IADEMPNO_FK FOREIGN KEY (EmpNo) REFERENCES IADEMPLOYEE (EmpNo),
CONSTRAINT IADEVENTNO_FK FOREIGN KEY (EventNo) REFERENCES IADEVENTREQUEST (EventNo) );
-------------------- IADEVENTPLANLINE --------------------------------
CREATE TABLE IADEVENTPLANLINE
(PlanNo VARCHAR(8) NOT NULL,
LineNumber INTEGER NOT NULL,
TimeStart DATETIME NOT NULL,
TimeEnd DATETIME NOT NULL,
NumberFld INTEGER NOT NULL,
LocNo VARCHAR(8) NOT NULL,
ResNo VARCHAR(8) NOT NULL,
CONSTRAINT TimeStartEndRelationship CHECK (TimeStart < TimeEnd),
CONSTRAINT IADEVENTPLANLINE_PK PRIMARY KEY (PlanNo, LineNumber),
CONSTRAINT LOCNO_FK FOREIGN KEY (LocNo) REFERENCES IADLOCATION (LocNo),
CONSTRAINT RESNO_FK FOREIGN KEY (ResNo) REFERENCES IADRESOURCE (ResNo),
CONSTRAINT PLANNO_FK FOREIGN KEY (PlanNo) REFERENCES IADEVENTPLAN (PlanNo) ON DELETE CASCADE );
Insert into IADEMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E100','Chuck Coordinator','Administration','chuck@colorado.edu','3-1111');
Insert into IADEMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E101','Mary Manager','Football','mary@colorado.edu','5-1111');
Insert into IADEMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E102','Sally Supervisor','Planning','sally@colorado.edu','3-2222');
Insert into IADEMPLOYEE (EMPNO,EMPNAME,DEPARTMENT,EMAIL,PHONE) values ('E103','Alan Administrator','Administration','alan@colorado.edu','3-3333');
Insert into IADCUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C100','Football','Box 352200','Y','Mary Manager','6857100','Boulder','CO','80309');
Insert into IADCUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C101','Men''s Basketball','Box 352400','Y','Sally Supervisor','5431700','Boulder','CO','80309');
Insert into IADCUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C103','Baseball','Box 352020','Y','Bill Baseball','5431234','Boulder','CO','80309');
Insert into IADCUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C104','Women''s Softball','Box 351200','Y','Sue Softball','5434321','Boulder','CO','80309');
Insert into IADCUSTOMER (CUSTNO,CUSTNAME,ADDRESS,INTERNAL,CONTACT,PHONE,CITY,STATE,ZIP) values ('C105','High School Football','123 AnyStreet','N','Coach Bob','4441234','Louisville','CO','80027');
Insert into IADRESOURCE (RESNO,RESNAME,RATE) values ('R100','attendant',10);
Insert into IADRESOURCE (RESNO,RESNAME,RATE) values ('R101','police',15);
Insert into IADRESOURCE (RESNO,RESNAME,RATE) values ('R102','usher',10);
Insert into IADRESOURCE (RESNO,RESNAME,RATE) values ('R103','nurse',20);
Insert into IADRESOURCE (RESNO,RESNAME,RATE) values ('R104','janitor',15);
Insert into IADRESOURCE (RESNO,RESNAME,RATE) values ('R105','food service',10);
Insert into IADFACILITY (FACNO,FACNAME) values ('F100','Football stadium');
Insert into IADFACILITY (FACNO,FACNAME) values ('F101','Basketball arena');
Insert into IADFACILITY (FACNO,FACNAME) values ('F102','Baseball field');
Insert into IADFACILITY (FACNO,FACNAME) values ('F103','Recreation room');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L100','F100','Locker room');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L101','F100','Plaza');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L102','F100','Vehicle gate');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L103','F101','Locker room');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L104','F100','Ticket Booth');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L105','F101','Gate');
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L106','F100','Pedestrian gate');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E100','25-OCT-18','06-JUN-18','C100','F100','08-JUN-18','Approved',5000,80000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E101','26-OCT-18','28-JUL-18','C100','F100',null,'Pending',5000,80000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E103','21-SEP-18','28-JUL-18','C100','F100','01-AUG-18','Approved',5000,80000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E102','14-SEP-18','28-JUL-18','C100','F100','31-JUL-18','Approved',5000,80000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E104','03-DEC-18','28-JUL-18','C101','F101','31-JUL-18','Approved',2000,12000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E105','05-DEC-18','28-JUL-18','C101','F101','01-AUG-18','Approved',2000,10000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E106','12-DEC-18','28-JUL-18','C101','F101','31-JUL-18','Approved',2000,10000,'B1000');
Insert into IADEVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,CUSTNO,FACNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E107','23-NOV-18','28-JUL-18','C105','F100','31-JUL-18','Denied',10000,5000,null);
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P100','E100','25-OCT-18','Standard operation','Operation','E102');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P101','E104','03-DEC-18','Watch for gate crashers','Operation','E100');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P102','E105','05-DEC-18','Standard operation','Operation','E102');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P103','E106','12-DEC-18','Watch for seat switching','Operation',null);
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P104','E101','26-OCT-18','Standard cleanup','Cleanup','E101');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P105','E100','25-OCT-18','Light cleanup','Cleanup','E101');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P199','E102','10-DEC-18','Standard operation','Operation','E101');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P299','E101','26-OCT-18',null,'Operation','E101');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P349','E106','12-DEC-18',null,'Setup','E101');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P85','E100','25-OCT-18','Standard operation','Cleanup','E102');
Insert into IADEVENTPLAN (PLANNO,EVENTNO,WORKDATE,NOTES,ACTIVITY,EMPNO) values ('P95','E101','26-OCT-18','Extra security','Cleanup','E102');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',1, '25-OCT-18 8:00:00', '25-OCT-18 17:00:00',2,'L100','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',2, '25-OCT-18 12:00:00','25-OCT-18 17:00:00', 2,'L101','R101');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',3, '25-OCT-18 7:00:00', '25-OCT-18 16:30:00', 1,'L102','R102');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P100',4, '25-OCT-18 18:00:00','25-OCT-18 22:00:00',2,'L100','R102');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P101',1, '3-DEC-18 18:00:00','3-DEC-18 20:00:00',2,'L103','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P101',2, '3-DEC-18 18:30:00','3-DEC-18 19:00:00',4,'L105','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P101',3, '3-DEC-18 19:00:00','3-DEC-18 20:00:00',2,'L103','R103');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P102',1, '5-DEC-18 18:00:00','5-DEC-18 19:00:00',2,'L103','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P102',2, '5-DEC-18 18:00:00','5-DEC-18 21:00:00',4,'L105','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P102',3, '5-DEC-18 19:00:00','5-DEC-18 22:00:00',2,'L103','R103');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P103',1, '12-DEC-18 18:00:00','12-DEC-18 21:00:00',2,'L103','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P103',2, '12-DEC-18 18:00:00','12-DEC-18 21:00:00',4,'L105','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P103',3, '12-DEC-18 19:00:00','12-DEC-18 22:00:00',2,'L103','R103');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P104',1, '26-OCT-18 18:00:00','26-OCT-18 22:00:00',4,'L101','R104');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P104',2, '26-OCT-18 18:00:00','26-OCT-18 22:00:00',4,'L100','R104');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P105',1, '25-OCT-18 18:00:00','25-OCT-18 22:00:00',4,'L101','R104');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P105',2, '25-OCT-18 18:00:00','25-OCT-18 22:00:00',4,'L100','R104');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P199',1, '10-DEC-18 8:00:00', '10-DEC-18 12:00:00',1,'L100','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P349',1, '12-DEC-18 12:00:00','12-DEC-18 15:30:00',1,'L103','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P85',1, '25-OCT-18 9:00:00', '25-OCT-18 17:00:00',5,'L100','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P85',2, '25-OCT-18 8:00:00', '25-OCT-18 17:00:00',2,'L102','R101');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P85',3, '25-OCT-18 10:00:00', '25-OCT-18 15:00:00',3,'L104','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',1, '26-OCT-18 8:00:00', '26-OCT-18 17:00:00',4,'L100','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',2, '26-OCT-18 9:00:00', '26-OCT-18 17:00:00',4,'L102','R101');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',3, '26-OCT-18 10:00:00', '26-OCT-18 15:00:00',4,'L106','R100');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',4, '26-OCT-18 13:00:00', '26-OCT-18 17:00:00',2,'L100','R103');
Insert into IADEVENTPLANLINE (PLANNO,LINENUMBER,TIMESTART,TIMEEND,NUMBERFLD,LOCNO,RESNO) values ('P95',5, '26-OCT-18 13:00:00', '26-OCT-18 17:00:00',2,'L101','R104');
Explanation / Answer
Answer:
Please find the SQL Queries and the screenshot below. Please also note that, as there was no entry in the Facility table for 'Swimming Pool', I have inserted a row in the facility table for the same. There are questions related to these rows. Please let me know if you have further questions.
Following are the insert queries that I used for 'Swimming Pool' facility.
--Facility
Insert into IADFACILITY (FACNO,FACNAME) values ('F107','Swimming Pool');
--Location
Insert into IADLOCATION values ('L108','F107','Locker room')
Insert into IADLOCATION values ('L109','F107','Plaza')
Insert into IADLOCATION values ('L110','F107','Vehicle gate')
Screenshot:
SQL Queries:
--a. Insert a new row in the Location table related to the Facility row. The new row should have “Locker Room” for the location name.
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L107','F103','Locker room'); -- New Row related to Fac No - F103
/*b. Change the location name for all locations of the swimming pool facility. The new location name should be “Door”.
You must not use the facility number of the swimming pool in your UPDATE statement.
You also cannot reference specific location numbers in your SQL statement.*/
Update IADLOCATION Set LocName = 'Door' from
IADLOCATION JOIN IADFACILITY ON IADFACILITY.FacNo = IADLOCATION.FacNo where IADFACILITY.FacName like 'Swimming Pool';
/*c. Insert a new row in the Location table related to the Facility row in modification problem 1. The new row should have “Gate” for the location name.*/
Insert into IADLOCATION (LOCNO,FACNO,LOCNAME) values ('L111','F107','Gate');
/*c. Delete the swimming pool facility and all related locations. When deleting the locations, you must not use the facility number of the swimming pool
in your DELETE statement. Pretend that the user knows only the facility name, not the facility number. In addition, your solution should not rely on changing
the design of the ICA database. In the design, the relationship from Facility to Location has the RESTRICT option for referenced rows.
You cannot change the option to CASCADE.*/
Delete IADLOCATION from IADLOCATION JOIN IADFACILITY ON IADFACILITY.FacNo = IADLOCATION.FacNo where IADFACILITY.FacName like 'Swimming Pool';
Go
Delete IADFACILITY where FacName like 'Swimming Pool';
Go
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.