1. For each table, identify the primary key and the foreign key(s). If a table d
ID: 3787827 • Letter: 1
Question
1. 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.
2. Do the tables exhibit entity integrity? Answer yes or no and then explain your answer.
3. Do the tables exhibit referential integrity? Answer yes or no and then explain your answer.
4. Write NA (Not Applicable) if the table does not have a foreign key.
5.Describe the type(s) of relationship(s) between STORE and REGION.
6.Create the ERD (Crow’s Foot) to show the relationship between STORE and REGION.
7.Describe the type(s) of relationship(s) between EMPLOYEE and STORE.
Table name: EMPLOYEE Database name: Ch03 Store Co EMP CODE EMP TITLE EMPLNAME EMP FNAME EMPLINITIAL EMPIDOB STORE CODE Williamson John 1 Mr 21-May-64 2 Ms. Ratuala Nancy 09-Feb-69 2 3 Ms. Greenboro Lottie R 02-Oct-61 4 4 Mrs. Rumpersfro Jennie S 01-Jun-71 5 5 Mr. Smith Robert L 23-Nov-59 3 6 Mr. Renselaer Cary A 25-Dec-65 1 7 Mr. allo Roberto S 31 Jul-62 3 8 Ms. Johnsson Elizabeth 10-Sep-68 1 9 Mr. Eindsmar Jack W 19-Apr-55 2 10 Mrs. Jones Rose R 06-Mar-66 4 21-Oct-72 12 Mr. Washington Alan Y 08-Sep-74 2 13 Mr. Smith Peter N 25-Aug-64 3 14 Ms. Smith Sherry H 25-May-66 4 15 Mr. Olenko Howard U 24-May-64 5 16 Mr. Archialo Barry V 03-Sep-60 5 17 Ms. Grimaldo Jeanine K 12-Nov-70 4 18 Mr. Rosenberg Andrew D 24-Jan-71 4 19 Mr. Rosten Peter F 03-Oct-68 4 Mokee Robert S 06-Mar-70 1 20 Mr 21 Ms. Baumann Jennifer A 11-Dec-74 3 Table name: STORE STORE CODE STORE NAME STORE YTD SALES REGION CODE EMP CODE 1003455.76 1 Access Junction 2 12 1421987.39 2 Database Corner 3 Tuple charge 986783.22 1 2 3 944568.56 4 Attribute Alley 5 Primary Key Point 2930098.45 1 15 Table name: REGION REGION CODE REGION DESCRIPT 1 East 2 WestExplanation / Answer
Solutions:
1.
TABLE
PRIMARY KEY
FOREIGN KEY(S)
EMPLOYEE
EMP_CODE
STORE_CODE
STORE
STORE_CODE
REGION_CODE, EMP_CODE
REGION
REGION_CODE
NONE
2.
TABLE
ENTITY INTEGRITY
EXPLANATION
EMPLOYEE
Yes
Each EMP_CODE value is unique and there are no nulls.
STORE
Yes
Each STORE_CODE value is unique and there are no nulls.
REGION
Yes
Each REGION_CODE value is unique and there are no nulls.
3,4
TABLE
REFERENTIAL INTEGRITY
EXPLANATION
EMPLOYEE
Yes
Each STORE_CODE value in EMPLOYEE points to an existing STORE_CODE value in STORE.
STORE
Yes
Each REGION_CODE value in STORE points to an existing REGION_CODE value in REGION and each EMP_CODE value in STORE points to an existing EMP_CODE value in EMPLOYEE.
REGION
NA
5.Because REGION_CODE values occur more than once in STORE, we may conclude that each REGION can contain many stores. But since each STORE is located in only one REGION, the relationship between STORE and REGION is M:1. (It is, of course, equally true that the relationship between REGION and STORE is 1:M.)
TABLE
PRIMARY KEY
FOREIGN KEY(S)
EMPLOYEE
EMP_CODE
STORE_CODE
STORE
STORE_CODE
REGION_CODE, EMP_CODE
REGION
REGION_CODE
NONE
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.