EXAMPLE OF
NORMALIZATION
WHAT IS NORMALIZATION?
• Normalization is a database design technique that reduces data
redundancy and eliminates undesirable characteristics like
Insertion, Update and Deletion Anomalies. Normalization rules
divides larger tables into smaller tables and links them using
relationships. The purpose of Normalization in SQL is to
eliminate redundant (repetitive) data and ensure data is stored
logically. (guru99, 2021)
1. 1NF (FIRST NORMAL FORM
Stu_I D Stu_name Stu_contact dob crc_I D crc_fee crc_duratio n Teacher
001 Kamal 0774635001, 5-Jun-97 crc00 Rs. 6 months Jehan
Perera 0257880480 1 150,000.00 Fernando
Nimal 0711825400, crc00 Rs. Jehan
002 0754848500 5-Jun-98 1 6 months
Silva 1 150,000.00 Fernando
003 Saman 0662284418, 25-Apr-96 crc00 Rs. Suesh
Bandara 0725845875 2 200,000.00 10 months Bandara
In 1NF each cell should contain a single value and each record needs to be unique.
2NF (SECOND NORMAL FORM)
• It should be in first normalized form.
• Partial dependencies must be removed.
• Primary keys must be selected for separated tables.
PRIMARY KEY : STU_ID
Stu_I D Fname Lname Stu_contact1 Stu_contact2 dob
001 Kamal Perera 774635001 257880480 5-Jun-97
002 Nimal Silva 711825400 7548485001 5-Jun-98
003 Saman Bandara 662284418 725845875 25-Apr-96
COURSE TABLE
2NF
crc_ID crc_fee crc_duration Teacher
crc001 Ksh. 150,000.00 6 months Jehan
Fernando
crc001 Ksh. 150,000.00 6 months Jehan
Fernando
crc002 Ksh. 200,000.00 10 months Suesh
Bandara
3NF (THIRD NORMAL FORM)
• It should be in second normalized form and existing transitive
dependencies must be removed.
Student Table
Stu_I Fname Lname Stu_contact Stu_contact dob crc_ID
D 1 2
001 Kamal Perera 774635001 257880480 5-Jun-
97 crc001
002 Nimal Silva 711825400 754848500 5-Jun-
98 crc001
1
003 Saman Bandara 662284418 725845875 25- crc002
Apr-96
Course Table
crc_ID crc_fee crc_duration Teacher
crc00 Rs. 150,000.00 6 months Jehan
1 Fernando
crc00 Rs. 150,000.00 6 months Jehan
1 Fernando
crc00 Rs. 200,000.00 10 months Suesh
2 Bandara
ER Diagrams in DBMS: Entity Relationship Diagram Model
• An Entity Relationship Diagram is a diagram that represents relationships among
entities in a database. It is commonly known as an ER Diagram.
• An ER Diagram in DBMS plays a crucial role in designing the database. Today’s
business world previews all the requirements demanded by the users in the form of an
ER Diagram.
• Later, it's forwarded to the database administrators to design the database.
• An Entity Relationship Diagram (ER Diagram) pictorially explains the relationship
between entities to be stored in a database.
• Fundamentally, the ER Diagram is a structural design of the database.
• It acts as a framework created with specialized symbols for the purpose of defining the
relationship between the database entities.
• ER diagram is created based on three principal components: entities, attributes, and
relationships.
• The diagram above showcases two entities –
1. Student and
2. Course, and their relationship.
• The relationship described between student and course is many-to-many, as a course can
be opted by several students, and a student can opt for more than one course.
• Student entity possesses attributes - Stu_Id, Stu_Name & Stu_Age.
• The course entity has attributes such as Cou_ID & Cou_Name.
What is an ER Model?
An Entity-Relationship Model represents the structure of the database with the help of a
diagram.
ER Modelling is a systematic process to design a database as it would require you to analyze
all data requirements before implementing your database.
Why Use ER Diagrams in DBMS?
• ER Diagram helps you conceptualize the database and lets you know which
fields need to be embedded for a particular entity
• ER Diagram gives a better understanding of the information to be stored in a
database
• It reduces complexity and allows database designers to build databases quickly
• It helps to describe elements using Entity-Relationship models
• It allows users to get a preview of the logical structure of the database
Symbols Used in ER Diagrams
•Rectangles: This Entity Relationship Diagram symbol represents entity types
•Ellipses: This symbol represents attributes
•Diamonds: This symbol represents relationship types
•Lines: It links attributes to entity types and entity types with other relationship types
•Primary key: Here, it underlines the attributes
•Double Ellipses: Represents multi-valued attributes
Components of ER Diagram
You base an ER Diagram on three basic concepts:
• Entities • Attributes • Relationships
• Weak Entity • Key Attribute • One-to-One Relationships
• Composite Attribute • One-to-Many Relationships
• Multivalued Attribute • Many-to-One Relationships
• Derived Attribute • Many-to-Many
Relationships
Entities
• An entity can be either a living or non-living component.
• It showcases an entity as a rectangle in an ER diagram.
• For example, in a student study course, both the student and the course are
entities.
Weak Entity
An entity that makes reliance over another entity is called a weak entity.
You showcase the weak entity as a double rectangle in ER Diagram.
In the example below, school is a strong entity because it has a primary key attribute - school number.
Unlike school, the classroom is a weak entity because it does not have any primary key and the room
number here acts only as a discriminator.
Attribute
• An attribute exhibits the properties of an entity.
• You can illustrate an attribute with an oval shape in an ER diagram.
Key attribute
• Key attribute uniquely identifies an entity from an entity set.
• It underlines the text of a key attribute.
• For example: For a student entity, the roll number can uniquely identify a
student from a set of students.
Composite Attribute
• An attribute that is composed of several other attributes is known as a composite attribute.
• An oval showcases the composite attribute, and the composite attribute oval is further
connected with other ovals.
Multivalued Attribute
• Some attributes can possess over one value, those attributes are called multivalued
attributes.
• The double oval shape is used to represent a multivalued attribute.
Relationship
• The diamond shape showcases a relationship in the ER diagram.
• It depicts the relationship between two entities.
• In the example below, both the student and the course are entities, and study
is the relationship between them.
One-to-One Relationship
• When a single element of an entity is associated with a single element of another entity,
it is called a one-to-one relationship.
• For example, a student has only one identification card and an identification card is
given to one person.
One-to-Many Relationship
• When a single element of an entity is associated with more than one element of another
entity, it is called a one-to-many relationship.
• For example, a customer can place many orders, but an order cannot be placed by many
customers.
Many-to-One Relationship
• When more than one element of an entity is related to a single element of another entity,
then it is called a many-to-one relationship.
• For example, students have to opt for a single course, but a course can have many
students.
Many-to-Many Relationship
• When more than one element of an entity is associated with more than one element of
another entity, this is called a many-to-many relationship.
• For example, you can assign an employee to many projects and a project can have many
employees.
ER DIAGRAM
How to Draw an ER Diagram?
Below are some important points to draw ER diagram:
•First, identify all the Entities. Embed all the entities in a rectangle and label them properly.
•Identify relationships between entities and connect them using a diamond in the middle, illustrating the
relationship. Do not connect relationships with each other.
•Connect attributes for entities and label them properly.
•Eradicate any redundant entities or relationships.
•Make sure your ER Diagram supports all the data provided to design the database.
•Effectively use colors to highlight key areas in your diagrams.
Conclusion
• ER Diagram in DBMS is widely used to describe the conceptual design
of databases.
• It helps both users and database developers to preview the structure of
the database before implementing the database.
ENTITIES
1. Customer
2. Job
3. Payment
4. Product
5. Load
6. Depot
7. Transport unit
ATTRIBUTES
1. Customer – (Cus_ID, Cus_lname, Cus_fname, Cus_address1, cus_address2, Cus_contact , Cus_type)
2. Job – (Job_ID, Job_date, depot_ID, Cus_ID, start_loc, end_loc)
3. Payment – (Pay_ID, Load_type, Cus_type, product_type)
4. Product – (product_ID, product_type, product_name, product_qty)
5. Load – (Load_ID, Load_date, Load_type, product_ID)
6. Depot – (Depot_ID, Depot_name, Depot_loc)
7. Transport unit – (Driver_ID, assist_ID, Tunit_ID, container_ID, Lorry_no)
PRIMARY KEYS
1. Customer – Cus_ID
2. Job – Job_ID
3. Payment - Pay_ID
4. Product - product_ID
5. Load - Load_ID
6. Depot - Depot_ID
7. Transport unit – Tunit_ID
Logical design
Interfaces to input and output data
Customer User Interface
Job user interface
Payment user interface
product user interface
Load user interface
Depot user interface
Transport unit user interface
