Transcript CS206 --- Electronic Commerce

Chapter 2
Database Modeling
Data Models
Concept Model & Data Model
Entity-Relationship diagrams
1
What are Data Model?
Computer simulation of the real world.
To abstract, denote and transact the
information in the real world.
2
Data Model should :
Simulate the real world comparatively truly
Be easy to understand
Be easy to be implemented by computer
3
Abstraction and Modeling
Two steps of the abstraction and modeling:
1. The objective objects in the real world is
abstracted to a concept model.
2. Concept model is converted to a data model that
supported by some DBMS.
The concept model is a middle layer between the real
world and the machine world.
客观对象
现实世界
概念模型
信息世界
数据模型
机器世界
4
The classification of the models
Concept model
 Also called information model.
 Modeling data from the viewpoint of users.
 a middle layer between the real world and the machine
world.
 Mainly used for a database design.
 The language in which the database designers and users
communicate with each other.
 It should be simple, clear and easy to understand by
users.
5
Data model
Modeling data from the viewpoint of computer.
Mainly used for a DBMS realization.
Traditional data models
Hierarchical Model
Network Model
Relational Model
Main factors of the data model
Data structure
Data operation
Integrity constraints
6
数据模型的组成要素
数据结构
 指所研究数据集合及数据间的联系
 是对系统静态特性的描述
数据操作
 对数据库中各类数据允许执行的操作及有关的操作规则
• 检索、更新（包括插入、删除、修改）
 是对系统动态特性的描述
数据的约束条件
 一组数据及其联系所具有的制约规则，用以限定符合数
据模型的数据库状态以及状态的变化，以保证数据的正
确、有效、相容
7
E-R Model
The concept model is represented by
Entity-Relationship（E-R）diagrams.
The E/R model
 Describes kinds of data and how they
connected.
 Can be converted to relational model later.
8
Entity Sets
Entity = “thing” or object.
Entity set = collection of similar entities.
 Similar to a class in object-oriented languages.
Attribute = property of (the entities of) an entity
set.
 Attributes are simple values, e.g. integers or
character strings.
9
E/R Diagrams
In an entity-relationship diagram:
 Entity set = rectangle.
 Attribute = oval, with a line to the rectangle
representing its entity set.
10
Example
name
address
Stars
Entity set Stars has two attributes, name
and address.
Each star has values for these two
attributes.
11
Relationships
A relationship connects two or more entity
sets.
It is represented by a diamond, with lines to
each of the entity sets involved.
12
Example
title
year Filmtype
Movies
Stars_in
address
name
Stars
length
The arrow pointing to entity set
studios indicates that each movie
is owned by a unique studio.
Owns
P41
name
Studios
address
13
Relationship Set
The current “value” of an entity set is the set
of entities that belong to it.
 Example: the set of all stars in our database.
The “value” of a relationship is a set of lists
of currently related entities, one from each of
the related entity sets.
14
Example
For the relationship Stars_ in, we might have a
relationship set like:
Movies
Basic Instinct
Stars
Sharon Stone
Total Recall
Arnold
Schwarzenegger
Sharon Stone
Total Recall
15
Multiway Relationships
Sometimes, we need a relationship that
connects more than two entity sets.
16
A Typical Relationship Set
Studios
Movies
stars
Universal Studio
King Kong
Jack Black
17
Many-Many Relationships
In a many-many relationship, an entity of
either set can be connected to many entities
of the other set.
 E.g., a star can appear in many movies; a movie
can have more than one star.
18
In Pictures:
many-many
19
Many-One Relationships
Some binary relationships are many -one
from one entity set to another.
 Each entity of the first set is connected to at
most one entity of the second set.
 But an entity of the second set can be connected
to zero, one, or many entities of the first set.
20
In Pictures:
many-one
21
Example
Owns, from Movies to Studios is manyone.
 A movie is owned by at most one studio.
 But a studio owns any number of movies.
22
One-One Relationships
In a one-one relationship, each entity of either
entity set is related to at most one entity of the
other set.
Example: Relationship Runs between entity sets
Studios and Presidents.
 A president can run only one studio and a studio has
only one president.
23
In Pictures:
one-one
24
Representing “Multiplicity”
Show a many-one relationship by an arrow
entering the “one” side.
Show a one-one relationship by arrows
entering both entity sets.
25
Attributes on Relationships
Sometimes it is useful to attach an attribute
to a relationship.
Think of this attribute as a property of
tuples in the relationship set.
26
Example
studios
Movies
Contracts
stars
salary
It is appropriate to associate a salary with the (star,
movie, studio) triple in the relationship set for the
Contracts relationship.
27
Keys
A key is a set of attributes for one entity set such
that no two entities in this set agree on all the
attributes of the key.
 It is allowed for two entities to agree on some, but not
all, of the key attributes.
We must designate a key for every entity set.
Keys in E/R Diagrams: Underline the key
attribute(s)
28
Example: a Multi-attribute Key
dept
number
hours
room
Courses
• Note that hours and room could also serve as a
key, but we must select only one key.
29
Design Techniques
1. Avoid redundancy.
2. Don’t use an entity set when an attribute
will do.
30
Avoiding Redundancy
Redundancy occurs when we say the same
thing in two or more different ways.
Redundancy wastes space and (more
importantly) encourages inconsistency.
 The two instances of the same fact may become
inconsistent if we change one and forget to
change the other.
31
Example: Good
title
Movies
year
name
Owns
addr
Studios
This design gives the name of each studio exactly once.
32
Example: Bad
title
Movies
year
name
Owns
addr
Studios
StudioName
This design states the name of each studio twice: as an attribute
and as a related entity.
33
Example: Bad
title
year
Movies
StudioName
StudioAddr
This design repeats the studio’s address once for each
movie and loses the address if there are temporarily no
movie for a studio.
34
Entity Sets Versus Attributes
 An entity set should satisfy at least one of
the following conditions:
 It is more than the name of something; it has
at least one nonkey attribute.
or
 It is the “many” in a many-one or many-many
relationship.
35
Example: Good
title
Movies
name
Owns
addr
Studio
•Studio deserves to be an entity set because of
the
nonkey attribute addr.
•Movies deserves to be an entity set because it is the
“many” of the many-one relationship Owns.
36
Example: Good
title
StudioName
Movies
There is no need to make the studio an entity set,
because we record nothing about studios besides their
name.
37
Example: Bad
title
Movies
name
Owns
Studios
Since the Studios is nothing but a name, and is not at
the “many” end of any relationship, it should not be an
entity set.
38
Reading Guide
Required: 2.2,2.5
Recommended: 2.3,2.8 ,《数据库系统概论》第一章
39
Exercises
To manage the materials of a factory, please give the E-R diagrams. Such
entity sets are involved as below:
 Warehouse: with attributes as Number, area and telephone
 Accessory : with attributes as number, Name, Specification, price
and description
 Supplier: with attributes as Number, name, addr, telephone and
account
 Project : with attributes as number and budget
 Employee : with attributes as number, name and age
The relationships among these entity sets are listed as below:
 A kind of accessory can be deposited in more than one warehouse. A
warehouse can accommodate more than one kind of accessory.
 There can be more than one employee in a warehouse, and a employee
can work in only one warehouse.
 Some employee can leader others.
 A supplier can supply various accessories to various projects. A
project can use accessories supplied by various suppliers. A kind of
accessory can be supplied by various supplier.
40