- Safaa-Dalloul
Download
Report
Transcript - Safaa-Dalloul
DATA MODELING
UNIT 7: DATA MODELING
Data Modeling
Key Definition
Data Model
Logical Data Model
Physical Data Model
Normalization
Entity Relationship Diagram
What Is an ERD?
Using the ERD to Show
Business Rules
Reading ERD
Elements of ERD
Creating an ERD
Steps in Building ERDs
Add attributes and assign
identifiers
Identify relationships
KEY DEFINITIONS
DATA MODEL
A formal way of representing the data that are used and created by a
business system.
Shows the people, places and things about which data is captured
and the relationships among them.
CONCEPTUAL DATA MODEL
Describes WHAT the system contains
LOGICAL DATA MODEL
Shows the organization of data without indicating how it is stored,
created, or manipulated.
PHYSICAL DATA MODEL
Shows how the data will actually be stored in databases or files.
NORMALIZATION
It is the process analysts use to validate data models
ENTITY RELATIONSHIP DIAGRAM
WHAT IS AN ERD
Entity
UniversityStudent
PK
Attributes
StudentID
StudentName
StudentDOB
StudentAge
Primary key
WHAT IS AN ERD
A picture/drawings showing the information created, stored, and
used by a business system.
Entities generally represent similar kinds of information
Lines drawn between entities show relationships among the data
High level business rules are also shown
USING THE ERD TO SHOW BUSINESS RULES
Business rules are constraints that are followed when the system is
in operation.
ERD symbols can show when one instance of an entity must exist for
an instance of another to exist.
A doctor must exist before appointments for the doctor can be made
EXAMPLE OF ERD
READING ERD
READING ERD
This diagram implement the ERD for a doctor Office system
Data can be organized into six different categories (Patients,
Appointment, doctors, bill, Payments, and Insurance company)
The Patient information include patient’s ID number, last name, first
name, address phone number …
READING ERD
Each category has a piece of information used to uniquely identify it,
as examples:
Patient ID number uniquely identify each patient
Bill number uniquely identify each bill
READING ERD
The
lines
connecting
the
six
categories
communicate
the
relationships that categories share. So you can understand that a
doctor is scheduled by an appointment that is scheduled by a patient
READING ERD
ERD is also presents high level business rules. Business rules are
constraints or guidelines that are followed during the operation of the
system.. They are rules like
Only one person can be seen by a doctor at a time
Payments can be made in the form of cash or check
Payment can be paid by insurance, patient, or combination of
both.
READING ERD
On the data model business rule are communicated by relationships,
in example
A patient can schedule by many appointment
A person does not have to be insured to become a patient
WHAT IS AN ERD ELEMENTS
CREATING AN ERD
Drawing the ERD is an iterative process of trial and revision
ERDs can become quite complex
STEPS IN BUILDING ERD
Identify the entities
Identify major categories of information
Verify that there is more than one instance of the entity that occurs
in the system
STEPS IN BUILDING ERD
Add attributes and assign identifiers
Identify attributes of the entity that are relevant to the system
under development
Select the entity’s identifier
STEPS IN BUILDING ERD
Identify Relationships
Start with an entity and identify all entities with which it shares
relationships
Describe the relationship with the appropriate verb phrase
Determine the cardinality and modality by discussing the business
rules with knowledgeable users
ADVANCED SYNTAX
Independent Entity
Is an entity that can exist without the help of another entity, such
as doctor, patient, and insurance company
These entities all have identifiers that were created using there
own attributes
ADVANCED SYNTAX
Dependent Entity
In some situations the child entity does required attributes from
the parent entity to uniquely identify an instance.
This child entity called dependent entity, such as the appointment
entity, which uses the physicianidnumber from doctors entity.
ADVANCED SYNTAX
Intersection Entity
It exists in order to capture some information about the
relationship that exists between two other entities.
Typically intersection entity are added to logical data model to
store information about two entities sharing an many to many
relationship
NORMALIZATION
It is a technique helps analysts to validate the model that they have
drawn.
It is a process whereby a series of rules are applied to a logical data
model to determine how well it formed is.
FIRST NORMAL FORM (1NF)
A logical data model in first normal form (1FN) if it not contains
attributes that have repeating values for a single instance of an
entity.
This form should solve repeating attributes and repeating groups’
problems.
Note the following example of the entity (special orders)
FIRST NORMAL FORM (1NF)
FIRST NORMAL FORM (1NF)
The attribute customer book preferences are a list of kind of books
customers like to read.
As you note there are a repeating attribute which is the preferences
And there is another repeating group with Books. This group of (isbn,
name, author,publicationyear, authoruniversity) they all may repeated
many times for each special orders.
SECOND NORMAL FORM (2NF)
In this form, for each entity, the attributes that serve as identifier can
determine the value for all of the other attributes for an instance in
an entity.
In the last example, the special order entity had three attributes that
were used as identifiers (special order date, customer last name, and
customer first name).
SECOND NORMAL FORM (2NF)
These attributes used to identify a customer, but can not identify the
special form attributes such as order status.
To resolve the problem, anew entity called Customer was created.
SECOND NORMAL FORM (2NF)
SECOND NORMAL FORM (2NF)
THIRD NORMAL FORM (3NF)
Occurs when a model is in both 1NF and 2NF and when in the
resulting entities none of the attributes are dependent on a non
identifier attribute.
In our example this problem with the book entity is that the author
university is dependent on the author.
SECOND NORMAL FORM (2NF)
And in the special order entity Store name, location and manager
are not dependent on special order.
To resolve this problem an entity called author was created, and
another entity called store was created.
THIRD NORMAL FORM (3NF)
THIRD NORMAL FORM (3NF)
THIRD NORMAL FORM (3NF)
Third normal form is also address problem caused by derived, or
calculated, attributes. No need to store calculated attributes such as
the age if we already stored the birthrate.
In our example, we dropped the attribute days in order from special
order entity because it can be derived from special order date.
EXERCISES
Draw an entity relationship diagram for the following situations:
Whenever new patients are seen for the first time, they complete a
patient information form that asks their name, address, phone
number, and insurance carries, all of which is stored in the patient
information file. Patient can be signed up with only with only carrier,
but they must be signed up to be seen by the doctor. Each time a
patient visits the doctor, an insurance claim is sent to the carrier for
payment. The claim must contain information about the visit. Such as
date, purpose, and cost. It would be possible for a patient to submit to
claims on the same day.
EXERCISES
Draw an entity relationship diagram for the following situations:
A department store has a bridal registry. This registry keeps
information about the customer (usually the bride), the products that
the store carries, and the products for which each customer registers.
Customer typically registers for the same products.