Create Presentation
Download Presentation

Download Presentation

The Relational Model and Relational Database Constraints

Download Presentation
## The Relational Model and Relational Database Constraints

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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)**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.**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**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.**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**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.**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.**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}**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.**Referential integrity constraints displayed on the COMPANY**relational database schema.**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.**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