1. Consider the following tables: AGENCY(AG_CODE, AG_NAME, AG_CONTACT) VOLUNTEER

Question

1. Consider the following tables:

AGENCY(AG_CODE, AG_NAME, AG_CONTACT)

VOLUNTEER(VOL_CODE, VOL_LNAME, VOL_FNAME, VOL_PHONE, AG_CODE)

For the tables given above, work on the problems below:

1. For each table, identify the primary key.

2. For each table, identify the foreign key(s).

3. Identify at least one candidate key. If there are no candidate keys, give an example of an attribute that could be used as a candidate key.

4. For one of the tables, give examples of a superkey and a secondary key.

5. For the given tables, explain how entity integrity and referential integrity could be violated. Give specific examples.

6. Use Visio to create a detailed Crow's Foot ERD. The diagram must include all entities, attributes, and relationships. Primary keys and foreign keys must be clearly identified on the diagram.

2. Consider the following tables:

PROJECT(PROJ_CODE, PROJ_NAME, PROJ_DESCRIPTION)

EMPLOYEE(EMP_CODE, EMP_LNAME, EMP_FNAME, EMP_TITLE, PROJ_CODE)

For the tables given above, work on the problems below:

1. For each table, identify the primary key.

2. For each table, identify the foreign key(s).

3. Identify at least one candidate key. If there are no candidate keys, give an example of an attribute that could be used as a candidate key.

4. For one of the tables, give examples of a superkey and a secondary key.

5. For the given tables, explain how entity integrity and referential integrity could be violated. Give specific examples.

6. Use Visio to create a detailed Crow's Foot ERD. The diagram must include all entities, attributes, and relationships. Primary keys and foreign keys must be clearly identified on the diagram.

Explanation / Answer

1.SOLUTION

1.AG_CODE is primary key for anency table

VOL_CODE is primary key for volunteer table

2.AG._CODE is foreign for volunter table

3.There is no candidate key beside AG_CODE & VOL_CODE as they are primary keys.

In volunteer table , candidate key can be (VOL_LNAME , VOL_FNAME , VOL_PHONE)

4.Superkey can be (AG_NAME, AG_CONTACT)

Secondary key can be AG_CODE

5.Referential integrity is violated when the relation to which a foreign key refers no longer exists

example : when the AG_CODE IS NO LONGER exists ,we cannot volunteer table to agency table.

Entity integrity is violated when primary key s invalid

example: Primary key AG_CODE Is invalid when type more 10 charater

---------------------------------------------------------x-----------------------------------------------------------

2.SOLUTION

1.PROJ_CODE is primary key for anency table

EMP_CODE is primary key for volunteer table

2.PROJ._CODE is foreign for volunter table

3.There is no candidate key beside PROJ_CODE & EMP_CODE as they are primary keys.

In volunteer table , candidate key can be (EMP_LNAME , EMP_FNAME , EMP_TITLE)

4.Superkey can be (PROJ_NAME, PROJ_DESCRIPTION)

Secondary key can be PROJ_CODE

5.Referential integrity is violated when the relation to which a foreign key refers no longer exists

example : when the PROJ_CODE IS NO LONGER exists ,we cannot volunteer table to agency table.

Entity integrity is violated when primary key s invalid

example:PROJ_CODE Is invalid when type more 10 charater.

------------------------------------------------------------------X-------------------------------------------------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.