1 / 61

Relational Model

Relational Model . Outline. Relational Model History Concepts Constraints. Relational Model History. Introduced by Ted Codd in 1970 Ted Codd was an IBM Research Fellow Laid the foundation for database theory Many database concepts & products based on this model.

nikki
Télécharger la présentation

Relational 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 Model

  2. Outline • Relational Model • History • Concepts • Constraints

  3. Relational Model History • Introduced by Ted Codd in 1970 • Ted Codd was an IBM Research Fellow • Laid the foundation for database theory • Many database concepts & products based on this model

  4. Why is the relational model so popular? • supported by a mathematical model • relations (tables) are a good tool to use when communicating information to users and developers • efficient implementations exist for the storing of relational information in the form of Relational DBMSs (RDBMSs)

  5. What is a Relation? • A Relation is a 2-dimensional table of values (rows and columns) • each row, or tuple, is a collection of related facts • the degree of the relation is the number of attributes in the relation • each column represents an attribute • each row is an instance of the relation

  6. What is a Relation (cont’d)? • So, a relation is a big table of facts. • Each column contains the same attribute data with the same data type • Each row describes a real-world instance of the relation • A Relational database contains one or more relations (or tables).

  7. Schema vs. Instance • the name of the relation and the set of attributes is called the schema (or the intension) • the current values in the relation represent an instance (or extension) of the data

  8. Relational Database Model • Enables us to view data logicallyrather than physically. • Reminds us of simpler file concept of data storage • Terminologies • Relational Database • Relation • Attribute • Domain • Tuple

  9. Relational Database Terminology • Relational Database: • Is a collection of normalized relations with distinct relation names. • Relation: • Is a two-dimensional structure (table) composed of rows and columns. • Relation is also called a table because the relational model’s creator, Codd, used the term relation as a synonym for table • Each cell of a table contains exactly one atomic (single) value. • Table name is distinct from all other table names in the database. • Each table must have an attribute or a combination of attributes that uniquely identifies each row.

  10. Relational Database Terminology • Tuple: • A record in a relation. • Each record is distinct; there are no duplicate records. • Order of records has no significance, theoretically. • Each table row (tuple) represents a single entity occurrence within the entity set. • Domain: • The set of allowable values for one or more attributes. • Values of a column are all from the same domain.

  11. Relational Database Terminology • Attribute: • A named column of a relation. Each column represents an attribute. • Each column has a distinct name within a table. • Order of columns has no significance. • All values in a column must conform to the same data format. For example, if the attribute is assigned an integer data format, all values in the column representing that attribute must be integer. • Each column has a specific range of values known as the attribute domain.

  12. Alternative Terminology • Relation, attribute, tuple • Table, column, record • File, field, row

  13. Summary of the Characteristics of a Relational Table

  14. Example Attribute Domains

  15. STUDENT Table Attribute Values

  16. Relational Keys 1/3 • Consists of one or more attributes that determine other attributes. • Primary key (PK) is an attribute (or a combination of attributes) that uniquely identifies any given entity (row) • Key’s role is based on determination • If you know the value of attribute A, you can look up (determine) the value of attribute B • Composite key is a key consists of more than one attribute. • Key Attribute is any attribute that is part of a key.

  17. Relational Keys 2/3 • Super key • A column, or a set of columns, that uniquely identifies a record within a table. • Candidate Key • A superkey that contains only the minimum number of attributes necessary for unique identification of each entity occurrence. • In each record, values of K uniquely identify that record (uniqueness). • No proper subset of K has the uniqueness property (irreducibility).

  18. Relational Keys 3/3 • Primary Key • Candidate key selected to identify records uniquely within table. • Alternate Keys • Candidate keys that are not selected to be primary key. • Foreign Key • Column, or set of columns, within one table that matches candidate key (primary key) of some (possibly same) table. • Secondary key • Key used strictly for data retrieval purposes. • Does not necessarily yield a unique number.

  19. Keys • Controlled redundancy (shared common attributes) makes the relational database work. • The primary key of one table appears again as the link (foreign key) in another table. • 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.

  20. 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 fullyfunctionally dependent on (A).

  21. Relational Database Keys

  22. Integrity Rules Revisited

  23. Example on Relational Keys

  24. Null Values • Can be taken to mean “unknown”, which means that the current value for a column at a certain record is unknown or not applicable. • Can represent • An unknown attribute value • A known, but missing, attribute value (no data entry). • A “not applicable” condition • It is not the same as zero or spaces, which are values. • Not permitted in primary key • Should be avoided in other attributes

  25. Relational Integrity Constraints 1/2 • In addition to that every column has an associated domain (domain constraints), there are two important relational integrity rules (constraints) which ensures that the data is accurate: Entity Integrity and Referential Integrity. • Entity Integrity • Is applied to the primary keys. • In a base table, no column of a primary key can be null. • If null values are allowed, then the key is not sufficient to provide unique identification of records.

  26. Relational Integrity Constraints 2/2 • Referential Integrity • Is applied to the foreign keys. • If FK exists in a table, either FK value must match a candidate key value of some record in its home table or FK value must be wholly null. • It’s not be possible to create a staff record with branch number B300, for example, unless there is already a record for a branch number B300 in the branch table. • However, we could create a new staff record with a null branch number. • Business Rules • It’s also possible for users to specify additional constraints that that the data must satisfy. • Rules that define or constrain some aspect of the organization.

  27. Example on Integrity Rules

  28. Relational Languages • Two main languages that have emerged for relational DBMS’s are: • SQL (Structured Query Language), standardized by ISO. • QBE (Query-by-Example), alternative graphical “point-and-click” way of querying database.

  29. Data Dictionary & System Catalog • Data dictionary • Used to provide detailed accounting of all tables found within the user/designer-created database • Contains (at least) all the attribute names and characteristics for each table in the system • Contains metadata—data about data • Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures

  30. A Sample Data Dictionary

  31. Data Dictionary & The System Catalog • System catalog • Contains metadata • Detailed system data dictionary that describes all objects within the database • Terms “system catalog” and “data dictionary” are often used interchangeably • Can be queried just like any user/designer-created table

  32. More formally…… • A domainD is a set of atomic values • local phone number – The set of 7-digit numbers • names – The set of names of persons • date of birth – Possible dates of birth for people • A relation schemaR(A1, A2, …, An) is a: • relation name (R) • list of attributes (A1, A2, …, An) • each attribute Ai is the name of a role played by some domain D in the relational schema R

  33. More formally (cont’d) • a relationr(R) is a subset of dom(A1) X dom(A2) X … X dom(An) • each element in a relation, called a tuple, is a collection of n values

  34. Student (name, address, phone number) Attribute Tuple

  35. Types of Constraints • Domain constraints • Key constraints • Integrity constraints • Entity Integrity Constraint • Referential Integrity Constraint • Semantic Integrity Constraint

  36. Domain Constraints • The value of each attribute, A, must be an atomic value from the domain of A • So, if an attribute is from the domain of a phone number, then the attribute must be a phone number.

  37. Key constraints • value of a key uniquely identifies a tuple in a relation • a superkeyK is subset of attributes of R such that: • no 2 tuples have same values for K • Every relation has at least one superkey; what is it?

  38. Keys (cont’d) • A key is a minimal superkey; a superkey from which we cannot remove any attributes and still be able to uniquely identify tuples in a relation • common keys – ID number, Social Insurance Number, etc.

  39. Keys (cont’d) • A relational schema may have more than one key • each key called a candidate key • one designated as the primary key

  40. Integrity Constraints • Integrity constraints are specified on a schema and hold for every instance of the schema • Entity integrity constraint • no primary key value can be null • Referential integrity constraint • if R1 refers to R2 then t1 r1(R1) must refer to an existing t2 r2(R2)

  41. Foreign Keys • a foreign keyin R is a set of attributes FK in R such that FK is a primary key of some other relation R’ • a foreign key is used to specify a referential integrity constraint • Example?

  42. Key examples Department (code, name, phone) Faculty (name, number, office, dept_code) FK dept_code  department (code) Course (name, number, dept_code) FK dept_code  department (code)

  43. 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. • 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]. • A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.

  44. Referential Integrity Constraint Statement of the constraint The value in the foreign key column (or columns) FK of the the referencing relation R1 can be either: (1) a value of an existing primary key value of the corresponding primary key PK in the referenced relation R2,, or.. (2) a null. In case (2), the FK in R1 should not be a part of its own primary key.

  45. Rules for Referential Integrity Constraints

  46. Specifying Referential Integrity Actions • If default referential integrity constraint is too strong, overriding the default referential integrity enforcement could be defined during database design • The policy will be programmed into triggers during implementation • Two referential integrity overrides • Cascading updates automatically change the value of the foreign key in all related child rows to the new value • Cascading deletions automatically delete all related child rows

  47. Enforcing Minimum Cardinality • If the minimum cardinality on the child is one, at least one child row must be connected to the parent • A required parent can be specified by making the foreign key value not null • A required child can be represented by creating update and delete referential integrity actions on the child and insert referential integrity actions on the parent • Such referential integrity actions must be declared during database design and trigger codes must be written during implementation

  48. Representing ID-Dependent Relationships • To represent ID-dependent relationships, primary key of the parent relation is added to the child relation • The new foreign key attribute becomes part of the child’s composite primary key • Referential integrity actions should be carefully determined • For cascading updates, data values are updated to keep child rows consistent with parent rows • If the entity represents multi-value attributes, cascading deletions are appropriate • Check user requirements when designing more complex situation

  49. Example: ID-Dependent Relationship

  50. Example: ID-Dependent Relationship

More Related