2 relational model

60 %
40 %
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 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)

Add a comment

Related presentations

Related pages

Relationale Datenbank – Wikipedia

Eine relationale Datenbank dient zur elektronischen Datenverwaltung in Computersystemen und beruht auf einem tabellenbasierten relationalen Datenbankmodell.
Read more

Relational model - Wikipedia, the free encyclopedia

The purpose of the relational model is to provide a declarative method for specifying data and queries: users directly state what information the database ...
Read more

Database Lesson #2 of 8 - The Relational Model - YouTube

Dr. Soper gives a lecture on the relational model and its role in the database world. Topics include relations, primary keys, composite keys ...
Read more

The Relational Model - UW Computer Sciences User Pages

Database Management Systems, R. Ramakrishnan 2 Why Study the Relational Model? Most widely used model. – Vendors: IBM, Informix, Microsoft, Oracle,
Read more

Entity-Relationship-Modell – Wikipedia

Das Entity-Relationship-Modell ... Überführung in ein relationales Modell ... ACM Transactions on Database Systems. 2, Nr. 2, 1977, ...
Read more

Chapter 2: Relational Model

Database System Concepts ­ 5th Edition, June 15, 2005 2. ©Silberschatz, Korth and Sudarshan Chapter 2: Relational Model
Read more

Chapter 2: Relational Model - Avi Silberschatz's Home Page

Database System Concepts - 5th Edition, Oct 5, 2006 2.2 ©Silberschatz, Korth and Sudarshan Chapter 2: Relational Model Structure of Relational Databases
Read more

Relational Model 2: Relational Algebra - Department of ...

Relational Model 2: Relational Algebra Yufei Tao Department of Computer Science and Engineering Chinese University of Hong Kong Relational Model 2 ...
Read more

2 - The Relational Model

1 CSC9Q5 / ITNP33 Database Principles and Applications The Relational Model 2 The Relational Data Model • Ritchie: Chapter 2, Connolly & Begg: Chapter 3
Read more

Vom ERM zum relationalen Modell - Homepage von Tino Hempel

Sonderfälle. is-a-Beziehungen Is-a-Beziehungen drücken Spezialisierungen bzw. Generalisierungen aus. Dabei gilt, der Spezialentitätstyp erbt alle ...
Read more