# The Grace

Topics: Data modeling, Foreign key, SQL Pages: 11 (2445 words) Published: February 7, 2012
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_CODEEMP_LNAMEJOB_CODE
14Rudell2
16Ruellardo1
17Smith3
20Smith2

Table name: BENEFIT
EMP_CODEPLAN_CODE
152
153
161
171
173
174
203

Table name: JOB
JOB_CODEJOB_DESCRIPTION
1Clerical
2Technical
3Managerial

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

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.

TABLEPRIMARY KEYFOREIGN KEY(S)
EMPLOYEEEMP_CODEJOB_CODE
BENEFITEMP_CODE_PLAN_CODEEMP_CODE,PLAN_CODE
JOBJOB_CODENONE
PLANPLAN_CODENONE

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

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

TABLEENTITY INTEGRITY
EMPLOYEENO
BENEFITYES
JOBYES
PLANYES
4.Do the tables exhibit referential integrity? Answer yes or no. Write NA (Not Applicable) if the table does not have a foreign key.

TABLEREFERENTIAL INTEGRITY
EMPLOYEEYES

BENEFITYES

JOBNO
PLANYES

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 name: Ch03_StoreCo
Table name: EMPLOYEE
EMP_CODEEMP_TITLEEMP_LNAMEEMP_FNAMEEMP_INITIALEMP_DOBSTORE_CODE 1Mr. Williamson John W21-May-643
2Ms. Ratula Nancy 09-Feb-692
3Ms. Greenboro Lottie R02-Oct-614
4Mrs.Rumpersfro Jennie S01-Jun-715
5Mr. Smith Robert L23-Nov-593
6Mr. Renselaer Cary A25-Dec-651
7Mr. Ogallo Roberto S31-Jul-623
8Ms. Johnsson Elizabeth I10-Sep-681
9Mr. Eindsmar Jack W19-Apr-552
10Mrs.Jones Rose R06-Mar-664
11Mr. Broderick Tom 21-Oct-723
12Mr. Washington Alan Y08-Sep-742
13Mr. Smith Peter N25-Aug-643
14Ms. Smith Sherry H25-May-664
15Mr. Olenko Howard U24-May-645
16Mr. Archialo Barry V03-Sep-605
17Ms. Grimaldo Jeanine K12-Nov-704
18Mr. Rosenberg Andrew D24-Jan-714
19Mr. Rosten Peter F03-Oct-684
20Mr. Mckee Robert S06-Mar-701
21Ms. Baumann Jennifer A11-Dec-743
Table name: STORE
STORE_CODESTORE_NAMESTORE_YTD_SALESREGION_CODEEMP_CODE
1Access Junction€792,730.0528
2Database Corner€1,123,370.04212
3Tuple Charge€779,558.7417
4Attribute Alley€746,209.1623
5Primary Key Point€2,314,777.78115
Table name: REGION
REGION_CODEREGION_DESCRIPT
1East
2West
6.For each table, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the space provided.

TABLEPRIMARY KEYFOREIGN KEY(S)
EMPLOYEEEMP_CODESTORE_CODE
STORESTORE_CODEEMP_CODE, REGION_CODE
REGIONREGION_CODENONE

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

TABLEENTITY INTEGRITY
EMPLOYEEYES
STOREYES
REGIONYES

8.Do the tables exhibit referential integrity? Answer yes or no. Write NA (Not Applicable) if the table does not have a foreign key.

TABLEREFERENTIAL INTEGRITY
EMPLOYEEYES
STORENO
REGIONYES

9.Describe the type(s) of relationship(s) between STORE and REGION. 1:* A Region can many stores
10.Create the ERD using UML...