Transcript Introduction to Database

Lecture 2
Data Modeling Using the Entity-Relationship (ER) Model
Database Approach
• Data definition language (DDL).
• Permits specification of data types, structures and any
data constraints.
• All specifications are stored in the database.
• Data manipulation language (DML).
• General enquiry facility (query language) of the data.
Database Approach
• Controlled access to database may
include:





a security system
an integrity system
a concurrency control system
a recovery control system
a user-accessible catalogue
Views

Allows each user to have his or her own view of the database.

A view is essentially some subset of the database.
Views - Benefits
Reduce complexity
Provide a level of security
Provide a mechanism to customize the appearance of the
database
Present a consistent, unchanging picture of the structure of the
database, even if the underlying database is changed
Components of DBMS Environment
Components of DBMS Environment

Hardware


Software


Can range from a PC to a network of computers.
DBMS, operating system, network software (if
necessary) and also the application programs.
Data

Used by the organization and a description of this data called the
schema.
Components of DBMS Environment

Procedures


Instructions and rules that should be applied to the
design and use of the database and DBMS.
People

Who uses the database with different views
E-R Model Constructs (Entity &
Attribute)
• Entity Instance
 Person, place, object, event, concept (often corresponds to a row in
a table)
• Entity Occurrence
Uniquely identifiable object of an entity type.

Entity Type

Collection of entities (often corresponds to a table). Group of
objects with same properties, identified by enterprise as having an
independent existence.
• Attribute
• Property or characteristic of an entity type (often corresponds to a
field in a table)
Relationship Types
• Relationship Instance
• Link between entities (corresponds to primary key-foreign key
equivalencies in related tables)
• Relationship Type
Category of relationship…link between entity types.
• Relationship Occurrence
• Uniquely identifiable association, which includes one
occurrence from each participating entity type.
Examples of Entity Types
ER diagram of Staff and Branch
entity types
Semantic of Has relationship type
ER diagram of Branch Has Staff
relationship
Sample E-R Diagram (Figure 3-1)
Relationship symbols
Entity
symbols
A special entity
that is also a
relationship
Relationship
cardinalities
specify how
many of each
entity type is
allowed
Attribute
symbols
Relationship
degrees specify
number of
entity types
involved
What Should an Entity Be?
• SHOULD BE:
• An object that will have many instances in the database
• An object that will be composed of multiple attributes
• An object that we are trying to model
• SHOULD NOT BE:
• A user of the database system
• An output of the database system (e.g. a report)
Figure 3-4
System user
Inappropriate entities
System output
Appropriate entities
Attributes (Attn.)
• Attribute - property or characteristic of an entity type
• Classifications of attributes:
•
•
•
•
•
Required versus Optional Attributes
Simple versus Composite Attribute
Single-Valued versus Multivalued Attribute
Stored versus Derived Attributes
Identifier Attributes
Identifiers (Keys)
• Identifier (Key) - An attribute (or combination of attributes)
that uniquely identifies individual instances of an entity type
• Simple Key versus Composite Key
• Candidate Key – an attribute that could be a key…satisfies the
requirements for being a key
Characteristics of Identifiers
• Will not repeat in value
• Will not be null
• No intelligent identifiers (e.g. containing locations or people that
might change)
• Substitute new, simple keys for long, composite keys
Figure 3-7 – A composite attribute
An attribute
broken into
component parts
Figure 3-9a – Simple key attribute
The key is underlined
Figure 3-9b – Composite key attribute
The key is composed
of two subparts
Figure 3-8 – Entity with a multivalued attribute (Skill) and
derived attribute (Years_Employed)
What’s wrong with this?
Derived
from date employed and current date
Multivalued:
an employee can have
more than one skill
Figure 3-19 – An attribute that is both multivalued and composite
This is an
example of
time-stamping
More on Relationships
• Relationship Types vs. Relationship Instances
• The relationship type is modeled as the diamond and lines
between entity types…the instance is between specific
entity instances
• Relationships can have attributes
• These describe features pertaining to the association between the
entities in the relationship
• Two entities can have more than one type of
relationship between them (multiple relationships)
• Associative Entity – combination of relationship and
entity
Degree of Relationships
• Degree of a relationship is the number of entity types that
participate in it
Unary Relationship
 Binary Relationship
 Ternary Relationship

Degree of relationships – from Figure 3-2
One entity
related to
another of
the same
entity type
Entities of
two different
types related
to each other
Entities of three
different types
related to each
other
Binary relationship called
POwns
Ternary relationship called
Registers
Quaternary relationship called
Arranges
Relationship Types
• Recursive Relationship
• Relationship type where same entity type participates more than
once in different roles.
• Relationships may be given role names to indicate
purpose that each participating entity type plays
in a relationship.
Recursive relationship called
Supervises with role names
Entities associated through two
distinct relationships with role
names
Cardinality of Relationships
• One-to-One
• Each entity in the relationship will have exactly one related
entity
• One-to-Many
• An entity on one side of the relationship can have many
related entities, but an entity on the other side will have a
maximum of one related entity
• Many-to-Many
• Entities on both sides of the relationship can have many
related entities on the other side
Example of a Database
(with a Conceptual Data
Model)
• Mini-world for the example:
• Part of a UNIVERSITY environment.
• Some mini-world entities:
•
•
•
•
•
STUDENTs
COURSEs
SECTIONs (of COURSEs)
(academic) DEPARTMENTs
INSTRUCTORs
Slide 1- 37