1 / 29

Chapter 2 Relational Theory

DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH. Raymond Frost – John Day – Craig Van Slyke. Chapter 2 Relational Theory. The Relational Model. The relational model is perhaps the simplest and most intuitive data model ever developed.

alexaponte
Download Presentation

Chapter 2 Relational Theory

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 DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH Raymond Frost – John Day – Craig Van Slyke Chapter 2 Relational Theory

  2. Chapter 2 The Relational Model • The relational model is perhaps the simplest and most intuitive data model ever developed. • The entire model is based upon tables with rows and columns. • Tables are called relations, hence the term relational model is used.

  3. Chapter 2 Equivalent Terms Exhibit 2-1: Relational Model-Equivalent Terms

  4. Chapter 2 Entity Relationship Diagrams One-to-Many Relationships: Parent vs. Child The crows foot points to the child. One customer places many orders; Each order belongs to one customer. One employee has many dependents; Each dependent belongs to one employee. Exhibit 2-2: One-to-Many Relationships

  5. Chapter 2 Many-to-Many Relationships A many-to-many relationship is represented by creating an associative entity. Exhibit 2-3: Many-to-Many Relationships

  6. Chapter 2 Attributes Exhibit 2-4: Attributes

  7. Chapter 2 Primary Keys A primary key uniquely identifies each record in a table. • Unique • Minimal • Not Null • Nonupdateable

  8. Chapter 2 Unique But NOT Minimal Primary Keys Exhibit 2-5: Unique But NOT Minimal Primary Keys

  9. Chapter 2 Unique and Minimal Primary Keys Exhibit 2-6: Email Is a Unique AND Minimal Primary Key

  10. Chapter 2 Primary Keys Formed from Existing Fields Exhibit 2-7: Primary Keys Formed from Existing Fields

  11. Chapter 2 Computer-Generated Primary Keys Computer-generated keys are often formed by adding one to the last number assigned. Primary keys might also be computer generated to establish a single-field primary key, as opposed to one formed of multiple fields. Exhibit 2-8: Computer-Generated Primary Keys

  12. Chapter 2 Foreign Keys Create Relationships A parent table reproduces its primary key values in every child table to which it connects. Because these reproduced values originate outside of the child table, they are called foreign keys. Foreign keys link the related records between parent and child tables. A foreign key is placed in the child table (following the crow’s foot). Foreign keys are boldfaced and follow the naming convention of PARENT_TABLENAME$parent_fieldname (Fleming and van Holle, 1998). Exhibit 2-9: Foreign Keys Create Relationships

  13. Chapter 2 Weak Entity A dependent table (also called a weak entity) is a child table that requires a parent table for identification. Exhibit 2-10: Weak Entity

  14. Chapter 2 Primary Key for an Associative Entity An associative table is a child of two parent tables that are in a many-to-many relationship. An order may list many products and a given product may appear on multiple orders. This many-to-many relationship between order and product is represented by the associative table LINEITEM. To form its primary key, LINEITEM uses both ORDER$id and PRODUCT$id. Exhibit 2-11: Primary Key for an Associative Entity

  15. Chapter 2 Data Integrity • Entity integrity: Every table must have a valid primary key. • Referential integrity: No unmatched foreign key values. Referential integrity requires that foreign key values match existing primary key values in the table to which they refer.

  16. Chapter 2 Exercise: Find the Entity and Referential Integrity Violations Exhibit 2-12: Find Two Entity Integrity and Three Referential Integrity Violations

  17. Chapter 2 Answers: = entity integrity violations =referential integrity violations

  18. Chapter 2 First Normal Form (1NF) A database is normalized when each field holds just one value, and each table has the correct set of fields. A table is said to be in first normal form (1NF) when each field in that table contains single values only. Exhibit 2-13: First Normal Form (1NF)

  19. Chapter 2 Types of Databases Hierarchical: Represents a database as a tree-structured hierarchy, similar to the folder system on a computer. Network: Represents a database as a network of connected tables. The major difference between a network and a relational database is that the relational database has foreign keys to make connections between tables, whereas the network database uses physical pointers to connect tables. Relational: A relational database is the easiest system in which to develop and maintain a database. Object oriented: The first type of database to allow programs (methods), and not just data, to be stored in the database. They were also the first databases to allow data objects to inherit structures from other data objects. Over time, relational products have become more object oriented—incorporating both stored procedures (programs) and inheritance—thereby eliminating the competitive advantage of object-oriented systems.

  20. Chapter 2 Advantages of Relational Databases • Reliance on logical, rather than physical, links between related records • Use of a fourth-generation language (4GL) • Allowance for a high degree of data independence

  21. Chapter 2 Views When developers create tables in a database, they are called base tables. A view, by contrast, is a subset derived from the base tables. Views enable the DBA to restrict the portion of the database visible to each user. The member_email_view contains only the address, first_name, and last_name fields, whereas the member_phone_view contains only the first_name, last_name, and telephone fields. Exhibit 2-14: Views Used to Restrict Access and Rename Columns

  22. Chapter 2 Practice: Draw the Crow’s Foot A consultant does many projects; each project is done by one consultant. Exhibit 2-15: ER Diagram for Practice Exercise 1

  23. Chapter 2 Exercise: Draw a New ERD Create an associative entity, ENROLLMENT, to represent the many-to-many relationship. Exhibit 2-16: ER Diagram for Practice Exercise 2

  24. Chapter 2 Exercise: Select a Primary Key Exhibit 2-17: Table for Practice Exercise 3

  25. Chapter 2 Exercise: Identify the Foreign Key Exhibit 2-18: ER Diagram for Practice Exercise 4

  26. Chapter 2 Exercise: Identify the Entity and Referential Integrity Violations There is one Entity Integrity and one Referential Integrity violation Exhibit 2-19: Tables for Practice Exercise 5

  27. Chapter 2 Printable Slide Versions The following slides contain non-animated versions of the previous animated slides for handouts.

  28. Chapter 2 Many-to-Many Relationships A many-to-many relationship is represented by creating an associative entity. Exhibit 2-3: Many-to-Many Relationships

  29. Chapter 2 Attributes Exhibit 2-4: Attributes

More Related