Question

Entity Relationship Diagrams are created for a variety of reasons. How would you describe the purpose of an entity-relationship diagram to someone who isn't familiar with data warehousing? How would you explain or define the join types, relationships, and primary /foreign keys?

Answer 1

E-R Diagram-

An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as people, objects or concepts relate to each other within a system. ER Diagrams are most often used to design or debug relational databases in the fields of software engineering, business information systems. Also known as ERDs or ER Models, they use a defined set of symbols such as rectangles, diamonds, ovals and connecting lines to depict the interconnectedness of entities, relationships and their attributes. They mirror grammatical structure, with entities as nouns and relationships as verbs.

• Each entity has its own attribute
• Entities are in rectangle box
• attributes are in ovals
• relationship among entities are in rohmbus
• The primary keys of each entities are in ovals with underline.

ER diagram has three main components:
1. Entity
An entity is an object or component of data. An entity is represented as rectangle in an ER diagram.

2. Attribute
An attribute describes the property of an entity. An attribute is represented as Oval in an ER diagram. There are four types of attributes:

1. Key attribute
2. Composite attribute
3. Multivalued attribute
4. Derived attribute

3. Relationship
A relationship is represented by diamond shape in ER diagram, it shows the relationship among entities. There are four types of relationships:
1. One to One
2. One to Many
3. Many to One
4. Many to Many

• A primary key is a minimal set of attributes (columns) in a table that uniquely identifies tuples (rows) in that table.
• Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

NORMALIZATION

Normalization is the process of organizing the data in the database.
It is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.
Normalization divides the larger table into the smaller table and links them using relationship.

1. First Normal Form (1NF)

A relation will be 1NF if it contains an atomic value.
It states that an attribute of a table cannot hold multiple values. It must hold only single-valued attribute.
First normal form disallows the multi-valued attribute, composite attribute, and their combinations.

2. Second Normal Form (2NF)-

In the 2NF, relational must be in 1NF.
In the second normal form, all non-key attributes are fully functional dependent on the primary key.
it should not have Partial Dependency.
Partial Dependency exists, when for a composite primary key, any attribute in the table depends only on a part of the primary key and not on the complete primary key.
To remove Partial dependency, we can divide the table, remove the attribute which is causing partial dependency, and move it to some other table where it fits in well.

3. Third Normal Form (3NF)

A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
3NF is used to reduce the data duplication. It is also used to achieve the data integrity.
If there is no transitive dependency for non-prime attributes, then the relation must be in third normal form.
A relation is in third normal form if it holds atleast one of the following conditions for every non-trivial function dependency X → Y.
X is a super key.
Y is a prime attribute, i.e., each element of Y is part of some candidate key.

4. Boycee Codd Normal Formal-BCNF-
For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:
It should be in the Third Normal Form.
And, for any dependency A → B, A should be a super key.

If any doubt, please feel free to ask and comment, i would like to answer them.
Thank you