Transcript Relationship Types - School of Computing

Introduction to Database
Review 1
What is a database
 Basic Definitions
 Database Management System (DBMS): A software
package to facilitate the process of




Defining - specifying types, organization (schema)
Constructing - loading the data
Manipulating - querying the data
One DBMS, many DBs, many applications
 Database system: A database and a DBMS
What is a database
 Pictorial Representation
Users/Programs
DATABASE
SYSTEM
Application Programs/Queries
DBMS
SOFTWARE
Software to Process
Queries/Programs
Software to Access
Stored Data
Stored Database
Definition
(Meta-Data)
Stored
Database
What is a database
 Functions of a DBMS
 Provides persistent, shared storage
 Objects live beyond program execution
 Shared by multiple applications
 DBMS reduces redundancy in Development and maintenance
 Provides multiple interfaces
 Query language, embedded query language, APIs, GUIs
 Protects against
 Software/hardware failure
 Security breaches
Classes of DB Users – Workers On the Scene
 Persons whose job involves daily use of a large
database
 Database administrators (DBAs)
 Database designers
 End users
 Application programmers
Data Models
 A data model is a data definition language along with a
data manipulation language
 Conceptual
 Representational
 Physical
Data Models
 A data definition language (DDL) describes database
schemas
 Data relationships
 Data semantics
 Integrity constraints
 Database schemas vs. instances
 Similar to types and variables in programming languages
 A data manipulation language (DML) is used for
querying and updating database instances
Schema vs. Instance
 Schema - Description of how data is organized and
constrained
 Instance - The data in a database (conforms to a
schema)
 Snapshot - Database state at a particular point in time
 Initially empty
 Database is populated or loaded DBMS ensures every state is a
valid state
 Schema evolution vs. data update
ANSI Three-Schema Architecture
 Defines DBMS schemas at three levels:
 Physical
 How data is stored on disk
 Data storage structures
 Access paths to the data
 Logical
 How we think the data is organised
 Conceptual structure
 Integrity constraints
 External
 What a user sees of the data
 View is often limited by security
Data Independence
 Each level is “independent” in the sense that a
completely different organization can be used
 Physical data independence - Physical level can change
without having to change the logical level
 Logical data independence - Logical level can change
without having to change the external level
Lifecycle of an Information System
1.
2.
3.
4.
5.
6.
7.
8.
Feasibility analysis
Requirements formulation
System design
Database design
Implementation
Validation and acceptance testing
Loading/data conversion
Operation
.Conversion Training
.Monitoring and tuning
.New requirements
Lifecycle of an Information System

transaction
design


logical
database
design
operation


chose
DBMS
load
data
ER model


feasibility
analysis
conceptual
database
design
requirements
cost/benefit
analysis


specify
reqs.
formulate
reqs.


system
design
database
design
plan for
information
system
requirements analysis phase


test and
evaluate
physical
database
design
tables, indexes,
and storage types
database design phase
Development Methodology using a DBMS

implement
implementation phase
Lifecycle of an Information System
 Successful design depends on an iterative approach,
starting at an abstract level and adding detail
 Appropriate data models at each level focus on the
important aspects
 The end result is a fully elaborated design, with each
major design decision well documented
 Modification should proceed from the appropriate level
downward
Entity-Relationship Model
 This model is used in conceptual design
 Popular in CASE tools, e.g., ERWin, DataArchitect
 A database can be modelled as
 a collection of entity types, and
 relationships among entity types
 Result is an ER Schema or an ER Diagram
Entity
Definition: An entity is an object that exists and is
distinguishable from other objects.
 Object, exists, distinguishable
 Video store mini-world example
 Physically exist
The CUSTOMER Jane Doe.
The Xtra-vision STORE.
The EMPLOYEE Juan Gonzales.
Entity, cont.
 Abstract or organisational entities that do not exist
physically
 The Introduction to Databases COURSE.
 The SCHOOL of Electrical Engineering and Computer
Science.
 Events
 The TUTORIAL on Friday, October 31, 2003.
 The EXAMINATION on Tuesday December 15, 2003.
Attribute
Definition: An attribute is a property of an entity.
 Feature
example: CUSTOMER
Name
Date of Birth (DOB)
example: VIDEO TAPE
Copy number Title
 CUSTOMER entity examples
Name=Jane, DOB=1/1/70
Name=Joe, DOB=5/6/75
Distinguishing Entities
 How can entities be distinguished from each other?
 Assumption: Each entity will have a unique
combination of attribute values.
 Example, assume the Customer entity type has Name and DOB
attributes. There may be several Customers with a Name of
‘Joe’. Also, there may be several Customers with a DOB of
5/6/75. But there can be at most one Customer with the Name
‘Joe’ and a DOB of 5/6/75.
CUSTOMER entities
Name=Joe, DOB=5/6/75
Name=Joe, DOB=10/7/49
Name=Fred, DOB=5/6/75
 Is there a smaller such set of attributes?
18
Keys




Definition: A super key of an entity type is a set of one or more
attributes whose values uniquely determine each entity.
Assume each Customer has a unique ID. The following are super
keys. {Name, DOB, ID}, {Name, ID}, {Name, DOB}, {DOB, ID},
{ID}
Definition: A candidate key is a minimal super key.
{Name, DOB} is a candidate key of Customer.
{ID} is a candidate key of Customer.
Definition: Generally, there are may be several candidate keys.
One of the keys is selected to be the primary key.
{ID} is the primary key of Customer.
ER Diagram - Diamond Notation
 An entity type is represented with a labelled rectangle.
Customer
 An attribute is represented with a labelled oval,
connected to an entity type with a line.
DOB
Customer
Name
Attributes - Diamond Notation
 Single valued versus multi-valued
 E.g., multiple telephone extensions
Customer
Extension
 Simple attributes versus structured attributes
 E.g., an address is composed of a number, a street, a city and a
state
Street
Customer
Address
City
State
Attributes, cont.
 Stored versus derived
 E.g., an age can be derived from a stored birthdate
DOB
Customer
Age
 Additional constraints - not represented in a diagram
 Null versus non-null
 Domain constrained
 E.g., a student number must be exactly 9 decimal digits
Keys in ER Diagrams
 An attribute that is part of the (primary) key is
underlined.
 E.g., ID uniquely identifies customers
ID
Name
Address
Customer
 Assume no two customers with the same name live at
the same address.
ID
Name
Address
Customer
Relationship
Definition: A relationship is an object that associates
entities.
relationship objects
Finding Nemo copy 1
Joe Jenkins
Jane Doe
Babe copy 3
Video Tape entities
Customer entities
Relationship Types - Diamond Notation
 A relationship type is represented with a labelled
diamond, and has lines to each of the entity types it
relates.
 The relationship type could have attributes.
Address
Customer ID
Customer
Rents
Video Tape
Name
Return Date
Location
 A relationship type does not have key attribute(s) – the
25
key comes from entity types that it relates.
Kinds of Relationship Types
 How many of each entity type participate in a
relationship type?




1-1
1-many
many-1
many-many
 Total vs. Partial participation
 Total participation means that every entity participates in the
relationship.
 Partial means that some entities might not participate.
 Bounds (min-max) on participation
 (At least) three different “diamond” notations
 Arrow vs. straight line with or without participation bounds
 DO NOT MIX NOTATIONS!
An Example One-to-One Relationship Type
 A Customer might be an Employee.
 An Employee IsA Customer.
 Participation is total on the Employee side in this
example.
Customer
IsA
Employee
Diagramming One-To-One Rel. Types
A
A
A
(0,1)
1
B
B
B
(0,1)
1
C
C
C
 An element in A is associated with at most one element
in C via the relationship B. An element in C is
associated with at most one element in A via B.
Total vs. Partial Participation
 Use a double line to indicate total participation.
 Example: Every A is in a B relationship with exactly
one C, but some C’s may be unrelated to an A.
A
1
B
1
C
 Using participation constraints, total participation is a
1 on the minimum bound.
A
(1,1)
B
(0,1)
C
An Example One-to-Many Relationship Type
 A Customer Rents zero to several Video Tapes.
 A Video Tape can be Rented by at most one Customer.
 Participation is partial on both sides in this example.
Customer
Rents
Video Tape
One-To-Many Relationship
A
A
A
(0,m)
1
B
B
B
(0,1)
m
C
C
C
 An element in A is associated with several (including 0)
elements in C via B. An element in C is associated with
at most one element in A via B.
An Example Many-to-Many Rel. Type
 A Performer Stars In one or many Films.
 A Film can Star zero to many Performers.
 Participation is total on the Performer in this example.
Performer
Stars In
Film
Many-To-Many Relationship
A
A
A
(0,m)
n
B
B
B
(0,n)
m
C
C
C
 An element in A is associated with several elements in C
via B.
 An element in C is associated with several elements in A
via B.
Weak Entity Types
 Definition: A weak entity type borrows key attributes
from another entity type (called the owning or strong
entity type) to uniquely identify entities.
 Example: A Video Tape has a Copy #, relative to a Film title
(e.g., `Babe copy 1’, `Babe copy 2’, `Finding Nemo copy 1’.
Copy # is not a key, but combined with Title it is (for Video
Tape).
Title
Film
(0,n)
Status
Copies
(1,1)
Copy #
VideoTape
 ER diagram - double box represents weak entity type.
 The existence of a Video Tape entity depends on the
existence of a Film entity.
Weak Entity Type – Partial Keys
 A weak entity type has a partial key (key is completed
by borrowing key attributes from owning entity
type(s)).
 Example: Key for Video Tape is (Title, Copy#).
Title
Film
(0,n)
Status
Copies
(1,1)
Copy #
VideoTape
 ER diagram - partial key represented with dashed line.
Weak Entity Type, cont.
 Semantics:
 Deletion of a Film entity requires deletion of that film's video
tape entities.
 A weak entity is related to precisely one entity in the
owning entity type, via a 1-1 or 1-many relationship.
 It is possible to introduce more attributes to the video
tape entity type, so that a primary key will exist, but
they may not be needed for database processing.