02_Relational Model

March, 2024

1) RELATIONAL DATABASE

Attribute Types

• Each attribute of a relation has a name
• The set of allowed values for each attribute is called the (attribute) domain of the attribute
• Attribute values are (normally) required to be atomic, that is, indivisible
  • e.g., multivalued attribute values are not atomic
  • e.g., composite attribute values are not atomic
• The special value null is a member of every domain
• The null value causes complications in the definition of many operations

Relation Schema and Instance

Relation Instance

Relations are Unordered 

Database 

Keys

2) ENTITY-RELATIONSHIP DIAGRAM

• ER modeling의 결과
• ERD는 표준이 없다 !

Determining Keys from E-R Sets

- weak의 primary key set은 weak에 있는 것만으로 primary 설정이 가능하다? (X)
- relationship set은 relationship cardinality(계수)를 가지고 결정할 수 있다
e.g., 1:1, 1:다, 다:다,,
relationship cardinality가 1:1, 1:다, 다:다인지를 알아야 결정할 수 있다

3) RELATIONAL QUERY LANGUAGE

Relational Query Languages

• Query Language
• Categories of languages
• “Pure” (relational query) languages
  • Relational Algebra: 언어지만 operator와 operand가 있다
  • *** relation끼리의 연산 결과는 relation이다 !
• Pure languages from underlying basis of query languages that people use

SQL & Relational Algebra

• SQL: Non-procedural Query Language
  • specify “what” information to get, not “how”
• From SQL to Relational Algebra: Basic steps in query processing
  • parsing & translation (from SQL to Relational Algebra)
  • Optimization
  • Evaluation

• Relational Algebra
  • procedural language
  • operands: relations
  • operators: basic(primitive) operators + additional operations
  • basic operators: select, project, union, set difference, cartesian product, rename 
    • The operator takes one or two relations as inputs, and give a new relation as a result

Basic operators of Relational Algebra

Select

Project

• projection의 결과에는 중복이 제거된다
• cf. sql에서는 project 해도 중복이 제거되지 않는다 (애초에 relation은 tuple의 집합이므로 중복이 없다)

Cartesian Product

Union

• union 하기 위해서는 schema가 같아야 한다 (compatible)

Set Difference

• 두 relation의 schema 같아야 한다

Additional operations of Relational Algebra

Set Intersection (교집합)

Natural Join