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
John, Liverpool
58 minutes ago

 

The work you provided was quick and on time. Would definitely recommend to my friends. Cheers!

Shomil Attari, Bristol
23 minutes ago

They are using the best vocabulary, formatting styles and language in assignments. They helped me a lot during the final paper submission. They have managed perfectly all of my assignments even they have provided me the quality of work. This is one of the best dissertations that help to provide companies as compared to others.

Joe Root, London
35 minutes ago

Their services are truly upstanding and are behind its product 100%. Great job, I will definitely be ordering again! I am really satisfied with my assignment works.

John Brunner, Wales
10 minutes ago

Quick Assignment has become a lifesaver for me when my strict time schedule did not allow me to complete my task within the desired time limit.  Thanks to the professional team of Quick Assignment to provide my assignments within time limit.