2 relational model

Information about 2 relational model

Published on October 30, 2007

Author: Talya

Source: authorstream.com

Content

The Relational Model Elisa Bertino CS Department and CERIAS Purdue University:  The Relational Model Elisa Bertino CS Department and CERIAS Purdue University The relational model :  The relational model It was developed by E. F. Codd in 1970 to enhance data independency and adopted as logical model in DBMS in 1980 It is based on the mathematical notion of relation; the model has thus a theoretical foundation that supports a formal definition of operations and properties of data The relations can be naturally represented by tables The relational model:  The relational model The relational model, even though it has not been the first data model, has become the most widely used and today is used in almost all commercial DBMS The reason of its popularity is that it provides languages that are simple and declarative, and powerful at the same time, to express operations for data access and manipulation Relation:  Relation Let D1, D2, …, Dn be n sets of values (not necessarily distinct) The Cartesian product D1×D2×…×Dn, is the set of all tuples (d1, d2, …, dn) such that d1D1, d2  D2, …, dn  Dn A relation on D1, D2, …, Dn is a (finite) subset of the Cartesian product D1×D2×…×Dn D1, D2, …, Dn are called domains of the relation A relation defined on n domain has degree n The number of tuples in the relation is the relation cardinality Domains:  Domains A domain is a set (possibly infinite) of values Examples: The set of the integer numbers The set of all character strings of length 20 The set {0,1} Example :  Example D1={a,b} D2={x,y,z} Cartesian Product D1 × D2 A relation r  D1 × D2 Relation – some properties:  Relation – some properties According to the definition, a relation is a set of tuples: (d1, d2,…, dn) such that d1D1, d2  D2,…, dn  Dn A relation is a set, thus:  no ordering is defined among the set of tuples  each tuple in a relation is distinct from all the other tuples in the relation A tuple is internally ordered: the i-th value of each tuple is a value from the i-th domain Notation:  Notation Let r a relation of degree k: let t be a tuple of r let i an integer in the set {1,...,k} t[i] denote the i-th component of t Example: let r={(0,a), (0,c),(1,b)} let t=(0,a) be a tuple of r t[2] = a t[1] = 0 Relational model:  Relational model We can see a relation as a table, in which each row is a tuple and each column corresponds to a component Each column has associated a name; such names are called attribute names The pair (attribute name, domain) is called attribute The set of attributes of a relation is the schema of the relation Relational model - Notation:  Relational model - Notation If a relation has name R and attributes with names A1, A2,...,Ak, the schema is often denoted by R(A1, A2,...,Ak) In addition UR = {A1, A2,...,Ak} denotes the set of all the attribute names of R   Example :  Example Info_Cities Schema: Info_Cities(City,Region,Population) Relational model:  Relational model In such definition of the relational model, the components of tuples are denoted by the names (notation based on names as opposed to the positional notation) Given a relation schema R(A1, A2,...,Ak), a tuple t on such schema can be represented as [A1:v1, A2:v2,...,Ak:vk] Where vi (i=1,..,k) is a value belonging to the set which is the domain of attribute Ai (denoted by dom(Ai)) t[Ai] denotes the value of attribute Ai for the tuple t Example:  Example t=[City: Rome, Region: Lazio, Population: 3000000] Is a tuple defined on the schema Info_City Positional notation t[1] = Rome t[City] = Rome The value of attribute City for the tuple t is Rome Null Values:  Null Values In some cases, information certain entities of the domain of interest may be missing: some tuples may not have a value for one or more attributes The lack of a value is handled through the use of a special value (null value) denoting the absence of a value (often denoted by “?”) The presence of null value requires using a 3-value Boolean logic for predicate evaluation The notion of key:  The notion of key Given a relation, a key of the relation is a set of attributes the values of which are unique for each tuple in the relation More precisely, a set X of attributes of a relation R, is a key of R if verifies both the following properties: For each state of R, there are no two distinct tuples in R having the same value for all attributes in X No proper subset of X verifies property (1)  Example:  Example In the previous example on cities: key(Info_Cities) = {City} If no two cities exist with the same name in different regions key(Info_Cities) = {City,Region) If different cities may exist having the same name in different regions


The notion of key: 

The notion of key A key cannot have null values A relation can have more than one attribute set verifying the properties of keys In some cases, one has to choose a key if the used system does not support multiple keys. In such case, the term candidate keys is used to refer to all possible keys The term primary key is used to refer to the selected key

The notion of foreign key: 

The notion of foreign key Let R and R’ be two relations such that: R has a set X of attributes R' has a set Y of attributes as key Y is an external key of R on R’ if Y is a subset of X In other words, if a relation R includes, among its attributes, a set of attributes which is the key of a relation R’, then such attribute set is an external key of R on R’ R’ is called referenced relation

The notion of foreign key: 

The notion of foreign key The foreign keys allow one to connect tuples of different relations; they represent a value-based mechanism to represent relationships among entities A tuple that must reference another tuple t needs thus to include among its attributes the values of the key of t

Example: 

Example Let define two relations representing information concerning the employees and departments of a company: Employees(Emp#,Name,Job,HiringD,Salary,Bonus,Dept#) key(Employees) = {Emp#} Foreign_key(Employees) = {Dept#} (referenced relation: Departments) Departments(Dept#,DeptName,Office#,Division,Manager) key(Departments) = {Dept#} Employees:  Employees Departments:  Departments Referential integrity:  Referential integrity Referential integrity represents an important semantic integrity constraint If a tuple t contains a foreign key with values v1,…,vn then the referenced relation must contain a tuple t’ that has as values of its key v1,…,vn The relations Employees and Departments verify the referential integrity Referential integrity:  Referential integrity Suppose that the following tuple be inserted in the Employee relation: [Emp#: 7899, Name: Smith, Job: technician, HiringD:03-Dic-81, Salary:2000, Bonus: 100, Dept#: 50] Such tuple violates the referential integrity in that no department exists (in the Departments relation) that has as value for the key the value 50. Referential integrity:  Referential integrity The database languages (SQL) allows the database designer to specify for which relations and for which attributes the referential integrity has to be maintained (and the actions to be executed in case of violations)

Related presentations


Other presentations created by Talya

Chapter 19
06. 11. 2007
0 views

Chapter 19

MDR TB Aaron
07. 01. 2008
0 views

MDR TB Aaron

Ourplanetearth
03. 10. 2007
0 views

Ourplanetearth

Serway CP poll ch05
09. 10. 2007
0 views

Serway CP poll ch05

ELISA powerpoint Agrow knowledge
24. 10. 2007
0 views

ELISA powerpoint Agrow knowledge

Presentation Jean Pierre Allain
24. 10. 2007
0 views

Presentation Jean Pierre Allain

H114m
26. 11. 2007
0 views

H114m

svd
03. 12. 2007
0 views

svd

Invasive Species ID Powerpoint
11. 12. 2007
0 views

Invasive Species ID Powerpoint

Fallacies vs Facts
12. 12. 2007
0 views

Fallacies vs Facts

upload c beatles64 4n27
29. 10. 2007
0 views

upload c beatles64 4n27

POSTMODERN MANAGEMENT
30. 10. 2007
0 views

POSTMODERN MANAGEMENT

Introduction Overview
02. 11. 2007
0 views

Introduction Overview

Ocean platforms prince
05. 11. 2007
0 views

Ocean platforms prince

AST PRESENTATION TO PCA
06. 11. 2007
0 views

AST PRESENTATION TO PCA

ANI
06. 11. 2007
0 views

ANI

rebecca 3rd
06. 11. 2007
0 views

rebecca 3rd

The Slave Diary Assessment
07. 11. 2007
0 views

The Slave Diary Assessment

Sugarcane presentation
23. 11. 2007
0 views

Sugarcane presentation

l 3
26. 11. 2007
0 views

l 3

Perth
04. 01. 2008
0 views

Perth

birds total
22. 11. 2007
0 views

birds total

TECNICASDE ESTUDIOA DISTANCIA
05. 01. 2008
0 views

TECNICASDE ESTUDIOA DISTANCIA

Sue Gries LC
07. 01. 2008
0 views

Sue Gries LC

morrill
03. 10. 2007
0 views

morrill

Naveen Kumar October 24 2006
12. 11. 2007
0 views

Naveen Kumar October 24 2006

nhs fellowship
25. 10. 2007
0 views

nhs fellowship

icml kdd2003
27. 09. 2007
0 views

icml kdd2003

FOSOntoWeb
30. 10. 2007
0 views

FOSOntoWeb

f ad 06112006
26. 10. 2007
0 views

f ad 06112006

20040112 SPACE04
03. 01. 2008
0 views

20040112 SPACE04

bobkov
03. 01. 2008
0 views

bobkov

AGM 2005
20. 02. 2008
0 views

AGM 2005

FL Unit 1 pgs 16 18
24. 02. 2008
0 views

FL Unit 1 pgs 16 18

Lecture15 2003 10 14 FINAL
27. 02. 2008
0 views

Lecture15 2003 10 14 FINAL

art1945 60
19. 12. 2007
0 views

art1945 60

lail
05. 03. 2008
0 views

lail

ISAE 7giu05def
31. 10. 2007
0 views

ISAE 7giu05def

acg
14. 03. 2008
0 views

acg

CentralDogma
27. 03. 2008
0 views

CentralDogma

Gorini Sidlaw Mon
30. 10. 2007
0 views

Gorini Sidlaw Mon

X Internet Q2 2002
13. 04. 2008
0 views

X Internet Q2 2002

AGM Mar 06 Final frm Kim e
07. 12. 2007
0 views

AGM Mar 06 Final frm Kim e

Ballroom Dance Lessons
23. 11. 2007
0 views

Ballroom Dance Lessons

Hosea HHFW 6 28 07
21. 11. 2007
0 views

Hosea HHFW 6 28 07

radiacevesmir
15. 11. 2007
0 views

radiacevesmir

Iyer
16. 11. 2007
0 views

Iyer

proebsting
05. 11. 2007
0 views

proebsting

200753051013737
28. 12. 2007
0 views

200753051013737

Schatzmann Nett
25. 10. 2007
0 views

Schatzmann Nett

ukio bankas
14. 11. 2007
0 views

ukio bankas

E145 WorkshopB Mktg
16. 11. 2007
0 views

E145 WorkshopB Mktg

StarMotion
13. 11. 2007
0 views

StarMotion

BEL Valves
20. 11. 2007
0 views

BEL Valves

herrera Parallel 4 1
28. 11. 2007
0 views

herrera Parallel 4 1

job search bootcamp
17. 12. 2007
0 views

job search bootcamp