1 / 52

Relational Model

Relational Model. Outline. Relational Model Concepts Relational Model Constraints & Relational Database Schemas Update Operation & Dealing with Constraint Violations. Relational Model Concept.

briar
Download Presentation

Relational Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Model dww-database system

  2. Outline • Relational Model Concepts • Relational Model Constraints & Relational Database Schemas • Update Operation & Dealing with Constraint Violations dww-database system

  3. Relational Model Concept • First introduced by Dr. E.F. Codd of IBM Research in 1970 in paper titled “Relational Model of Data for Large Shared Data Banks”  one of great papers in computer science • The Relational Model of Data is Based on the Concept of Relations • A Relation is a Mathematical Concept Based on the Concept of Sets • The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations. dww-database system

  4. Relations • Relational DBMS products store data in the form of relations, a special type of table • A relation is a two-dimensional table that has the following characteristics • Rows contain data about an entity • Columns contain data about attributes of the entity • Cells of the table hold a single value • All entries in a column are of the same kind • Each column has a unique name • The order of the columns is unimportant – concept of set • The order of the rows is unimportant • No two rows may be identical dww-database system

  5. relation name t1[An] A1 A2 An R ...... v11 v12 v1n t1 Tuples v21 v22 v2n t2 . . . vm1 vm2 vmn tm Relations (cont) • Set of Tuples and Typically Shown as a Table With Columns and Rows. • Column (Field) Represents an Attribute • Row (Tuple) Represents an Entity Instance Attributes dww-database system

  6. Equivalent Relational Terminology • Although not all tables are relations, the terms table and relation are normally used interchangeably dww-database system

  7. Example: Relation dww-database system

  8. Example: Tables Not Relations dww-database system

  9. Examples • Are the Following Relations in a Relational Model? • Why or Why Not? A B C D A B C D R1 R2 a2 {b1, b2} c1 d5 a2 b2 c6 d1 a2 b7 c9 d5 a2 b7 c9 d5 a2 b23 c22 d1 a2 b7 c9 d5 …... …... Employee E# Ename AGE ADDRESS E2 Diamond 45 1888 Buford Hyw. E1 Smith 30 3302 Peachtree Rd., Atlanta, GA E3 Evan Baker Ct. Atlanta dww-database system

  10. Formal Definition • A Relation may be defined in multiple ways. • The Schema of a Relation: R (A1, A2, .....An) Relation schema R is defined over attributes A1, A2, .....An. 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. dww-database system

  11. Formal Definition • A tuple is an ordered set of values • Each value is derived from an appropriate domain. • Each row in the CUSTOMER table may be referred to as a tuple in the table and would consist of four values. • <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">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 attributes of the relation. dww-database system

  12. Formal Definition • A domain has a logical definition: e.g.,“USA_phone_numbers” are the set of 10 digit phone numbers valid in the U.S. • A domain may have a data-type or a format defined for it. The USA_phone_numbers may have a format: (ddd)-ddd-dddd where each d is a decimal digit. E.g., Dates have various formats such as monthname, date, year or yyyy-mm-dd, or dd mm,yyyy etc. • An attribute designates the role played by the domain. E.g., the domain Date may be used to define attributes “Invoice-date” and “Payment-date”. dww-database system

  13. Formal Definition • The relation is formed over the Cartesian Pproduct 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. • Formally, Given R(A1, A2, .........., An) r(R)  dom (A1) X dom (A2) X ....X dom(An) • R: schema of the relation • r of R: a specific "value" or population of R. • R is also called the intension of a relation • r is also called the extension of a relation dww-database system

  14. Formal Definition • Let S1 = {0,1} • Let S2 = {a,b,c} • Let R  S1 X S2 • Then for example: r(R) = {<0,a> , <0,b> , <1,c> } is one possible “state” or “population” or “extension” r of the relation R, defined over domains S1 and S2. It has three tuples. dww-database system

  15. Two Versions of a Student Relation dww-database system

  16. PNO PNAME BUDGET ENO PNO RESP DUR Relation Schemes • Example • EMP(ENO, ENAME, TITLE, SAL) • PROJ (PNO, PNAME, BUDGET) • WORKS(ENO, PNO, RESP, DUR) • Underlined Attributes are Relation Keys which Uniquely Distinguish Among Tuples (Rows) • Tabular Form ENO ENAME TITLE SAL EMP PROJ WORKS dww-database system

  17. EMP WORKS ENO PNO RESP ENO ENAME TITLE DUR E1 J. Doe Elect. Eng. E1 P1 Manager 12 E2 M. Smith Syst. Anal. E2 P1 Analyst 24 E3 A. Lee Mech. Eng. E2 P2 Analyst 6 E4 J. Miller Programmer E3 P3 Consultant 10 E5 B. Casey Syst. Anal. E3 P4 Engineer 48 E6 L. Chu Elect. Eng. E4 P2 Programmer 18 E5 P2 Manager 24 E7 R. Davis Mech. Eng. E6 P4 Manager 48 E8 J. Jones Syst. Anal. PROJ[PNO] P1 P2 P3 P4 P5 EMP[TITLE] Elect.Eng Syst. Anal Mech. Eng Programmer E7 P3 Engineer 36 E7 P5 Engineer 23 E8 P3 Manager 40 PROJ PNO PNAME BUDGET P1 Instrumentation 150000 P2 Database Develop. 135000 P3 CAD/CAM 250000 P4 Maintenance 310000 P5 CAD/CAM 500000 Relation Instances dww-database system

  18. Examples • Exercise: • R(A, B) is a Relation Schema Defined over A and B • Let domain(A) = {a1, a2} and domain(B) = {0, 1, 2} • Which of the Following are Relations of R? • {(a1, 1), (a1, 2), (a2, 0)} • {(a1, 0), (a1, 1), (a1, 2)} • {(a1, 1), (a2, 2}, (a0, 0)} • {(a1, 1), (a2, a2}, (a0, a0)} • {(a1, 1, c1), (a2, 2)} • What if Attribute A is a Key? dww-database system

  19. Characteristics of Attributes • Attribute Name • An Attribute Name Refers to a Position in a Tuple by Name Rather than Position • An Attribute Name Indicates the Role of a Domain in a Relation • Attribute Names must be Unique Within Relations • By Using Attribute Names we can Disregard the Ordering of Field Values in Tuples • Attribute Value - Must have a Value • Must Be an Atomic Value • Can Be a Null Value Meaning “Not Known”, “Not Applicable” ... dww-database system

  20. Constraint in Database • Inherent Constraint • Constraint that are inherent in the data model • Characteristics relation • Schema-based constraint • Constraint that can be directly expressed in the schemas of the data model, typically specifying in DDL • Domain constraint, key constraint, etc • Application-based constraint • Constraint that can not be directly expressed in the data model and must be expressed and enforced by the application program • Trigger, assertion, etc dww-database system

  21. Relational Integrity Constraints • IC: Conditions that Must Hold on All Valid Relation Instances at Any Given Database State • Why are Integrity Constraints Needed? What Happens when we try to Delete a Flight? FLT-SCHEDULE CUSTOMER FLT# CUST-NAME CUST# DepT Dest ArrT RESERVATION FLT# DATE CUST# dww-database system

  22. Relational Integrity Constraints Classification • There are Three Main Types of Constraints: • Key Constraints • Entity Integrity Constraints • Referential Integrity Constraints • Other Types of Semantic Constraints: • Domain Constraints • Transition Constraints • Set Constraints • DBMSs Handle Some But Not All Constraints dww-database system

  23. Types of Keys • A key is one or more columns of a relation that identifies a row • Composite key is a key that contains two or more attributes • A relation has one unique primary key and may also have additional unique keys called candidate keys • Primary key is used to • Represent the table in relationships • Organize table storage • Generate indexes dww-database system

  24. Key Constraints • Superkey (SK): • Any Subset of Attributes Whose Values are Guaranteed to Distinguish Among Tuples • Candidate Key (CK): • A Superkey with a Minimal Set of Attributes (No Attribute Can Be Removed Without Destroying the Uniqueness -- Minimal Identity) • A Value of an Attribute or a Set of Attributes in a Relation That Uniquely Identifies a Tuple • There may be Multiple Candidate Keys dww-database system

  25. Key Constraints • Primary Key (PK): • Choose One From Candidate Keys • The Primary Key Attributed are Underlined • Foreign Key (FK): • An Attribute or a Combination of Attributes (Say A) of Relation R1 Which Occurs as the Primary Key of another Relation R2 (Defined on the Same Domain) • Allows Linkages Between Relations that are Tracked and Establish Dependencies • Useful to Capture ER Relationships dww-database system

  26. Superkeys and Candidate Keys: Examples • Example: • The CAR relation schema: CAR(State, Reg#, SerialNo, Make, Model, Year) • Its primary key is {State, Reg#} • It has two candidate keys • Key1 = {State, Reg#} • Key2 = {SerialNo} • {SerialNo, Make} is a Superkey but not a Candidate KeyWhy?If Remove SerialNo, Make is not a Primary Key dww-database system

  27. Another Schema with Key What are Typically Used as Keys for Cars? dww-database system

  28. A Complete Schema with Keys ... Keys Allow us to Establish Links Between Relations What is This Similar to in ER? dww-database system

  29. …and Corresponding DB Tables Which Represent Tuples/Instances of Each Relation A S C null W B null null 1 4 5 5 dww-database system

  30. …with Remaining DB Tables dww-database system

  31. r2(PROJ) r1(PROJ) PNO PNO PNAME PNAME BUDGET BUDGET P1 P11 Instrumentation Instrumentation 450000 150000 P2 P12 Database Develop. Database Develop. 135000 145000 P13 P3 CAD/CAM CAD/CAM 250000 150000 Maintenance Maintenance P4 P14 450000 310000 P5 P15 CAD/CAM Wireless Web 350000 500000 Examples • Relational Schema PROJ(PNO, PNAME, BUDGET), we Assume that PNO is the Primary Key • The Two Tables Below are Relations of PROJ • Questions: • Is (PNO,PNAME) a Superkey in Either? Both? • Is PNAME a Candidate Key? Explain Your Answer. • Is (PNAME,BUDGET) a Superkey in Either? Both? dww-database system

  32. Relational Database Schema: A Set S of Relation Schemas (R1, R2, ..., Rn) That Belong to the Same Database S is the Name of the Database S = {R1, R2, ..., Rn} Entity Integrity: For Any Ri in S, Pki is the Primary Key of R Attributes in Pki Cannot Have Null Values in any Tuple of R(ri) T[Pki] < > Null for Any Tuple T in R(r) Entity Integrity Constraint dww-database system

  33. A Constraint Involving Two Relations Used to Specify a Relationship Among Tuples in Referencing Relation and Referenced Relation Referential Integrity Constraints dww-database system

  34. Definition: R1and R2 have a Referential Integrity Constraint If Tuples in the Referencing Relation R1 have a Set of Foreign Key (FK) Attributes That Reference the Primary Key PK of the Referenced Relation R2 A Tuple T1 in R1( A1, A2 , ..., An)is Said to Reference a Tuple T2 in R2 if $ FK {A1, A2 , ..., An} such that T1[fk] = T2[pk] Referential Integrity Constraints dww-database system

  35. EMP ENO ENAME TITLE E1 P1 Manager 12 E2 P1 Analyst 24 E1 J. Doe Elect. Eng. E2 P2 Analyst 6 E3 P3 Consultant 10 E2 M. Smith Syst. Anal. E3 P4 Engineer 48 E3 A. Lee Mech. Eng. E4 P2 Programmer 18 E5 P2 Manager 24 E4 J. Miller Programmer E6 P4 Manager 48 E5 B. Casey Syst. Anal. E7 P3 Engineer 36 E6 L. Chu Elect. Eng. E7 P5 Engineer 23 E8 P3 Manager 40 E7 Mech. Eng. R. Davis E8 J. Jones Syst. Anal. PROJ PNO PNAME BUDGET P1 Instrumentation 150000 P2 Database Develop. 135000 P3 CAD/CAM 250000 P4 Maintenance 310000 P5 CAD/CAM 500000 Examples WORKS ENO PNO RESP DUR E9 P3 Engineer 30 dww-database system

  36. Referential Integrity Constraints • A Referential Integrity Constraint Can Be Displayed in a Relational Database Schema as a Directed Arc From R1.FK to R2.PK EMP PROJ ENO ENAME TITLE PNO PNAME BUDGET ENO PNO RESP DUR WORK WORK[ENO] is a subset of EMP[ENO] WORK[PNO] is a subset of PROJ[PNO] dww-database system

  37. Another Example: Referential Integrity What Do these Arrows Represent in ER Diagram? dww-database system

  38. Transition Integrity Constraint • Can be defined to deal with state changes in the database • Sometimes called dynamic constraints • Example: “the salary of an employee can only increase” dww-database system

  39. Integrity Constraints Summary • Relational Database: Set of Relations Satisfying the Integrity Constraints • Integrity Constraints (ICs): Conditions that Must Hold on All Valid Relation Instances • Key Constraints - Uniqueness of Keys • Entity ICs - No Primary Key Value is Null • Referential ICs Between Two Relations, Cross References Must Point to Existing Tuples • Domain ICs are Limits on the Value of Particular Attribute • Transition ICs Indicate the Way ValuesChanges Due to Database Update dww-database system

  40. Operations on Relations • A DBMS Operates via User Queries to Read and Change Data in a Database • Changes Can be Inserting, Deleting, or Updating (Equivalent to a Delete followed by Insert) • One Critical Issue in DB Operations is Integrity Constraints Maintenance in the Presence of • INSERTING a Tuple • DELETING a Tuple • UPDATING/MODIFYING a Tuple. dww-database system

  41. Problem Statements • Integrity Constraints (ICs) Should Not Be Violated by Update Operations • To Maintain ICs, Updates may Need to be Propagated and Cause Other Updates Automatically • Common Method: Group Several Update Operations Together As a Single Transaction • If Integrity Violation, Several Actions Can Be Taken: • Cancel Operation that Caused Violation (REJECT) • Perform the Operation but Inform User of Violation • Trigger Additional Updates So the Violation is Corrected (CASCADE Option, SET NULL Option) • Execute a User-specified Error-Correction Routine dww-database system

  42. Insertion Operations on Relations • Insert a Duplicate Key Violates Key Integrity: • Check If Duplicates Occur • Insert a Null Key Violates Entity Integrity: • Check If Null is in Any Key • Insert a Tuple Whose Foreign Key Attribute Pointing to an Non-existent Tuple Violates Referential Integrity: • Check the Existence of Referred Tuple dww-database system

  43. Insertion Operations on Relations • Correction Actions: • Reject the Update • Correct the Violation - Change Null, Duplicate, Etc. • Cascade the Access - Insert a New Tuple That Did Not Exist dww-database system

  44. E1 J. Doe Elect. Eng. E2 M. Smith Syst. Anal. E3 A. Lee Mech. Eng. E4 J. Miller Programmer E1 Engineer 36 E5 B. Casey Syst. Anal. ? E1 P5 Engineer E6 L. Chu E8 P3 Manager 40 ? E3 R. Davis Mech. Eng. ? ? ? PROJ PNO PNAME BUDGET P1 Instrumentation 150000 P2 Database Develop. 135000 P3 CAD/CAM 250000 P4 Maintenance 310000 P5 CAD/CAM 500000 Examples EMP WORKS ENO ENO PNO RESP ENAME TITLE DUR E1 P1 Manager 12 E2 P1 Analyst 24 E2 P2 Analyst 6 E3 P3 Consultant 10 E3 P4 Engineer 48 E4 P2 Programmer 18 E5 P2 Manager 24 dww-database system

  45. Deletion Operations on Relations • Deleting a Tuple Referred to by Other Tuples in Database (via FKs) would Violate Referential Integrity • Action: • Check for Incoming Pointers of the Deleted Tuple. • Group the Deletion and the Post-processing of the Referencing Pointers in a Single Transaction dww-database system

  46. Deletion Operations on Relations • Three Options If Deletion Causes a Violation • Reject the Deletion • Attempt to Cascade (Propagate) the Deletion by Deleting the Tuples which Reference the Tuple being or to be Deleted • Modify the Referencing Attribute Values that Cause the Violation; Each Values is Set to Null or Changed to Reference to Another Valid Tuple dww-database system

  47. E1 J. Doe Elect. Eng. E2 M. Smith Syst. Anal. E3 A. Lee Mech. Eng. E4 J. Miller Programmer E5 B. Casey Syst. Anal. 1. Cascading Deleting this tuple? PROJ PNO PNAME BUDGET E5 2. reference revision? P1 Instrumentation 150000 P2 Database Develop. 135000 P3 CAD/CAM 250000 P4 Maintenance 310000 P5 CAD/CAM 500000 Example EMP WORKS ENO PNO RESP ENO ENAME TITLE DUR E1 P1 Manager 12 E2 P1 Analyst 24 E2 P2 Analyst 6 E3 P3 Consultant 10 E3 P5 Engineer 48 E4 P2 Programmer 18 E6 L. Chu Elect. Eng. E5 P2 Manager 24 P4 Manager 48 E6 dww-database system

  48. Modify Operations on Relations • Modify Operation Changes Values of One or More Attributes in a Tuple (or Tuples) of a Given Relation R • Maintaining ICs Requires to Check If the Modifying Attributes Are Primary Key or Foreign Keys. dww-database system

  49. Modify Operations on Relations • Integrity Check Actions: • Case 1: • If the Attributes to be Modified are Neither a Primary Key nor a Foreign Key, Modify Causes No Problems • Must Check and Confirm that the New Value is of Correct Data Type and Domain • Case 2: • Modifying a Primary Key Value Similar to Deleting One Tuple and Insert Another in its Place dww-database system

  50. Constraints and Update Operations • Three Types of Update Operations: • INSERT, DELETE, MODIFY • Constraint Maintenance During Updates: • The Types of Constraints That Most DBMSs Maintain are • Key Constraints • Entity Constraints • Referential Integrity Constraints dww-database system

More Related