1 / 35

Chapter 2

Chapter 2. The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. In this chapter, you will learn: . That the relational database model takes a logical view of data

bidelia
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. In this chapter, you will learn: • That the relational database model takes a logical view of data • That the relational database model’s basic components are entities and their attributes, and relationships among entities • How entities and their attributes are organized into tables • About relational database operators, the data dictionary, and the system catalog • How data redundancy is handled in the relational database model • Why indexing is important Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  3. Logical View of Data • Relational Database • Designer focuses on logical representation rather than physical • Use of table advantageous • Structural and data independence • Related records stored in independent tables • Logical simplicity • Allows for more effective design strategies Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  4. Logical View of Data (con’t.) • Entities and Attributes • Entity is a person, place, event, or thing about which data is collected • Attributes are characteristics of the entity • Tables • Holds related entities or entity set • Also called relations • Comprised of rows and columns Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  5. Table Characteristics • Two-dimensional structure with rows and columns • Rows (tuples) represent single entity • Columns represent attributes • Row/column intersection represents single value • Tables must have an attribute to uniquely identify each row Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  6. Table Characteristics (con’t.) • Column values all have same data format • Each column has range of values called attribute domain • Order of the rows and columns is immaterial to the DBMS Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  7. Keys • One or more attributes that determine other attributes • Key attribute • Composite key • Full functional dependence • Entity integrity • Uniqueness • No ‘null’ value in key Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  8. Example Tables Figure 2.1 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  9. Simple Relational Database Figure 2.2 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  10. Keys (con’t.) • Superkey • Uniquely identifies each entity • Candidate key • Minimal superkey • Primary key • Candidate key to uniquely identify all other attributes in a given row • Secondary key • Used only for data retrieval • Foreign key • Values must match primary key in another table Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  11. Integrity Rules • Entity integrity • Ensures all entities are unique • Each entity has unique key • Referential integrity • Foreign key must have null value or match primary key values • Makes it impossible to delete row whose primary key has mandatory matching foreign key values in another table Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  12. Relational Database Operators • Relational algebra determines table manipulations • Key operators • SELECT • PROJECT • JOIN • Other operators • INTERSECT • UNION • DIFFERENCE • PRODUCT • DIVIDE Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  13. Union Combines all rows Figure 2.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  14. Intersect Yields rows that appear in both tables Figure 2.6 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  15. Difference Yields rows not found in other tables Figure 2.7 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  16. Product Yields all possible pairs from two tables Figure 2.8 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  17. Select Yields a subset of rows based on specified criterion Figure 2.9 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  18. Project Yields all values for selected attributes Figure 2.10 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  19. Join Information from two or more tables is combined Figure 2.11 Figure 2.14 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  20. Natural Join Process • Links tables by selecting rows with common values in common attribute(s) • Three-stage process • Product creates one table • Select yields appropriate rows • Project yields single copy of each attribute to eliminate duplicate columns Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  21. Other Joins • EquiJOIN • Links tables based on equality condition that compares specified columns of tables • Does not eliminate duplicate columns • Join criteria must be explicitly defined • Theta JOIN • EquiJOIN that compares specified columns of each table using operator other than equality one • Outer JOIN • Matched pairs are retained • Unmatched values in other tables left null • Right and left Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  22. Divide Requires user of single-column table and two-column table Figure 2.17 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  23. 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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  24. Relationships within Relational Database • Relationship classifications • 1:1 • 1:M • M:N • E-R Model • ERD Maps E-R model • Chen • Crow’s Feet Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  25. 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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  26. Example 1:M Relationship Figure 2.18 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  27. Example 1:M Relationship Figure 2.20 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  28. Example M:N Relationship Figure 2.23 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  29. Example M:N Relationship Figure 2.24 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  30. Converting M:N Relationship to Two 1:M Relationships Figure 2.25 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  31. Converting M:N Relationship to Two 1:M Relationships (con’t.) Figure 2.26 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  32. Converting M:N Relationship to Two 1:M Relationships (con’t.) Figure 2.27 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  33. Converting M:N Relationship to Two 1:M Relationships (con’t.) Figure 2.28 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  34. Data Redundancy Revisited • Foreign keys can reduce redundancy • Some redundancy is desirable • Called controlled redundancy • Speed • Information requirements Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  35. Indexes • Points to location • Makes retrieval of data faster Figure 2.31 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

More Related