Recent Questions and Assignment Topics

ITC556_201960_3_Assessment 3_Normalization

Solution

Task Title: Database Systems Assignment 3

Subject Code: ITC556_201960

University: Charles Sturt University

Objective: Main objective of this assignment to get familiar with the Database designing aspects like Normalization, ER Diagram designing, Functional Dependency identification etc.

 

Overview: For this task, a sample dataset is given in the form of a table. From the given table, student suppose to derive the data entities. After the entity identification, students must perform Entity relationship diagram (ERD) generation, Dependency diagram drawing and relation normalization up to a third normal form (3NF).

 

Required Environment:

  • For the drawing of the database designing diagrams draw.io online designing tool is required.

Steps of the Assignment:

  • Designing the initial Dependency Diagram: This is entirely done based on the given table. Only one relation is derived from the entire table.
  • Partial dependency removal (2NF conversion): As there was no multivalve attribute, so the relation is directly converted into 2NF by removing the partial dependency.  
  • Transitive dependency removal (3NF-conversion): 2NF relation can be converted to the 3NF form by removing all transitive dependencies.
  • Representation of the normalized relationship using ERD: Entity Relationship Diagram is used to represent the entire normalized relationship. Crowfoot notation are used in the ERD.

 

Structure of the given sample data:

Attribute Name

Sample Value

Sample Value

Sample Value

Sample Value

EMP_NUM

123

104

118

 

PROF_RANK

Professor

Asst. Professor

Assoc. Professor

Assoc. Professor

EMP_NAME

Ghee

Rankin

Ortega

Smith

DEPT_CODE

CIS

CHEM

CIS

ENG

DEPT_NAME

Computer Info.

Systems

Chemistry

Computer Info.

Systems

English

PROF_OFFICE

KDD-567

BLF-119

KDD-562

PRT-345

ADVISEE

1215, 2312, 3233,

2218, 2098

3102, 2782, 3311, 

2008, 2876, 2222,

3745, 1783,

2378

2134, 2789, 3456,

2002, 2046, 2018,

2764

2873, 2765, 2238,

2901, 2308

COMMITTEE_CODE

PROMO, TRAF

APPL, DEV

DEV

SPR, TRAF

PROMO, SPR

DEV

JOURNAL_CODE

JMIS, QED,

JMGT

 

JCIS, JMGT

 

 

Figure 3: ERD of the Normalized Relationship

 

 

Latest Reviews
Jhony Braistow, Bristol
59 seconds ago

I am highly satisfied with the assignment work of Quick Assignment. They assured me for providing quality assignment services before deadline and customize my assignment according to my specifications.

Stanley Myers, Cambridge
31 minutes ago

 Electrical Engineering I thank Assignment Service Leeds for helping me get great grades by providing a professional research report.

Giles Clarke, London
50 minutes ago

I am really thankful for your help; I am lucky to find this website as they are providing excellent services.

Oliver, Bristol
30 minutes ago

 

Great work! Well done! Thank you for your guidance!