Description
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
|
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
|
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
|
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!_____________________________________________
