Relational Model

1 .Chap 3,6 6e & 7 5e : Relational Model Parts 1 & 2 Prof. Steven A. Demurjian , Sr. Computer Science & Engineering Department The University of Connecticut 191 Auditorium Road, Box U-155 Storrs, CT 06269-3155 steve@engr.uconn.edu http://www.engr.uconn.edu/~steve (860) 486 - 4818 A large portion of these slides are being used with the permission of Dr. Ling Lui, Associate Professor, College of Computing, Georgia Tech. The remainder of these slides have been adapted from the AWL web site for the textbook.

2 .Combining Chapters 3 and 6 6ed What is a Relational Data Model? Schema, Tables, Attributes/Columns, Tuples Characteristics and Examples Referential Integrity Superkeys , Candidate Primary and Foreign Keys Referential Integrity Constrains Relational Algebra Selection, Project, Join, Union, Intersection Advanced Concepts Operations on Relations

3 .Essentials of Relational Approach Relational Model of Data is Based on the Concept of Relations A Relation is a Mathematical Concept Based on Sets Theory of Relations Provides a Formal Foundation for the Relational Data Model The Model Was First Proposed by Dr. E.F. Codd (IBM) in 1970 in the Paper, Entitled "A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970

4 .R A1 A2 An ...... . . . Tuples Attributes relation name t 1 t 2 t m v 11 v 12 v 1n v 21 v m1 v 2n v mn v 22 v m2 t 1 [A n ] Relational Data Model: Data Structure Relational Data Model Structures a Database as a Set of Relations. A Relation Set of Tuples and Typically Shown as a Table With Columns and Rows. Column (Field) Represents an Attribute Row (Tuple) Represents an Entity or a Relationship

5 .Two Versions of a Student Relation

6 . STUDENT (s#, sname, email, dept) Domain(s#): Number(9) Domain(sname): Char(30) Domain(email): Char(20) Domain(dept): Char(15) Basic Concepts - Relation Schema A Schema of a Relation Denoted as R(A 1 :D 1 , A 2 :D 2 , ..., A n :D n ) Set of Attributes That Describe a Relation Denoted by {A 1 :D 1 , A 2 :D 2 , ..., A n :D n }, where A i ( i =1, …, n) is Attribute Name and D i is Domain Over Which A i is Defined Domain The Set of Values From which the Values of an Attribute A j are Drawn, Denoted by Domain( A j ) Example

7 .Relation Scheme - Definition of a Relation Set of Attributes that Describe a Relation e.g., R( A 1 , A 2 , ..., A n ) Domain - Set of Values from which the Values of an Attribute Are Drawn Denoted by Domain( a j ) Relation (Relation Instance) Subset of the Cartesian Product of Domains that Defines its Schema Occurrence of a Relation Scheme R(r) = {T 1 , T 2 , ..., T m }. Cardinality is the Number of Tuples Basic Concepts

8 .Tuple A Row in a Relational Table - t i = {v i1 ,v i2 , ...,v in } Attribute A Column in a Relational Table Projection of an Attribute A j is {v 1j ,v 2j , ..., v mj }, a Subset of Domain( A j . ) Several Attributes may be Defined on the same Domain (e.g., date of purchase, date of order, etc.) Null Value Special Value Meaning “not known” or “not applicable” … Must be a Value - Even if it is Null Degree - the Number of Attributes Basic Concepts

9 .What is an Example? R(A , B) is a Relation Schema Defined over A and B Let domain(A) = {a1, a2} and domain(B) = {0, 1, 2} Tuples are: <a1, 0>, <a1, 2>, <a2, 2> etc. How Many Possible Tuples are there? Entire Relation is a set: {<a1, 0>, <a1, 2>, <a2, 2> etc. }

10 .EMP PROJ WORKS ENO ENAME TITLE SAL PNO PNAME BUDGET RESP PNO ENO 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

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

12 .Examples (cont.) Quiz: 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?

13 .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” ... Not Possible to have Undefined Value

14 .Characteristics of Relations No Duplicate Tuples It is a Set! The Primary Key Always Exists No Explicit or Implicit Ordering of Tuples No Ordering of Attributes (If They Are Referred to by Their Names) All Attribute Values Are Atomic A Special Null Value is Used to Represent Values that are Unknown or Inapplicable to Certain Tuples Thus - If “No” Value is Desired, “Null” is Used

15 .Employee A B C D a2 {b1, b2} c1 d5 a2 b7 c9 d5 a2 b23 c22 d1 …... A B C D a2 b2 c6 d1 a2 b7 c9 d5 a2 b7 c9 d5 …... R1 R2 E# Ename AGE ADDRESS E1 Smith 30 3302 Peachtree Rd., Atlanta, GA E2 Diamond 45 1888 Buford Hyw. E3 Evan Baker Ct. Atlanta Other Examples Which of Following are Relations in a Relational Model?

16 .Relational Schema R( A 1 :D 1 , A 2 :D 2 , ..., A n : D n ) Relation R(r) With Tuples of n Columns Denoted as T i = {v i1 ,v i2 , ...,v in } Attributes A j (I=1,…,m) and R[ a j ] = {v 1j ,v 2j , ..., v mj }, Domain( a j . ) is a Subset of D 1 , and Several Attributes may be Defined on the Same Domain Degree N: Number of Attributes Cardinality M: Number of Tuples Data Structure: Summary

17 .Quiz R(A, B) is a Relation Schema Defined Over A and B Let Domain(A) = {a1, a2} and Domain(B) = {0, 1, 2} Is R(A, B) Equivalent to R(B, A)? How May Possible Tuples? Is the Set {(a1, 1), (a2, 2}, (a0, 0)} a Relation of Schema R? What is the Degree of a Relation of Schema R? What is the Cardinality of the Following Relation {(a1, 1), (a1, 2), (a2, 0)} of Schema R? Yes No 2 3 2 × 3 = 6

18 .Summary of Model: Common Terms Informal Table Column Row (Instance) Table Definition Populated Table Formal Relation Attribute Tuple Schema of Relation Extension

19 .Interpretation Linguistic Logical Logical Schema Tuple declaration assertion predicate fact instance of assertion values of satisfying predicate Summary of Model: Theoretical Foundation Notion of Relation and Tuple is Modeled as in Set Theory Changes From Set Theory Existence of Null Value in the Tuples Most Implementation Allow Duplicate Tuples in Result Sets (such as Projection) Interpretation of Relations:

20 .Summary of Model: Features Simple and Mathematically Elegant Simple, Uniform Data Structure Solid Theoretical Foundation Advantage of the Relational Model: Simplicity Separation Between Data and Data Access Easier to Define Data and Data Structure Easier to Write Queries (Specify What Not How) Relational DBMS can do More for You PC-Based Systems have Brought DB to Masses MS Access - Easy to Use Integration with Office Tools (Word, Excel)

21 .RESERVATION FLT# DATE CUST# FLT-SCHEDULE FLT# CUSTOMER CUST# CUST-NAME DepT Dest ArrT Relational Integrity Constraints Integrity Constraints ( ICs): Conditions that Must Hold on All Valid Relation Instances at Any Given DB State Why are Integrity Constraints Needed? Multiple Flights, Customers, and Cust /Flight/Date What Happens when we try to Delete a Flight?

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 Think About Programming Related Constraints Throughout Our Upcoming Discussion

23 .Key Constraints Superkey (SK): Any Subset of Attributes Whose Values are Guaranteed to Distinguish Among Tuples Could be All Attributes of Entire Relation 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

24 .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 What are Foreign Keys in:

25 .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} which are also superkeys { SerialNo, Make } is a Superkey but not a Key Why? If Remove SerialNo , Make is not a Primary Key

26 .Another Schema with Key What are Typically Used as Keys for Cars?

27 .A Complete Schema with Keys ... Keys Allow us to Establish Links Between Relations

28 . …and Corresponding DB Tables Which Represent Tuples/Instances of Each Relation A S C null W B null null 1 4 5 5 5 Houston

29 . …with Remaining DB Tables