Consider the table in first normal form (1NF) below, then answer Questions 3.4 and 3.5.
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.
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}
To normalize the table in 2NF and 3NF, we need to identify functional dependencies and create separate tables for each dependency.
3.4 Normalizing to Second Normal Form (2NF):
Step 1: Identify the functional dependencies:
- PHOTOGRAPHER ID -> PHOTOGRAPHER NAME & SURNAME
- PHOTO ID -> PHOTO DESCRIPTION
- GENRE ID -> GENRE DESCRIPTION
Step 2: Create separate tables for each functional dependency:
Table 1: PHOTOGRAPHER
PHOTOGRAPHER ID (PK) | PHOTOGRAPHER NAME & SURNAME
Table 2: PHOTO
PHOTO ID (PK) | PHOTO DESCRIPTION
Table 3: GENRE
GENRE ID (PK) | GENRE DESCRIPTION
Table 4: DATA_ORG_1NF (linking table)
PHOTOGRAPHER ID (FK) | PHOTO ID (FK) | GENRE ID (FK)
The DATA_ORG_1NF table becomes a linking table that contains foreign keys for the corresponding IDs in each table.
Dependency Diagram for 2NF:
+--------------+ +---------+ +-------+
| PHOTOGRAPHER| | PHOTO | | GENRE |
+--------------+ +---------+ +-------+
|PHOTOGRAPHER ID| <------| PHOTO ID| |GENRE ID|
| NAME & SURNAME| | DESCRIPTION| |DESCRIPTION|
+--------------+ +---------+ +-------+
|
+------------------+
| DATA_ORG_1NF |
+------------------+
|PHOTOGRAPHER ID |
| PHOTO ID |
| GENRE ID |
+------------------+
3.5 Normalizing to Third Normal Form (3NF):
Step 1: Identify the transitive dependencies:
- PHOTOGRAPHER ID -> PHOTOGRAPHER NAME & SURNAME
- PHOTO ID -> PHOTO DESCRIPTION
- GENRE ID -> GENRE DESCRIPTION
Step 2: Normalize the tables to remove transitive dependencies:
Table 1: PHOTOGRAPHER
PHOTOGRAPHER ID (PK)
Table 2: PHOTOGRAPHER_NAME
PHOTOGRAPHER ID (PK) | PHOTOGRAPHER NAME & SURNAME
Table 3: PHOTO
PHOTO ID (PK)
Table 4: PHOTO_DESCRIPTION
PHOTO ID (PK) | PHOTO DESCRIPTION
Table 5: GENRE
GENRE ID (PK)
Table 6: GENRE_DESCRIPTION
GENRE ID (PK) | GENRE DESCRIPTION
Table 7: DATA_ORG_1NF (linking table)
PHOTOGRAPHER ID (FK) | PHOTO ID (FK) | GENRE ID (FK)
Dependency Diagram for 3NF:
+--------------+ +--------------------+
| PHOTOGRAPHER| |PHOTOGRAPHER_NAME|
+--------------+ +--------------------+
| PHOTOGRAPHER ID | ------> | PHOTOGRAPHER ID |
+--------------+ | NAME & SURNAME |
+--------------------+
|
+--------------+
| PHOTO |
+--------------+
| PHOTO ID |
+--------------+
|
+------------------+
| PHOTO_DESCRIPTION |
+------------------+
| PHOTO ID |
| DESCRIPTION |
+------------------+
|
+---------+
| GENRE |
+---------+
| GENRE ID |
+---------+
|
+-------------------+
| GENRE_DESCRIPTION |
+-------------------+
| GENRE ID |
| DESCRIPTION |
+-------------------+
|
+------------------+
| DATA_ORG_1NF |
+------------------+
|PHOTOGRAPHER ID |
| PHOTO ID |
| GENRE ID |
+------------------+
This diagram shows that each table has non-transitive dependencies, and the relationships between the tables are preserved through foreign key references.