CS计算机代考程序代写 database ER Conceptual Database Design

Conceptual Database Design

2/14/20 1

1. Conceptual Database Design

2/14/20 2

Appl E-R Relational DB

Database Design

Entity-Relationship Model

• The Entity-Relationship (ER) model is a high-

level conceptual data model (Chen in 1966).

• ER is used mainly as a design tool.

2/14/20 3

Entity-Relationship Model(cont)

• Entity type: Group of object with the same properties

• Entity: member of an entity type – analogous to an object.

• Attribute: a property of object

• Relationship: among objects

– ER can model “n-way” relationship,

– ER models a relationship and its inverse by a single
relationship.

2/14/20 4

1.1 Entity and Attributes

• Entities represent things in the real word.

• Attributes describe properties of entities.

• Attributes may be

– simple(atomic) e.g. sex = ’Female’, or

– composite e.g. name consists of title (Dr), Initials
(C.C.), family name (Chen).

2/14/20 5

1.1 Entity and Attributes(cont)

• Each entity has values for each attribute.

• Attributes may be

– single-valued e.g. student number, name, or

– multivalued e.g. keywords = neural networks,

computer graphics, databases.

2/14/20 6

1.1 Entity and Attributes(cont)

• Each simple attribute has a value set (domain): the set of possible
values for that attribute.

• In a composite attribute A = (A1, . . . , An), suppose that V1, …, Vn are
the domains of A1, . . . , An.

• The domain V of A is V1 × . . . × Vn.

• Mathematically, an attribute A of an entity type E is a function

A : E → ℘(V ) .

• where V is the domain of A, and ℘(V ) is the power set of V

• For single-valued attributes, A(e) must be a singleton.
2/14/20 7

1.1 Entity and Attributes(cont)

• An attribute can have a null value if, for example:
– there is no suitable value e.g. a student may have no

interests: keywords = NULL

– the true value is not known e.g. the marriage date of a
person is not known: marriage date = NULL.

• A derived attribute is one whose value can be derived
from other attributes and entities. e.g. number of
students.

2/14/20 8

1.1 Entity and Attributes(cont)

• An entity type is a set of entities with the same
attributes.

• It is described by an entity schema: a name and a
list of attributes.

• The set of individual entity instances at a
particular moment in time is called an extension
of the entity type.

2/14/20 9

1.1 Entity and Attributes(cont)

Schema
(Intension)

RESEACHER
Name, Payroll_no, No_of_students,

Keywords

DEPARTMENT
Name

Instances
(Extension)

(Dr C.C. Chen, 230-0013, 3, Neural Networks)

(Dr R. Wilkinson, 231-0091, 1, Databases)

Computer Science
Psychology

Management

2/14/20 10

1.1 Entity and Attributes(cont)

• An entity type usually has a key: a set of attributes that
uniquely identifies an entity. For example:
– {payroll number} is a key of RESEARCHER,

– {name} is a key of DEPARTMENT.

• There may be more than one possible key.

• An important constraint is the key constraint: in any
extension of the entity type, there cannot be two entities
having the same values for their key attributes.

2/14/20 11

1.1 Entity and Attributes(cont)

• We can describe schemata with composite

attributes using ()’s and with multi-valued

attributes using {}’s. e.g.

2/14/20 12

1.1 Entity and Attributes(cont)
CAR

Registration(Registration No, State), Make, Model, Year, {Colour}
((ARQ) 595, Vic), Datsun, 120Y, 1972, {green})

((8HR) 696, WA), Mazda, 929, 1979, {grey, black})

2/14/20 13

1.1 Entity and Attributes(cont)

• Entities and their attributes can also be
described with Entity-Relationship Diagrams
(ERDs). e.g.

2/14/20 14

Registration
Number

Registration

State

Make Model Year

Colour
CAR

1.2 Relationships

• A relationship represents an association between things.

• A relationship type R among n entity types E1, . . . , En
is a set of associations among entities from these types.

• Mathematically, a relationship type R among entity

types E1, . . . , En is a subset of E1 × . . . × En.

• Each instance r = (e1, . . . , en) in R is a relationship.

2/14/20 15

1.2 Relationships(cont)

• We say that E1, . . . , En participate in R.

• Similarly if r = (e1, . . . , en) is an instance of R, we say that each ei
participates in r.

• The degree of R is the number of participating entity types. For
example,

– ENROLMENT could be a ternary (degree 3) relationship between
RESEARCHER, STUDENT and COURSE.

• We can illustrate this using an occurrence diagram:

2/14/20 16

2/14/20 17

1.2 Relationships(cont)

• Entities and their relationships can also be
represented using Entity-Relationship
diagrams:

2/14/20 18

ENROLME
NT

RESEARCHER

COURSE

STUDENT

1.2 Relationships(cont)

• Each entity type that participates in a relationship plays a particular role in

the relationship.

• An entity type can play

– different roles in different relationships, or

– more than one role in a relationship.

• A role name can be used to distinguish these.

• For example, ENROLMENT could be a relationship between PERSON(as

researcher), PERSON(as student) and COURSE as in the diagram below:

2/14/20 19

2/14/20 20

1.2 Relationships(cont)

• Or, using an ERD:

• This is called a recursive relationship.

2/14/20 21

1.3 Weak entity types

• Some entity types do not have a key of their own.

• Such entity types are called weak entity types.

• Entities of a weak entity type can be identified by a partial
key and by being related to another entity type – owner.

• The relationship type between a weak entity type to its
owner is the identifying relationship of the weak entity type.

2/14/20 22

1.3 Weak entity types(cont)

• For example, a TAX PAYER entity may be related to

several DEPENDENT, identified by their names.

• In this example, DEPENDENT is called a weak entity,

{Name} is a partial key for it. The identifying

relationship between DEPENDENT and TAX PAYER

is IS DEPENDENT OF. TAX PAYER is said to own

DEPENDENT.

2/14/20 23

1.4 Constraints on relationship types

• Relationship types usually have certain constraints that limit the possible

combinations of entities participating in relationship instances.

• They should reflect the correct factors

• Cardinality ratio constraint: specifies the number of relationship instances

an entity can participate in.

• Example: A research grant supports only one research project, but a

research project may be supported by many grants. PROJECT:GRANT is a

1 : N relationship.

2/14/20 24

1.4 Constraints on relationship types(cont)

• This is illustrated in the occurrence diagram
below:

2/14/20 25

1.4 Constraints on relationship types(cont)

• We can also show this in an ERD:

2/14/20 26

SUPPORTSGRANT PROJECT
N 1

1.4 Constraints on relationship types(cont)

• Example: Consider a database of AFL (here substitute
your favourite team sport) statistics. The relationship
of head coaches to clubs is an example of a 1 : 1
relationship.

2/14/20 27

1.4 Constraints on relationship types(cont)

• With an ERD:

2/14/20 28

COACHESEMPLOYEE CLUB
1 1

1.4 Constraints on relationship types(cont)

• Example: An example of an N : M relationship
is authorship of publications:

2/14/20 29

1.4 Constraints on relationship types(cont)

• The equivalent ERD:

2/14/20 30

AUTHOR_O
F

PERSON PUBLICATION
N M

1.2.4 Constraints on relationship
types(cont)

• Another kind of constraint that can be represented

using the ER model is a

– Participation constraint: participation of an entity in a

relationship can be:

• total: every entity must participate e.g. every publication has an

author.

• partial: not necessarily total. e.g. not every person has publications.

2/14/20 31

1.4 Constraints on relationship types(cont)

• This can be shown with an ERD like the one
below:

2/14/20 32

AUTHOR_O
F

PERSON PUBLICATION
N M

1.5 Attributes of relationship types

• Relationship types can have attributes – for
example,

– a researcher may work on several projects. The
fraction of her time devoted to a particular project

could be an attribute of the WORKS ON relationship

type.

• This can be shown in an ERD as below:

2/14/20 33

1.5 Attributes of relationship types(cont)

2/14/20 34

1.5 Attributes of relationship types(cont)

• The notation used for ERDs is summarised in
Elmasre/Navathe Figure 3.15.

2/14/20 35

1.6 Enhanced ER (EER) model

• Designers must use additionally modelling concepts

to

– represent the requirements from applications as accurately

and explicitly as possible.

2/14/20 36

1.6 Enhanced ER (EER) model(cont)

• There are many extensions to the ER model. We will
look at one:

– Specialisation: the process of defining a set of subclasses of
an entity type; this entity type is called the superclass of the

specialization.

– Generalisation: a reverse process of specialisation.

• A subclass inherits all the attributes of the superclasses.

2/14/20 37

1.6 Enhanced ER (EER) model(cont)

• A specialisation involves the following aspects:

– Define a set of subclasses of an entity type.

– Associate additional specific attributes with each subclass.

– Establish additional specific relationship types between each subclass and other

entity types, or other subclasses.

• A subclass may have multiple superclasses.

• A specialisation:

– may be either total or partial; and

– may be either disjoint or overlapping.

2/14/20 38

2/14/20 39

1.7 Design Principles

• Faithfulness: reflect reality.

• Avoid redundancy.

• Picking the right kind of element.

2/14/20 40