CS计算机代考程序代写 data structure database ER The Relational Data Model

The Relational Data Model

2/14/20 1

2. The Relational Data Model

• use a simple and uniform data structure: the

relation

• has been implemented in most commercial

database systems

• has a solid theoretic foundation.

2/14/20 2

2.1 Structures

• In the relational model, everything is described using relations.

• A relation can be thought of as a named table.

• Each column of the table corresponds to a named attribute.

• The set of allowed values for an attribute is called its domain.

• Each row of the table is called a tuple of the relation.

• N.B. There is no ordering of column or rows.

2/14/20 3

Example
PLAYER

Name Position Goals Age Height Weight
Heady Half-forward 17 24 183 83
Sumich Full-forward 59 26 191 92
Langdon Utility 23 23 189 86

PLAYER
Name Age Height Weight Goals Position

Sumich 26 191 92 59 Full-forward
Langdon 23 189 86 23 Utility
Heady 24 183 83 17 Half-forward

2/14/20 4

Above two tables are the same relation —- Player

• Mathematically,
– a domain D is a set of atomic values (having some fixed

data type) which represent some semantic meaning.

– an attribute, A, is the name of a role played by a domain,
dom(A).

– a relation schema R, denoted by

R(A1,A2, …,An), is a set of attributes

R = {A1,A2, …,An}.

Composite and multivalued attributes are disallowed!

2/14/20 5

• A tuple, t(A1,A2, …,An), is a point in dom(A1) × . . . × dom(An)

where each dom(Aj) is the domain of Aj.

• A relation (or a relation instance) is a set of tuples: a subset of

dom(A1) × . . . × dom(An).

• A relation schema is used to describe a relation.

• The degree of a relation is the number of attributes of its relation
schema.

2/14/20 6

Relational Data Model vs ER Model:

• Relation schema (intension) ⇄ entity or relationship type schema (intension).

• attributes ⇄ attributes

• tuple ⇄ instance of entity/relationship

• relation (instance, extension) ⇄ entity/relationship extension

• composite and multivalued attributes are allowed in ER model, but not allowed in
relational data model.

2/14/20 7

• Keys are used to identify tuples in a relation.

• A superkey is a set of attributes that uniquely determines a tuple.

• Note that this is a property of the relation that does not depend on the

current relation instance.

• A candidate key is a superkey, none of whose proper subsets is a superkey.

• Keys are determined by the applications.

• E.g. if {Name} is unique then it is a candidate key for PLAYER; otherwise

we need to use the whole tuple or create a candidate key, say PID.

• {Goals} usually cannot not be a candidate key since different players might

have the same number of goals.

• {Name, Goals} is a superkey but not a candidate key if {Name} is a key.

2/14/20 8

• A primary key is a designated candidate key.

• In many applications it is necessary to invent a primary key if there
is no natural one – often this would be a non-negative integer

• e.g. Person_number.

• When a relation schema has several candidate keys, usually better to
choose a primary key with a single attribute or a small number of

attributes.

2/14/20 9

2.2 Integrity constraints
• There are several kinds of integrity constraints that are an integral part of the

relational model:

• 2.2.1 Key constraint: candidate key values must be unique for every relation

instance.

• 2.2.2 Entity integrity: an attribute that is part of a primary key cannot be NULL.

• 2.2.3 Referential integrity: The third kind has to do with “foreign keys”.

2/14/20 10

• Foreign keys are used to refer to a tuple in another relation.

• A set, FK, of attributes from a relation schema R1 may be a foreign key if

– the attributes have the same domains as the attributes in the primary key of another relation

schema R2, and

– a value of FK in a tuple t1 of R1 either occurs as a value of PK for some tuple t2 in R2 or is null.

• Referential integrity: The value of FK must occur in the other relation or be entirely

NULL.

2/14/20 11

2.2.4 Checking constraints on updates

– To maintain the integrity of the database, we need to check that integrity

constraints will not be violated before proceeding with an update.

– Example: Suppose we have the following schema with foreign keys as

shown:

2/14/20 12

2/14/20 13

<2, Dr. V. Ciesielski>
insert

2/14/20 14


insert

2/14/20 15

<5, 6, 2, Psychology, Ph.D>

insert

2/14/20 16

• Insertions: When inserting, we need to check

– that the candidate keys are not already present,

– that the value of each foreign key either

–is all null, or

– is all non-NULL and occurs in the referenced relation.

Examples:

1. Insert < 2, Dr.V.Ciesielski > into RESEARCHER

Allowed? No. Violates a key constraint.

Action? Reject or allow the user to correct.

2/14/20 17

2. Insert < Comp.Sci.,NULL > into COURSE

Allowed? No. Violates the entity integrity

constraint.

Action: Reject or correct.

3. Insert < 5, 6, 2, Psychology, Ph.D. > into

ENROLMENT

Allowed? No. Violates a referential integrity

constraint (There is no person number 6).

Action: Reject, correct or accept after insertion

of person number 6.

2/14/20 18

• Deletions: When deleting, we need to check referential

integrity – check whether the primary key occurs in another

relation.

Examples:

1. Delete tuple with Person# = 2 from RESEARCHER

Allowed? No. Violates the referential integrity.

Action: Reject, correct or modify the ENROLMENT tuple by

2/14/20 19

• deleting it (note that the this requires another

integrity check, possibly causing a cascade of

deletions), or

• setting the foreign key value to NULL (note this

can’t be done if it is part of a primary key), or

• setting the foreign key value to another

acceptable value.

2/14/20 20

Modifications:

If the modified attribute is a

• primary key: this is similar to deleting and

then reinserting.

• foreign key: check that the new value refers

to an existing tuple.

• neither: no problems can arise.

2/14/20 21

2.2.5 Relational database definition

– A relational database schema, is a set of relation
schema {R1, . . . ,Rm} and a set of integrity
constraints.

– A relational database instance is a set of relation
instances {r1, . . . , rm} such that each ri is an
instance of Ri , and the integrity constraints are
satisfied.

2/14/20 22

2.3 ER to Relational Data Model Mapping

• One technique for database design is to first design a
conceptual schema using a high-level data model, and

then map it to a conceptual schema in the DBMS data

model for the chosen DBMS.

• Here we look at a way to do this mapping from the ER
to the relational data model.

• It involves the following 7 steps.

2/14/20 23

• Example: ER RDB

2/14/20 24

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

* *

• Step 1 : For each regular (not weak) entity type E,
create a relation R with

– Attributes : All simple attributes (and simple components

of composite attributes) of E.

– Key : Choose one of the keys of E as the primary key for

the relation.

2/14/20 25

• Step 1a : For each specialised entity type E,

with parent entity type P, create a relation R

with

– Attributes : The attributes of the key of P, plus the

simple attributes of E.

– Key : The key of P.

2/14/20 26

• Example: ER RDB

2/14/20 27

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

2/14/20 28

SSN Fname Lname Birdate

Name Location

Pname Pnumber

Employee

Department

Project

• Step 2 : For each weak entity type W, with owner
entity type E, create a relation R with

– Attributes : All simple attributes (and simple
components of composite attributes) of W, and include

as a foreign key the prime attributes of the relation

derived from E.

– Key : The foreign key plus the partial key of W.

2/14/20 29

• Example: ER RDB

2/14/20 30

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

2/14/20 31

SSN Fname Lname Birdate

Name Location

Pname Pnumber

Employee

Department

Project

SSN Fname Birdate Relation

Dependent

• Step 3 : For each 1:1 relationship type B. Let E and F be the participating

entity types. Let S and T be the corresponding relations.

– Choose one of S and T (prefer one that participates totally), say S.

– Add the attributes of the primary key of T to S as a foreign key.

– Add the simple attributes (and simple components of composite attributes) of B

as attributes of S.

(Alternative: merge the two entity types and the relationship into a single

relation, especially if both participate totally and do not participate in other

relationships).

2/14/20 32

• Example: ER RDB

2/14/20 33

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

2/14/20 34

SSN Fname Lname Birdate

Name Location MSSN Mdate

Pname Pnumber

Employee

Department

Project

SSN Fname Birdate Relation

Dependent

• Step 4 : For each regular 1:N relationship type B.

– Let E and F be the participating entity types.

– Let E by the entity type on the 1 side, F the one on the N side.

– Let S and T be the corresponding relations.

– Add the attributes of the primary key of S to T as a foreign key.

– Add to T any simple attributes (or simple components of composite

attributes) of the relationship.

(Notice that this doesn’t add any new tuples, just attributes.)

2/14/20 35

• Example: ER RDB

2/14/20 36

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

2/14/20 37

SSN Fname Lname Birdate Dname

Name Location MSSN Mdate

Pname Pnumber

Employee

Department

Project

SSN Fname Birdate Relation

Dependent

• Step 5 : For each N:M relationship type B. Create

a new relation R. Let E and F be the participating entity types. Let S

and T be the corresponding relations.

– Attributes : The key of S and the key of T as foreign keys, plus the

simple attributes (and simple components of composite attributes) of B.

– Key : The key of S and the key of T.

2/14/20 38

• Example: ER RDB

2/14/20 39

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

2/14/20 40

SSN Fname Lname Birdate Dname

Name Location MSSN Mdate

Pname Pnumber

Employee

Department

Project

SSN Fname Birdate Relation

Dependent

Pname ESSN Time

Participation

• Step 6 : For each multivalued attribute A. Create a new
relation R. Let A be an attribute of E.

– Attributes :
1. A (if A is a simple attribute) together with the key of

E as a foreign key.
2. The simple components of A (if A is a composite

attribute), together with the key of E as a foreign key.

– Key : All attributes.

2/14/20 41

• Example: ER RDB

2/14/20 42

SSN

SSN

Birdate

Fname

Lname

Employee

Works_For

Family

Dependent

Fname- – – – — Birdate Relation

Mdate

Manager

Participation

Time

Phone

Name Location

Department

Project

Pname Pnumber

m

m

m

n

1

11
1

2/14/20 43

SSN Fname Lname Birdate Dname

Name Location MSSN Msdate

Pname Pnumber

SSN Fname Birdate Relation

Pname ESSN Time

Dname Dnumber

Employee

Department

Project

Dependent

Participation

D_number

• Step 7 : For each n-ary relationship type (n > 2). Create a
new relation with

– Attributes : as for Step 5.

– Key : as for Step 5, except that if one of the participating entity
types has participation ratio 1, its key can be used as a key for
the new relation.

2/14/20 44
* *