Download Understanding the Relational Data Model: Lecture 3 and more Study notes Mobile Computing in PDF only on Docsity!
DBDI/ Lecture 3
The Relational Data Model
Dr. Ala Al-Zobaidie
The slides are based on the textbook Database Systems by Thomas Connolly & Carolyn Begg 30/05/2007 Lecture 3 2
Lecture’s Objectives
- Terminology of relational model.
- How tables are used to represent data.
- Properties of database relations.
- How to identify candidate, primary, and foreign keys.
- Meaning of entity integrity and referential integrity.
- Purpose and advantages of views.
- Codd’s 12 Rules
30/05/2007 Lecture 3 3
Relational Model Terminology
- A relation is a table with columns and rows. - Only applies to logical structure of the database, not the physical structure.
- Attribute is a named column of a relation.
- Domain is the set of allowable values for one or more attributes.
30/05/2007 Lecture 3 4
Relational Model Terminology
- Tuple is a row of a relation.
- Degree is the number of attributes in a relation.
- Cardinality is the number of tuples in a relation.
- Relational Database is a collection of normalized relations with distinct relation names.
30/05/2007 Lecture 3 5
Instances of Branch and Staff (part)
Relations
30/05/2007 Lecture 3 6
Examples of Attribute Domains
30/05/2007 Lecture 3 7
Alternative Terminology for Relational
Model
30/05/2007 Lecture 3 8
Database Relations
- Relation schema
- Named relation defined by a set of attribute and domain name pairs.
- Relational database schema
- Set of relation schemas, each with a distinct name.
30/05/2007 Lecture 3 9
Properties of Relations
- Relation name is distinct from all other relation names in relational schema.
- Each cell of relation contains exactly one atomic (single) value.
- Each attribute has a distinct name.
- Values of an attribute are all from the same domain.
30/05/2007 Lecture 3 10
Properties of Relations
- Each tuple is distinct; there are no duplicate tuples.
- Order of attributes has no significance.
- Order of tuples has no significance, theoretically.
30/05/2007 Lecture 3 11
Relational Keys
- Superkey
- An attribute, or a set of attributes, that uniquely identifies a tuple within a relation.
- Candidate Key
- Superkey (K) such that no proper subset is a superkey within the relation.
- In each tuple of R, values of K uniquely identify that tuple (uniqueness).
- No proper subset of K has the uniqueness property (irreducibility).
30/05/2007 Lecture 3 12
Relational Keys
- Primary Key
- Candidate key selected to identify tuples uniquely within relation.
- Alternate Keys
- Candidate keys that are not selected to be primary key.
- Foreign Key
- Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation.
30/05/2007 Lecture 3 19
Updating Views
- All updates to a base relation should
be immediately reflected in all views that reference that base relation.
- If view is updated, underlying base
relation should reflect change.
30/05/2007 Lecture 3 20
Updating Views
- There are restrictions on types of modifications that can be made through views: - Updates are allowed if query involves a single base relation and contains a candidate key of base relation. - Updates are not allowed involving multiple base relations. - Updates are not allowed involving aggregation or grouping operations.
30/05/2007 Lecture 3 21
Updating Views
- Classes of views are defined as:
- theoretically not updateable
- theoretically updateable
- partially updateable.
30/05/2007 Lecture 3 22
Classifying a DBMS as a Relational System
- 12 rules (Codd )+ rule 0 (a foundational rule)
- Classified into 5 functional areas consist of
- Foundational rules
- Structural rules
- Integrity rules
- Data manipulation rules
- Data independence rules
- Provides a test to assess whether a system is a RDBMS.
- If these rules are not satisfied, the product should not be considered relational.
30/05/2007 Lecture 3 23
Codd’s 12 Rules / Foundational rules 0 ,1,2,3,4,5,6,7,8,9,10,11,
- Foundational rules (0 &12)
- Provides a test to assess whether a system is a relational DBMS.
- If these rules are not satisfied, the product should not be considered relational.
- Rule 0 – Foundational rule
30/05/2007 Lecture 3 24
Codd’s 12 Rules / Foundational rules 0 ,1,2,3,4,5,6,7,8,9,10,11, 12
- Rule 12 – Non-subversion rule
- Maintain Integrity rules should not be compromised - due to limitation of language (e.g. record oriented), or - security violation
30/05/2007 Lecture 3 25
Codd’s 12 Rules / Structural rules 0 , 1 ,2,3,4,5, 6 ,7,8,9,10,11, 12
- Structural rules (Rule 1 & Rule 6)
- Fundamental structural concept is the relation.
- RDBMS must support several structural features, including relations, domains, primary, and foreign keys.
- There should be a primary key for each relation in the database.
• Rule 1 – Information representation
• Rule 6 – View updating
30/05/2007 Lecture 3 26
Codd’s 12 Rules / Integrity rules 0 , 1 ,2, 3 ,4,5, 6 ,7,8,9, 10 ,11, 12
- Integrity rules (Rule 3 and Rule 10)
- Support of data integrity is an important criterion when assessing the suitability of a product.
- The more integrity constraints maintained by the DBMS product, rather than by application programs, the better the guarantee of data quality.
• Rule 3 – Systematic treatment of
null values
• Rule 10 – Integrity independence
30/05/2007 Lecture 3 27
Codd’s 12 Rules / Data manipulation rules 0 , 1 , 2 , 3 ,4,5, 6 ,7,8,9, 10 ,11, 12
- Data manipulation rules (Rule 2, Rule 4, Rule 5, and Rule 7) - An ideal relational DBMS should support 18 manipulation features. - These features define the completeness of the query language. - Adherence to rules insulates the user and application programs from the physical and logical mechanisms that implement the data management capabilities.
30/05/2007 Lecture 3 28
Codd’s 12 Rules / Data manipulation rules 0 , 1 , 2 , 3 , 4 , 5 , 6 , 7 ,8,9, 10 ,11, 12
• Rule 2 –Guaranteed access
• Rule 4 –Dynamic on-line catalogue
based on the relational model
• Rule 5 –Comprehensive data
sublanguage
• Rule 7 –High-level High-level
insert, update, delete
30/05/2007 Lecture 3 29
Codd’s 12 Rules / Data independence rules 0,1,2,3,4,5,6,7,8,9, 10 ,11, 12
- Data independence rules (Rule 8, Rule 9, and Rule 11) - Specify the independence of data from the applications that use the data. - Adherence to these rules ensures that both users and developers are protected from having to change the applications following low-level reorganizations of the database.
- Rule 8 – Physical data independence
- Rule 9 – Logical data independence
- Rule 11 –Distribution independence
30/05/2007 Lecture 3 30
Summary
- Relational Model and its features
- Classifying a Relation DBMS
- Represent data.
- Properties of database relations.
- Candidate, primary, and foreign keys.
- Entity integrity and referential integrity.
- Views.
- Codd’s 12 Rules
