Chapter 3

1. Chapter 3 Relational Data Model

2. Concept • Introduction • Relation • Attribute,Tuple,Domain • Relational Schema • Database Schema • The Relational Algebra • Query Languages • Views

3. Introduction • The relational model was formally introduced by Dr. E. F. Codd in 1970 and has evolved since then, through a series of writings. • The model provides a simple, yet rigorously defined, concept of how users perceive data. • A relational database is a collection of two-dimensional tables. • The organization of data into relational tables is known as the logical view of the database.

4. Introduction • A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. • In such a database the data and relations between them are organised in tables. • A table is a collection of records and each record in a table contains the same fields.

5. Introduction

6. Components of Relational Model • Data structure: Represented in the form of tables with rows and columns • Data manipulation: Powerful operations (SQL) used to manipulate data stored in relations • Data integrity: Facilities are included to specify business rules that maintain the integrity of data when they are manipulated.

7. Attribute,Domain,Tupple • Each attribute of a relation has a name • The set of allowed values for each attribute is called the domain of the attribute • Attribute values are (normally) required to be atomic, that is, indivisible • E.g. multivalued attribute values are not atomic • E.g. composite attribute values are not atomic • The special value null is a member of every domain • The null value causes complications in the definition of many operations • we shall ignore the effect of null values in our main presentation and consider their effect later

8. Example

9. Relation • A named two-dimensional table of data. Each relation consists of a finite set of named columns and an arbitrary number of unnamed rows. Notation: RELATION NAME(Attribute1, Attribute2…) Example: EMPLOYEE(EmpID, Name, Dept, Salary)

10. Properties of Relations • Each relation (or table) has a unique name • Entries in columns are atomic (no repeating groups - single valued) • Entries in columns are from the same domain • Each row is unique (no duplicate rows) • The sequence of columns (left to right) is insignificant • The sequence of rows (top to bottom) is insignificant

11. Relational Keys • Allow storage and retrieval of a row of data based on stored values of that data • Primary Key: An attribute (or combination of attributes) that uniquely identify each row in a relation • Composite Key: A primary key that consists of more than one attribute

12. Foreign Key • An attribute in one table that is a primary key in another table. Foreign Keys Purchase Order PO # PODate Vendor # Item # Vendor Name Address Balance Vendor # Item Reord- Pt. Name QOH Item #

13. Integrity Constraints(ràng buộc toàn vẹn) • Facilitate maintaining accurate data • Domain Constraints: the set of values that may be assigned to an attribute must be from the same domain • Domain name, meaning, data type, size/length, allowable values or ranges • Operational Constraints

14. Integrity Constraints • Entity Integrity: Every relation must have a primary key and the data values of that key are valid. • The primary key can never be null • Referential Integrity: Either each foreign key value must match a primary key value in the other relation or the foreign key value must be null

15. Referential Integrity Constraints CUSTOMER Customer-ID Customer-Name Customer-Address ORDER Order-ID Order-Date Customer-ID ORDER LINE Order-IDProduct-ID Quantity PRODUCT Product-ID Product-Description Product-Finish Unit-Price On-Hand

16. Well Structured Relations • Minimum Redundancy • Users can insert, modify and delete row (instances) without errors or inconsistencies (Anomalies). • Anomalies: errors that may result when a user attempts to update a table that contains redundant data.

17. The Relational Algebra • The relational algebra is a procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation as their result.

18. Relational Algebra • Fundamental operations in the relational algebra: • Select • Project • Union • set intersection • Cartesian product • Division • Join

19. The Union Operation • allows to find tuples that are • a union operation r U s is: • r U(+) s ={t/ t r or t s} r’

20. The Union Operation • For a union operation r U s to be valid, we require that two conditions hold: • The relations r and s must have the same number of attributes. • The domains of the ith attribute of r and the ith attribute of s must be the same, for all i.

21. The Union Operation • find the names of all bank customers who have either an account or a loan or both.

22. The Set-Intersection Operation • Find all tuples that are in both relation r1 and relation r2 • r3= r1* r2 = {t/ t  r1 and t  r2}

23. The Set-Intersection • find all customers who have both a loan and an account.

24. The Set Difference(Minus) Operation • Allows to find tuples that are in one relation but are not in another. • r3= r1- r2 ={t/ t  r1 and t  r2} • Set differences must be taken between compatible relations. • r and s must have the same arity • attribute domains of r and s must be compatible

25. The Set Difference(Minus) Operation • find all customers of the bank who have an account but not a loan

26. The Select Operation • The select operation selects tuples that satisfy a given condition.. • Q+=A1,A2,...,An • Condition D(x) • Q' = q /q Q /q satisfy D(x)

27. The Select Operation • We can find all tuples in which the amount lent is more than $1200

28. The projectOperation • Projection is the operation of selecting certain attributes from a relation R to form a new relation S. • X, X = {A1, A2,..., An} • r’=r.X=r[X]={t’/t  r and t’=t.X}

29. The projectOperation • Find those customers who live in Harrison.

30. Cartesian product • The cartesian product of two tables combines each row in one table with each row in the other table. • Q1(A1,A2,...,Am) • Q2( B1,B2,...,Bn), • Q3= Q1* Q2=q3=(q1,q2)Q3/q1Q1 q2Q2

31. Cartesian product

32. Division Operation • Suited to queries that include the phrase “for all”. • Let r and s be relations on schemas R and S respectively where • R = (A1, …, Am, B1, …, Bn) • S = (B1, …, Bn) The result of r  s is a relation on schema R – S = (A1, …, Am) r  s = { t | t   R-S(r)   u  s ( tu  r ) }

33. S D E R A B C D E a 1  a  a 1 b 1  a  a 1  a  b 1  a  a 1  a  b 3  a  a 1  a  b 1  a  b 1 Division Operation

34. The Natural-Join Opeation • Outer Join • left outer join • Right Outer join • Inner join Inner join left outer join right outer join full outer join

35. Exercises –Xác định các loại quan hệ • Cho biết các nhân viên ở phòng số 4 • Quan hệ: NHANVIEN • Thuộc tính: PHG • Điều kiện: PHG=4

36. Exercises –Xác định các loại đại số quan hệ • Cho biết họ tên và lương của các nhân viên • Quan hệ: NHANVIEN • Thuộc tính: HONV, TENNV, LUONG

37. Exercises –Xác định các loại đại số quan hệ • Với mỗi phòng ban, cho biết thông tin của người trưởng phòng • Quan hệ: PHONGBAN, NHANVIEN • Thuộc tính: TRPHG, MAPHG, TENNV, HONV, …

38. Exercises –Xác định các loại đại số quan hệ • Cho biết mã nhân viên tham gia tất cả các đề án • Quan hệ: PHANCONG, DEAN • Thuộc tính: MANV

39. Exercises –Xác định các loại đại số quan hệ • Cho biết họ tên nhân viên và tên phòng ban mà họ phụ trách nếu có • Quan hệ: NHANVIEN, PHONGBAN • Thuộc tinh: TENNV, TENPH

40. Exercises • Quản lý điểm thi của sinh viên với các thông tin MASV, HOTEN, MONHOC,TENKHOA, DIEMTHI