The Grace

http://www.oppapers.com/essays/Value-Chain-Analysis-Ba/689012

http://www.oppapers.com/essays/Value-Chain-Analysis-Ba/689012
Problem A

Use the database shown in Figure P3.1 to work Problems 1-7. Note that the database is composed of four tables that reflect these relationships:
• An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs.
• An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs.

Note also that the *:* relationship has been broken down into two 1:* relationships for which the BENEFIT table serves as the composite or bridge entity.
Database name: Ch03_BeneCo Table name: EMPLOYEE
EMP_CODE EMP_LNAME JOB_CODE
14 Rudell 2
15 McDade 1
16 Ruellardo 1
17 Smith 3
20 Smith 2

Table name: BENEFIT
EMP_CODE PLAN_CODE
15 2
15 3
16 1
17 1
17 3
17 4
20 3

Table name: JOB
JOB_CODE JOB_DESCRIPTION
1 Clerical
2 Technical
3 Managerial

Table name: PLAN
PLAN_CODE PLAN_DESCRIPTION
1 Term life
2 Stock purchase
3 Long-term disability
4 Dental

1. For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the assigned space provided.

TABLE PRIMARY KEY FOREIGN KEY(S)
EMPLOYEE EMP_CODE JOB_CODE
BENEFIT EMP_CODE_PLAN_CODE EMP_CODE,PLAN_CODE
JOB JOB_CODE NONE
PLAN PLAN_CODE NONE

2. Create the ERD to show the relationship between EMPLOYEE and JOB.

3. Do the tables exhibit entity integrity? Answer yes or no.

TABLE ENTITY INTEGRITY
EMPLOYEE NO
BENEFIT YES
JOB YES
PLAN YES
4. Do the tables exhibit referential integrity? Answer yes or no. Write NA (Not Applicable) if the table does not have a foreign key.

TABLE REFERENTIAL INTEGRITY
EMPLOYEE YES

BENEFIT YES

JOB NO
PLAN YES

5. Create the ERD using Crow’s Foot notation to show the relationships among EMPLOYEE, BENEFIT, JOB, and PLAN.

Problem B

Use the database Ch03_StoreCo to answer Problems 7-13.
Database