1 / 100

Relational Data Model 建置資料模式

Relational Data Model 建置資料模式. (Based on Chapter 7 in Fundamentals of Database Systems by Elmasri and Navathe, Ed. 4). Outline.

halee-dickson
Download Presentation

Relational Data 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 Data Model建置資料模式 (Based on Chapter 7 in Fundamentals of Database Systems by Elmasri and Navathe, Ed. 4)

  2. Outline 1 Relational Model Concepts2 Characteristics of Relations3 Relational Integrity Constraints 3.1 Key Constraints 3.2 Entity Integrity Constraints 3.3 Referential Integrity Constraints4 Update Operations on Relations5 Relational Algebra Operations 5.1 SELECT s and PROJECT P 5.2 Set Operations 5.3 JOIN Operations 5.4 Additional Relational Operations The Relational Data Model

  3. BASICS • Data are stored in Relations(tables). • A Relation is a mathematical concept based on the ideas of sets. • Relational Model has a strong theoretic foundation. • The model was first proposed by Dr. E.F. Codd of IBM in 1970. The Relational Data Model

  4. INFORMAL DEFINITIONS • RELATION: • A table of values • A relation may be thought of as a set of rows. • A relation may alternatively be though of as a set of columns. • Each row of the relation may be given an identifier(一筆資料的代號). • Each column is called by its column name or column header or attribute name. The Relational Data Model

  5. The Relational Data Model

  6. FORMAL DEFINITIONS • A Relation may be defined in multiple ways. • The Schema of a Relation: R (A1, A2, .....An)Relation R is defined over attributes A1, A2, ..…,An The Relational Data Model

  7. FORMAL DEFINITIONS (contd.) • 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. The Relational Data Model

  8. FORMAL DEFINITIONS (contd.) • 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. The Relational Data Model

  9. FORMAL DEFINITIONS (contd.) • For example: • <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000"> is a tuple belonging to the CUSTOMER relation. The Relational Data Model

  10. FORMAL DEFINITIONS (contd.) • A relation may be regarded as a set of tuples (rows). • Columns in a table are also called as attributes of the relation. The Relational Data Model

  11. Informal Terms Table Column Row Value in a column Table Definition Populated Table(內容) Formal Terms Relation Attribute / Domain Tuple Domain Schema of a relation Extension DEFINITION SUMMARY The Relational Data Model

  12. Notes: • Whereas languages like SQL use the informal terms of TABLE (e.g. CREATE TABLE), COLUMN (e.g. SYSCOLUMN variable), the relational database textbooks present the model and operations on it using the formal terms(I.e., relation). The Relational Data Model

  13. 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. The Relational Data Model

  14. 2 Characteristics of Relations • Ordering of attributes in a relation schema R (and of values within each tuple): • We will consider the attributes in R(A1, A2, ..., An) and the values in t=<v1, v2, ..., vn> to be ordered . • (However, a more general alternative definition of relation does not require this ordering). The Relational Data Model

  15. 2 Characteristics of Relations • 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. The Relational Data Model

  16. Notation(表示法): • We refer to component values of a tuple t by t[Ai] = vi (the value of attribute Ai for tuple t). • Similarly, t[Au, Av, ..., Aw] refers to the subtuple of t containing the values of attributes Au, Av, ..., Aw, respectively. The Relational Data Model

  17. 3 Relational Integrity Constraints • Constraints(限制) are conditions that must hold on all valid relation instances. • There are three main types of constraints: • Domain constraints(欄位值合乎型態) • Key constraints, (要有鍵值欄位) • Entity integrity constraints, and (鍵值欄位不可為空值Null) • Referential integrity constraints(參考時必須參考的到) The Relational Data Model

  18. 3.0 Domain Constraints • Domain constraints specify that the value of each attribute A must be atomic value from domain dom(A).(值必須與型態一致) The Relational Data Model

  19. 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 t1 and t2 in r(R), t1[SK] t2[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. The Relational Data Model

  20. 3.1 Key Constraints Example: • The CAR relation schema:CAR(State, Reg#, SerialNo, Make, Model, Year) • has two keys • Key1 = {State, Reg#}, • Key2 = {SerialNo}, • which are also superkeys. • {SerialNo, Make} is a superkey but not a key. The Relational Data Model

  21. 3.1 Key Constraints • If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. • The primary key attributes are underlined. The Relational Data Model

  22. Entity Integrity: • t[PK] <> null for any tuple t in r(R) • 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. The Relational Data Model

  23. Note: • Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key. The Relational Data Model

  24. 3.3 Referential Integrity • A constraint involving two relations (the previous constraints involve a single relation). • Used to specify a relationship among tuples in two relations: the referencing relation(參考表格) and the referenced relation(被參考表格). The Relational Data Model

  25. Foreign key • Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2.(欄位的值為另一個表格的鍵值欄位) • A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK].(t1參考t2) • A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2. The Relational Data Model

  26. Referential Integrity • Referential integrity states that: For any tuple t1 in R1 there exists a tuple t2 in R2 such that t1[FK] = t2[PK], otherwise, t1[FK] = NULL • 簡言之，不能參考的到一個不存在的值 The Relational Data Model

  27. The Relational Data Model

  28. The Relational Data Model

  29. 4 Update Operations on Relations • There are three basic update operations on relations: • INSERT a tuple. • DELETE a tuple. • MODIFY a tuple. • Integrity constraints should not be violated by the update operations. The Relational Data Model

  30. Several update operations may have to be grouped together. (transactions) • Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints. The Relational Data Model

  31. In case of integrity violation, several actions can be taken: • cancel the operation that causes the violation (REJECT option) • perform the operation but inform the user of the violation • trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) • execute a user-specified error-correction routine The Relational Data Model

  32. 5 The Relational Algebra(關聯代數) • 一組表示查詢的符號，用以表示查詢的過程 • Operations to manipulate relations. • Used to specify retrieval requests (queries). • Query result is in the form of a relation. The Relational Data Model

  33. 5 The Relational Algebra • Relational Operations: • 5.1 SELECT s and PROJECT P operations. • 5.2 Set operations: • These include UNION ∪, INTERSECTION ∩, DIFFERENCE －, CARTESIAN PRODUCT . • 5.3 JOIN operations . • 5.4 Other relational operations: • DIVISION, OUTER JOIN, AGGREGATE FUNCTIONS. The Relational Data Model

  34. 5.1 SELECT operation (denoted by s ): • Selects the tuples (rows) from a relation R that satisfy a certain selection condition c • Form of the operation: sc(R) • The condition c is an arbitrary Boolean expression on the attributes of R The Relational Data Model

  35. 5.1 SELECT operation (denoted by s ): • Resulting relation has the same attributes as R • Resulting relation includes each tuple in r(R) whose attribute values satisfy the condition c The Relational Data Model

  36. Boolean expression • The Boolean expression specified in <selection condition> is made up of a number of clauses of the form:(條件式的寫法) • <attribute name> <comparison op> <constant value>, or • <attribute name> <comparison op> <attribute name> • Where <attribute name> is the name of the attribute of R, • <comparison op> is normally one of the operators {=, <, , >, , }, and • <constant value> is a constant value from attribute domain. The Relational Data Model

  37. Boolean expression • Clauses can be arbitrarily connected by the Boolean operators AND, OR, and NOT to form a general selection condition. The Relational Data Model

  38. Examples: • s DNO=4(EMPLOYEE) • sSALARY>30000(EMPLOYEE) • s(DNO=4 AND SALARY>25000) OR (EMPLOYEE) (DNO=5 AND SALARY>30000) The Relational Data Model

  39. 5.1PROJECT operation (denoted by P ): • Keeps only certain attributes (columns) from a relation R specified in an attribute list L(所要取出的欄位) • Form of operation: PL(R) • Resulting relation has only those attributes of R specified in L • PNAME,LNAME,SALARY(EMPLOYEE) The Relational Data Model

  40. Eliminates duplicate tuples • Duplicate tuples are eliminated by the P operation. • 重複資料刪除 • The PROJECT operation eliminates duplicate tuples in the resulting relation so that it remains a mathematical set (no duplicate elements) The Relational Data Model

  41. Example: • P SEX,SALARY(EMPLOYEE) • If several male employees have salary 30000, only a single tuple <M, 30000> is kept in the resulting relation. The Relational Data Model

  42. The Relational Data Model

  43. The Relational Data Model

  44. Sequences of operations: • Several operations can be combined to form a relational algebra expression (query) • 將運算組合成為查詢式子 The Relational Data Model

  45. Example: • Retrieve the names and salaries of employees who work in department 4: • PFNAME,LNAME,SALARY (sDNO=4 (EMPLOYEE) ) • Relational algebra expression The Relational Data Model

  46. Sequences of operations • Alternatively, we specify explicit intermediate relations for each step: • DEPT4_EMPS  sDNO=4(EMPLOYEE) • R  PFNAME,LNAME,SALARY(DEPT4_EMPS) • 以多步驟表示查詢 The Relational Data Model

  47. Sequences of operations • 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  sDNO=4(EMPLOYEE) • R(FIRSTNAME,LASTNAME,SALARY)  PFNAME,LNAME,SALARY (DEPT4_EMPS) The Relational Data Model

  48. The Relational Data Model

  49. 5.2 Set Operations(集合運算) • Binary operations from mathematical set theory:(將tuple看成集合元素) • UNION: R1  R2, • INTERSECTION: R1  R2, • SET DIFFERENCE: R1  R2, • CARTESIAN PRODUCT: R1  R2. The Relational Data Model

  50. Union compatibility • For , ,  , the operand relations R1(A1, A2, ..., An) and R2(B1, B2, ..., Bn) • must have the same number of attributes, and • the domains of corresponding attributes must be compatible; • that is, dom(Ai)=dom(Bi) for i=1, 2, ..., n. • This condition is called union compatibility. • 類似的資料才能作集合運算 The Relational Data Model

More Related