database access project

all you need to do is open 2010 microsoft access and follow the instructions to be done with access project. the file that i uploaded is right down, explains every single steps

INFS330 MANAGEMENT INFORMATION SYSTEMS: FALL 2013

DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 1/7

• Individuals must complete the work independently.
• MS Access procedures for this assignment are explained and demonstrated in class.
• 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.

A. Column definitions for LXXX_DEPARTMENT:
i. LXXX_DEP_ID (Text, PK)

ii. LXXX_DEP_NAME (Text)
iii. LXXX_DEP_CITY (Text); city where department is located

B. Column definitions for LXXX_EMPLOYEE:

i. LXXX_EMP_ID (Text, PK)
ii. LXXX_EMP_NAME (Text)

iii. LXXX_EMP_CITY (Text); city where employee lives
iv. LXXX_EMP_PHONE (Text)
v. LXXX_EMP_SALARY (Currency)

vi. LXXX_DEP_ID (Text, FK)

C. Column definitions for LXXX_PROJECT:
i. LXXX_PROJ_ID (Text, PK)

ii. LXXX_START_DATE (Date/Time)
iii. LXXX_END_DATE (Date/Time)
iv. LXXX_PROJ_TYPE_ID (TEXT, FK)
v. LXXX_PROJ_DETAIL (TEXT)

D. Column definitions for LXXX_PROJECT_TYPE:

i. LXXX_PROJ_TYPE_ID (Text, PK)
ii. LXXX_PROJ_TYPE (Text)

E. Column definitions for LXXX_ROLE:

i. LXXX_ROLE_ID (Text, PK)
ii. LXXX_ROLE_NAME (Text)

F. Column definitions for LXXX_EMP_PROJ_RECORD:

i. LXXX_EMP_PROJ_ID (AutoNumber, PK)
ii. LXXX_EMP_ID (Text, FK)

iii. LXXX_ROLE_ID (Text, FK)
iv. LXXX_PROJ_ID (Text, FK)

INFS 330 MANAGEMENT INFORMATION SYSTEMS: FALL 2013
DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 2/7

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

INFS 330 MANAGEMENT INFORMATION SYSTEMS: FALL 2013
DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 3/7

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 MANAGEMENT INFORMATION SYSTEMS: FALL 2013
DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 4/7

LXXX_EMPLOYEE
 

LXXX_EMP_ID LXXX_EMP_NAME LXXX_EMP_CITY LXXX_EMP_PHONE LXXX_EMP_SALARY LXXX_DEP_ID
001 JOE
 SMITH XXX_CHICAGO XXX1112222 $70,100.00 A
002 JANE
 SANDERS XXX_SCHAUMBURG XXX2221111 $75,230.00 A
003 MARY
 CONNOR XXX_CHICAGO XXX1122223 $73,500.00 B
004 DAVID
 CANNON XXX_OAKBROOK XXX2902300 $69,650.00 C
005 KEVIN
 STEVEN XXX_OAKBROOK XXX8797777 $65,750.00 D
006 STEVE
 MASON XXX_HOFFMAN
 

ESTATES
XXX9897778 $71,250.00 E

007 SUSAN
 CATZ XXX_BARRINGTON XXX4037575 $73,000.00 E
008 NICHOLAS
 MATAG XXX_CHICAGO XXX5544556 $81,800.00 B
009 JOE
 WILLIAMS XXX_OAKBROOK XXX8789090 $68,680.00 B
010 BILL
 MASUDA XXX_HOFFMAN
 

ESTATES
XXX2322323 $66,770.00 E

011 MICHEL
 MAZIANI XXX_ELGIN XXX6567453 $67,670.00 B
012 TIM
 SCHMIDT XXX_CHICAGO XXX1233122 $72,320.00 C
013 RACHEL
 SNEIDER XXX_ELGIN XXX9540000 $74,440.00 D
014 CHRIS
 CHANNON XXX_SCHAUMBURG XXX0123344 $85,500.00 A
015 NAT
 KING XXX_SCHAUMBURG XXX7655675 $86,860.00 B
016 MATT
 MATHEW XXX_CHICAGO XXX2121212 $70,100.00 D
017 DEBBIE
 ROE XXX_HOFFMAN
 

ESTATES
XXX6766545 $71,525.00 C

018 ELIZABETH
 
BROWN

XXX_ELGIN XXX0951342 $69,898.00 A

019 JUDY
 RICKERT XXX_HINSDALE XXX0077077 $68,800.00 A
020 CHARLIE
 WANG XXX_BARRINGTON XXX6547676 $75,650.00 C
021 MICHAEL
 KING XXX_OAKBROOK XXX4322344 $90,460.00 B
022 SARAH
 GREENE XXX_CHICAGO XXX2339090 $87,980.00 B
023 NANCY
 LEE XXX_OAKBROOK XXX6567877 $89,190.00 D
024 CHRIS
 LOWEY XXX_CHICAGO XXX3777333 $90,100.00 E
025 TIM
 BROWN XXX_SCHAUMBURG XXX7876554 $92,345.00 A
026 JANE
 RICE XXX_CHICAGO XXX5685688 $95,870.00 A

 
 
 
 
 
 

INFS 330 MANAGEMENT INFORMATION SYSTEMS: FALL 2013
DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 5/7

LXXX_PROJECT
 
LXXX_PROJ_ID LXXX_START_DATE LXXX_END_DATE LXXX_PROJ_TYPE_ID LXXX_PROJ_DETAIL
PR001 3/1/2008 4/1/2008 PT02 XXX_2008
 CUSTOMER
 

SATISFACTION
 SURVEY
PR002 5/1/2008 6/1/2008 PT02 XXX_2008
 CUSTOMER
 

PRODUCT
 PREFERENCE
 
SURVEY

PR003 3/1/2009 4/1/2009 PT02 XXX_2009
 CUSTOMER
 
SATISFACTION
 SURVEY

PR004 5/1/2009 6/1/2009 PT02 XXX_2009
 CUSTOMER
 
PRODUCT
 PREFERENCE
 
SURVEY

PR005 1/1/2008 3/31/2008 PT01 XXX_SMART
 PHONE
 
DEVELOPMENT
 -­‐
 PHASE
 1

PR006 4/1/2008 6/30/2008 PT01 XXX_SMART
 PHONE
 
DEVELOPMENT
 -­‐
 PHASE
 2

PR007 8/1/2009 8/8/2009 PT03 XXX_2009
 ANNUAL
 
CUSTOMER
 GOLF
 OUTING

PR008 6/2/2008 6/6/2008 PT04 XXX_2008
 PRODUCT
 
TRAINING

PR009 3/2/2009 3/6/2009 PT04 XXX_2009
 MANAGEMENT
 
TRAINING

PR010 9/7/2009 9/11/2009 PT04 XXX_2009
 SYSTEM
 TRAINING

INFS 330 MANAGEMENT INFORMATION SYSTEMS: FALL 2013
DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 6/7

LXXX_EMP_PROJ_RECORD
 
LXXX_EMP_PROJ_ID LXXX_EMP_ID LXXX_ROLE_ID LXXX_PROJ_ID

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

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

INFS 330 MANAGEMENT INFORMATION SYSTEMS: FALL 2013
DATABASE ASSIGNMENT: DUE 11/21/13 1:00PM (Blackboard)

INFS 330 Database Assignment Page 7/7

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

Query 1: List all employees by LXXX_EMP_ID, LXXX_EMP_NAME,

LXXX_EMP_CITY, LXXX_EMP_PHONE, LXXX_DEP_ID, order by
LXXX_DEP_ID.

Query 2: List all employees by LXXX_EMP_ID, LXXX_EMP_NAME,

LXXX_DEP_NAME, order by LXXX_DEP_NAME.

Query 3: List all employees by LXXX_EMP_ID, LXXX_EMP_NAME,

LXXX_DEP_NAME who work for the “XXX_SALES” department.

Query 4: 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

Query 5: 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.

Query 6: List all employees by LXXX_EMP_ID, LXXX_EMP_NAME,

LXXX_ROLE_NAME who have worked in the role of an
“XXX_PROJECT MANAGER”.

Query 7: Display the average salary of all sales employees in the first column with

column name “AVERAGE SALES SALARY”, using the AVG built-in
function. The format of the output is shown below.

AVERAGE
 SALES
 SALARY

$XX,X

XX

Query 8: Display the count of all employees making over $70,000 in the first column

with column name “NUMBER OVER 70K”, using the COUNT built-in
function. The format of the output is shown below.

NUMBER
 OVER
 70K

XX