Relational Data Model: Relation

Relational Data Model • The relational data model represents data in the form of tables where the relation is • “a named two-dimensional table of data”. The relational data model can be defined as • “a model that represent data as collection of tables in which all data relationships are represented by common values in related tables” • Relational data model consists of the following three components PRESENTED BY S. HAYAT

1

Relational Data Model • Data Structure: Data are organized in the form of tables with rows and columns. • Data manipulation: powerful operation (using SQL) are used to maintain data stored in the relation. • Data integrity: facilities are included to specify business rules that maintain the integrity of data when they are manipulated.

PRESENTED BY S. HAYAT

2

Relational Data Model • Environment of relational data model: the following terminology is used in relational data model environment • Relation: “a named two dimensional table of data”. OR “relation is a table with columns and rows”. • Attribute: an attribute is a named column of a relation” • Domain: “ a domain is a set of allowable values for one or more attributes” • Tuple: “A tuple is a row of a relation”. • Degree: the degree of a relation is the number of attributes it contains. PRESENTED BY S. HAYAT

3

Relational Data Model • Cardinality: the cardinality of a relation is the number of tuples it contains. • Alternative terminology: Alternative terminology for relational model terms Formal term

Alternative 1

Alternative 2

Relation

Table

File

Tuple

Row

Record

Attribute

Column

Field

PRESENTED BY S. HAYAT

4

Relational Data Model • Properties of a relation: A relation has the following properties – The relation has a name that is distinct from all other relation names in the relational schema. – Each cell of the relation contains exactly one atomic(single) value. – Each attribute has a distinct name. – The values of an attribute are all from 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. PRESENTED BY S. HAYAT

5

Relational Data Model • • • • • •

Relational keys. Superkey Primary key Composite key Foreign key ( discussed in previous week) Integrity constraints: the relational data model includes several types of constraints or business rules whose purpose is to facilitate maintaining the accuracy and integrity of data in the database. The following are the major types of integrity constraints

PRESENTED BY S. HAYAT

6

Relational Data Model • Domain Constraints: All of the values that appear in a column of a relation must be taken from the same domain. A domain is a set of values that may be assigned to an attribute. • Entity Integrity: The entity integrity rule is designed to ensure that every relation has a primary key, and that the data values for that primary key are valid. In particular, it guarantees that every primary key attribute is non-null. • Null: a value that may be assigned to an attribute when no other value applies or when the applicable value is unknown. PRESENTED BY S. HAYAT

7

Relational Data Model • Referential Integrity – rule that states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side. (Or the foreign key can be null) – For example: Delete Rules • Restrict – don’t allow delete of “parent” side if related rows exist in “dependent” side • Cascade – automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted • Set-to-Null – set the foreign key in the dependent side to null if deleting from the parent side  not allowed for weak entities

PRESENTED BY S. HAYAT

8

Relational Data Model Referential integrity constraints (Pine Valley Furniture)

Referential integrity constraints are drawn via arrows from dependent to parent table

PRESENTED BY S. HAYAT

9

Relational Data Model • Enterprise constraints: additional rules specified by the users or database administrators of a database. • View: the dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessarily exist in the database but can be produced upon request by a particular user, at the time of request. • Base relation: a named relation corresponding to an entity in the conceptual schema whose tuples are physically stored in the database. PRESENTED BY S. HAYAT

10