3. The Relational Data Model

Converting ER to DBMS Tables

Problem: This entity relationship does not have 1-1 direct mapping. So we can’t convert it directly

💡

예를 들어서 relationship같은 경우 어떻게 처리하고 저장할 것인지에 대해서는 ER model이 답하지 않는다. 따라서 DBMS table과 ER이 direct mapping되지 않는다는 것이다.

그래서 Relational Data model 이 등장하게 된 것이다.

💡

Relational Model은 DBMS와 direct mapping할 수 있다.

A Relation

주의할 점은 Relationship와 relation은 구분해야한다는 것이다.

• Relationship: Entity relationship model에서 나온 concept

• relation: Entity / DBMS table

• Attributes name: DBMS Column

• Tuples: DBMS rows

💡

즉 relation은 set of tuple로 이해할 수 있다.

• Note that it is an unordered rows

  → Set is an unordered list

• Key of a Relation: an attribute that uniquely identifies that particular row in the table

  → Sometimes we create keys artificially (e.g. if no key exists)

  💡

  예를 들어서 composite해서 key를 만들어낼 수도 있다.

• Primary key : A chosen key

  → If there are multiple keys, choose one

💡

단 Relational model에서는 primary key에만 underline을 표시한다.

Characteristics of Relations

• Values in a tuple are atomic (indivisible/single value)

  → Multi-value is not allowed

  💡

  하지만, 1개의 value면 상관이 없다. 예를 들어 string이면 늘려버리면 된다.

• Each value in a tuple must be from the domain of the attribute

  💡

  예를 들어 int에 대해서는 다룰 수 있는 범위가 제한되어있는 것으로 이해하면 된다.

• Value can be null

Foreign Key

A relation uses another relation’s primary key as an attribute

Note that foreign keys can have duplicated values.

Formal Definitions

• The Schema: description of a Relation
  • R(A1, A2, … , An)
  • R is the name of the relation
  • A1, A2, …, An are the attributes of the relation
  • Example : STUDENT(Name, SSN, Home_phone, …, GPA)

• Domain: each attribute has a set of values/data type

  → Example: integer, data, varchar, etc

• tuple: ordered set of values of attributes
  • written inside <…>
  • Example: <”Benjamin Bayer”, “305-61-2435”, “373-1616”, 2918 Bluebonnet Lane, …>

  💡

  ordered tuple이기 때문에 순서를 바꿀 수 없다. 즉 order of attribute values matter

• r(R)
  • Table / set of tuples/rows
    • r(R) = {t1, t2, …, tn} where each ti is an n-tuple
    • Here, ti = <v1, v2, …, vn> where each vj element of dom(Aj)
  • A specific state/value/population of relation R

Terminology Summary

Relational Integrity Constraints

Basic operations for changing the database

1. INSERT : a new tuple

2. DELETE : an existing tuple

3. MODIFY : an existing tuple

해당 operation에 의해서 database의 state가 변화가 된다는 점을 기억해야 한다.

예를 들어 employee의 address가 달라지면 supervisor가 달라지게 된다.

Basic Idea : Constraints are conditions that must hold on all valid states (after every INSERT, UPDATE, DELETE)

• 3 types of constraints
  • Key constraints
  • Entity integrity constraints
  • Referential integrity (Foreign Key) constraints

Key Constraints

If a relation has several candidate keys, one is chosen arbitrarily as a primary key

→ The primary-key value is used to uniquely identify each tuple in a relation

예를 들어 Texas ABC-739를 또 추가하려는 query를 날린 경우 해당 query를 reject하는 것이다.

• General rule : Choose as primary key the smallest of the candidate keys (in terms of size)

  💡

  컴퓨터 architecture 관점으로 보면 더 작은 data size에 해당하는 것을 데려오는 것이 더 유리하다. 따라서 일반적으로 data size가 클수록 더 inefficient하기 때문에 더 적은 data size에 해당하는 것을 primary key로 설정하는 것이다.

Entity Integrity Constraints

The primary key attributes can’t be null

1. Primary key values are used to identify the individual tuples

2. If PK has several attributes (i.e. composite PK), null is not allowed in any of these attributes

Referential Integrity Constraints

Referential Integrity/Foreign key constraints: the value in the FK column can be either

1. a value of an existing PK value from another relation

2. a null

   → 아직 대응되지 않은 것으로 이해하면 된다.

Other types of Constraints

• Constraints can be defined using SQL-99

• CREATE TABLE statement in SQL allows

  

  → Defining keys/candidate-keys, restrict NULL values, FKs, etc

  → varchar : 크기를 제한시켜두지 않은 것

  💡

  unique (Name, Surname)을 적어둔 것은 unique임을 명시하기 위함이다. 중복된 값을 추가하는 query가 온 경우 reject할 수 있게 된다.

INSERT on Relations

INSERT can violate:

• Domain constraint : outside the domain

• Key constraint : if value already exists

• Referential integrity : Foreign key not present

• Entity integrity : Primary key value is null

DELETE on Relations

DELETE may violate referential integrity on FK : when the PK value is deleted from the original table : there are 3 Solutions:

1. RESTRICT: reject the deletion

2. CASCADE: propagate into the foreign keys

3. SET NULL: set the foreign key keys value to NULL

UPDATE on Relations

UPDATE may violate the following Constraints:

• Primary key : Similar to DELETE + INSERT

• Foreign key : Similar to DELETE

• An ordinary attribute (neither PK nor FK):
  • can violate domain or NULL value constraints

How the Data is Stored in a Table?