🗊Презентация Relational Data Structure

Lecture 5. Relational Data Structure National Aviation University Computer Science Faculty Department of Software Engineering

CONTENT Relation in mathematics Relation definition Domains, attributes, schemas and instances of relations in RDM Relations and tables Keys of relations

Nonformal introduction to relations Form of representation: As a table By using a condition

Relation definition

Additional terms

Representation of binary relations

Basic operations

Property of binary relations

Examples of binary relations

Schema of a relation

Property of attributes and schemas Properties of the relation attributes: Each attribute of a relation has a name. The set of allowed values for each attribute is called the domain of the attribute. Different attributes may have the same domain. Attribute values are required to be atomic, that is, indivisible.

Relation instance Properties of a relation instance: Order of attributes is arbitrary, but it is defined by a relation schema. Order of tuples is arbitrary (tuples may be located in an arbitrary order) Tuples must be unique within the instance

Relational data structure The relational schema and its instance have the following properties: Name of the relations in relational schema must be unique

Relations and tables

Term correspondence

Keys

Simple and compound keys

Redundant and minimal keys

Primary key Example. Relation: STUDENT (ID-No, Name, Pasp-ser, Pasp-No, Course) has two minimal (that is why candidate) keys: ID-No Pasp-ser, Pasp-No

Properties of a primary key Main properties (integrity constraints): Primary key values must never be duplicated. That is they are unique within a relation. But there may be duplicates in parts of compound primary key. Primary key cannot have NULL values. Additional properties: Every relation must have one and only one primary key. Primary key do not influence attribute order. Primary keys do not influence tuple order.

Example of a primary key

Foreign key

Example of a foreign key

Property of foreign key Main property (integrity constraint): Value of a foreign key cannot reference to absent values of the primary key of the parent relation. It is so called referential integrity constraint of the foreign key. Additional properties: Foreign key can contain duplicate values. Foreign key can contain NULL values.

Supporting referential constraint Manipulating by tuples of a child relation: When a tuple is added or updated referential constraint is tested and if it is violated the corresponding action (adding/updating) is rejected. When a tuple is deleted referential constraint cannot be violated. Manipulating by tuples of a parent relation : When a tuple is added referential constraint cannot be violated. When deleting of updating: Tuple deleting/updating is not done if there are references from child relation, that violate referential integrity constraint. Deleting/updating tuple of parent relation causes deleting/updating tuples of a child relation that references the tuple of the parent relation. Deleting/updating tuple of parent relation causes setting NULL values to corresponding tuples of a child relation . Deleting/updating tuple of parent relation causes setting DEFAULT values to corresponding tuples of a child relation.

Supporting referential integrity constraints in SQL

Supporting referential constraint – RESTRICT

Supporting referential constraint – CASCADE

Supporting referential constraint – SET NULL

Supporting referential constraint – SET DEFAULT

Recursive foreign key

Cross-reference foreign keys

Foreign key as a part of primary key