Question

Research and explain with your own words Chen’s entity-relationship data (ERD) model and the process of normalization from first normal form to third normal form. Discuss what types of information are stored in your sector (or sector of interest) and what types of information are shared with a third party (if any).

Answer 1

In an entity relationship model , entity can be an object or concept or people about which we want to store information. It is denoted in rectangles. There can be a weak entity which actually depends upon the existence of main entity as it cannot be identified with its own attributes. An action which is represented in diamond shape depicts the relationship between two entities. An entity or relationship can have several characteristics or attributes which are shown in oval shape. Peter Chen is credited with developing this ER model for database design.

For example a developer is an entity who creates a website and hence develoler represented in rectange. The action he does is creating a website and hence diamond shape would represent creates and the other two entities would be website and customer both again in rectangle shape. Customer takes action by visiting the website hence the relationship between website and customer represented in diamond shape would be visits.

Now Normalization in an entity relationship model is one where an entity is identified which has repeated similar attributes. These repeated attributes are clubbed together and a new entity is created for them. Now for these new entities an unique attribute is identified and it is linked with the original entity as the new entities cannot stand on their own. Normalization helps in removing anomalies.

First normal form is where a relation meets the criteria of relation. The attributes of an entity becomes the attributes of relation and each entity becomes a relation. A relation which has unique attribute name (column name) and no repeating rows where each column has single value qualifies for first normal form. If a key is defined for the relation then it meets the unique row requirement. A relation is in second normal form when its non key attributes are dependent on the key attribute and is free from partial key dependencies. A relation is in third normal form when it is in second normal form and there are no transitive dependencies.

Process of normalization:

First normal form : Example a table or relation contains the following:

Course	content
Accounting	Balance sheet, stock markets
Programming	Java

Here the attributes have more than one value hence in first normal form it is rearranged as follows:

Course	content
Accounting	Balance sheet
Accounting	Stock market
Programming	Java

Second Normal form: In a purchase order table

Customer ID	Item ID	Item name
1	1	Detergent
2	2	Apparel

Here the customer ID and item ID are primary key and the item name is non key attribute which should depend on both primary key but it can also be identified sokely by item ID hence there is a partial key dependency which can be eliminated by breaking the original table into two tables one table with customer and item id and other with Item ID and Item name.

Third normal form: In case of student zip code table student name is dependent on student ID and zip code is dependent on city which is in turn dependent on student ID and name hence there is transitive dependency . The solution would be to split into two tables where one table contains student ID , name and city and other one contains zip and city code.

The types of information stored in our audit sector organization are Client details, Audit files through Ids, projects, employees and departments. Most of these information is confidential and is not shared with third party.