KayeILSQL-02-Data Modeling and Normalization

Contents

Based On

Entity Relationship(ER) Model

• What is a Model?
• What is an Entity Relationship(ER) Model?
• Why do we Need an ER Model?
• What are the Benefits of an ER Model?
• What are the Building Blocks of an ER Model?
• Entity
• Attribute
• Relationship

Business Narrative and Data Model

• How Do We Start Creating an ER Model?
• Business Narrative
• Create an ER Model From a Business Narrative
• Things of Significance >> ENTITIES
• Details of the Things of Significance >> ATTRIBUTES
• RELATIONSHIPS Between ENTITIES
• Business Narrative - European Parliament
• From a Business Narrative to an ER Model
• The Fictitious Add-On Business Narrative
• ER Model of the COUNTRY and the POLITICAL GROUP
• Entity Diagramming Conventions
• Relationship Diagramming Conventions
• Connectivity of a Relationship
• Cardinality of a Relationship
• Optionality of a Relationship
• Formula to Read an ER Diagram
• Attribute Diagramming Conventions
• Attribute Representation
• Attribute Names
• Attribute Optionality
• Entity Class/Type and Entity Instance
• Entity Class/Type
• Entity Instance
• Unique Identifier
• Domain

Dependency

• Key Attributes
• Non-Key Attributes
• Functional Dependency
• Sample Entity for Learning Purposes
• Which Attribute makes up the Primary Key of the Sample Entity?
• Is "pg code" the Primary Key?
• Is "country code" the Primary Key?
• The Composite Primary Key
• Types of Dependency
• Partial Dependency
• Partial Dependency on "pg code"
• Partial Dependency on "country code"
• Full Dependency
• Transitive Dependency

Redundancy and Anomalies

• Redundancy of Data
• "pg code" related Redundancy
• "country code" related Redundancy
• How can Redundant Data cause Huge Problems in Databases?
• Anomalies
• Deletion Anomaly
• Insertion Anomaly
• Entities in Lower Form

Normalization

• Normalization-Definition
• Three Stages of Normalization
• First Normal Form (1NF)
• 1NF Conditions
• Conversion from Lower Form to 1NF
• An Entity Not Yet in 1NF
• Entity POLITICAL GROUP in 1NF
• Entity MEP Not Yet in 1NF
• Entity MEP in 1NF
• Entity ROLE in 1NF
• Composite Entity
• Second Normal Form (2NF)
• 2NF Conditions
• Conversion from 1NF to 2NF
• EP SEATS
• POLITICAL GROUP / MEP / ROLE
• Third Normal Form (3NF)
• 3NF Conditions
• Conversion from 2NF to 3NF
• EP SEATS 1 / EP SEATS 2 / EP SEATS 3
• POLITICAL GROUP / MEP / ROLE

Summary of the ER Modeling Process

Our ER Model of the European Parliament

• Important Notes on Our ER Model
• Our ER Model of the EP—in Third Normal Form(3NF)

The Story of the European Union

References

The European Parliament (EP) is the parliamentary institution of the European Union (EU).

The European Parliament is elected by the citizens of the European Union to represent their interests.

Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.

Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the European Union's nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.

The European Parliament model and data used in this SQL text, are based on the elections which were held in June 2004. The European Parliament of 2004 elections had 785 members from all 27 EU countries.

The sources for the data, which are all freely obtainable from the European Union website, and from several leaflets of the EU institutions, are listed in the References section.

These sources are used to create the fictitious Business Narrative of the "Data Modelling and Normalization" Chapter.

We called this Business Narrative; "the Story of the European Union" which explains about the EU and EP, for purposes of Data Modeling.

The Story of the European Union is the last Chapter of Kaye is Learning SQL.

To design a database, we follow a method or a technique called Entity Relationship Modeling.

Throughout the text, we use the abbreviation ER for Entity Relationship.

A model is a simplified representation, which is used to explain a real world system or a real world event.

From Wikipedia - The Free Encyclopedia

• A Model is a pattern, a plan, a representation, especially a representation in miniature.
• A Model is a description designed to show the main object or workings of an object, system or concept.

From Wiktionary - a wiki based Open Content Dictionary

A Model is a simplified representation used to explain the workings of a real world system or event. For example, a Computer Weather Model which predicts the path of the hurricane.

A model is a structural design of a complex system. For example, a Business Model.

A Model is a miniature representation of a physical object. For example, a Model of a World War II Fighter Plane.

An ER Model separates "the information required by a business" from "the activities/processes performed within a business".

Although businesses can change their activities, the type of information they need to keep tends to remain unchanged.

We need an ER Model to specify the information needs of our organization.

The objective is "to produce a model;

• that can be understood by the Business Users
and at the same time,
• contains sufficient information for the IT Staff to build a Database System.

Please remember we have mentioned in the previous chapter (Relational Approach), that;

a Business User can be:

• a Director
• a Manager
• a Project Leader
• an Accountant
• a Call Center Staff
• a Warehouse Staff
• a Shipping Staff
• that is, anybody who is responsible of doing at least one piece of work in their workplace.

an IT Staff can be:

• a Business Analyst
• a Database Designer
• an Application Developer
• a Database Administrator
• a Reports Developer
• and so on...

An ER Model must be clear enough to be understood by the Business Users, and it should hold enough information fot the IT Staff to be able to build a Database System.

An ER Model documents information for the organization in a clear, precise format.

An ER Model provides an easily understood picture of the database.

The building blocks of an ER Model are;

• Entities
• Attributes
• Relationships

We have already briefly learned about Entities, Attributes and Relationships in the previous chapter (Relational Approach), but now we will examine them once more within the context of the Entity Relationship Model.

• An Entity is a thing of importance in our organization.
• An Entity is a discrete category.
• An Entity is a thing of significance about which our organization needs to hold information, for example 'Member of the European Parliament' or 'MEP' .

• An Attribute is any characteristic of an Entity.
• An Attribute holds specific pieces of information that needs to be known about an Entity.
• For example 'name of the MEP', 'country of the MEP', 'political group of the MEP'.

• A Relationship is a named significant association between two Entities.
• A Relationship is binary. It is always an association between;
  • two Entities
  or
  • an Entity and itself

When an Entity has a Relationship with itself, there is a recursive relationship, and the Entity is called a 'Self-Referencing Entity'.

To create an ER Model, we start by creating a 'Business Narrative'.

The 'Business Narrative' will tell us;

"the pieces of information required for the business of our organization to function today and in the future".

Using this Business Narrative, we will;

• derive Entities
• derive their Attributes
• derive their Relationships
• then we will build up an Entity Relationship Model of Our Business

I am a Business Analyst working in the IT Organization of the European Union. One of my team's main tasks these days is to create a Business Information System that can be used by all the employees and users of my organization.

• To be able to build a Business Information System, we need a Database.
• To be able to build a Database, we need a Database Design.
• To be able to create a Database Design, we need a Data Model of the Business.
• Therefore,we must start by building the Data Model of Our Business.
• To be able to build a Data Model of Our Business, we need to get together the Business Narrative of Our Organization - that is, we need the Story of the European Union.

The best way to learn this story, that is, the Story of the European Union is, to listen to the employees who work in our organization.

ER Models are derived from Business Narratives or Business Specifications.

The Business Narrative describes Things of Significance.

These Things of Significance are mapped into ENTITIES of the ER MODEL.

The Business Narrative also describes the Details of the Things of Business Significance.

These detailed descriptions are mapped into the ATTRIBUTES of the ER MODEL.

The Business Narrative describes the RELATIONSHIPS between ENTITIES as well.

We listened to the story of the European Union as told by the employees in the EU Institutions. We will now concentrate on the European Parliament which is sufficient enough for our purposes of learning about the Elementary Basics of a Database.

The Business Narrative says:

The European Parliament is the parliamentary institution of the European Union. The European Parliament (EP) is elected by the citizens of the European Union to represent their interests. Its origins go back to the 1950s and the founding Treaties. Since 1979 its members have been directly elected by the citizens of the EU.

Elections are held every five years, and every EU citizen is entitled to vote, and to stand as a candidate, wherever they live in the EU. Parliament thus expresses the democratic will of the Union’s nearly 500 million citizens and it represents their interests in discussions with the other EU institutions.

The latest elections were in June 2004. Parliament has 785 members from all 27 EU countries.

...

...

Members of the European Parliament (MEPs) do not sit in national blocks, but in EU-wide political groups. Between them, they represent all views on political issues and European integration, from the strongly pro-federalist to the openly Eurosceptic. A political group comprises Members elected in at least one fifth of the EU countries and has a minimum of 20 Members.

There are currently seven political groups in the European Parliament. Members who do not belong to any of the groups are known as 'non-attached Members — non-inscrits (NI)'. Political groups have their own staff and the Members have parliamentary assistants.

...

...

As well as telling about the structure and workings of the European Parliament, the employees in the EP added on the following:

We want to track some basic information on the EU COUNTRIES. For each EU COUNTRY, we want to track information like country name, capital city, population, area, gdp, purchasing power standard and so on. We would like to track information on not only the current member countries but also on candidate countries, and some other countries about which statistical data is held by EUROSTAT — the statistical office of the European Communities. We call this the status of the country.

We want to track some basic information on the POLITICAL GROUPs also. For each POLITICAL GROUP, we want to track the political group name, location, address, telephones, e-mails, website information as well as a description of the aim of the POLITICAL GROUP. We would like to track information on the MEMBERS OF THE EUROPEAN PARLIAMENT also. We have to track their names, e-mails, telephones, salaries and so on.

Most of the MEMBERS OF THE EUROPEAN PARLIAMENT(MEPs) have more than one task they perform. For example, an MEP can be a Chair or a Co-Chair or a Vice-President or a Queastor and at the same time, an MEP takes part in several Commissions and Delegations. For example, an MEP can be a Member in one Delegation, and a Chair in another Delegation or a Commission. We need a multiplier for each of these roles. The role multiplier helps us to calculate the total salary of an MEP. We would like to track all these roles.

A POLITICAL GROUP of the EP comprises Members elected in at least one fifth of the EU COUNTRIES and has a minimum of 20 MEMBERS OF THE EUROPEAN PARLIAMENT(MEPs).

An EU COUNTRY may have many MEMBERS OF THE EUROPEAN PARLIAMENT(MEPs) who belong to various POLITICAL GROUPs of the EP.

However, a MEMBER OF THE EUROPEAN PARLIAMENT(MEP) must belong to one and only one POLITICAL GROUP in the European Parliament and an MEP is a citizen of one and only one EU COUNTRY.

• Each of the Boxes in the diagram above is an Entity, and the line between them is a Relationship.
• The line has a Forked Ending (Many) on both sides, indicating a Many to Many Relationship.
• The line on the Left-Hand-Side is a Dashed Line, showing that the Relationship is Optional .
• The line on the Right-Hand-Side is a Solid Line, showing that the Relationship is Mandatory.

The relationship can be read from Left to Right to tell us that:

• The phrase MAY have is used to tell us that this is an Optional Relationship.

The relationship can be read from Right to Left to tell us that:

• The phrase MUST comprise of is used to tell us that this is a Mandatory Relationship.

As we can see in the "ER Model of the COUNTRY and the POLITICAL GROUP" of the previous section, there are some Conventions we must follow when we draw/create Entity Diagrams.

These Conventions are summarized below.

Summary of Entity Diagramming Conventions

• An Entity is represented diagrammatically by a SOFTBOX, i.e. a rectangle with rounded corners, with a name. The name of the Entity in this figure is EU COUNTRY.

• The SOFTBOX can be of any size or shape, sufficient enough to hold the Entity and Attribute names in it and the Relationship lines attached to it.

• Entity name must be in the Singular and shown in Upper Case. For example, EU COUNTRY or EP POLITICAL GROUP.

• Entity name must be unique – we cannot have another entity with the same name in the same model.
• A Synonym is an alternate name for an entity and is represented within brackets next to the entity name. For example, ROLE(POSITION).
• The Name for an Entity must be one that represents a Class or a Type and NOT an Instance of that Entity. For example, 'BELGIUM' or 'POLAND' could not be names for Entities – the Entity is EU COUNTRY and 'BELGIUM' and 'POLAND' are two Instances of this Entity.

• A Relationship is represented by a Line that joins two Entity Softboxes together, or a Relationship recursively joins One Entity Softbox to itself.
• Each Relationship has a lowercase name placed near the appropriate end.

One of the types of Relationships between Entities can be classified as follows:

• 1:1 (One-to-One)
• 1:M (One-to-Many)
• M:M (Many-to-Many)

These types of Relationships between Entities are called Connectivity or Multiplicity.

At the Many (One or More) end, the relationship line joins a Softbox at three points, known as a "crowsfoot" or a "triangle".

At the One (One and Only One) end, the line joins a Softbox at one point or sometimes alternatively with vertical lines.

Connectivity of a Relationship

The Relationship between Two Entities can be given using the lower and upper limits .

This information is called the Cardinality.

The Cardinality is written Next to Each Entity in the form (m,n), where:

• m is the minimum number
• n is the maximum number

An example is shown diagrammatically below.

Each EU Country may have many MEPs who belong to many Political Groups of the EP.

Each Political Group of the EP must comprise at least 20 MEPs who are elected in at least one fifth of the EU Countries.

Currently there are 27 EU Member countries and one fifth is approximately five. Thus the minimum Cardinality for an EU COUNTRY is 5.

Each relationship has an Optionality.

Mandatory Relationship

Where a Relationship End is Mandatory, a Solid Line is drawn for that half of the Relationship.

Optional Relationship

Where the Relationship End is Optional, a Broken or Dashed Line is drawn for that half of the Relationship.

An Optional Relationship can occur in the following Connectivities:

• 1:1 (One-to-One)
• 1:M (One-to-Many)
• M:M (Many-to-Many)

An Optional Relationship can also occur on ONE OR BOTH SIDES of the Relationship.

Let's have a look at the ER Diagram of the EU COUNTRY and the EP POLITICAL GROUP we have seen earlier.

This ER Diagram has an Optional Relationship on one end, and a Mandatory Relationship on the other end:

Reading from Left to Right:

Reading from Right to Left:

Using the Conventions given so far, we come up with a Formula to Read an ER Diagram.

This is how we Read the ER Diagram above:

From Left to Right

From Right to Left

Attribute Representation

An Attribute is represented by writing its name;

• in the Singular
and
• in Lower Case

Attribute Names

• Attribute Names Do Not Have To Be Unique in the Same Model.
• However, Attribute Names Have to be Unique within an Entity.
• For example, two Entities in the Same Model can both have Attributes named "email".
• However, an Entity Cannot Have two Attributes named "email".

Attribute Optionality

Each attribute has an Optionality. The Optionality of an attribute is identified by using an Attribute Tag.

• Mandatory Attributes
  • A value MUST BE KNOWN for each Entity occurrence.
  • The Attribute Name is tagged with an asterisk "*".
• Optional Attributes
  • A value MAY BE KNOWN for each Entity occurrence.
  • The Attribute Name is tagged with an "o".

The Definitions we have looked at so far for Entity, Relationship and Attribute are all Definitions that represent a Class or a Type – Not an Instance.

We will now see the difference between an Entity Class and an Entity Instance.

• An Entity Class is a collection of items that share common properties or characteristics.
• Each Entity Class in an ER Model is given a name.
• Since the name represents a set of items, it is always Singular.
• An Entity Class can also be called an Entity Type.

Below is an Entity Class in our Data Model:

An Entity Instance is a single occurrence of an Entity Class.

There is only one Entity Class of EU COUNTRY, whereas there are twenty-seven Instances of this Entity.

Below are some of the Instances of the Entity Class EU COUNTRY.

Please note that not all attributes are displayed.

• In an Entity Class, each Entity Instance MUST BE UNIQUELY IDENTIFIABLE.
• Each Instance of the Entity MUST BE SEPARATE and DISTINCTLY IDENTIFIABLE from All Other Instances of that Entity Class.

The Unique Identifier (UID) May Be:

• An Attribute
• A Combination of Attributes
• A Combination of Relationships
• A Combination of Attribute(s) and Relationship(s)

Unique Identifier Diagramming Conventions

• Tag each Attribute that is part of the UID with a number sign "#".
• Place a bar (|) to indicate that a Relationship is part of the Entity's UID.
• A Relationship included in a UID
  • Must be Mandatory
  and
  • Must Be One and Only One
  in the direction that participates with the UID.

This is also explained in the Composite Entity section of Normalization .

A Domain is the Set of Values that may be assigned to an Attribute.

Example:

We can define the Domain for the Values of the Attribute 'role title' of the entity ROLE can take.

Example:

For the "salary" attribute of the MEP entity, we can set a Minimum Salary Value and a Maximum Salary Value.

For example, the minimum salary value = 10000, and the maximum salary value = 30000.

These Minimum and Maximum values define a Range for the Salary.

Example:

Let's say we have an entity which holds details of the European Institutes in our Data Model, called EU INSTITUTES.

"institute_name" is one of the attributes of this entity.

• Normalization is a technique which REDUCES "Redundancy/Duplication of Data".

• Higher Redundancy means; Entities are in Lower Form.
• Lower Redundancy means; Entities are in Higher Normal Form.

• Normalization is a Decomposition Process to Split Entities.
• The Splitting is performed carefully so that, No Information is Lost.

Normalization is basicallly achieved by going through Three Stages of Database Design:

We will now investigate the "Three Stages of Normalization" in the following sections.

1 NF - Condition #1

• The Primary Key is defined.
• This includes a Composite Key, if a Single Attribute Cannot Be Used as a Primary Key.

1 NF - Condition #2

• All Non-Key Attributes show Functional Dependency on the Primary Key Components/Key-Attributes.

Another way of saying this, is;

• In a 1NF Entity, No Attributes Exist that have No Functional Dependency on the Primary Key Attributes.
• All Non-Key Attributes in a 1NF Entity are
  • either Fully Dependent
  • or Partially Dependent
  • or Transitively Dependent
  on the Key-Attributes.

1 NF - Condition #3

• In a 1NF Entity, All Attributes must be Single-Valued , i.e. Attributes MUST NOT BE Repeated .
• An Entity May Only Have One Attribute to Represent a Specific Characteristic of that Entity.
• If Multiple/Repeating Attributes are Essential to Represent a Specific Characteristic, then We Must Create a New Entity to hold them.
• This New Entity which holds the Multiple/Repeating Attributes, will have a Many to One Relationship to the Original Entity.
In the section "Conversion from Lower Form to 1NF" , we will see an Example of an Entity with Repeating Attributes .
• We will create a New Entity which Holds the Repeating Attributes.
• We will Join this New Entity with a Many to One Relationship to the Original Entity .
• Thus, the Original Entity will be Converted to 1NF from a Lower Form .

We will now look at an Entity which is Not Yet in 1NF.

Our (Fictitious Add-On) Business Narrative said:

Using this Business Narrative, we created the following attributes within the entity POLITICAL GROUP:

There seems to be a lot of Repeating Attributes in the entity POLITICAL GROUP.

...
...

We can see the Repeating Attributes of the entity POLITICAL GROUP above, and also in the Entity Attributes below.

It is clear that the entity POLITICAL GROUP is NOT YET in 1NF.

It Does Not Satisfy Condition #3 of 1NF.

Entity POLITICAL GROUP is still in Lower Form.

We will now Convert the entity POLITICAL GROUP into 1NF from the Lower Form it is in.

This is what we must do:

• STEP 1: We will Remove the Repeating Attributes from the entity POLITICAL GROUP.
• STEP 2: We will create a New Entity (MEP) which Holds these Repeating Attributes.
• STEP 3: We will Join this New Entity (MEP) with a Many to One Relationship to the Original Entity — which is the POLITICAL GROUP.
• STEP 4: Thus, the Original Entity — POLITICAL GROUP will be Converted into 1NF from a Lower Form .

• Relationship From Left to Right

Each MEP MUST belong to One and Only One POLITICAL GROUP.

• Relationship From Right to Left

A POLITICAL GROUP MUST comprise MEPs elected in at least one fifth of the EU COUNTRIES and has a minimum of 20 MEPs.

Summary of the Conversion Process of POLITICAL GROUP from Lower Form to 1NF:

• STEP 1: We Removed the Repeating Attributes from POLITICAL GROUP.
• STEP 2: We created a New Entity called MEMBER OF THE EUROPEAN PARLIAMENT (MEP), and placed the Repeating Attributes in this new entity MEP.
• STEP 3: We defined a MANY TO ONE Relationship between MEP and POLITICAL GROUP.
• STEP 4: Entity POLITICAL GROUP is now in 1NF.

Entity POLITICAL GROUP is now in First Normal Form (1NF):

• 1 NF - Condition #1 The Primary Key is defined.
• 1 NF - Condition #2 All Non-Key Attributes are Functionally Dependent on the Primary Key.
• 1 NF - Condition #3 No Repeating Attributes exist.

The entity MEP still has some Repeating Attributes in it.

These Repeating Attributes are the different roles an MEP (Member of the Parliament) may have.

In fact, we have more information on each of these different roles like "id of a role", "code of a role", "title of a role", "salary of a role" and so on ...

Here they are;

There seems to be a lot of Repeating Attributes in the entity MEP.

We can see the Repeating Attributes of the entity MEP above, and also in the Entity Attributes below.

It is clear that the entity MEP is NOT YET in 1NF.

It Does Not Satisfy Condition #3 of 1NF.

Entity MEP is still in Lower Form.

We will now Convert the entity MEP into 1NF from the Lower Form it is in.

This is what we must do:

• STEP 1: We will Remove the Repeating Attributes from the entity MEP.
• STEP 2: We will create a New Entity (ROLE) which Holds these Repeating Attributes.
• STEP 3: We will Join this New Entity (ROLE) with a Many to One Relationship to the Original Entity — which is MEP.
• STEP 4: Thus, the Original Entity — MEP will be Converted into 1NF from a Lower Form .

• Relationship From Left to Right

Each ROLE MUST be held by One and Only One MEP.

• Relationship From Right to Left

Each MEP MUST have One or More ROLES.

Summary of the Conversion Process of MEP from Lower Form to 1NF:

• STEP 1: We Removed the Repeating Attributes from MEP.
• STEP 2: We created a New Entity called ROLE, and placed the Repeating Attributes in this new entity ROLE.
• STEP 3: We defined a MANY TO ONE Relationship between ROLE and MEP.
• STEP 4: Entity MEP is now in 1NF.

Entity MEP is now in First Normal Form (1NF):

• 1 NF - Condition #1 The Primary Key is defined.
• 1 NF - Condition #2 All Non-Key Attributes are Functionally Dependent on the Primary Key.
• 1 NF - Condition #3 No Repeating Attributes exist.

During the process of Converting entity MEP from Lower Form to 1NF , entity ROLE has also become an entity in First Normal Form — 1NF.

• 1 NF - Condition #1 The Primary Key is defined.
• 1 NF - Condition #2 All Non-Key Attributes are Functionally Dependent on the Primary Key.
• 1 NF - Condition #3 No Repeating Attributes exist.

We now come across a new concept called Composite Entity or Associative Entity.

Let's have a look back at STEP 3: Join ROLE to MEP with a MANY TO ONE Relationship of the previous section.

We have joined ROLE to MEP with a MANY-TO-ONE Relationship.

The Relationships read as follows:

• Relationship From Left to Right

Each ROLE MUST be held by One and Only One MEP.

• Relationship From Right to Left

Each MEP MUST have One or More ROLES.

These Relationships mirror our Business Narrative:

"...

Most of the MEMBERS OF THE EUROPEAN PARLIAMENT(MEPs) have more than one task they perform. For example, an MEP can be a Chair or a Co-Chair or a Vice-President or a Queastor and at the same time, an MEP can take part in several Commissions and Delegations.
For example, an MEP can be a Member in one Delegation, and a Chair in another Delegation or a Commission. We need a multiplier for each of these roles. The role multiplier helps us to calculate the total salary of an MEP. We would like to track all of these roles.

..."

It is obvious from our Business Narrative that one role can be held by more than one MEP.

For instance, there will be more than one Co-Chair, or more than one Queastor, or so on among the Member of the Parliaments.

Hence, the Relationship must be MANY-TO-MANY (M:M), and NOT MANY-TO-ONE (M:1).

We now enhance the ER Diagram of ROLE and MEP.

The Relationships now read as follows:

• Relationship From Left to Right

Each ROLE MAY be held by One or More MEPs.

• Relationship From Right to Left

Each MEP MUST have One or More ROLES.

We now have a MANY-TO-MANY Relationship between ROLE and MEP.

2 NF - Condition #1

• All 1NF Requirements are fulfilled.

2 NF - Condition #2

• There is NO Partial Dependency.

As we have already learned, Partial Dependency exists in an entity in which One or More Non-Key Attributes are Dependent on Only One of the Component of the Composite Primary Key.

Question

Answer

We have already seen in the Dependency section, that the Sample Entity EP SEATS has Partial Dependency.

• Some of the Non-Key Attributes are Dependent on One Component of the Composite Primary Key,
and
• Some of the Non-Key Attributes are Dependent on the Other Component of the Composite Primary Key.

Therefore, EP SEATS is NOT in 2NF.

To Convert the entity EP SEATS from 1NF to 2NF, we have to make sure that the entity is FREE of Partial Dependency .

We have already seen that "pg name", "pg chairman / co-chair1" and "co-chair2" are Partially Dependent on the Composite Primary Key .

They are Dependent on the "pg code" which is One component of the Composite Primary Key.

Likewise, attributes "country name" , "population" and "capital" are Dependent on the "country code" which is the Other Component of the Composite Primary Key.

Therefore, they are Partially Dependent on the Composite Primary Key.

Attributes "population of capital" and "number of universities in capital" are NOT even Partially Dependent on the Primary Key.

These Attributes are Dependent on the attribute "capital", which is itself Dependent on the "country code".

Therefore, attributes "population of capital" and "number of universities in capital" are Transitively Dependent on "country code".

We want to end up with Entities where NO Partial Dependency exists.

This is achieved by Decomposing the EP SEATS which is in 1NF, into Three Entities in 2NF.

We Decomposed EP SEATS into Three Entities;

• EP SEATS 1
• EP SEATS 2
• EP SEATS 3

No Partial Dependency exists in any of the Three Entities.

Therefore, we can say that they are in Second Normal Form or they are in 2NF.

We have already seen in the section Conversion from Lower Form to 1NF that the entities POLITICAL GROUP, MEP and ROLE are in First Normal Form — 1NF .

All of their NON-KEY Attributes are Dependent on the Primary Key.

As their Primary Keys are made up of only One Attribute, there can be NO Partial Dependency.

Hence, the entities POLITICAL GROUP, MEP and ROLE are also in Second Normal Form —2NF.

3 NF - Condition #1

• All 2NF Requirements are fulfilled.

3 NF - Condition #2

• There is NO Transitive Dependency.

An Entity that Has Transitive Dependency is NOT in Third Normal Form but has to be Decomposed Further to achieve 3NF.

However, an Entity in 2NF that Does NOT Contain any Transitive Dependencies among its attributes is already in 3NF.

Entities EP SEATS 1 and EP SEATS 2;

• are in Second Normal Form — 2NF
• they have NO Transitive Dependency

Therefore, the entities EP SEATS 1 and EP SEATS 2 are already in Third Normal Form — 3NF.

The entity EP SEATS 3 is also in Second Normal Form — 2NF.

However, EP SEATS 3 has Transitive Dependency:

Attributes "population of capital" and "number of universities in capital" are Dependent on the Non-Key Attribute "capital".

EP SEATS 3 needs to be converted into 3NF.

This is what we do to convert EP SEATS 3 into 3NF:

• We move the Attributes with Transitive Dependency into a new entity — EP SEATS 4.
• We keep the Primary Key Attribute ("capital") of the new entity EP SEATS 4, as a Foreign Key Attribute in the existing entity EP SEATS 3.

EP SEATS 3 and EP SEATS 4 are now in 3NF.

We have already seen in the previous section "Conversion from 1NF to 2NF" that the entities POLITICAL GROUP, MEP and ROLE are in Second Normal Form — 2NF.

They have NO Transitive Dependency either.

Hence, the entities POLITICAL GROUP, MEP and ROLE are in 3NF.

• In our Business Organisation, we distinguish/differentiate/pick out/discriminate the things of significance about which information needs to be known or held.
• These are our entities.
• These entities must be mutually exclusive.
• Each entity is represented on a diagram by means of a box, with the entity name in singular and upper case.

• We add to these entities the business relationships.
• Business relationships are "named significant associations" between entities.
• These relationships are shown as a line between two entity boxes.
• Cardinality
• Each end of the relationship line has a degree which is also called cardinality.
• A triangle or crowsfoot means many, no triangle means one.
• Optionality
• Each end of the relationship line has an optionality.
• A dotted line means optional, a solid line means mandatory.

• For each entity we list the specific pieces of information that need to be known or held.
• These are called attributes.
• Attributes are shown within the entity as names in lower case.
• No attributes can be repeated in an entity.

• We find out how each occurrence/each instance of an entity can be uniquely identified.
• This can be achieved by "some combination of attributes and/or relationships".
• When an attribute is part of the unique identifier, it is shown by a # mark.
• When a relationship end is part of the unique identifier, it is shown by a bar across the relationship line. A relationship included in a UID must be mandatory and one and only one in the direction that participates in the UID.

• If we follow the process above rigorously, we will automatically obtain a Normalized Model.
• A Normalized Model allows NO Data Redundancy in the Database.

Important Note

This is a partial ER Model of the European Union which consists of the European Parliament and is sufficient enough for the purposes of this text, which is to teach the Elementary Basics of the SQL Language.

Important Note

The names of Political Groups, MEPs and their Roles are taken from the website of the European Parliament https://www.europarl.europa.eu/portal/en and from several publications of the EU, which are listed in the References section, and whose reproduction is authorized, provided the source is acknowledged.

Therefore data related to these are true to reality.

Important Note

However, the concept of Role Multipliers of the MEPs are completely fictitious.

The data for the attributes of 'salary' and 'role multiplier' are made-up. These attributes are used later on in explaining some SQL Concepts.

• ISBN 92-79-03653-X , How the European Union works: Booklet by EU Publications Office
• ISBN 978-92-824-2203-8, Information handbook of the Council of the European Union, DG Press, General Secretariat of the Council
• European Union

  https://europa.eu/european-union/index_en

• European Council

  https://www.consilium.europa.eu/en/

• European Parliament

  https://www.europarl.europa.eu/portal/en

• The Official Directory of the European Union (Who is Who)

  https://op.europa.eu/en/web/who-is-who