CSE-241: Relational Data Model and Database Constraints

AthleticIrrational avatar
AthleticIrrational
·
·
Download

Start Quiz

Study Flashcards

34 Questions

In a relation schema R, what is assumed about the attributes and values within each tuple?

They are ordered

What type of value is used to represent unknown or inapplicable values in a tuple?

Null value

How are component values of a tuple referred to?

t[Ai] = vi

What is a key of a relation schema R?

A minimal super key

What is the purpose of relational integrity constraints?

To ensure data consistency

How many main types of relational integrity constraints are there?

3

In a relational database schema, what type of arc is used to display a referential integrity constraint?

Directed arc

What are the two possible values that a foreign key column can have in a relational database?

Only null or an existing primary key value

In the CAR relation, what is the primary key?

Serial_no

In the SALE relation, what is the foreign key?

Serial_no

What type of constraint is based on application semantics and cannot be expressed by the model?

Semantic Integrity Constraint

What is an example of a Semantic Integrity Constraint?

A maximum number of values

What is the difference between a super key and a key?

A super key is a set of attributes that uniquely identifies a tuple, but it may contain additional attributes

What is the primary key chosen from?

From a set of candidate keys

What is the purpose of the primary key attribute?

To identify the individual tuples in a relation

What is the Entity Integrity constraint?

A constraint that ensures primary key attributes cannot have null values

What is the purpose of the foreign key attribute?

To reference the primary key attributes of the referenced relation

What is the Referential Integrity constraint used for?

To specify a relationship among tuples in two relations

What is the purpose of integrity constraints in a database?

To ensure that update operations do not violate the database's consistency

What happens when an update operation violates an integrity constraint?

The operation is cancelled and the user is informed

What is the purpose of a candidate key in a relational schema?

To specify a unique identifier for a table

What is the result of mapping an ERD into a relational schema?

A set of tables with foreign key relationships

What type of relationship is represented by a foreign key?

One-to-many

What is the purpose of an assertion in SQL-99?

To specify a constraint on a table

What is the primary role of a key in a relational database?

To determine the values of other attributes

What is the term for the concept where attribute B's value is determined by attribute A's value?

Functional dependence

What is the characteristic of a relational database that allows tables to be linked together?

Controlled redundancy

What is the term for an attribute that is part of a key?

Key attribute

What is the purpose of a foreign key in a relational database?

To refer to an existing valid tuple in another relation

What is the term for a key that uniquely identifies each row in a table?

Primary key

What is the term for the concept where the ordering of tuples in a relation is not considered?

Ordering independence

What is the purpose of a relational diagram?

To represent entities, attributes, and relationships

What is the term for an attribute that can represent an unknown attribute value, a known but missing attribute value, or a 'not applicable' condition?

Nulls

What is the term for a key that is composed of more than one attribute?

Composite key

Study Notes

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.

Make Your Own Quizzes and Flashcards

Convert your notes into interactive study material.

Get started for free

More Quizzes Like This

Oracle 12c User Management Quiz
10 questions
Relational Databases and SQL Quiz
10 questions
Use Quizgecko on...
Browser
Browser