Database) Chapter2. Relational Model

Chapter2. Relational Model

-attribute (column) / tuples (rows)

Attribute Type

-> 각각 attribute에 허락된 value의 set들을 domain이라 부름

-> Attribute의 값들은 atomic하다 -> multivalued structure가 아님

-> null이라는 특별한 값은 모든 domain의 멤버

a1, a2, ... , an are attributes -> attribute의 domain을 각각

d1, d2, ... , dn이라 할 때 -> relation r의 tuple은 (A1, A2, ... , An) 단, Ai는 Di의 원소

-Keys

- Key는 Relation의 attribute의 부분집합이다. 만약 K의 value가 unique tuple을 identify 할 수 있는 조건이라면 K를 R의 SuperKey라 한다.

- 만약 그 SuperKey가 minimal하다면 (superkey에서의 내부 원소 개수가 최소라면) 이를 Candidate Key라한다.

- Candidate Key중에 선택받은 Key를 Primary key라 지정하고 이를 Table을 determine하는 데 이용한다.

-Foreign Key: 하나의 Relation에서 나타나는 value중에 다른 relation에도 나타나는 것.

-> Foreign Key는 다른 table의 tuple (row)를 unique하게 identify할 수 있는 어느 table의 field를 의미한다. -> 다른 table의 primary key or candidate key

-Referencing relation – the table containing the foreign key

-Referenced relation – the table containing the candidate key

Relational Algebra (a set of operations on relations)

selection: return rows of the input relation that satisfy the predicate

시그마와 같은 기호로 Relational Algebra를 표현하며, select * from tables where predicate와 같은 SQL로 표현됨.

projection: Return attributes from all rows of the input relation. Remove duplicate tuples from the output.

Selection할 때 특정 attribute만 추출한다는 생각과 비슷함 -> select A1, A2 from table

Natural Join: Output pairs of rows from two input relations that have the same value on all attributes that have the same name

-> 공통 attribute중에서 같은 value를 갖는 것끼리 묶음 select * from T1 natural join T2와 같은 식으로 표현됨.

Cartesian Product: Output all pairs of rows from the two input relations.

-> 모든 tuple들을 모든 경우의 수로 묶음 -> select * from T1, T2

Union: Output the union of tuples from the two input realations.

Select A1 from T1 where A1 > 50000

union

Select A1 from T1 where A1 < 30000

과 같은 식으로 SQL를 Union 시킬 수 있음.

​

많은 예제)

​

2.6 7 8 9 12

select *
from takes natural join student
where year >= 2009

select *
from (select *
      from takes
      where year >= 2009) natural join student

select ID, name, course_id
from student natural join takes

select distinct names
from employees
where city = 'Miami'

----------------------------------------------------------------
employee (person_name, street, city)
works (person_name, company_name, salary)
company(company_name, city)
branch(branch_name, branch_city, assets)
customer(customer_name, customer_street, customer_city)
loan(loan_number, branch_name, amount)
borrower(customer_name, loan_number)
account(account_number, branch_name, balance)
depositor(customer_name, account_number)

-----------------------------------------------------------------

select names
from branch
where branch_city = 'Chicago';

select customer_name
from borrower natural join loat
where branch_name = 'Downtown'

select names
from employees
where salary > 100000;

select names
from employees
where salary > 100000 and city = 'Miami'