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
Vanessa Angel, Melbourne
34 minutes ago

I am highly pleased with the work provided by quickassignment.co.uk. The services availed from them enabled me to clear my course in an ease manner.

Tim Walker, Cambridge
31 minutes ago

I am perusing MBA; recently I was assigned with marketing assignment in which I have to perform STP analysis for a hypothetical company. It is really gruelling for me to complete my assignment due to its complex measures. I have approached quick assignment and they really provide me an optimal solution for the assignment. Hearty thankful!  

Damon Hill, Norwich
57 minutes ago

I was really quite confused regarding innovative project proposals However certainly one of my friends asked me about quick assignment. I have approached them and the solution that they have provided me is excellent as per my teacher’s view. They are providing services on-time and responding quickly to the queries.

Joe Root, London
39 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.