IST 331 Homework 3. Chapter 6. Introduction to SQL
V. Matos A fragment of a Project-Employee-Assignment (PEA) database
Spring 2004
Use MS-Access to implement the PROJECT-EMPLOYEE-ASSIGNMENT (PEA) database depicted on figure 6-2 (see the Appendix).
· Define the three tables: PROJECT, EMPLOYEE, and ASSIGNMENT
· Draw the RELATIONSHIP chart and enforce the referential integrity constraints indicated in the IDEF1X diagram of Figure 6-2
· Populate the three tables: PROJECT, EMPLOYEE, and ASSIGNMENT (correction: the last ProjectID value should be 1600 instead of 1500)
· Create new queries for each of the queries on pages: 200, 202, 206,207, 209(last one), 212, 213(first), 215(first).
· Queries should be named according to the following format: Q+pageNum+Letter. For instance the first query on page 200 is Q200A, the second is Q200B, and so on.
Notes:
· Literals in MS-ACCESS are enclosed in double-quotes (instead of single ‘ quotes)
· The partial matching construct for MS-ACCESS uses the * syntax such as: Phone LIKE “285*’ to retrieve any phone beginning with 285.
Prepare a Word report showing each query-ID, SQL command, and the corresponding output table. For example, the following fragment shows the first query of page 200.
Q200A.
SELECT Name, Department, MaxHours
FROM PROJECT;
Query1 /Name / Department / MaxHours /
Q3 Portfolio Analysis / Finance / 75
Q3 Tax Prep / Accounting / 145
Q4 Product Plan / Marketing / 138
Q4 Portfolio Analysis / Finance / 110
IST 331 Kroenke. Chapter 6. Introduction to SQL
V. Matos A fragment of a Project-Employee-Assignment (PEA) database
Spring 2004
SQL script to create the physical PEA database (try this in Oracle)
CREATE TABLE PROJECT (
ProjectID Integer Not Null,
Name Char(25) Unique Not Null,
Department VarChar(100) Null,
MaxHours Numeric(6,1) Default 100,
CONSTRAINT ProjectPK PRIMARY KEY (ProjectID));
CREATE TABLE EMPLOYEE (
EmployeeNumber Integer Not Null,
Name Char(25) Not Null,
Phone Char(8),
Department VarChar(100),
CONSTRAINT EmployeePK PRIMARY KEY (EmployeeNumber));
CREATE TABLE ASSIGNMENT (
ProjectID Integer Not Null,
EmployeeNum Integer Not Null,
HoursWorked Numeric (5,2) DEFAULT 10,
CONSTRAINT AssignmentPK PRIMARY KEY (ProjectID, EmployeeNum));
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT EmployeeFK
FOREIGN KEY (EmployeeNum) REFERENCES EMPLOYEE (EmployeeNumber);
ALTER TABLE ASSIGNMENT
ADD CONSTRAINT ProjectFK
FOREIGN KEY (ProjectID) REFERENCES PROJECT (ProjectID)
ON DELETE CASCADE;
An example of the loading of data in Oracle
insert into PROJECT (ProjectID, Name, Department, MaxHours) values (
1000, 'Q3 Portfolio Analysis', 'Finance', 75.0);
insert into PROJECT (ProjectID, Name, Department, MaxHours) values (
1200,'Q3 Tax Prep','Accounting', 145.0);
insert into PROJECT (ProjectID, Name, Department, MaxHours) values (
1400, 'Q4 Product Plan','Marketing', 138.0);
insert into PROJECT (ProjectID, Name, Department, MaxHours) values (
1500,'Q4 Portfolio Analysis', 'Finance', 110.0);
Implementing the PEA database in MS-Access
Correction:
PROJECT /ProjectID / Name / Department / MaxHours /
1000 / Q3 Portfolio Analysis / Finance / 75
1200 / Q3 Tax Prep / Accounting / 145
1400 / Q4 Product Plan / Marketing / 138
1600 / Q4 Portfolio Analysis / Finance / 110
Examples
Some examples of SQL queries taken from the book are given in the next sections. Query Q200A represents the first request on page 200, Q200B is the second query in page 200, and so on.
Q200A. For each project, give the project name, department in charge, and maximum number of allocated hours.
SELECT Name, Department, MaxHours
FROM PROJECT;
Query1 /Name / Department / MaxHours /
Q3 Portfolio Analysis / Finance / 75
Q3 Tax Prep / Accounting / 145
Q4 Product Plan / Marketing / 138
Q4 Portfolio Analysis / Finance / 110
Q201A. Make a table showing department names
SELECT DISTINCT Department
FROM PROJECT
Q202A. Obtain the project’s information for those under the control of Finance which in addition have more than 100 hours allocated.
SELECT *
FROM PROJECT
WHERE Department ="Finance" AND MaxHours > 100;
Q202A /ProjectID / Name / Department / MaxHours /
1600 / Q4 Portfolio Analysis / Finance / 110
Q202C. Make a list showing the name, phone and department of those employees who work for Accounting, Finance, or Marketing.
SELECT Name, Phone, Department
FROM EMPLOYEE
WHERE Department IN ( "Accounting" ,"Finance", "Marketing" );
Q202C /Name / Phone / Department /
Mary Jacobs / 285-8879 / Accounting
Kenji Numoto / 287-0098 / Marketing
Heather Jones / 287-9981 / Finance
Rosalie Jackson / 285-1237 / Accounting
Kim Sung / 287-3222 / Marketing
.