1 / 12

The Relational Data Model

The Relational Data Model. TCU Database Systems Last update: September 2004 Reference: Elmasri 4 th edition, chapter 5. Introduction . Codd (1970) Theoretical (mathematical) foundation Concepts relation data structure integrity constraints Operations relational algebra and extensions.

elke
Download Presentation

The 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. The Relational Data Model TCU Database Systems Last update: September 2004 Reference: Elmasri 4th edition, chapter 5

  2. Introduction • Codd (1970) • Theoretical (mathematical) foundation • Concepts • relation data structure • integrity constraints • Operations • relational algebra and extensions

  3. Definitions • Figure 5.1 (informal definitions) • Domain D: a set of atomic (indivisible) values • {Names}, {valid GPA’s}, {valid grades} • must specify a data type {char(30)}, {[0.0,4.0]},{A,B,C,D,F} • dom(Ai)=data type

  4. Definition: Relation Schema • think of as template or a Prolog predicate • R(A1, A2,...,An) where • R = relation name • Ai=attribute • n = degree of relation R • example: PET(Name, Type, Breed, Birthdate, Weight)

  5. Definition: Relation • r(R): a relation r of the relation schema R(A1,A2,...,An) • set of n-tuples r={t1, t2,...,tm} • each n-tuple t is an ordered list, t=<v1,v2,...,vn>, where • vi is an element of dom(Ai) or null (unknown/D.N.E.) • More formally • a relation r(R) is a subset of the Cartesian product of the domains that define R • Max size (assuming finite domains):

  6. Definition: Relational DB Schema • Set of relation schemas AND integrity constraints • Figure 5.5 • A relational DB instance is a set of relation instances that satisfy the integrity constraints • Figure 5.6

  7. Characteristics of Relations • Row order (ordering of tuples) is insignificant • formally, but for performance it may be • Column order is insignificant • given a unique name or a fixed order • All values are atomic - can’t be subdivided • called the first normal form assumption • no composite attributes (e.g., address) • no multivalued attributes (e.g., car colors)

  8. Relational Model Constraints • Domain • Key • Entity Integrity • Referential Integrity • Others in section 5.2.5 and later in normalization • Generally no ability to declaratively specify • semantic integrity constraints (max hours all projects<56; salary<boss) • heuristics (folks usually weigh more than their age)

  9. Domain Constraint • attribute value is an atomic value from domain • Example domains (from Oracle) • number(l,d): l=length, d=decimal digits • char(size): fixed length • varchar(size): variable length char string • date • long: up to 2 GB • many allow subranges and enumerated data types

  10. Key Constraint • Each tuple in r is unique (a set) • superkey: any subset of attributes for which the values are unique in all valid DB states • key: a minimal superkey • candidate key: multiple keys may be possible • primary key: the candidate key selected • prefer integer key or key with fewest attributes • Figure 5.4

  11. Integrity Constraints • Entity Integrity • no primary key value can be null • Referential Integrity • foreign key • same domain as p.k. attribute of referenced schema • value equals an existing referenced attribute or is null (if not part of a p.k.) • Figures 5.6, 5.7

  12. Updates • DBMS must enforce integrity constraints • Insert • can violate all 4 I.C.’s • DBMS rejects or asks for correction • Delete • can only violate referential integrity • reject/cascade/modify referencing attribute value (null, default or another tuple value) • Modify • can violate all 4 if p.k. or f.k. • else domain constraint only examples pp 141-143

More Related