IT201 Unit#8 Class Assignments I and II

Please put all answers in red color font.

Scenario:

Your team is going to going to troubleshoot queries between different tables in the following database (all columns for each table are listed below):

Table A:

Primary Key: PKA

No foreign keys and no other columns.

Table B:

Primary Key: PKA and PKC (a composite key)

Foreign Key: PKA (references primary key PKA from Table A)

Foreign Key: PKC (references primary key PKC from Table C)

No other columns.

Table C:

Primary Key: PKC

No foreign keys and no other columns.

Table D:

Primary Key: PKC and PKE (a composite key)

Foreign Key: PKC (references primary key PKC from Table C)

Foreign Key PKE (references primary key PKE from Table E)

PKAA (just an ordinary column -not part of any keys)

Table E:

Primary Key: PKE

No foreign keys and no other columns.

Activity I Relationship Types (5 points total)

#1 Assuming there are no 1:1 (one to one) relationships and no additional tables in the database identify the many side of each relationship (which table would have the crows foot symbol on it in an ERD diagram) between the two tables specified in each scenario:

The relationship between Table A and Table B -is it Table A or Table B? (1 point)

Answer (enter in red ):

The relationship between Table B and Table C -is it Table B or Table C? (1 point)

Answer (enter in red ):

The relationship between Table C and Table D -is it Table C or Table D? (1 point)

Answer (enter in red ):

The relationship between Table D and Table E -is it Table D or Table E? (1 point)

Answer (enter in red ):

#2 Are there any Intersection tables here? If so which ones? Are there an Association tables here? If so which ones? (1 point) Answer (enter in red ):

Activity II Joins (5 points total)

#1 If you wanted to create a query that showed related data from Table A and Table E, how many tables would have to be included in the query? (1 point) Answer (enter in red ):

#2 Identify all of the join condition(s) necessary to make sure the query in #1 does not create a cross-join. Use qualified column names using dot notation. (4 points)

For example, suppose instead of Tables A, B, C, D, and E suppose you had just two tables X and Y:

Table X:

Primary Key: PKX

No foreign keys.

Table Y:

Primary Key: PKY

Foreign Key: PKX (references primary key PKX from Table X)

In order to get related records from Table X and Table Y you would need the following join condition (using qualified column names and dot notation):

X.PKX = Y.PKX

Put your answer for Tables A, B, C, D, and E here

Answer (enter in red ):