Transcript Entities - GTU Campus

Objectives
 In this lesson, you will learn to:
 Map an entity-relationship diagram to tables
in relation to the following:




Regular entities
Attributes
Relationships
Weak entities
 Tips on Logical Database Design
RDBMS Concepts/ Session 2 / 1 of 23
Conceptual Model
 Reflects entities and their relationships,
based on the data processing needs of
an organization
 Can be mapped to a relational,
hierarchical, or network model
 Is independent of individual
applications, database management
systems, hardware, and physical
storage of data
RDBMS Concepts/ Session 2 / 2 of 23
Object-based Logical Model
 There are various object-based models.
The most widely used is the entityrelationship model (E/R model)
RDBMS Concepts/ Session 2 / 3 of 23
The Entity-relationship Model
 Introduced by Peter Chen
 Chen introduced not only the E/R model,
but also a corresponding diagramming
technique
RDBMS Concepts/ Session 2 / 4 of 23
Entities
 Chen defined an entity as “a thing, which can be
easily identified”
 An entity is any object, place, person, or activity
about which data is recorded
 In the diagramming technique, entities are
named and represented inside a box
 An entity type is a set of things that share
common properties
 STUDENT, COURSE, and GRADE are examples of
entity type
 An entity type is usually in uppercase
RDBMS Concepts/ Session 2 / 5 of 23
Entities (Contd.)
 Types of Entities
 Dependent entity
• Is an entity whose existence depends on the
existence of another entity and are also called
weak entities
 Independent entity
• Is an entity which does not depend on any
other entity for existence and are also called
regular entities
RDBMS Concepts/ Session 2 / 6 of 23
Relationships
 Chen defines a relationship as “an association
among entities”.
 For example, the relationship between students
and instructors represents the fact that an
instructor teaches several students and a student
is taught by several instructors. This relationship
could be named TEACH.
 Relationships are depicted as a diamond with
the name of the relationship type.
RDBMS Concepts/ Session 2 / 7 of 23
Relationships (Contd.)
 A relationship type is an association of
entity types (STUDENT-INSTRUCTOR).
 A relationship can associate an entity with
itself. For example, one instructor in a
university may marry another instructor.
 Multiple relationships can also exist
between the same entities.
RDBMS Concepts/ Session 2 / 8 of 23
Wait a while…
1. The following statement has been
extracted from a case presented by a
manufacturer regarding the maintenance
of their data: “A supplier ships certain
parts.” Identify the entities mentioned in
this statement, and their relationship.
Draw a diagram depicting the
relationship.
RDBMS Concepts/ Session 2 / 9 of 23
Types of Relationships
 There are three types of relationships:
 One-to-One
 One-to-Many (or Many-to-One)
 Many-to-Many
RDBMS Concepts/ Session 2 / 10 of 23
One-to-One Relationship
 Consider the example of a university. For
one DEPARTMENT (like the department
of social sciences) there can be only one
department head. This is a one-to-one
relationship.
RDBMS Concepts/ Session 2 / 11 of 23
Many-to-One Relationship
 A STUDENT can MAJOR in only one
course, but
many STUDENTs would have registered
for a given
MAJOR course. This is a many-to-one
relationship.
RDBMS Concepts/ Session 2 / 12 of 23
Many-to-Many Relationship
 A STUDENT can take many COURSEs
and many
STUDENTs can register for a given
COURSE. This is
a many-to-many relationship.
RDBMS Concepts/ Session 2 / 13 of 23
Wait a while…
1. What do the following E/R diagrams
represent?
RDBMS Concepts/ Session 2 / 14 of 23
Attributes
 Attributes are a property of a given entity
 Attributes are depicted as ellipses,
labeled with the name of the property
RDBMS Concepts/ Session 2 / 15 of 23
Mapping Entity-Relationship
Diagrams to Tables
 A database that conforms to an E/R diagram
can be represented by a collection of tables
in the relational system
 The mapping of E/R diagrams to tables can
be discussed in relation to the following:
 Regular entities
 Attributes
 Relationships
RDBMS Concepts/ Session 2 / 16 of 23
Regular Entities
 They can exist in isolation, independent
of any other entity
 They are the “building blocks” of the
database
 Each regular entity maps to a table
RDBMS Concepts/ Session 2 / 17 of 23
Attributes
 Each property or attribute shown in the
E/R diagram maps to a column in the
appropriate table
RDBMS Concepts/ Session 2 / 18 of 23
Tips on Logical Database Design
 Attributes:
 Do not introduce any unnecessary
attributes
 An attribute serves three purposes:
• To identify its owner entity
• To refer to another entity
• To simplify the description of an entity
RDBMS Concepts/ Session 2 / 19 of 23
Tips on Logical Database Design
(Contd.)
 Keys:
 Relational systems require keys that
uniquely identify the rows of a table.
 There are various types of keys:
•
•
•
•
•
Primary
Foreign
Candidate
Alternate
Composite
RDBMS Concepts/ Session 2 / 20 of 23
Tips on Logical Database Design
(Contd.)
 Any attribute (or set of attributes) that
uniquely identifies a row in a table is a
candidate for the primary key. Such an
attribute is called a candidate key.
 Any attribute that is a candidate for the
primary key but is not the primary key is
called the alternate key.
 Keys can be simple or composite
• A simple key is composed of a single attribute
• A composite key, on the other hand, comprises
two or more attributes
RDBMS Concepts/ Session 2 / 21 of 23
Wait a while…
1. Define the following terms:
a. Candidate Key
b. Alternate Key
RDBMS Concepts/ Session 2 / 22 of 23
Tips on Logical Database Design

1.
2.
3.
4.
5.
In planning the database, regardless of its
size and complexity, use these basic steps:
Gather information.
Identify the objects.
Model the objects.
Identify the types of information for each
object.
Identify the relationships between objects.
RDBMS Concepts/ Session 2 / 23 of 23
Online Transaction Processing
vs. Decision Support
 Online Transaction processing database
applications are optimal for managing
changing data, and usually have a large
number of users who will be simultaneously
performing transactions that change real-time
data.
 Common examples of these types of
databases are airline ticketing systems and
banking transaction systems. The primary
concerns in this type of application are
concurrency and atomicity.
RDBMS Concepts/ Session 2 / 24 of 23
OLTP Design Considerations
1. Good data placement.
 Determine the likely access patterns of the
data and place frequently accessed data
together. Use filegroups and RAID
(redundant array of independent disks)
systems to assist in this.
2. Short transactions to minimize long-term
locks and improve concurrency.
 Avoid user interaction during transactions.
Whenever possible, execute a single stored
procedure to process the entire transaction
RDBMS Concepts/ Session 2 / 25 of 23
1. Online backup.
 schedule the backup process to occur during
times of low activity to minimize effects on
users.
2. High normalization of the database.
 Reduce redundant information as much as
possible to increase the speed of updates and
hence improve concurrency.
3. Careful use of indexes.
4. Optimum hardware configuration to handle
the large numbers of concurrent users and
quick response times required by an OLTP
RDBMS Concepts/ Session 2 / 26 of 23
system.
Decision Support
 Decision-support database applications
are optimal for data queries that do not
change data.
 The tables in a decision-support
database are heavily indexed, and the
raw data is often preprocessed and
organized to support the various types
of queries to be used.
RDBMS Concepts/ Session 2 / 27 of 23
Decision Support Design
Considerations

Decision-support system databases should
be designed to promote:
1. Heavy indexing.
1. Decision-support systems have low update
requirements but large volumes of data. Use
many indexes to improve query performance.
2. Denormalization of the database.
1. Introduce preaggregated or summarized data
to satisfy common query requirements and
improve query response times.
RDBMS Concepts/ Session 2 / 28 of 23
Summary
In this lesson, you learned that:
 The conceptual model reflects entities and their
relationships. Data analysis helps determine
entities and relationships. The conceptual
model is independent of the system where it is
to be implemented.
 Regular entities are not dependent. They can
exist in isolation, independent of any other
entity.
 Each entity maps to a table. Each attribute in
an E/R diagram maps to an attribute in a table.
RDBMS Concepts/ Session 2 / 29 of 23
Summary (Contd.)
 The mapping of relationships depends on the
type of relationship. Each type of relationship
maps to tables in a different manner in the
relational database management system.
 In one-to-one relationships, one instance of
an entity can relate to only one instance of
the related entity.
 In a one-to-many relationship, one instance of
an entity can relate to more than one instance
of the related entity.
 Many-to-many relationships map to tables.
 One-to-one relationships are not very
common and may map to foreign keys in
tables.
RDBMS Concepts/ Session 2 / 30 of 23
Summary (Contd.)
 A candidate key is a candidate for the primary
key. An alternate key is a candidate key that is
not a primary key.
RDBMS Concepts/ Session 2 / 31 of 23