Main concepts of relational model  The relational model was proposed in 1970 by Edgar Codd  The relational model assumes that data is stored in two-dimensional.

Similar presentations

Presentation on theme: "Main concepts of relational model  The relational model was proposed in 1970 by Edgar Codd  The relational model assumes that data is stored in two-dimensional."— Presentation transcript:

1

2
Main concepts of relational model

3
 The relational model was proposed in 1970 by Edgar Codd  The relational model assumes that data is stored in two-dimensional tables called relations As an example, information about company cars (vehicle identification number, number plate, mark and year) can be stored in the following relation: VINNUMBER_PLATEMARKYEAR GTRE545WRTH256452EPA60PLFord Fusion I2003 THER186ACVG636853EL432PLOpel Corsa II2005 WDH144TETU063632EZG42PLCitroen C3 III2011

6
Definition (Relation): Let T(R) denotes the set of all tuples defined on the set R. A finite subset of the set T(R) is a called a relation with the schema R. This subset is also called an instance of the schema R. Relations with the schema R (instances of R) will be denoted by I, J, K,.... Table representation of relations Let I be a relation with the schema R={A 1,A 2,...,A n }. According to the definition I:={r 1, r 2,...,r m }, where r i : R  D(R), r i (A j )  D(A j ), i=1,...,m, j=1,...,n. Since each r i function has the same set of arguments, therefore relation I can be described by the following table: A1A1 A2A2... AnAn r 1 (A 1 )r 1 (A 2 )... r 1 (A n ) r 2 (A 1 )r 2 (A 2 )... r 2 (A n )... r m (A 1 )r m (A 2 )... r m (A n )

10
 In the relational model the best known formal query languages are relational algebra and relational calculus.  The relational algebra is based on a set of operators (e.g. selection, projection, join, union, intersect, etc.) which are applied to relation instances. Queries in relational algebra are expressed in the procedural manner: each query specifies a sequence of operations needed to compute the desired answer.  The relational calculus provides a declarative manner of formulating queries. It assumes that a query specifies only the desired answer without describing a precise sequence of required operations.

16
The projection operator  chooses a set of specified attributes from a given relation. Let R={A 1,A 2,...,A n } be relation schema and let P  {A 1,A 2,...,A n }. Definition 2.4 (Tuple restriction) : A tuple s  T(P) is called a restriction of r  T(R) to P if and only if s(A i )=r(A i ) for each A i  P. The tuple restriction is denoted by r[P].

20
The selection operator  is used to produce a horizontal subset of a given relation by selecting only the tuples which meet the specified selection condition. Definition (Selection): Let I(R) be an instance of relation schema R={A 1,A 2,...,A n }, SC a selection condition (a boolean expression involving terms connected by logical connectives). The set  SC (I(R)) := {r  I(R); SC(r)=true} is called selection of I(R) on the basis of the SC condition.

25
Example Consider a relation schema COMPANY_CAR={VIN, NUMBER_PLATE, MARK, YEAR}. In COMPANY_CAR, for instance, attributes NUMBER_PLATE, MARK, and YEAR are functionally dependent on attribute VIN. These dependencies are represented as follows (in short VINNUMBER_PLATE MARK YEAR ): VINNUMBER_PLATE VINMARK VINYEAR The above dependencies hold for any instance of the COMPANY_CA R schema, in particular for the following instance called I(COMPANY_CAR) : VINNUMBER_PLATEMARKYEAR GTRE545WRTH256452EPA60PLFord Fusion I2003 THER186ACVG636853EL432PLOpel Corsa II2005 WDH144TETU063632EZG42PLCitroen C3 III2011

26
Note that the functional dependency MARKYEAR is also satisfied in I(COMPANY_CAR). However it does not hold for any instance of COMPANY_CAR. It is enough to consider the instance below to show that the condition occurring in the Definition does not hold: In other words, the sentence “cars having the same mark have the same production year” is not true in general case. VINnumber_platemarkyear GTRE545WRTH256452EPA60PLFord Fusion I2003 THER186ACVG636853EL432PLOpel Corsa II2005 WDH144TETU063632EZG42PLCitroen C3 III2011 YDAT EL476PLCitroen C3 III2005

27
Let F be a subset of the set of all functional dependencies over a relation schema R, F:={X  Y, X,Y  R}. The set of all functional dependencies which can be derived from a given set F is called the closure of F, denoted as F +. The following rules, called Armstrong's axioms allow to construct the set F + (X,Y,Z  R): 1. Y  X  X  Y (reflexivity) 2. X  Y  X  Z  Y  Z (augmentation) 3. X  Y  Y  Z  X  Z (transitivity)

28
Introducing functional dependencies allows to extend the notion of a relation schema. Let A denote the previously defined relation schema R, A={A 1,A 2,...,A n } (each A i is assigned to the set of values D(A i )). Let F be a set of all functional dependencies defined on A, F={X  Y, X,Y  A}. The ordered pair R=(A,F) is called a relation schema.

29
Each relation schema R=(A,F) possesses a certain minimal subset of attributes whose values uniquely identify tuples of any instance of R. Such a subset of attributes is called a key (candidate key) for the schema. In other words, a subset K  A is a key if and only if the following conditions hold  There are no tuples of any instance of R which have the same values in K.  No proper subset of K have unique identification property (key must be minimal). In the words case, a key can contain all the attributes of the given relation schema. Note that a schema can possess several keys. A primary key is one of all the keys selected by a database designer.

30
Example 2 Consider the relation schema COMPANY_CAR=(A,F) in which A={VIN, NUMBER_PLATE, MARK, YEAR}. To determine keys for COMPANY_CAR all its instances must be taken into account. The attributes VIN and NUMBER_PLATE always uniquely identify tuples occurring in any instance of the COMPANY_CAR schema. Besides there is no proper subset of these attributes which has unique identification property. The primary key for COMPANY_CAR is one of its keys.

31
A set of attributes of a given relation can points to a primary key in another relation. This set is called foreign key. The values inserted to a foreign key column must match the values stored in the primary key column of the reference relation. Example Relations and their keys (person_id is the primary key of Person, phone_id is the primary key of Phone, p_id is the foreign key of Phone which points to person_id.

32
Example Relations and their keys ( person_id is the primary key of PERSON, phone_id is the primary key of PHONE, p_id is the foreign key of PHONE which points to person_id. PERSON PHONE The p_id column can store only the values occurring in the column person_id of PERSON. person_ifirst_namelast_name 1EmilyOrman 2MartinLindsey 3AnthonyCain phone_idnumbertypep_id M M S1