INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019DATABASE 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 IDin LXXX for the table and column definitions; XXX in the table contents represents thelast 3 digits of your student ID.
1. (6 PTS) Create six tables LXXX_DEPARTMENT, LXXX_EMPLOYEE,LXXX_PROJECT, LXXX_PROJECT_TYPE, LXXX_ROLE andLXXX_EMP_PROJ_RECORD using MS ACCESS.
- Column definitions for LXXX_DEPARTMENT:
- LXXX_DEP_ID (Text, PK)
- LXXX_DEP_NAME (Text)
- LXXX_DEP_CITY (Text); city where department is located
- Column definitions for LXXX_EMPLOYEE:
- LXXX_EMP_ID (Text, PK)
- LXXX_EMP_NAME (Text)
- LXXX_EMP_CITY (Text); city where employee lives
- LXXX_EMP_PHONE (Text)
- LXXX_EMP_SALARY (Currency)
- LXXX_DEP_ID (Text, FK)
- Column definitions for LXXX_PROJECT:
- LXXX_PROJ_ID (Text, PK)
- LXXX_START_DATE (Date/Time)
- LXXX_END_DATE (Date/Time)
- LXXX_PROJ_TYPE_ID (TEXT, FK)
- LXXX_PROJ_DETAIL (TEXT)
- Column definitions for LXXX_PROJECT_TYPE:
- LXXX_PROJ_TYPE_ID (Text, PK)
- LXXX_PROJ_TYPE (Text)
- Column definitions for LXXX_ROLE:
- LXXX_ROLE_ID (Text, PK)
- LXXX_ROLE_NAME (Text)
- Column definitions for LXXX_EMP_PROJ_RECORD:
- LXXX_EMP_PROJ_ID (AutoNumber, PK)
- LXXX_EMP_ID (Text, FK)
- LXXX_ROLE_ID (Text, FK)
- LXXX_PROJ_ID (Text, FK)
INFS 330 Database Assignment
Page 1/7
INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019DATABASE 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 2019DATABASE 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 2019DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)
LXXX_EMPLOYEELXXX_EMP_ID LXXX_EMP_NAME
- 001 JOE SMITH
- 002 JANE SANDERS
- 003 MARY CONNOR
- 004 DAVID CANNON
- 005 KEVIN STEVEN
- 006 STEVE MASON
- 007 SUSAN CATZ
- 008 NICHOLAS MATAG
- 009 JOE WILLIAMS
- 010 BILL MASUDA
- 011 MICHEL MAZIANI
- 012 TIM SCHMIDT
- 013 RACHEL SNEIDER
- 014 CHRIS CHANNON
- 015 NAT KING
- 016 MATT MATHEW
- 017 DEBBIE ROE
- 018 ELIZABETHBROWN
- 019 JUDY RICKERT
- 020 CHARLIE WANG
- 021 MICHAEL KING
- 022 SARAH GREENE
- 023 NANCY LEE
- 024 CHRIS LOWEY
- 025 TIM BROWN
- 026 JANE RICE
LXXX_EMP_CITY
XXX_CHICAGO
XXX_SCHAUMBURG
XXX_CHICAGO
XXX_OAKBROOK
XXX_OAKBROOK
XXX_HOFFMANESTATES
XXX_BARRINGTON
XXX_CHICAGO
XXX_OAKBROOK
XXX_HOFFMANESTATES
XXX_ELGIN
XXX_CHICAGO
XXX_ELGIN
XXX_SCHAUMBURG
XXX_SCHAUMBURG
XXX_CHICAGO
XXX_HOFFMANESTATES
XXX_ELGIN
XXX_HINSDALEXXX_BARRINGTONXXX_OAKBROOKXXX_CHICAGOXXX_OAKBROOKXXX_CHICAGOXXX_SCHAUMBURGXXX_CHICAGO
LXXX_EMP_PHONE LXXX_EMP_SALARY
LXXX_DEP_ID
INFS 330 Database Assignment
Page 4/7
XXX1112222XXX2221111XXX1122223XXX2902300XXX8797777XXX9897778
XXX4037575XXX5544556XXX8789090XXX2322323
XXX6567453XXX1233122XXX9540000XXX0123344XXX7655675XXX2121212XXX6766545
XXX0951342
XXX0077077XXX6547676XXX4322344XXX2339090XXX6567877XXX3777333XXX7876554XXX5685688
$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 2019DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)
LXXX_PROJECTLXXX_PROJ_ID LXXX_START_DATE LXXX_END_DATE LXXX_PROJ_TYPE_ID
LXXX_PROJ_DETAIL
XXX_2008 CUSTOMERSATISFACTION SURVEY
XXX_2008 CUSTOMERPRODUCT PREFERENCESURVEY
XXX_2009 CUSTOMERSATISFACTION SURVEY
XXX_2009 CUSTOMERPRODUCT PREFERENCESURVEY
XXX_SMART PHONEDEVELOPMENT – PHASE 1
XXX_SMART PHONEDEVELOPMENT – PHASE 2
XXX_2009 ANNUALCUSTOMER GOLF OUTING
XXX_2008 PRODUCTTRAINING
XXX_2009 MANAGEMENTTRAINING
XXX_2009 SYSTEM TRAINING
- PR001 3/1/2008
- PR002 5/1/2008
- PR003 3/1/2009
- PR004 5/1/2009
- PR005 1/1/2008
- PR006 4/1/2008
- PR007 8/1/2009
- PR008 6/2/2008
- PR009 3/2/2009
- PR010 9/7/2009
4/1/2008 PT026/1/2008 PT02
4/1/2009 PT026/1/2009 PT02
3/31/2008 PT016/30/2008 PT018/8/2009 PT036/6/2008 PT043/6/2009 PT049/11/2009 PT04
INFS 330 Database Assignment
Page 5/7
INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019DATABASE ASSIGNMENT: DUE NOVEMBER 25, 2019 1:00PM (Blackboard)
LXXX_EMP_PROJ_RECORDLXXX_EMP_PROJ_ID LXXX_EMP_ID LXXX_ROLE_ID LXXX_PROJ_ID
INFS 330 Database Assignment
Page 6/7
1 001 R022 003 R013 014 R014 002 R025 008 R016 018 R017 019 R028 021 R019 001 R01
10 002 R0211 011 R0112 018 R0113 014 R0114 024 R0215 005 R0116 006 R0117 007 R0218 019 R0119 005 R0120 021 R0121 022 R0122 006 R0323 010 R0124 016 R0325 023 R0126 012 R0327 017 R01
PR001PR001PR001PR002PR002PR002PR003PR003PR003PR004PR004PR004PR005PR005PR005PR005PR006PR006PR006PR007PR007PR008PR008PR009PR009PR010PR010
INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2019DATABASE 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 byLXXX_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 liveand 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 whohave 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”.