Consider the table in first normal form (1NF) below, then answer Questions 3.4 and 3.5.

Question

Database name: Photographer_Photos
Table name: DATA_ORG_1NF
PHOTOGRAPHER
ID
PHOTOGRAPHER
NAME & SURNAME
PHOTO
ID
PHOTO
DESCRIPTION
GENRE
ID
GENRE
DESCRIPTION
A001 Thabo Ndlovu B001 Lion’s Pride C001 Wildlife
A002 Clark Grobler B002 Prada Proud C002 Fashion
A003 Sbonelo Mthaba B003 Flower Arrangement C004 Still Life Photos
A004 Teagan Boland B004 Rush Hour Traffic C003 Street Photos
A001 Thabo Ndlovu B005 Taking Flight C001 Wildlife
A003 Sbonelo Mthaba B006 Worker Protests C005 Photojournalism
A002 Clark Grobler B007 Oakley Summer 2023 C002 Fashion
A001 Thabo Ndlovu B008 Farm Life C001 Wildlife
A002 Clark Grobler B009 Antique Chair C004 Still Life Photos
Q.3.4 Normalise the table presented above to second normal form (2NF), showing all
steps with explanations. All steps and the final answer must be in dependency
diagram format.
(15)
Q.3.5 Once you have answered Question 3.4, extend your answer to normalise the
table above to the third normal form (3NF), showing all steps with explanations.
All steps and the final answer must be in dependency diagram format.

Answer 1

Q.3.4:

Step 1: Separate into two tables based on unique columns.

Table 1: PHOTOGRAPHER_PHOTOS
PHOTOGRAPHER_ID (PK)
PHOTO_ID (FK)
PHOTO_DESCRIPTION
GENRE_ID (FK)

Table 2: PHOTOGRAPHERS
ID (PK)
NAME
SURNAME

Step 2: Determine dependencies and eliminate partial dependencies.

In Table 1, the PHOTO_DESCRIPTION column is dependent on the PHOTO_ID, not on the PHOTOGRAPHER_ID. Therefore, we move it to Table 2:

Table 1: PHOTOGRAPHER_PHOTOS
PHOTOGRAPHER_ID (PK)
PHOTO_ID (FK)
GENRE_ID (FK)

Table 2: PHOTOGRAPHERS
ID (PK)
NAME
SURNAME
PHOTO_ID (FK)
PHOTO_DESCRIPTION

Step 3: Ensure all non-key attributes are dependent on the primary key.

In Table 2, there is a partial dependency between the PHOTO_ID and PHOTO_DESCRIPTION columns on the ID column. Therefore, we move PHOTO_ID and PHOTO_DESCRIPTION to a new Table 3:

Table 1: PHOTOGRAPHER_PHOTOS
PHOTOGRAPHER_ID (PK)
PHOTO_ID (FK)
GENRE_ID (FK)

Table 2: PHOTOGRAPHERS
ID (PK)
NAME
SURNAME

Table 3: PHOTOS
PHOTO_ID (PK)
PHOTO_DESCRIPTION
GENRE_ID (FK)

Dependency Diagram:

PHOTOGRAPHER_PHOTOS: {PHOTOGRAPHER_ID} → {PHOTO_ID, GENRE_ID}
PHOTOGRAPHERS: {ID} → {NAME, SURNAME}
PHOTOS: {PHOTO_ID} → {PHOTO_DESCRIPTION, GENRE_ID}

Q.3.5:

Step 1: Separate into two tables based on unique columns.

Table 1: PHOTOGRAPHER_PHOTOS
PHOTOGRAPHER_ID (PK)
PHOTO_ID (FK)

Table 2: PHOTOS
PHOTO_ID (PK)
PHOTO_DESCRIPTION
GENRE_ID (FK)

Table 3: PHOTOGRAPHERS
ID (PK)
NAME
SURNAME

Step 2: Determine dependencies and eliminate partial dependencies.

In Table 2, there is a transitive dependency between GENRE_DESCRIPTION and GENRE_ID on PHOTO_ID. Therefore, we move the GENRE_DESCRIPTION column to a new Table 4:

Table 1: PHOTOGRAPHER_PHOTOS
PHOTOGRAPHER_ID (PK)
PHOTO_ID (FK)

Table 2: PHOTOS
PHOTO_ID (PK)
PHOTO_DESCRIPTION
GENRE_ID (FK)

Table 3: PHOTOGRAPHERS
ID (PK)
NAME
SURNAME

Table 4: GENRES
GENRE_ID (PK)
GENRE_DESCRIPTION

Step 3: Ensure all non-key attributes are dependent on the primary key.

All tables are already in 3NF.

Dependency Diagram:

PHOTOGRAPHER_PHOTOS: {PHOTOGRAPHER_ID} → {PHOTO_ID}
PHOTOS: {PHOTO_ID} → {PHOTO_DESCRIPTION,GENRE_ID}
PHOTOGRAPHERS: {ID} → {NAME, SURNAME}
GENRES: {GENRE_ID} → {GENRE_DESCRIPTION}