The Relational Model and Relational Database Constraints - PowerPoint PPT Presentation

the relational model and relational database constraints n.
Skip this Video
Loading SlideShow in 5 Seconds..
The Relational Model and Relational Database Constraints PowerPoint Presentation
Download Presentation
The Relational Model and Relational Database Constraints

play fullscreen
1 / 14
The Relational Model and Relational Database Constraints
Download Presentation
Download Presentation

The Relational Model and Relational Database Constraints

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. The Relational Model and Relational Database Constraints Ms. Hatoon Al-Sagri CCIS – IS Department

  2. Database Design Steps in building a database for an application: Real-world domain Conceptual Model (EER) DBMS data model Create Schema (DDL) Load data (DML)

  3. Relational Data Structure In the relational model, all data is logically structured within relations (tables). • Relation is a table with columns & rows. Holds information about entities. • Attribute is a named column of a relation. • Domain is the set of allowable values for one or more attributes. Every attribute in a relation is defined on a domain. • Tuple is a row of a relation. • Degree of a relation is the number of attributes it contains. • Cardinality of a relation is the number of tuples it contains. • Relational database is a collection of normalized relations with distinct relation names.

  4. Relational Data Structure Attributes STUDENT Relation Name StudentNo LName FName Initial DOB GPA 4170010 Al-Saleh Amal M. 04-06-78 3.91 4182000 Al-Ghanem Nora A. 4.20 02-12-79 Relation Tuples 4182034 Al-Fahad Cardinality 01-11-74 Laila A. 4.01 Saod 4188134 Amal F. 22-04-73 3.01 4189860 Rashed Rana I. 2.31 30-01-78 Al-Fahad 4192134 19-03-79 Rania M. 3.50 Degree

  5. Properties of Relations • The relation has a name that is distinct from all other relation names in the relational DB • Each cell of the relation contains exactly single value • Each attribute has a distinct name • The values of an attribute are all of the same domain • Each tuple is distinct. There are no duplicate tuples • The order of attributes has no significance • The order of tuples has no significance; theoretically.

  6. Relational Integrity Constraints Constraints are conditions that must hold on allvalid relation instances. There are four main types of constraints: • Domain constraints • Key constraints • Entity integrity constraints • Referential integrity constraints

  7. Relational Keys

  8. Key Constraints • Primary key of R:A set of attributes PK of R such that no two tuples in any valid relation instance will have the same value for PK. Example: The STUDENT relation schema: STUDENT( Ssn, Name, Dob, Age, Address) The attribute set {Ssn} is a key of STUDENT because no two student tuples can have the same value for Ssn.

  9. Entity Integrity Constraints • Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple. This is because primary key values are used to identify the individual tuples. PK  null for any tuple • Note: Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key.

  10. DB Relations • Relation schema is a named relation defined by a set of attributes If A1, A2, .., An are a set of attributes, then relation schema R is: R = (A1, A2, .., An) • Relational Database Schema is a set of relation schemas, each with a distinct name If R1, R2, .., Rn are a set of relation schemas, then relational schema R is: R= {R1, R2, .., Rn}

  11. Referential Integrity Constraints • 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.

  12. Referential integrity constraints displayed on the COMPANY relational database schema.

  13. Update Operations on Relations • INSERT a tuple. • DELETE a tuple. • MODIFY a tuple. - Integrity constraints should not be violated by the update operations. - Several update operations may have to be grouped together. - Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints.

  14. Update Operations on Relations • In case of integrity violation, several actions can be taken: • Cancel the operation that causes the violation (REJECT option) • Perform the operation but inform the user of the violation • Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) • Execute a user-specified error-correction routine