Information about 2 relational model

The Relational Model Elisa BertinoCS Department and CERIASPurdue 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 d1D1, 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 d1D1, 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