1 / 43

Chapter 2

Chapter 2. The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. TOPICS. ENTITIES & ATTRIBUTES TABLES KEYS INTEGRITY RULES RELATIONAL ALGEBRA DATA DICTIONARY CONTROLLED REDUNDANCY INDEXING. Database Design.

lou
Download Presentation

Chapter 2

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 2 The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel

  2. TOPICS • ENTITIES & ATTRIBUTES • TABLES • KEYS • INTEGRITY RULES • RELATIONAL ALGEBRA • DATA DICTIONARY • CONTROLLED REDUNDANCY • INDEXING

  3. Database Design • Determine Entities • Identify each entities characteristics (attributes) • Name attributes • Assign data types and size • Determine constraints and domain • Identify candidate keys • Assign Primary key • Assign secondary (alternate) keys

  4. Logical View of Data • Relational Database • Designer focuses on logical (what) representation rather than physical (how) • Use of table advantageous • Structural and data independence • Related records stored in independent tables • Logical simplicity • Allows for more effective design strategies

  5. Logical View of Data (con’t.) • Entities and Attributes • Entity is a person, place, event, or thing about which data is collected • Entity names are SINGULAR NOUNS • Attributes are characteristics of the entity • Tables • Holds related entities or entity set • Also called relations • Comprised of rows and columns

  6. Summary of the Characteristics of a Relational Table Table 2.1

  7. Example Tables Figure 2.1

  8. Keys • Controlled redundancy (shared common attributes) makes the relational database work. • The primary key is an attribute that uniquely identifies any given entity(row). • To have entity integrity, a table must have a primary key and must not have a null value in the primary key.

  9. Keys • The primary key of one table appears again as the link (foreign key) in another table • This is where controlled redundancy comes into place. • If the foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key are said to exhibit referential integrity

  10. Keys • A key helps define entity relationships. • The key’s role is based on a concept known as determination, which is used in the definition of functional dependence. • The attribute B is functionally dependent on A if A determines B. • An attribute that is part of a key is known as a key attribute. • A multi-attribute key is known as a composite key. • If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A).

  11. Relational Database Keys

  12. Integrity Rules Revisited

  13. Figure 2.4 An Illustration of Integrity Rules

  14. Relational Database Operators • The degree of relational completeness can be defined by the extent to which relational algebra is supported. • Relational algebra defines the theoretical way of manipulating table contents using the eight relational functions: SELECT, PROJECT, JOIN, INTERSECT, UNION, DIFFERENCE, PRODUCT, and DIVIDE.

  15. Union Combines all rows Figure 2.5

  16. Intersect Yields rows that appear in both tables Figure 2.6

  17. Difference Yields rows not found in other tables Figure 2.7

  18. Product Yields all possible pairs from two tables

  19. Divide Requires use of single-column table and two-column table Figure 2.17

  20. Select Yields a subset of rows based on specified criterion

  21. Project Yields all values for selected attributes (columns)

  22. Join Information from two or more tables is combined Figure 2.11 Figure 2.14

  23. Relational Database Operators • Natural JOIN links tables by selecting only the rows with common values in their common attribute(s). It is the result of a three-stage process: • A PRODUCT of the tables is created. (Figure 2.12) • A SELECT is performed on the output of the first step to yield only the rows for which the common attribute values match. (Figure 2.13) • A PROJECT is performed to yield a single copy of each attribute, thereby eliminating the duplicate column. (Figure 2.14)

  24. Natural Join, Step 1: PRODUCT Figure 2.12

  25. Figure 2.13 Natural Join, Step 2: SELECT Figure 2.14 Natural Join, Step 3: PROJECT

  26. Join • Natural join: EquiJoin with the duplicate column removed. Performed by a Project on the result of equijoin. (aka Inner Join) • When the term Join is mentioned without any prefix, it is implied to be Natural Join. • Outer Join: Unmatched rows from the participating tables are retained in the result table with unmatched attributes left blank or null. • Left Outer join keeps all tuples from the left relation • Right Outer Join keeps all tuples from the right relation • Theta Join: EquiJoin with the equality operator replaced by any other comparison operator, such as greater than, less than, etc.

  27. Outer JOIN - Examples

  28. Left Outer Join - Example

  29. Right Outer Join - Example

  30. Data Dictionary and System Catalog • Data dictionary • Provides detailed account of all tables found within database • Metadata • Attribute names and characteristics • System catalog • Detailed data dictionary • System-created database • Stores database characteristics and contents • Tables can be queried just like any other tables • Automatically produces database documentation

  31. A Sample Data Dictionary

  32. Relationships within Relational Database • Relationship classifications • 1:1 • 1:M • M:N • E-R Model • ERD Maps E-R model • Chen • Crow’s Feet

  33. ERD Symbols • Rectangles represent entities • Diamonds represent the relationship(s) between the entities • “1” side of relationship • Number 1 in Chen Model • Bar crossing line in Crow’s Feet Model • “Many” relationships • Letter “M” and “N” in Chen Model • Three pronged “Crow’s foot” in Crow’s Feet Model

  34. Example 1:M Relationship Figure 2.18

  35. Example 1:M Relationship

  36. Example M:N Relationship

  37. Example M:N Relationship Figure 2.24

  38. Converting M:N Relationship to Two 1:M Relationships Figure 2.25

  39. Converting M:N Relationship to Two 1:M Relationships (con’t.) Figure 2.26

  40. Converting M:N Relationship to Two 1:M Relationships (con’t.) Figure 2.27

  41. Converting M:N Relationship to Two 1:M Relationships (con’t.) Figure 2.28

  42. Data Redundancy Revisited • Foreign keys can reduce redundancy • Some redundancy is desirable • Called controlled redundancy • Speed • Information requirements

  43. Indexes • Points to location • Makes retrieval of data faster Figure 2.31

More Related