Transcript Entity

Technology Review
Professor Martin
Professor Xiong
CSUS
This lecture is based primarily on Romney & Steinbart(2003). It
also draws on Martin (2002).
Updated on: Monday, January 27, 2003
Agenda



Database Management–An
Introduction
Relational Database
Entity-Relationship Diagram
WHAT IS DATA MANAGEMENT?
(The first seven slides are based on Watson (2002))


The management of organizational memory
Involves designing, using, and managing
memory systems of modern organizations
EXAMPLES OF INDIVIDUAL AND
ORGANIZATIONAL MEMORY SYSTEMS

INDIVIDUAL
Internal
memory
External memory (diaries, bookmarks, address
books)

ORGANIZATIONAL
examples
people, filing cabinets, policy
manuals, planning boards, and computers. (Do
organizations have external memories?)
Characteristics similar to Individual memory
DESIRABLE ATTRIBUTES OF
ORGANIZATIONAL MEMORY

Shareable
 readily

Transportable
 Easily

accessed by more than one person at a time
moved to a decision maker
Secure
 Protected

Accurate
 Reliable,

precise records
Timely
 Current

from destruction and unauthorized use
and up-to-date
Relevant
 Appropriate
to the decision
TYPICAL PROBLEMS WITH FILEBASED SYSTEMS







Organizational memory may be seen as a vast,
disorganized data warehouse. Problems include:
Redundancy: same data stored in different memories
Data control: data not managed as a valuable resource
Interface: difficult to access data
Delays: long delays in responding to requests for data
Lack of reality: data do not reflect the complexity of the
real world
Lack of data integration: data dispersed across different
systems; also where data is stored may not be known.
File-Oriented Approach
File # 1
Item A
Item B
Item C
Application
program #1
File # 2
Item B
Item D
Item E
Application
program #2
DATABASE APPROACH TO
MANAGING PERSISTENT DATA
The
database approach emphasizes the
integration and sharing of data across
the organization.
Database Approach
Application
program #1
Database
Item A
Item B
Item C
Item D
Item E
Database
management
system
Application
program #2
Application
program #3
BENEFITS OF
THE DATABASE APPROACH






Redundancy can be reduced
Thus, inconsistency can be avoided
Integration of data
Data can be shared among applications
Standards can be enforced by the DBA
formats, representation, naming,
documentation
Security restrictions can be applied
BENEFITS OF
THE DATABASE APPROACH

Data integrity can be maintained
by minimizing inconsistency
by having controls to check against
incorrect updates, especially in the
multi-user context
BENEFITS OF
THE DATABASE APPROACH

Data independence
 Broadly
-- the immunity of applications to change in
storage structure and access technique
 Logical -- capacity to change conceptual schema
without changing application programs (e.g., adding
an attribute or an entity type)
 Physical -- capacity to change internal schema
without having to change external or conceptual
schema (e.g., creating additional access structures to
improve retrieval performance)



Ease of Application Development
Data accessibility and responsiveness enhanced
Reduced program maintenance
SOME DEFINITIONS



What is a database?
a shared collection of logically related
persistent data, designed to meet the
needs of multiple users usually within
an organization.
What is a database management system?
DBMS is a collection of programs that
enables users to define, construct and
manipulate a database. (More detailed
defn. later).
What is a database system?
FUNCTIONS OF A DBMS






Data definition using DDL
Data manipulation using DML
Data security and integrity
Data recovery and concurrency control
Data dictionary
Satisfactory performance
STEPS IN DATABASE
DEVELOPMENT PROCESS

Analysis
creation

of the Entity-Relationship Model
Design
Logical
Database Design
creation of normalized relations
Physical Database Design
specification storage technology
requirements
specification/ creation of appropriate file
structures
Schemas



What are schemas?
A schema describes the logical
structure of a database.
There are three levels of schemas:
1 Conceptual-level schema
2 External-level schema
3 Internal-level schema
Schemas



The conceptual-level schema is an
organization-wide view of the entire
database.
The external-level schema consists
of a set of individual user views of
portions of the database, also
referred to as a subschema.
The internal-level schema provides a
low-level view of the database.
Agenda



Database Management–An
Introduction
Relational Database
Entity-Relationship Diagram
Relational Databases



A data model is an abstract
representation of the contents of a
database.
The relational data model represents
everything in the database as being
stored in the form of tables.
Technically, these tables are called
relations.
Basic Requirements of the
Relational Data Model
1
2
3
Primary keys must be unique.
Every foreign key must either be null
or have a value corresponding to the
value of a primary key in another
relation.
Each column in a table must describe
a characteristic of the object
identified by the primary key.
Basic Requirements of the
Relational Data Model
4
5
6
Each column in a row must be singlevalued.
The value in every row of a specific
column must be of the same data
type.
Neither column order nor row order
is significant.
Accessing records

Records are typically
updated, stored, and
retrieved using an identifier
called a primary key
– customer number for customer file
– invoice number for invoice file
– stock number for inventory file
Accessing Records
A secondary key is another
field used to identify a record
 Secondary keys do not uniquely
identify individual records
 Examples of secondary keys
– invoice due date
– zip code
– bank customer last name

Accessing Records


Foreign key:
attribute
(field) in one table
(record)
that matches
primary key in
another table
Used to link tables
together
Relational Database
Primary
Key
***
Product Vendor
Number Code
123467 ZDG
243893 CFC
277883 TBT
476556 BBC
775622
DFF
Product Table
Go to top of
Vendor Table
Search
sequentially
until find
‘BBC”
Foreign
Key
Vendor Table
Vendor
Code
ACC
Ship
Mode
BAD
ARP
BBC
TRK
CAC
UPS
TRK
Relational Databases
Formal
Term
Less
Formal
Term
Data
Processing
Term
relation
table
file
tuple
row
record
attribute
column
field
Agenda



Database Management–An
Introduction
Relational Database
Entity-Relationship Diagram
ENTITY-RELATIONSHIP MODEL
(proposed by CHEN, 1976)


A detailed logical representation of data for an
organization or business area
Four Basic Constructs:
-Entity
-Relationship
-Attribute
-Cardinality (participation)
1. ENTITY: Entities are named objects
in the universe of discourse

Types of entities
Thing (truck, building)
 Person (customer, employee)
 Event
 Instant duration (sale, purchase, cash receipt)
 Extended duration (month-long use of a truck, a
course offering that starts on JAN 3 & ends on 15
May)

 Concept
(category of customer, course)
 SYMBOL -- Rectangle
Customer
Course
offering
2. RELATIONSHIP : Association
between two (or more ?) entities


Examples:
employee “assigned to” building
customer “participates in” sale
professor “teaches” course-offering
SYMBOL -- Diamond
Customer
participates
in
Sale
3. ATTRIBUTE : Characteristics or elementary properties of
entities or relationships. They are used for actual
communication about the real world phenomena
represented by entities or relationships



Example attributes for the entity INVENTORY:
 stock#, color, price, cost, weight
A primary key is a special attribute used to represent an
instance of an entity or relationship in a database
 Must be unique and universal
 Can be a concatenated (combined key)
 “No representation without identification”
 For this class, we assume that relationships are identified
by the keys of their participating entities
SYMBOL – small connected circle (filled in for primary key)
Inventory
Stock#
Color
Price
4. Participation CARDINALITY (min, max):
These show the correspondence of
entities and relationships
A
(min, max)
Entity “A” participates in
relationship “rel” at a
minimum of
- “0” times (optional)
- “1” time (mandatory)
rel
B
Entity “A” participates in
relationship “rel” at a
maximum of
- “1” time (single time only)
- “n” times (many times)
4. Participation CARDINALITY (min, max):
(other side of relationship)
A
Entity “B” participates in
relationship “rel” at a
minimum of
- “0” times (optional)
- “1” time (mandatory)
rel
(min, max)
B
Entity “B” participates in
relationship “rel” at a
maximum of
- “1” time (single time only)
- “n” times (many times)
An Example











Assuming two entities (EMPLOYEE and COURSE), draw an E-R
diagram for the following (sample data). Assume that
Employee_name and Course_titles are unique. Also assume other
attributes such as Employee Address, and Course Credits.
Employee_name
Course_title
Date_completed
Chen
C++
06/98
Chen
Java
09/98
Lisa
C++
06/98
Lisa
SQL
03/99
Trina
Java
03/98
Heikki
Perl
06/98
Heikki
Java
09/98
……….
…….
……..
……….
…….
……..
More Examples


A company has a number of employees. The attributes
of EMPLOYEE include NAME, ADDRESS, and BIRTHDATE. The company also has several projects. The
attributes of Project include PROJECT_CODE,
DESCRIPTION, and START_DATE. Each employee may
be assigned to one or more projects, or may not be
assigned to any project. A project is required to have at
least one employee assigned, but may have several
employees assigned.
A university has a large number of courses in its
catalog. Attributes of courses include CRS_NO,
CRS_NAME, and UNITS. Each course may have one or
more other courses as prerequisites, or may have no
prerequisite.
Assignment


A college course may have one or more scheduled
sections, or may not have a scheduled section.
COURSE attributes include CRS_ID, CRS_NAME,
and UNITS. Attributes of SECTION include
SECTION_NO and INSTRUCTOR.
A laboratory has several chemists who work on
various projects, and who may use certain kinds of
equipment on each project. Attributes of CHEMIST
include CHEMIST_ID, NAME, and PHONE. Attributes
of PROJECT include PROJ_ID and START_DATE.
Attributes of EQUIPMENT include EQUIP_NO and
COST.
Topics Discussed



Database Management–An
Introduction
Relational Database
Entity-Relationship Diagram