CISP247 Ch 3 Exercise 2
Entity integrity and referential integrity are important because they are the basis for expressing and implementing relationship in the entity relationship model.
Entity integrity ensures that each row is uniquely identified by the primary key. Therefore, entity integrity means that a proper search for an existing row will always be successful.
Referential integrity means, that if the foreign key contains a value, that value refers to an existing valid row in another table. Note that a null value is possible. Therefore, referential integrity ensures that it will be impossible to assign non-existing foreign key value to a table. It also insures that you can’t delete a row in a table that is acting as a foreign key in another table.
EMPLOYEE

EMP_CODE
EMP_LNAME
EMP_FNAME
STORE_CODE
4001
Whocares
Julie
1
0500
Enoughalready
Heather
15
1110
Smith
Bobbysue
25
0002
Davis
Dave

STORE

STORE_CODE
STORE_NAME
STORE_LOCATION
STORE_REGION
1
LANSING1
Lansing
1
15
PLAINWELL
Kalamazoo
1
225
DETROIT15
Pontiac
3

REGION

REGION_CODE
REGION_DESCRIPTION
REGION_MGR
1
West Michigan
0002
2
Northern Michigan
5001
3
East Michigan
5003
1
Central Michigan
0002

Fill in the table for the tables above
Table
PK (list Column names)
FK (list Column Names)
EI (Y/N)
RI (Y/N)
EMPLOYEE
EMP_CODE,
EMP_LNAME, EMP_FNAME, STORE_CODE y n
STORE
STORE_CODE,
STORE_NAME, STORE_LOCATION, STORE_REGION n y
REGION
REGION_CODE
REGION_DESCRIPTION
n y TABLE 1

EMP_CODE
EMP_LNAME
EMP_FNAME
STORE_CODE
4001
Whocares
Julie
1
0500
Enoughalready
Heather
15
1110
Smith
Bobbysue
25
0002
Davis
Dave

TABLE 2

EMP_CODE
EMP_LNAME
EMP_FNAME
STORE_CODE
4002
Stevens
Brian
1
0500
Enoughalready
Heather
15
0003
Smith
John
15
0002
Davis
Dave

TABLE 3

Benefit_Code
Benefit_Desc
Benefit_Cost
Dental
Dental Coverage
$40
Health
Health Coverage
$70

TABLE 4

STORE_CODE
STORE_NAME
STORE_LOCATION
STORE_REGION
1
LANSING1
Lansing
1
15
PLAINWELL
Kalamazoo
1
225
DETROIT15
Pontiac
3

1) UNION on EMP_CODE , Table 1 and 2
2) INTERSECT on EMP_CODE, Table 1 and 2
3) Table 1 DIFFERENCE Table 2
4) PRODUCT Table 1 and Table 3
5) PROJECT Table 2 EMP_CODE AND STORE_CODE
6) DIVIDE Table 4 by table below results should be from the Region Column
STORE_CODE
1
15

7) NATURAL JOIN for Table 1 and Table 4
8) LEFT OUTER JOIN for Table 1 and Table 4
9) RIGHT OUTER JOIN for Table 1 and Table 4

Enter results below
1) combines duplicates
EMP_CODE
EMP_LNAME
EMP_FNAME
STORE_CODE
4001
Whocares
Julie
1
0500
Enoughalready
Heather
15
1110
Smith
Bobbysue
25
0002
Davis
Dave

4002
Stevens
Brian
1
0003
Smith
John
15
2) things that are the same
EMP_CODE
EMP_LNAME
EMP_FNAME
STORE_CODE
0500
Enoughalready
Heather
15
0002
Davis
Dave

3) – left outer join???????
EMP_CODE
EMP_LNAME
EMP_FNAME
STORE_CODE
4001
Whocares
Julie
1
1110
Smith
Bobbysue
25

4)
EMP_CODE
EMP_LNAME
EMP_FNAME