Expert Answer:database project, access

Answer & Explanation: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.

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 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

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 ELIZABETH
BROWN

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_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

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 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”.

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more