Please enable JavaScript.
Coggle requires JavaScript to display documents.
CHAPTER 3: ER MODEL & NORMALIZATION, Dannellia, catherine, catherine,…
CHAPTER 3: ER MODEL & NORMALIZATION
3.2 ER MODEL
JAZRIN
1: M
A row from
one table can have multiple matching rows in another table
this relationship is defined as a
one to many
relationship.
This relationship can be created by using Primary key- Foreign key relationship
Example : a person can own many car and a car can be own by a person.
Dominic Mathew
3 Basic Element of E-R model
Entity:
- An entity is a person, a place an object, an event of a concept in the user environment about which the organization wishes to maintain data,
An entity
is a noun.
Attribute:
- A description or an entity characteristic is known as an attribute, Used to differentiate object in an entity
Relationship:
- Relationship is the link between entities,
Label = verb/conjunction
Figure 4.13 CLASS is optional to COURSE.
Figure4.14 COURSE and CLASS is mandatory relationship
Weak Entity
1) For each weak entity, you need to produce a relationship an insert all the simple attributes into the relationship.
2) This attribute is a copy of the primary key for the common entity which is related to this weak entity
Gibson
example in BCNF
Gibson
Relationship Participation
classified as compulsory or optional
optional
occurrence in entity A may not be associated to entity B every time
Minimum cardinality: 0
compulsory
minimum cardinality: 1
Gibson
strong entity
a Student is produced and each attribute of Student entity will become a field or Student relationship. The primary key of Student entity will become the primary key of Student relationship
WARDINA
EXAMPLE OF WEAK & STRONG ENTITY
From this picture, it shows relationship between two entities which is customer and loan
Loan is a weak entity
so the symbol is double rectangle shape
Customer is a strong entity
so the symbol is rectangle shape
WARDINA
EXAMPLE OF COMPOSITE ENTITY USING CROW FOOT
This picture is a composite entity of many to many relationship between STUDENT and CLASS
In composite entity, we can simplified the many to many relationship by adding one more entity between the two original entities
Based on this picture, the many to many relationship is STUDENT and CLASS so after being simplified to composite entity, the entity ENROLL is added between CLASS and STUDENT entity
HAZIQ
RELATIONSHIP
Cardinality:
-Relationship Cardinality describe the number of relationship between one entity to other entity.
-In other word, it is the number of entity occurrences related to other entity occurrences.
-Depends on the policy of the organization.
-Cardinality is determined by the policy of the organizational called business regulations.
-1:1 Relationship (one-to-one)
-1:M Relationship (one-to-many)
-M:N Relationship (many-to-many)
Dannellia
Abstraction Of The Relationship Scheme From the Logical Data Model
Abstract a relationship from logical data model through process of mapping from a conceptual data model
Relationship between two entities is represented by the
mechanism of a primary key and a foreign key in the relationship scheme
To place attributes, categorize the 'parent' and 'child' entities
A copy of a 'parent' key will be placed in the 'child' entity and acts as a foreign key
Dannellia
-Two entities can have more than one relationship.
Eg. STUDENT can register for a COURSE and STUDENT can also drop a course
Adning
1:1 Relationship
For each 1:1 binary relationship between Entity E1 and E2 in the logical data model, a copy of a primary key for entity E1(parent) will be placed in entity E2 (child) to represent the foreign key.
The determination for parent entity and child entity depends on the relationship participation between the entities involved.
The entity with optional relationship participation is categorized as the parent entity, and the entity with mandatory relationship participation is categorized as child entity.
Crow Foot Symbols
Example of ER Model: Chen
What Is An Entity Relationship Model?
The ER model is expressed in terms of entities in the business environment, the relationships among those entities and the attributes (properties) of both entities and their relationships.
An entity relationship model (ER model) is a detailed, logical representation of the data for an organization or for a business area.
E-R Diagram notation can be used are Chen and Crow Feet.
Entity Participation
b.) Weak Entity
This attribute is a copy of the primary key for the common entity which is related to this weak entity.
For each weak entity, you need to produce a relationship and insert all the simple attributes( or simple attributes of the composite attribute) into the relationship.
Apart from that, an attribute representing the foreign key is produced.
athirah
Database
entity
is a thing, person, place, unit, object or any item about which the data should be captured and stored in the form of properties, and tables.
ENTITY : CATEGORY AND EXAMPLE
COMPOSITE ENTITY
1:1 Relationship
In a one-to-one relationship, one record in a table is associated with one and only one record in another table.
For example, in a school database, each student has only one student ID, and each student ID is assigned to only one person.
ERIKA
ARIF
Abstraction Of The Relationship Scheme From the Logical Data Model
The process of abstracting the relationship scheme requires several considerations as follows
Strong Entity
Weak Entity
1:M Relationship
1:1 Relationship
Type of Attribute
Simple Attribute
only one component
exist independently
cannot be broken up
example : IC number, student ID
Composite Attribute
comprises of many components
each on existing independently
example: address with sub attribute such as house_no, road_no, town, postcode
Single-value Attribute
consists of only a single value
example: IC number
Multi-value Attribute
an attribute consisting of many values
example: phone number
Derived Attribute
its value is derived from the value of related attribute
example: age attribute derived from the date of birth
Do not insert the system environment which is modeled as a component of the ER model
Summary
Normalization
:
a process of splitting relations into well-structed relations
allow user to insert, update and delete records without causing inconsistencies
Functional dependencies
full
partial
transitive
Normal forms
1NF
2NF
3NF
BCNF
ENTITY TYPE
Strong entitiy
complete by itself and is not dependent on any other entity type.
It possess a primary key which describes each instance in the strong entity set uniquely.
represented by a square with a single line unlike a Weak Entity which contained double lines.
WEAK ENTITY
an entity that cannot be uniquely identified by its attributes alone
it must use a foreign key in conjunction with its attributes to create a primary key.
There are two types of weak entities associative entities and subtype entities.
The M:N relationship between STUDENT and CLASS
1:1 Relationship
it. means that for each occurrence of one entity there is one or many. occurrences of a related entity.
WAN
COMPOSITE ENTITY
Many-To-Many relationship identified as show a complex relationship.
Can simplified by transforming relationship to Composite Entity.
1:1 Relationship
Primary Key for entity E1 (parent) will be replaced in entity E2 (child) to foreign key.
The determination for parent entity and child entity depend on the relationship participation.
The entity with optional relationship participation is categorized as parent entity.
Mandatory relationship participation is categorized as child entity.
Syazwan
Recursive/Unary Relationship is a relationship involving only one entity
Binary Relationship is a relationship between two entities
Ternary Relationship is a simultaneous relationship between the three entities
Relationship type
Guideline and steps in construction an ER model
An entity must contain description. As such, an object with only one characteristic is reffered to as attribute and not entity
If the value of an attribute is important to the organization, the attribute can be made into an entity
3.1 NORMALIZATION
Elton
Boyce-codd Normal Form
-A table is in 3NF if it is in 2NF and there are no transitive dependencies
-If a nonkey attribute is the determinant of a key attribute that table still confroms to 3NF requirement but fail BCNF requirements.
Boyce-codd Normal Form
A table is in BCNF is every determinant in a table is a candidate key.
BCNF can be violated only if the table contains more than one candidate key.
If a table contains only one candidate key, the 3NF and BCNF are equivalent.
BCNF is considered as a special case of 3NF.
Dannellia
CARDINALITY
1 occurrence in entity A is associated to 1 occurrence in entity B and 1 occurrence in entity B is Associated to Occurrence in entity A
Top down methodology
Determine entity and relationship between them.
Start with main entity and followed by other entities
Determine the attributes related to the entities.
Determine the attributes related to the relationship (if there are any).
Choose the keys for the entity.
Determine the domain for each attribute.
Combine the diagram of entity, relationship and attribute to develop a complete E-R model. The must be made without allowing entities to exist independently. (hanging)
Thoroughly check and refined the E-R model if necessary (discuss with user).
Abstraction Of The Relationship Scheme From the Logical Data Model
Strong Entity
-For each common entity (strong), you need construct a relationship by inserting all the simple attributes of the common entity into the relationship.
-In the case of composite attribute, you merely need to insert simple attributes which form the composite attribute mentioned.
Syazwan
After normalization
Normalization complete. it become
After Normalization
Course (Courseld, CourseTitle, Teacher)
StudentGrade (StudId, Courseld, Grade)
Student (StudId, StudName, Phone, Major)
Teacher (Teacher, TeacherRoom)
Normalization process =
1NF - 2NF - 3NF - BCNF
WARDINA
THIRD NORMAL FORM EXAMPLE
Solution :
Split and make a new table to remove transistive dependency
From this table name Course, :
attribute TeacherRoom is functionally dependent on Teacher attribute( a non key attribute) which is can lead to transistive dependency
WARDINA
FUNCTIONAL DEPENDENCY
for example :
attribute B is functionally dependent on attribute A
represented as :
A determines B // A -> B
refers to particular relationship between 2 attributes
Gibson
Repeating group within columns
The designer might ,alternatively retain the single Telephone Number column but alter its domain ,making it string of sufficient length to accommodate multiple telephone numbers
Gibson
Insertion
The inability to add data to the database due to absence of other data
Dominic Mathew
Insert anomaly the inability to add data to the database due to absence of other data.
JAZRIN
BCNF Example:
A+B-> C,D
( Primary key for attribute A and B determine attribute C and D)
C -> B
(Only attribute C(non-primary key) determine attribute B)
When this happen, convert this table into BCNF by dividing it into two table :
A+C->D
C->B
JAZRIN
Repeating Group
a series of attributes that are repeated throughout database table
First normal form
Every attribute value is atomic.
A relation is said to be in 1NF if :
-cells are single valued.
-all entries in any column are of the same kind.
-each column has a unique name, but the order of the columns is not important.
-no two rows are identical, but the order of the rows is not important.
No multi-valued attributes/ repeating groups.
JAZRIN
Normalization
is a process of structuring a database (usually a relational database) in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.
Anding
THE OBJECTIVES OF NORMALIZATION
To free the collection of relations from undesirable insertion, update and deletion dependencies.
To reduce the need for restructuring the collection of relations as new of data are introduced and thus increase the life span of application programs.
To make the relational model more informative to users.
Primary key(PK) - StudId, CourseId
Dependemcies:
StudId, CourseId -> Grade
StudId -> Studname, Phone, Major
CourseId -> CourseTitle, TeacherId, TechearRoom
Teacher -> TeacherRoom
Update- An update anomaly is a data inconsistency that results from a data redundancy and a partial update.
A design that complies with 1NF
A design that is unambiguously in 1NF make use of two tables: Customer Name Table and Customer Telephone Number Table.
athirah
NORMALIZATION PROCESS
accomplished in stages
each corresponds to a normal form
Normalization’s stages
First normal form
Second normal form
Third normal form
Boyce-Codd normal form
Example of 2NF
ERIKA
NORMALIZATION
Anomalies – unexpected results from an operation.
Deletion
Modification
Insertion
Repeating Group Across Columns
The designer might attempt to get around this restriction by defining Telephone Number columns.
Example
The structure for previous table:
STUID + STAFFID -> CLASSCODE, ENROLLGRADE
CLASSCODE-> STAFFID
ARIF
NORMALIZATION
The goal of normal forms is to allow you to take a table or collection of tables and produce a new collection of tables that represents the same information but is free of problems
Enables you to identify the existence of potential problems called anomalies, in the design of a relational database.
Normalization reduces data dependency and helps to eliminate the anomalies that result from those redundancies.
Does not eliminate data redundancies, instead it produces controlled redundancy, which is needed to link the tables in a database.
Partial Dependency
Occurs when:
functionally dependent on only a part of a multi-attribute key, also known as composite key
A table :
with only a single attribute primary key cannot exhibit partial dependency
for example:
PROJ_NUM determine PROJ_NAME
HAZIQ
2NF
No partial dependencies
In 1NF, all is fully dependent on the primary key
NORMALIZATION PROCESS
several concepts:
FUNCTIONAL DEPENDENCY
PARTIAL DEPENDENCY
TRANSITIVE DEPENDENCY
Third normal form (3NF)
if it is in 2NF
no non key attribute is transitively dependent on the primary key.
The attribute ID is the identification key.
WAN
The Objective Of Normalization
free the collection of relations from undesirable insertion , update and deletion dependencies.
reduce the need for restructuring the collection of relation.
make the relational model more informative to users.
make the collection of relations neutral to the query statistics.
To Convert to 2NF,
must remove partial dependency.
EXAMPLE :
STUDENT
(Studld<KP>, StudName, Tel, Major)
COURSE
(CourseId<KP>, Course Title, Teacher, TeacherRoom)
STUDENTGRADE
(StudId<KP>,CourseId<KP>,Grade)
Dannellia
catherine
catherine
catherine
catherine
catherine
Mauldorna
Mauldorna
Enviroment where application will be constructed
Mauldorna
from the Teacher table : - TeacherRoom depends on Teacher
Mauldorna
Mauldorna
shahrul
shahrul
shahrul
shahrul
shahrul
Dannellia
