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
Oliver Armstrong, Manchester
37 minutes ago

While taking the services of Quick Assignment, I was able to get maximum grades in my academic record. I genuinely recommend my friends for availing specialised services of Quick Assignments.

Linda Jones, Southampton
2 minutes ago

It is my own personal experience that they are providing assistance perfectly for the completion of assignments. They are having highly professional, knowledgeable experts. I have got “A” grade in my research assignment just because of “Quick Assignment”. I’ m really grateful!!

Tim Walker, Cambridge
11 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!  

Stanley Myers, Cambridge
25 minutes ago

Better customer support is the crucial thing that most of the customer wants on a prior basis. Quick Assignment Services provide better and effective customer support, so customers do not have to be worried.