# 2 relational model

Information about 2 relational model

Published on October 30, 2007

Author: Talya

Source: authorstream.com

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)

06. 11. 2007
0 views

17. 12. 2007
0 views

07. 01. 2008
0 views

03. 10. 2007
0 views

09. 10. 2007
0 views

24. 10. 2007
0 views

24. 10. 2007
0 views

26. 11. 2007
0 views

26. 11. 2007
0 views

03. 12. 2007
0 views

11. 12. 2007
0 views

12. 12. 2007
0 views

26. 10. 2007
0 views

29. 10. 2007
0 views

30. 10. 2007
0 views

02. 11. 2007
0 views

05. 11. 2007
0 views

06. 11. 2007
0 views

06. 11. 2007
0 views

06. 11. 2007
0 views

07. 11. 2007
0 views

19. 11. 2007
0 views

23. 11. 2007
0 views

26. 11. 2007
0 views

28. 12. 2007
0 views

04. 01. 2008
0 views

22. 11. 2007
0 views

05. 01. 2008
0 views

07. 01. 2008
0 views

03. 10. 2007
0 views

12. 11. 2007
0 views

25. 10. 2007
0 views

27. 09. 2007
0 views

30. 10. 2007
0 views

26. 10. 2007
0 views

03. 01. 2008
0 views

03. 01. 2008
0 views

31. 10. 2007
0 views

20. 02. 2008
0 views

24. 02. 2008
0 views

27. 02. 2008
0 views

19. 12. 2007
0 views

05. 03. 2008
0 views

31. 10. 2007
0 views

14. 03. 2008
0 views

27. 03. 2008
0 views

30. 10. 2007
0 views

13. 04. 2008
0 views

07. 12. 2007
0 views

23. 11. 2007
0 views

21. 11. 2007
0 views

15. 11. 2007
0 views

16. 11. 2007
0 views

05. 11. 2007
0 views

28. 12. 2007
0 views

25. 10. 2007
0 views

14. 11. 2007
0 views

16. 11. 2007
0 views

28. 12. 2007
0 views

13. 11. 2007
0 views

20. 11. 2007
0 views

28. 11. 2007
0 views

17. 12. 2007
0 views