INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: The work must be completed independently by individuals.

• MS Access procedures for this assignment are explained and demonstrated in class. Further help can be found online, e.g. Basic tasks for an Access desktop database, https://support.office.com/en-us/article/Basic-tasks-for-an-Access-desktop- database-5DDB8595-497C-4366-8327-AE79D2ABDC9C
• Notations: L=the first letter of your last name, XXX=the last 3 digits of your student ID in LXXX for the table and column definitions; XXX in the table contents represents the last 3 digits of your student ID.

1. (6 PTS) Create six tables LXXX_DEPARTMENT, LXXX_EMPLOYEE, LXXX_PROJECT, LXXX_PROJECT_TYPE, LXXX_ROLE and LXXX_EMP_PROJ_RECORD using MS ACCESS.

1. Column definitions for LXXX_DEPARTMENT:
   1. LXXX_DEP_ID (Text, PK)
   2. LXXX_DEP_NAME (Text)
   3. LXXX_DEP_CITY (Text); city where department is located
2. Column definitions for LXXX_EMPLOYEE:
   1. LXXX_EMP_ID (Text, PK)
   2. LXXX_EMP_NAME (Text)
   3. LXXX_EMP_CITY (Text); city where employee lives
   4. LXXX_EMP_PHONE (Text)
   5. LXXX_EMP_SALARY (Currency)
   6. LXXX_DEP_ID (Text, FK)
3. Column definitions for LXXX_PROJECT:
   1. LXXX_PROJ_ID (Text, PK)
   2. LXXX_START_DATE (Date/Time)
   3. LXXX_END_DATE (Date/Time)
   4. LXXX_PROJ_TYPE_ID (TEXT, FK)
   5. LXXX_PROJ_DETAIL (TEXT)
4. Column definitions for LXXX_PROJECT_TYPE:
   1. LXXX_PROJ_TYPE_ID (Text, PK)
   2. LXXX_PROJ_TYPE (Text)
5. Column definitions for LXXX_ROLE:
   1. LXXX_ROLE_ID (Text, PK)
   2. LXXX_ROLE_NAME (Text)
6. Column definitions for LXXX_EMP_PROJ_RECORD:
   1. LXXX_EMP_PROJ_ID (AutoNumber, PK)
   2. LXXX_EMP_ID (Text, FK)
   3. LXXX_ROLE_ID (Text, FK)
   4. LXXX_PROJ_ID (Text, FK)

INFS 330 Database Assignment

Page 1/7

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)

2. (4 PTS) Create an Entity Relationship Diagram using MS Access.

INFS 330 Database Assignment Page 2/7

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)

3. (6 PTS) Insert the following sample data into the tables.

LXXX_DEPARTMENT

LXXX_DEP_ID LXXX_DEP_NAME LXXX_DEP_CITY

A XXX_MARKETING XXX_SCHAUMBURG

B XXX_SALES XXX_CHICAGO

C XXX_FINANCE & ACCOUNTING XXX_OAKBROOK

D XXX_HUMAN RESOURCE XXX_OAKBROOK

E XXX_RESEARCH & DEVELOPMENT XXX_HOFFMAN ESTATES

LXXX_ROLE
LXXX_ROLE_ID	LXXX_ROLE_NAME
R01	XXX_MEMBER
R02	XXX_PROJECT MANAGER
R03	XXX_INSTRUCTOR

LXXX_PROJECT_TYPE
LXXX_PROJ_TYPE_ID	LXXX_PROJ_TYPE
PT01	XXX_PRODUCT DEVELOPMENT
PT02	XXX_CUSTOMER SURVEY
PT03	XXX_CUSTOMER GOLF OUTING
PT04	XXX_TRAINING

INFS 330 Database Assignment Page 3/7

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)

LXXX_EMPLOYEE LXXX_EMP_ID LXXX_EMP_NAME

1. 001 JOE SMITH
2. 002 JANE SANDERS
3. 003 MARY CONNOR
4. 004 DAVID CANNON
5. 005 KEVIN STEVEN
6. 006 STEVE MASON
7. 007 SUSAN CATZ
8. 008 NICHOLAS MATAG
9. 009 JOE WILLIAMS
10. 010 BILL MASUDA
11. 011 MICHEL MAZIANI
12. 012 TIM SCHMIDT
13. 013 RACHEL SNEIDER
14. 014 CHRIS CHANNON
15. 015 NAT KING
16. 016 MATT MATHEW
17. 017 DEBBIE ROE
18. 018 ELIZABETH BROWN
19. 019 JUDY RICKERT
20. 020 CHARLIE WANG
21. 021 MICHAEL KING
22. 022 SARAH GREENE
23. 023 NANCY LEE
24. 024 CHRIS LOWEY
25. 025 TIM BROWN
26. 026 JANE RICE

LXXX_EMP_CITY

XXX_CHICAGO

XXX_SCHAUMBURG

XXX_CHICAGO

XXX_OAKBROOK

XXX_OAKBROOK

XXX_HOFFMAN ESTATES

XXX_BARRINGTON

XXX_CHICAGO

XXX_OAKBROOK

XXX_HOFFMAN ESTATES

XXX_ELGIN

XXX_CHICAGO

XXX_ELGIN

XXX_SCHAUMBURG

XXX_SCHAUMBURG

XXX_CHICAGO

XXX_HOFFMAN ESTATES

XXX_ELGIN

XXX_HINSDALE XXX_BARRINGTON XXX_OAKBROOK XXX_CHICAGO XXX_OAKBROOK XXX_CHICAGO XXX_SCHAUMBURG XXX_CHICAGO

LXXX_EMP_PHONE LXXX_EMP_SALARY

LXXX_DEP_ID

INFS 330 Database Assignment

Page 4/7

XXX1112222 XXX2221111 XXX1122223 XXX2902300 XXX8797777 XXX9897778

XXX4037575 XXX5544556 XXX8789090 XXX2322323

XXX6567453 XXX1233122 XXX9540000 XXX0123344 XXX7655675 XXX2121212 XXX6766545

XXX0951342

XXX0077077 XXX6547676 XXX4322344 XXX2339090 XXX6567877 XXX3777333 XXX7876554 XXX5685688

$70,100.00 A $75,230.00 A $73,500.00 B $69,650.00 C $65,750.00 D $71,250.00 E

$73,000.00 E $81,800.00 B $68,680.00 B $66,770.00 E

$67,670.00 B $72,320.00 C $74,440.00 D $85,500.00 A $86,860.00 B $70,100.00 D $71,525.00 C

$69,898.00 A

$68,800.00 A $75,650.00 C $90,460.00 B $87,980.00 B $89,190.00 D $90,100.00 E $92,345.00 A $95,870.00 A

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)

LXXX_PROJECT LXXX_PROJ_ID LXXX_START_DATE LXXX_END_DATE LXXX_PROJ_TYPE_ID

LXXX_PROJ_DETAIL

XXX_2008 CUSTOMER SATISFACTION SURVEY

XXX_2008 CUSTOMER PRODUCT PREFERENCE SURVEY

XXX_2009 CUSTOMER SATISFACTION SURVEY

XXX_2009 CUSTOMER PRODUCT PREFERENCE SURVEY

XXX_SMART PHONE DEVELOPMENT – PHASE 1

XXX_SMART PHONE DEVELOPMENT – PHASE 2

XXX_2009 ANNUAL CUSTOMER GOLF OUTING

XXX_2008 PRODUCT TRAINING

XXX_2009 MANAGEMENT TRAINING

XXX_2009 SYSTEM TRAINING

1. PR001 3/1/2008
2. PR002 5/1/2008
3. PR003 3/1/2009
4. PR004 5/1/2009
5. PR005 1/1/2008
6. PR006 4/1/2008
7. PR007 8/1/2009
8. PR008 6/2/2008
9. PR009 3/2/2009
10. PR010 9/7/2009

4/1/2008 PT02 6/1/2008 PT02

4/1/2009 PT02 6/1/2009 PT02

3/31/2008 PT01 6/30/2008 PT01 8/8/2009 PT03 6/6/2008 PT04 3/6/2009 PT04 9/11/2009 PT04

INFS 330 Database Assignment

Page 5/7

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)

LXXX_EMP_PROJ_RECORD LXXX_EMP_PROJ_ID LXXX_EMP_ID LXXX_ROLE_ID LXXX_PROJ_ID

INFS 330 Database Assignment

Page 6/7

1 001 R02 2 003 R01 3 014 R01 4 002 R02 5 008 R01 6 018 R01 7 019 R02 8 021 R01 9 001 R01

10 002 R02 11 011 R01 12 018 R01 13 014 R01 14 024 R02 15 005 R01 16 006 R01 17 007 R02 18 019 R01 19 005 R01 20 021 R01 21 022 R01 22 006 R03 23 010 R01 24 016 R03 25 023 R01 26 012 R03 27 017 R01

PR001 PR001 PR001 PR002 PR002 PR002 PR003 PR003 PR003 PR004 PR004 PR004 PR005 PR005 PR005 PR005 PR006 PR006 PR006 PR007 PR007 PR008 PR008 PR009 PR009 PR010 PR010

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019 DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)

4. (24 PTS) Develop SQL statements to do the following:

Query 1:

Query 2: Query 3: Query 4:

Query 5:

Query 6:

List all employees by LXXX_EMP_ID, LXXX_EMP_NAME, LXXX_EMP_CITY, LXXX_EMP_PHONE, LXXX_DEP_ID, order by LXXX_DEP_ID.

List all employees by LXXX_EMP_ID, LXXX_EMP_NAME, LXXX_DEP_NAME, order by LXXX_DEP_NAME.

List all employees by LXXX_EMP_ID, LXXX_EMP_NAME, LXXX_DEP_NAME who work for the “XXX_SALES” department.

List all employees by LXXX_EMP_ID, LXXX_EMP_NAME, LXXX_DEP_NAME, LXXX_EMP_CITY, LXXX_DEP_CITY who live and work in the same city, order by LXXX_DEP_CITY

List all employees by LXXX_EMP_ID, LXXX_EMP_NAME, LXXX_PROJ_TYPE, LXXX_START_DATE, LXXX_END_DATE who have worked in either an “XXX_CUSTOMER SURVEY” project or an “XXX_CUSTOMER GOLF OUTING” project.

List all employees by LXXX_EMP_ID, LXXX_EMP_NAME, LXXX_ROLE_NAME who have worked in the role of an “XXX_PROJECT MANAGER”.