Assignment 1 Solution

Instruction:




check “Database_Table_Description.doc” file.This file contain general information of databse and their model used in this assignment. Try to identify the foreign key and primary key in ERD diagram.



First you need to create all 7 table with constraint.



after creating table run the insert query given in “insert.txt” table.



How to submit:



You should submit your assignment in the form of two file one is “query.txt” and another is “screen_shot.pdf”.



“query.txt” file contain the create,select,update and Data modification query which you run on the database.



“screen_shot.pdf” file contain screen shot of all the outputs. e.g after creating the table run “describe table” query on each table and take screen shot of it.for select query you should take the output of each select query and same goes for data modification.









Part A: Table Creation Weightage:1.5
Customer




custno
custname


address


Internal


contact


phone


city


state


zip


C100
Football


Box 352200


Y
Mary Manager


6857100


Boulder


CO


80309


C101
Men's Basketball


Box 352400


Y
Sally Supervisor


5431700


Boulder


CO


80309


C103
Baseball


Box 352020


Y
Bill Baseball


5431234


Boulder


CO


80309


C104
Women's Softball


Box 351200


Y
Sue Softball


5434321


Boulder


CO


80309


C105
High School Football


123 AnyStreet


N
Coach Bob


4441234


Louisville


CO


80027
















































Employee




empno
empname


department


email


phone


E100
Chuck Coordinator


Administration


chuck@colorado.edu


3-1111


E101
Mary Manager


Football


mary@colorado.edu


5-1111


E102
Sally Supervisor


Planning


sally@colorado.edu


3-2222


E103
Alan Administrator


Administration


alan@colorado.edu


3-3333













Facility






















facno






facname








F100




Football stadium






F101




Basketball arena






F102




Baseball field






F103




Recreation room












Location










locno
facno


locname


L100
F100


Locker room
L101
F100


Plaza
L102
F100


Vehicle gate
L103
F101


Locker room
L104
F100


Ticket Booth
L105
F101


Gate
L106
F100


Pedestrian gate



ResourceTbl




resno
resname


rate


R100
attendant


$10.00


R101
police


$15.00


R102
usher


$10.00


R103
nurse


$20.00


R104
janitor


$15.00


R105
food service


$10.00


EventRequest




eventno
dateheld


datereq


facno


custno


dateauth
status


estcost


estaudience
budno


E100
25-Oct-2013


06-Jun-2013
F100


C100


08-Jun-2013
Approved


$5,000.00


80000
B1000


E101
26-Oct-2013


28-Jul-2013
F100
C100




Pending


$5,000.00


80000
B1000


E102
14-Sep-2013


28-Jul-2013
F100
C100


31-Jul-2013
Approved


$5,000.00


80000
B1000


E103
21-Sep-2013


28-Jul-2013
F100
C100


01-Aug-2013
Approved


$5,000.00


80000
B1000


E104
03-Dec-2013


28-Jul-2013
F101
C101


31-Jul-2013
Approved


$2,000.00


12000
B1000


E105
05-Dec-2013


28-Jul-2013


F101


C101


01-Aug-2013
Approved


$2,000.00


10000
B1000


E106
12-Dec-2013


28-Jul-2013


F101


C101


31-Jul-2013
Approved


$2,000.00


10000
B1000


E107
23-Nov-2013


28-Jul-2013


F100


C105


31-Jul-2013
Denied


$10,000.00


5000








































EventPlan




planno
eventno


workdate


notes


activity


empno


P100
E100


25-Oct-2013


Standard operation


Operation


E102


P101
E104


03-Dec-2013


Watch for gate crashers


Operation


E100


P102
E105


05-Dec-2013


Standard operation


Operation


E102


P103
E106


12-Dec-2013


Watch for seat switching


Operation






P104
E101


26-Oct-2013


Standard cleanup


Cleanup


E101


P105
E100


25-Oct-2013


Light cleanup


Cleanup


E101


P199
E102


10-Dec-2013


Standard operation


Operation


E101


P299
E101


26-Oct-2013






Operation


E101


P349
E106


12-Dec-2013






Cleanup


E101


P85
E100


25-Oct-2013


Standard operation


Setup


E102


P95
E101


26-Oct-2013


Extra security


Setup


E102


EventPlanLine




PlanNo
LineNo




TimeStart


TimeEnd


NumberFld
LocNo


ResNo


P100


1
25-Oct-2013 8:00
25-Oct-2013 17:00


2
L100


R100


P100


2
25-Oct-2013 12:00
25-Oct-2013 17:00


2
L101


R101


P100


3
25-Oct-2013 7:00
25-Oct-2013 16:30


1
L102


R102


P100


4
25-Oct-2013 18:00
12-Dec-2013 22:00


2
L100


R102


P101


1
3-Dec-2013 18:00
3-Dec-2013 20:00


2
L103


R100


P101


2
3-Dec-2013 18:30
3-Dec-2013 19:00


4
L105


R100


P101


3
3-Dec-2013 19:00
3-Dec-2013 20:00


2
L103


R103


P102


1
5-Dec-2013 18:00
5-Dec-2013 19:00


2
L103


R100


P102


2
5-Dec-2013 18:00
5-Dec-2013 21:00


4
L105


R100


P102


3
5-Dec-2013 19:00
5-Dec-2013 22:00


2
L103


R103


P103


1
12-Dec-2013 18:00
12-Dec-2013 21:00


2
L103


R100


P103


2
12-Dec-2013 18:00
12-Dec-2013 21:00


4
L105


R100


P103


3
12-Dec-2013 19:00
12-Dec-2013 22:00


2
L103


R103


P104


1
26-Oct-2013 18:00
26-Oct-2013 22:00


4
L101


R104


P104


2
26-Oct-2013 18:00
26-Oct-2013 22:00


4
L100


R104


P105


1
25-Oct-2013 18:00
25-Oct-2013 22:00


4
L101


R104


P105


2
25-Oct-2013 18:00
25-Oct-2013 22:00


4
L100


R104


P199


1
10-Dec-2013 8:00
10-Dec-2013 12:00


1
L100


R100


P349


1
12-Dec-2013 12:00
12-Dec-2013 15:30


1
L103


R100


P85


1
25-Oct-2013 9:00
25-Oct-2013 17:00


5
L100


R100


P85


2
25-Oct-2013 8:00
25-Oct-2013 17:00


2
L102


R101


P85


3
25-Oct-2013 10:00
25-Oct-2013 15:00


3
L104


R100


P95


1
26-Oct-2013 8:00
26-Oct-2013 17:00


4
L100


R100


P95


2
26-Oct-2013 9:00
26-Oct-2013 17:00


4
L102


R101


P95


3
26-Oct-2013 10:00
26-Oct-2013 15:00


4
L106


R100


P95


4
26-Oct-2013 13:00
26-Oct-2013 17:00


2
L100


R103


P95


5
26-Oct-2013 13:00
26-Oct-2013 17:00


2
L101


R104








Primary keys:




Primary key fields are (CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo).




Data types:




Identify the Data types from data given in the table.







Constraints:




First go watch this vide. Link is given below.




https://www.youtube.com/watch?v=S6Ptz63B5Rk




For each primary key, you should specify a PRIMARY KEY constraint clause. For single column primary keys (CustNo, LocNo, EventNo, PlanNo, EmpNo, ResNo, and FacNo), the constraint clause can be inline or external. For multiple column primary keys (combination of PlanNo and LineNo), the CONSTRAINT clause must be external.



For each foreign key, you should specify a FOREIGN KEY constraint clause. The constraint clauses can be inline or separate.



Define NOT NULL constraints for all columns except eventplan.empno, EventRequest.DateAuth, EventRequest.BudNo, and EventPlan.Notes. Make sure that you define NOT NULL constraints for the PK of each table. Because of MySQL syntax limitations for NOT NULL constraints (inline with no constraint name and no CONSTRAINT keyword), you should define inline NOT NULL constraints.



Define a named CHECK constraint to restrict the eventrequest.status column to have a value of



“Pending”, “Denied”, or “Approved”. You can use the IN operator in this constraint. In MySQL, the syntax does not allow the CONSTRAINT keyword and a constraint name for CHECK constraints. You should use the CHECK keyword followed the condition enclosed in parentheses.




Define named CHECK constraints to ensure that the resource.rate and eventrequest.estaudience are greater than 0. In MySQL, you cannot use a constraint name and the CONSTRAINT keyword for CHECK constraints. In MySQL, the syntax does not allow the CONSTRAINT keyword and a constraint name for CHECK constraints. You should use the CHECK keyword followed the condition enclosed in parentheses.



Define a named CHECK constraint involving EventPlanLine.TimeStart and EventPlanLineTimeEnd. The start time should be smaller (chronologically before) than the end time. This CHECK constraint must be external because it involves two columns. In MySQL, the syntax does not allow the CONSTRAINT keyword and a constraint name for CHECK constraints. You should use the CHECK keyword followed the condition enclosed in parentheses.
Part B: Query Processing Weightage:2.5










Query:




Part A




List the city, state, and zip codes in the customer table. Your result should not have duplicates. (Hint: The DISTINCT keyword eliminates duplicates.)



List the name, department, phone number, and email address of employees with a phone number beginning with “3-”.



List all columns of the resource table with a rate between $10 and $20. Sort the result by rate.



List the event requests with a status of “Approved” or “Denied” and an authorized date in July 2013.



Include the event number, authorization date, and status in the output. (Hint: see the examples in Module 4 for date constants in Oracle and MySQL.)




List the location number and name of locations that are part of the “Basketball arena”. Your WHERE clause should not have a condition involving the facility number compared to a constant (“F101”). Instead, you should use a condition on the FacName column for the value of “Basketball arena”.



For each event plan, list the plan number, count of the event plan lines, and sum of the number of resources assigned. For example, plan number “P100” has 4 lines and 7 resources assigned. You only need to consider event plans that have at least one line.









Part B:




For event requests, list the event number, event date (eventrequest.dateheld), and count of the event plans. Only include event requests in the result if the event request has more than one related event plan with a work date in December 2013.



List the plan number, event number, work date, and activity of event plans meeting the following two conditions: (1) the work date is in December 2013 and (2) the event is held in the “Basketball arena”. Your query must not use the facility number (“F101”) of the basketball arena in the WHERE clause. Instead, you should use a condition on the FacName column for the value of “Basketball arena”.



List the event number, event date, status, and estimated cost of events where there is an event plan managed by Mary Manager and the event is held in the basketball arena in the period October 1 to December 31, 2013. Your query must not use the facility number (“F101”) of the basketball arena or the employee number (“E101”) of “Mary Manager” in the WHERE clause. Thus, the WHERE clause should not have conditions involving the facility number or employee number compared to constant values.



List the plan number, line number, resource name, number of resources (eventplanline.number), location name, time start, and time end where the event is held at the basketball arena, the event plan has activity of activity of “Operation”, and the event plan has a work date in the period October



1 to December 31, 2013. Your query must not use the facility number (“F101”) of the basketball arena in the WHERE clause. Instead, you should use a condition on the FacName column for the value of “Basketball arena”.










Part C:




Database Modification Problems







Insert a new row into the Facility table with facility name “Swimming Pool”.



Insert a new row in the Location table related to the Facility row in modification problem 1. The new row should have “Door” for the location name.



Insert a new row in the Location table related to the Facility row in modification problem 1. The new row should have “Locker Room” for the location name.



Change the location name of “Door” to “Gate” for the row inserted in modification problem 2.



Delete the row inserted in modification problem 3.
























___________________________________Enjoy!_____________________________________________