• Non ci sono risultati.

# A note on the ER-to-relational mapping: the case of one-to-one relationships

N/A
N/A
Protected

Condividi "A note on the ER-to-relational mapping: the case of one-to-one relationships"

Copied!
15
0
0

Testo completo

(1)

## the case of one-to-one relationships

Angelo Montanari

Dept. of Mathematics, Computer Science, and Physics University of Udine, Italy

Course on Data Management for Big Data

(2)

## The mapping of one-to-one relationships

We have to distinguish among 3 different cases:

I E1(PK1,A1) − (1,1) −R(A) − (0,1) −E2(PK2,A2) where PK 1 is the key of entity E1, A1 is an attribute of E1, A is an attribute of relationship R, PK 2 is the key of entity E2, and A2 is an attribute of E2

(the case

E1(PK1,A1) − (0,1) −R(A) − (1,1) −E2(PK2,A2) is completely symmetric, and thus ignored)

I E1(PK1,A1) − (0,1) −R(A) − (0,1) −E2(PK2,A2)

I E1(PK1,A1) − (1,1) −R(A) − (1,1) −E2(PK2,A2)

(3)

## The case of ( 1 , 1 ) − ( 0 , 1 ) relationships

How can we map the following ER schema into a corresponding relational one (introducing no redundancy, and preserving as much as possible information/constraints)?

E1(PK1,A1) − (1,1) −R(A) − (0,1) −E2(PK2,A2)

Relational schema:

E1(PK 1,A1,PK 2,A)and E2(PK 2,A2), where PK 2 is a foreign key of relation E1 that refers to the primary key PK 2 of relation E2.

I (1,_)on E1 side: PK 2 NOT NULL in E1

I (_,1)on E1 side: PK 1 is the primary key

I (0,_)on E2 side: PK 2 foreign key in E1 referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in E1

(4)

## The case of ( 1 , 1 ) − ( 0 , 1 ) relationships

How can we map the following ER schema into a corresponding relational one (introducing no redundancy, and preserving as much as possible information/constraints)?

E1(PK1,A1) − (1,1) −R(A) − (0,1) −E2(PK2,A2)

Relational schema:

E1(PK 1,A1,PK 2,A)and E2(PK 2,A2), where PK 2 is a foreign key of relation E1 that refers to the primary key PK 2 of relation E2.

I (1,_)on E1 side: PK 2 NOT NULL in E1

I (_,1)on E1 side: PK 1 is the primary key

I (0,_)on E2 side: PK 2 foreign key in E1 referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in E1

(5)

## The case of ( 1 , 1 ) − ( 0 , 1 ) relationships

How can we map the following ER schema into a corresponding relational one (introducing no redundancy, and preserving as much as possible information/constraints)?

E1(PK1,A1) − (1,1) −R(A) − (0,1) −E2(PK2,A2)

Relational schema:

E1(PK 1,A1,PK 2,A)and E2(PK 2,A2), where PK 2 is a foreign key of relation E1 that refers to the primary key PK 2 of relation E2.

I (1,_)on E1 side: PK 2 NOT NULL in E1

I (_,1)on E1 side: PK 1 is the primary key

I (0,_)on E2 side: PK 2 foreign key in E1 referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in E1

(6)

## The case of ( 0 , 1 ) − ( 0 , 1 ) relationships

E1(PK1,A1) − (0,1) −R(A) − (0,1) −E2(PK2,A2)

Relational schema:

E1(PK 1,A1,PK 2,A)and E2(PK 2,A2), where PK 2 is a foreign key of relation E1 that refers to the primary key PK 2 of relation E2.

I (0,_)on E1 side: PK 2 can be NULL in E1

I (_,1)on E1 side: PK 1 is the primary key

I (0,_)on E2 side: PK 2 foreign key in E1 referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in E1

E1(PK 1,A1)and E2(PK 2,A2,PK 1,A), where PK 1 is a foreign key of relation E2 that refers to the primary key PK 1 of relation E1, works as well.

(7)

## The case of ( 0 , 1 ) − ( 0 , 1 ) relationships

E1(PK1,A1) − (0,1) −R(A) − (0,1) −E2(PK2,A2)

Relational schema:

E1(PK 1,A1,PK 2,A)and E2(PK 2,A2), where PK 2 is a foreign key of relation E1 that refers to the primary key PK 2 of relation E2.

I (0,_)on E1 side: PK 2 can be NULL in E1

I (_,1)on E1 side: PK 1 is the primary key

I (0,_)on E2 side: PK 2 foreign key in E1 referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in E1

E1(PK 1,A1)and E2(PK 2,A2,PK 1,A), where PK 1 is a foreign key of relation E2 that refers to the primary key PK 1 of relation E1, works as well.

(8)

## The case of ( 0 , 1 ) − ( 0 , 1 ) relationships

E1(PK1,A1) − (0,1) −R(A) − (0,1) −E2(PK2,A2)

Relational schema:

E1(PK 1,A1,PK 2,A)and E2(PK 2,A2), where PK 2 is a foreign key of relation E1 that refers to the primary key PK 2 of relation E2.

I (0,_)on E1 side: PK 2 can be NULL in E1

I (_,1)on E1 side: PK 1 is the primary key

I (0,_)on E2 side: PK 2 foreign key in E1 referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in E1

E1(PK 1,A1)and E2(PK 2,A2,PK 1,A), where PK 1 is a foreign key of relation E2 that refers to the primary key PK 1 of relation E1, works as well.

(9)

## The case of ( 0 , 1 ) − ( 0 , 1 ) relationships (contn’d)

How do we choose between the two options? Participation of entities in the relationship can be a criterion.

In case the partecipation of both entities in the relationship is very low, a third option is possible

Relational schema:

E1(PK 1,A1), E2(PK 2,A2), and R(PK 1,PK 2,A)(or

R(PK 1,PK 2,A)), where PK 1 is a foreign key of relation R that refers to the primary key PK 1 of relation E1 and PK 2 is a foreign key of relation R that refers to the primary key PK 2 of relation E2.

I (0,_)on E1 side: PK 1 foreign key in R referring to the primary key of E1

I (_,1)on E1 side: PK 1 is the primary key of R

I (0,_)on E2 side: PK 2 foreign key in R referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in R

(10)

## The case of ( 0 , 1 ) − ( 0 , 1 ) relationships (contn’d)

How do we choose between the two options? Participation of entities in the relationship can be a criterion.

In case the partecipation of both entities in the relationship is very low, a third option is possible

Relational schema:

E1(PK 1,A1), E2(PK 2,A2), and R(PK 1,PK 2,A)(or

R(PK 1,PK 2,A)), where PK 1 is a foreign key of relation R that refers to the primary key PK 1 of relation E1 and PK 2 is a foreign key of relation R that refers to the primary key PK 2 of relation E2.

I (0,_)on E1 side: PK 1 foreign key in R referring to the primary key of E1

I (_,1)on E1 side: PK 1 is the primary key of R

I (0,_)on E2 side: PK 2 foreign key in R referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in R

(11)

## The case of ( 0 , 1 ) − ( 0 , 1 ) relationships (contn’d)

How do we choose between the two options? Participation of entities in the relationship can be a criterion.

In case the partecipation of both entities in the relationship is very low, a third option is possible

Relational schema:

E1(PK 1,A1), E2(PK 2,A2), and R(PK 1,PK 2,A)(or

R(PK 1,PK 2,A)), where PK 1 is a foreign key of relation R that refers to the primary key PK 1 of relation E1 and PK 2 is a foreign key of relation R that refers to the primary key PK 2 of relation E2.

I (0,_)on E1 side: PK 1 foreign key in R referring to the primary key of E1

I (_,1)on E1 side: PK 1 is the primary key of R

I (0,_)on E2 side: PK 2 foreign key in R referring to the primary key of E2

I (_,1)on E2 side: PK 2 UNIQUE in R

(12)

## The case of ( 1 , 1 ) − ( 1 , 1 ) relationships

No one of the previous solutions works with

E1(PK1,A1) − (1,1) −R(A) − (1,1) −E2(PK2,A2)

Relational schema:

R(PK 1,A1,PK 2,A2,A)(or R(PK 1,A1,PK 2,A2,A))

I (1,_)on E1 side: PK 2 NOT NULL in R

I (_,1)on E1 side: PK 1 is the primary key (UNIQUE)

I (1,_)on E2 side: PK 1 is the primary key (NOT NULL)

I (_,1)on E2 side: PK 2 UNIQUE in R

(13)

## The case of ( 1 , 1 ) − ( 1 , 1 ) relationships

No one of the previous solutions works with

E1(PK1,A1) − (1,1) −R(A) − (1,1) −E2(PK2,A2)

Relational schema:

R(PK 1,A1,PK 2,A2,A)(or R(PK 1,A1,PK 2,A2,A))

I (1,_)on E1 side: PK 2 NOT NULL in R

I (_,1)on E1 side: PK 1 is the primary key (UNIQUE)

I (1,_)on E2 side: PK 1 is the primary key (NOT NULL)

I (_,1)on E2 side: PK 2 UNIQUE in R

(14)

## The case of ( 1 , 1 ) − ( 1 , 1 ) relationships

No one of the previous solutions works with

E1(PK1,A1) − (1,1) −R(A) − (1,1) −E2(PK2,A2)

Relational schema:

R(PK 1,A1,PK 2,A2,A)(or R(PK 1,A1,PK 2,A2,A))

I (1,_)on E1 side: PK 2 NOT NULL in R

I (_,1)on E1 side: PK 1 is the primary key (UNIQUE)

I (1,_)on E2 side: PK 1 is the primary key (NOT NULL)

I (_,1)on E2 side: PK 2 UNIQUE in R

(15)

## The case of one-to-many and many-to-many relationships

A similar analysis can be done for the cases of both one-to-many and many-to-many relationships

Such an analysis allows one to determine

I which constraints can be directly encoded in the relational schema, and

I which ones need to be explicitly forced.

Riferimenti

Documenti correlati

IURP 8QLYHUVLW\ WR ODERXU PDUNHW LQ SDUWLFXODU E\ H[SORULQJ WKH WLHV EHWZHHQ GHJUHH DQG HFRQRPLF VHFWRU RI WKH ÀUVW SRVW GHJUHH MRE ÀUVW MRE DFWLYDWLRQ ÀUVW MRE SODFH ment).

We surveyed scientists working in traditional climate sciences, life- and geosciences as well as economists and social scientists because we assume that their diverse

An implication of this study is that some level of supply chain complexity is beneficial for having competitive performance and also for a better recovery of operational

1. Assuming that every individual does the same quantity of work, that everybody already possesses the quantity of means corresponding to the degree zero of happiness, and

The new Master of Science in Geosciences for Risk and Environment Management, activated in the academic year 2020-2021, represents the evolution of the previous course in

1) From the point of view of the history of science and mathematics, the late 30s and the 40s of the 17th century represent a particular period because the mathematical technique was

This imply to study diffraction using two, three slits and a diffracting grating, to analyze the role of the various parameters, as the number of slits or their separation in order