CSE-241: Relational Data Model and Database Constraints

Relational Data Model

• A relation schema R is defined as R(A1, A2,..., An), where A1, A2,..., An are attributes.
• The values in a tuple are considered atomic (indivisible) and a special null value is used to represent unknown or inapplicable values.
• The component values of a tuple t can be referred to as t[Ai] = vi, where Ai is an attribute and vi is the value of Ai for tuple t.

Relational Integrity Constraints

• Constraints are conditions that must hold on all valid relation instances.
• There are three main types of constraints: key constraints, entity integrity constraints, and referential integrity constraints.

Key Constraints

• A super key of R is a set of attributes SK of R such that no two tuples in any valid relation instance r(R) will have the same value for SK.
• A key of R is a "minimal" super key, meaning that removal of any attribute from K results in a set of attributes that is not a super key.
• Example: The CAR relation schema has two keys: Key1 = {State, Reg#} and Key2 = {SerialNo}, which are also super keys.

Entity Integrity Constraints

• The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R).
• This ensures that primary key values are used to identify the individual tuples.

Referential Integrity Constraints

• A constraint involving 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].

Update Operations

• Update operations on relations include INSERT, DELETE, and MODIFY.
• Integrity constraints should not be violated by the update operations.
• Several update operations may have to be grouped together, and updates may propagate to cause other updates automatically to maintain integrity constraints.

Mapping ERD into Relational Schema

• Strong entity type relation is mapped to a relation (table) in the relational schema.
• Weak entity type relation is mapped to a relation with a foreign key (FK) in the relational schema.
• 1:1 or 1:N relationship type is mapped to a foreign key in the relational schema.
• M:N relationship type is mapped to a relation with composite primary key and two foreign keys in the relational schema.
• Composite attribute is mapped to a set of simple component attributes in the relational schema.
• Multivalued attribute is mapped to a relation with composite primary key and foreign key in the relational schema.

Relational Model

• The relational model represents the logical view of the data.
• Developed by E.F. Codd (IBM) in 1970, and the first commercial system was introduced in 1981-82.
• The relational model is composed of tables (relations) and represents a matrix of row and column intersections.
• Each row in a relation is called a tuple, and the model provides structural and data independence.

Keys

• A key is a set of attributes that uniquely identifies each tuple in a relation.
• A super key is a set of attributes that uniquely identifies each tuple in a relation.
• A candidate key is a super key without unnecessary attributes.
• A composite key is a key composed of more than one attribute.
• A foreign key is an attribute whose values match primary key values in the related table.
• Referential integrity ensures that a foreign key contains a value that refers to an existing valid tuple in another relation.

This quiz covers the relational data model and database constraints, including the logical view of data. It's based on Chapter 4 of the Database Systems course at Menoufia University's Faculty of Electronic Engineering.