INFS 330 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,XXX

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

Section South Management Consultation 1

Section South Management Consultation

Identify the Client/Problem

Section South Management Consultation 2

Section South Management Consultation

The client for this project is what will be referred as Section South. Section South consists of an analytical team that provides analytical products to a customer with in a latest time of value. I currently work as a manager in another team that helps solicit these products and deliver these products to the customer. Section South works for Lockheed Martin.

The Lockheed Martin mission statement for this section is to provide the customer with fused analytical products to enable more precise decision making by managers/leaders in other organizations. Because these decisions are normally associated with a timeline, the ability to meet timelines is imperative. It is the management responsibility to ensure the staff is completing the necessary requirements in a time that will benefit the customers.

Because of the nature of the work environment there will be instances where the true names of the Sections are not identified. Section South consisted of a twenty-two person team, with four direct supervisor positions, and two manager positions. I currently work and communicate directly with the two managers and the four direct supervisors. I intend to look at the current requirements for that section and try and identify the root cause(s) of the production timeline challenges.

The product produced by our company is an analytical type of product that has to be produced in a timely manner to be of value. One of the primary goals of the company is to give an in depth analysis addressing a specific problem set in the customers latest time of value for the product. Section South within the organization has had challenges maintaining the production level to still meet the timeline requested by the customer. Looking at the problem I

Section South Management Consultation 3

identified multiple incidents, now the question is what the root cause of these instances is. First, a 3 Dimensional product was needed by the customer on 15 August. This product was not completed until 21 August. At this time it was no longer of value to the customer. The second incident was an analytical product that was needed by 13 September, but was not completed until 21 September. This represents only a small sample of timeline issues identified over the past 6 months. Because these incidents represent a problem with production it is important to now minimize any further impacts.

Products produced are Information Technology driven and all employees receive specific training to become proficient in the production process. This IT framed environment requires a large amount of resources to support the production. Each product is unique in design based on the customer’s request. The section currently has checks in place to ensure quality control of these products prior to delivery. The quality of the product to the customer is just as important as the timeline. Several questions were asked. So is there a problem with the quality control process that is limiting the employees or sections ability to complete the production in the time required. Is there a problem that is directly attributed to the communication between managers and subordinates? Is this attributed to the daily activities in the work place environment or management control of those activities? This can all be somewhat summed up with two words, the human dimension.

The question still exists as to the root cause of the problem. As the problem was explored several areas that may be the cause of these timeline challenges serviced. Communication among managers and employees seemed to be the biggest of the challenges. This was clearly identified based on employees not understanding expectations for products assigned to them for production. This also creates an

Previous Next

Get help from top-rated tutors in any subject.

Efficiently complete your homework and academic assignments by getting help from the experts at homeworkarchive.com