1 / 40

Chapter 3

Chapter 3. Relational Data Model. Concept. Introduction Relation Attribute,Tuple,Domain Relational Schema Database Schema The Relational Algebra Query Languages Views. Introduction.

lucita
Download Presentation

Chapter 3

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. 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

More Related