1 / 14

The Relational Model and Relational Database Constraints

The Relational Model and Relational Database Constraints. Ms. Hatoon Al-Sagri CCIS – IS Department. 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).

ciel
Download Presentation

The Relational Model and Relational Database Constraints

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

More Related