Microsoft Access Database Project

User Generated

rqvaqmrxb

Computer Science

Description

There are four parts to the project, all steps must be completed

-Create six tables

-Create an Entity Relationship Diagram using MS Access.

-Insert sample data into tables.

-Develop SQL statements


IMPORTANT:

  • Notations: L=the first letter of last name, XXX=the last 3 digits of student ID in LXXX for the table and column definitions; XXX in the table contents represents the last 3 digits of your student ID.

First letter of last name is M

Last 3 digits of student ID are 215

Example: Do not type LXXX_DEPARTMENT as in the PDF, type as M215_DEPARTMENT

Unformatted Attachment Preview

INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 1:00PM (Blackboard) • • 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-desktopdatabase-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. 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 Database Assignment Page 1/7 INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 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 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 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 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 1:00PM (Blackboard) 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 $95,870.00 A INFS 330 Database Assignment XXX5685688 Page 4/7 INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 1:00PM (Blackboard) 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 INFS 330 Database Assignment XXX_2009 SYSTEM TRAINING Page 5/7 INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 1:00PM (Blackboard) 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 Database Assignment Page 6/7 INFS 330-01 INFORMATION RESOURCE MANAGEMENT: FALL 2017 DATABASE ASSIGNMENT: DUE NOV 27, 2017 1:00PM (Blackboard) 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”. INFS 330 Database Assignment Page 7/7
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

The access file has everything that is requiredAlso check the word document for outlineunzip the access

1. Table creation

2. Create an Entity Relationship Diagram using MS Access.

3. Insert the following sample data into the tables
All tables has been inserted
4.

Develop SQL statements to do the following:
Query 1
SELECT M215_EMPLOYEE.M215_EMP_ID, M215_EMPLOYEE.M215_EMP_NAME,
M215_EMPLOYEE.M215_EMP_CITY, M215_EMPLOYEE.M215_EMP_PHONE,
M215_EMPLOYEE.M215_DEP_ID
FROM M215_EMPLOYEE ORDER BY M215_EMPLOYEE.M215_DEP_ID;
Query 2
SELECT M215_EMPLOYEE.M215_EMP_ID, M215_EMPLOYEE.M215_EMP_NAME,
M215_DEPARTMENT.M215_DEP_NAME
FROM M215_DEPARTMENT INNER JOIN M215_EMPLOYEE ON
M215_DEPARTMENT.[M215_DEP_ID] = M215_EMPLOYEE.[M215_DEP_ID] ORDER BY
M215_DEPARTMENT.M215_DEP_NAME ;
QUERY 3
SELECT M215_EMPLOYEE.M215_EMP_ID, M215_EMPLOYEE.M215_EMP_NAME,
M215_DEPARTMENT.M215_DEP_NAME
FROM M215_DEPARTMENT INNER JOIN M215_EMPLOYEE O...


Anonymous
Great! Studypool always delivers quality work.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags