The Relational Data Model 1.Relational Model Concepts 2.Characteristics of Relations 3.Relational...

Click here to load reader

  • date post

  • Category


  • view

  • download


Embed Size (px)

Transcript of The Relational Data Model 1.Relational Model Concepts 2.Characteristics of Relations 3.Relational...

  • Slide 1
  • The Relational Data Model 1.Relational Model Concepts 2.Characteristics of Relations 3.Relational Integrity Constraints 3.1Key Constraints 3.2Entity Integrity Constraints 3.3Referential Integrity Constraints 4.Update Operations on Relations 5.Relational Algebra Operations 5.1SELECT and PROJECT 5.2Set Operations 5.3JOIN Operations 5.4Additional Relational Operations
  • Slide 2
  • 1. Relational Model Concepts Basis Of The Model The relational Model of Data is based on the concept of a Relation. A relation is a mathematical concept based on the ideas of sets. The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations. We review the essentials of the relational approach in this chapter
  • Slide 3
  • Informal Definitions RELATION: A table of values A relation may be thought of as a set of rows. A relation may alternately be thought of as a set of columns. Each row of the relation may be given an identifier. Each column typically is called by its column name or column header or attribute name.
  • Slide 4
  • Formal Definitions A Relation may be defined in multiple ways. The Schema of a Relation: R (A 1, A 2,.....A n ) Relation R is defined over attributes A 1, A 2,.....A n For Example - CUSTOMER (Cust-id, Cust-name, Address, Phone#) Here, CUSTOMER is a relation defined over the four attributes Cust-id, Cust-name, Address, Phone#, each of which has a domain or a set of valid values. For example, the domain of Cust-id is 6 digit numbers. A tuple is an ordered set of values Each value is derived from an appropriate domain. Each row in the CUSTOMER table may be called as a tuple in the table and would consist of four values. is a tuple belonging to the CUSTOMER relation. A relation may be regarded as a set of tuples (rows). Columns in a table are also called as attributes of the relation.
  • Slide 5
  • Formal Definitions (contd.) The relation is formed over the cartesian product of the sets; each set has values from a domain; that domain is used in a specific role which is conveyed by the attribute name. For example, attribute Cust-name is defined over the domain of strings of 25 characters. The role these strings play in the CUSTOMER relation is that of the name of customers.
  • Slide 6
  • Formally, Given R(A 1, A 2,..., A n ) r(R) subset-of dom (A 1 ) X dom (A 2 ) X... X dom(A n ) r of R: a specific "value" or population of R. R: schema of the relation R is also called the intension of a relation r is also called the extension of a relation Example: Let have R(A,B) and domain A be S 1 and domain B be S 2. Let S 1 = {0,1}and Let S 2 = {a,b,c} r(R) is a subset-of S 1 X S 2 for example: r(R) = {,, } is a relation defined over R.
  • Slide 7
  • Definition Summary Informal TermsFormal Terms TableRelation ColumnAttribute/Domain RowTuple Values in a columnDomain Table DefinitionSchema of Relation Populated TableExtension Notes: Whereas languages like SQL use the informal terms of TABLE (e.g. CREATE TABLE), the relational database textbooks present the model and operations on it using the formal terms.
  • Slide 8
  • Slide 9
  • 2. Characteristics of Relations Ordering of tuples in a relation r(R): The tuples are not considered to be ordered, even though they appear to be in the tabular form. Ordering of attributes in a relation schema R (and of values within each tuple): We will consider the attributes in R(A 1, A 2,..., A n ) and the values in t= to be ordered. (However, a more general alternative definition of relation does not require this ordering).
  • Slide 10
  • Characteristics of Relations (Continued) Values in a tuple: All values are considered atomic (indivisible). A special null value is used to represent values that are unknown or inapplicable to certain tuples. Notation: We refer to component values of a tuple t by t[A i ] = v i (the value of attribute A i for tuple t). Similarly, t[A u, A v,..., A z ] refers to the subtuple of values from t corresponding to attributes specified in R. A u, A v,..., A z, is a list of attributes of R.
  • Slide 11
  • Slide 12
  • 3. Constraints Constraints are: conditions that must hold on all valid relation instances. There are three main types of constraints: Key constraints Entity integrity constraints Rreferential integrity constraints
  • Slide 13
  • 3.1 Key Constraints Superkey of R: A set of attributes SK of R such that no two tuples in any valid relation instance r(R) will have the same value for SK. That is, for any distinct tuples t 1 and t 2 in r(R), t 1 [SK] t 2 [SK]. Key of R: A "minimal" superkey; that is, a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey. Example: The CAR relation schema: CAR(State, Reg#, SerialNo, Make, Model, Year) has two keys Key 1 = {State, Reg#}, Key 2 = {SerialNo} which are also superkeys. {SerialNo, Make} is a superkey but not a key. primary key of R: If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. The primary key attributes are underlined.
  • Slide 14
  • Slide 15
  • 3.2 Entity Integrity Relational Database Schema: A set S of relation schemas that belong to the same database. S is the name of the database. S = {R 1, R 2,..., R n } Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R). This is because primary key values are used to identify the individual tuples. t[PK] null for any tuple t in r(R) Note: Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key. 30000 (EMPLOYEE) (DNO=4 AND SALARY>25000) OR DNO=5 (EMPLOYEE)">
  • 5.1 SELECT and PROJECT SELECT operation (denoted by ): -Selects the tuples (rows) from a relation R that satisfy a certain selection condition c. -Form of the operation: c (R) -The condition c is an arbitrary Boolean expression on the attributes of R. -Resulting relation has the same attributes as R. -Resulting relation includes each tuple in r(R) whose attribute values satisfy the condition c. Examples: DNO=4 (EMPLOYEE) SALARY>30000 (EMPLOYEE) (DNO=4 AND SALARY>25000) OR DNO=5 (EMPLOYEE)
  • Slide 23
  • PROJECT operation (denoted by ): -Keeps only certain attributes (columns) from a relation R specified in an attribute list. -Form of operation: (R) -Resulting relation has only those attributes of R specified in L from every tuple of R.
  • Slide 24
  • Projection (continued) Example: (EMPLOYEE) -The PROJECT operation eliminates duplicate tuples in the resulting relation so that it remains a mathematical set (no duplicate elements) Example: (EMPLOYEE) If several male employees have salary 30000, only a single tuple is kept in the resulting relation. Duplicate tuples are eliminated by the operation.
  • Slide 25
  • Slide 26
  • Sequence of operations -Several operations can be combined to form a relational algebra expression (query) -Example: Retrieve the names and salaries of employees who work in department 4: ( DNO=4 (EMPLOYEE) ) -Alternatively, we specify explicit intermediate relations for each step: DEPT4_EMPS DNO=4 (EMPLOYEE) R (DEPT4_EMPS)
  • Slide 27
  • Renaming attributes -Attributes can optionally be renamed in the resulting left-hand-side relation (this may be required for some operations that will be presented later): DEPT4_EMPS DNO=4 (EMPLOYEE) R(FIRSTNAME,LASTNAME,SALARY) (DEPT4_EMPS)
  • Slide 28
  • R
  • Slide 29
  • 5.2 Set Operations -Binary operations from mathematical set theory: UNION: R 1 U R 2, INTERSECTION: R 1 R 2, SET DIFFERENCE: R 1 - R 2, CARTESIAN PRODUCT: R 1 X R 2. -For U, , -, the operand relations R 1 (A 1, A 2,..., A n ) and R 2 (B 1, B 2,..., B n ) must have the same number of attributes, and the domains of corresponding attributes must be compatible; that is, dom(A i )=dom(B i ) for i=1, 2,..., n. This condition is called union compatibility. -The resulting relation for U, , or - has the same attribute names as the first operand relation R 1 (by convention).
  • Slide 30
  • Slide 31
  • Slide 32
  • Cartesian Product For the relations: R 1 (A 1, A 2,..., A m ) and R 2 (B 1, B 2,..., B n ) the Cartesian Product of R 1 and R 2 is: R(A 1, A 2,..., A m, B 1, B 2,..., B n ) R 1 (A 1, A 2,..., A m ) X R 2 (B 1, B 2,..., B n ) -A tuple t exists in R for each combination of tuples t 1 from R 1 and t 2 from R 2 such that: t[A 1, A 2,..., A m ]=t 1 and t[B 1, B 2,..., B n ]=t 2 -If R 1 has n 1 tuples and R 2 has n 2 tuples, then R will have n 1 *n 2 tuples.
  • Slide 33
  • Cartesian Product (Continued) -CARTESIAN PRODUCT is a meaningless operation on its own but, It can combine related tuples from two relations if followed by the appropriate SELECT operation. Example: We can combine each DEPARTMENT tuple with the EMPLOYEE tuple of the manager buy the following two operations: DEP_EMP DEPARTMENT X EMPLOYEE DEPT_MANAGER MGRSSN=SSN (DEP_EMP)
  • Slide 34
  • Slide 35
  • 5.3 JOIN Operations THETA JOIN: Similar to a CARTESIAN PRODUCT followed by a SELECT. The condition c is called a join condition. R(A 1, A 2,..., A m, B 1, B 2,..., B n ) R 1 (A 1, A 2,..., A m ) X c R 2 (B 1, B 2,..., B n )
  • Sli