Seunghan Lee

Seunghan Lee

Deep Learning, Data Science, Statistics

( 출처 : 연세대학교 데이터베이스 시스템 수업 (CSI6541) 강의자료 )

Q1.

Student (sid, ssn, sname, did) 에서 superkey 를 모두 나열하시오. (15 points.)

  • sid: student identifier
  • ssn: social security number (주민번호에 해당)
  • sname: student name
  • did: department identifier


A1.

개념 : superkey

  • 정의 : if K is sufficient to identify a unique tuple of each possible relation
  • 예시 : K1 = (sid, sname), K2 = (sname, dname)


정답 :

  • 원소 1개 :

    • (sid)
    • (ssn)

  • 원소 2개 :

    • (sid) 관련

      • (sid, ssn), (sid, sname), (sid, did)

    • (ssn) 관련

      • (ssn, sid), (ssn, sname), (ssn, did)

        ( 순서 무관하므로, (ssn, sid)는 생략 가능 )

  • 원소 3개 :

    • (sid) 관련
      • (sid, ssn, sname), (sid, ssn, did), (sid, sname, did)
    • (ssn) 관련
      • (ssn, sid, sname), (ssn, sid, did), (ssn, sname, did)

  • (원소 4개)

    • (sid, ssn, sname, did)


Q2.

Consider the following relational database schema

  • Suppliers={sid,sname,address} with key {sid}
  • Parts={pid, pname, color} with key {pid}
  • Catalog={sid,pid,cost} with key {sid,pid} and foreign keys
    • [sid] ⊆ Suppliers[sid]
    • [pid] ⊆ Parts[pid]

Define this database schema in SQL, as close as possible.


A2.

CREATE TABLE Suppliers
  sid number(10) PRIMARY KEY,
  sname varchar(40) NOT NULL,
  address varchar(60) NOT NULL);

CREATE TABLE Parts
  pid number(15) PRIMARY KEY,
  pname varchar(30) NOT NULL,
  color varchar(15) NOT NULL);

CREATE TABLE Catalog(
  sid number(10),
  pid number(15),
  cost real NOT NULL,
  PRIMARY KEY (sid, pid),
  FOREIGN KEY sid REFERENCES Suppliers,
  FOREIGN KEY pid REFERENCES Parts);


Q3.

다음 schema 를 이용해 각각의 query 를 SQL 문으로 표현하시오. (20 points)

Suppliers(sid, sname, address)
Parts(pid, pname, color)
Catalog(sid, pid, cost)

  • A. Find the pnames of parts for which there is some supplier.

  • B. Find the snames of suppliers who supply every part.

  • C. Find the snames of suppliers who supply every red part
  • D. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
  • E. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).
  • F. For each part, find the sname of the supplier who charges the most for that part.
  • G. Find the sids of suppliers who supply only red parts
  • H. Find the sids of suppliers who supply a red part and a green part
  • I. Find the sids of suppliers who supply a red part or a green part.
  • J. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.
  • K. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.


A3.

https://canvas.auckland.ac.nz/files/1905124/download?download_frd=1


A. Find the pnames of parts for which there is some supplier.

SELECT DISTINCT P.pname
FROM Parts P, Catalog C
WHERE P.pid = C.pid;


B. Find the snames of suppliers who supply every part.

SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS ((SELECT P.pid
                   FROM Parts P)
                  EXCEPT
                  (SELECT C.pid
                   FROM Catalog C
                   WHERE C.sid = S.sid ));


C. Find the snames of suppliers who supply every red part

SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS ((SELECT P.pid
                   FROM Parts P
                   WHERE P.color = 'red' )
                  EXCEPT
                  (SELECT C.pid
                   FROM Catalog C
                   WHERE C.sid = S.sid ));


D. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

SELECT P.PNAME
FROM Parts P, Catalog C, Suppliers S
WHERE P.pid = C.pid 
	AND C.sid = S.sid
	AND S.sname = 'Acme Widget Suppliers'
	AND NOT EXISTS (SELECT *
                  FROM Catalog C1, Supppliers S1
                  WHERE P.pid = C1.pid 
                  	AND C1.sid = S1.sid 
                  	AND S1.sname <> 'Acme Widget Suppliers');


E. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).

SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > (SELECT AVG(C1.cost)
                FROM Catalog C1
                WHERE C1.pid = C.pid) ;


F. For each part, find the sname of the supplier who charges the most for that part.

SELECT P.pid, S.sname
FROM Parts P, Suppliers S, Catalog C
WHERE C.pid = P.pid 
	AND C.sid = S.sid 
	AND C.cost = (SELECT MAX(C1.cost)
                FROM Catalog C1
                WHERE C1.pid = P.pid);


G. Find the sids of suppliers who supply only red parts

SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT *
                  FROM Parts P
                  WHERE P.pid = C.pid 
                  	AND P.color <> 'red');


H. Find the sids of suppliers who supply a red part and a green part

(SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid 
	AND P.color = 'red')
	
INTERSECT

(SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid 
	AND P1.color = 'green');


I. Find the sids of suppliers who supply a red part or a green part.

(SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid 
 	AND P.color = 'red')
 	
UNION

(SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid 
 	AND P1.color = 'green');


J. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.

SELECT S.sname, COUNT(*) as PartCount
FROM Suppliers S, Parts P, Catalog C
WHERE C.pid = P.pid 
	AND C.sid = S.sid
GROUP BY S.sname, S.sid
HAVING EVERY (P.color = 'green');


K. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.

SELECT S.sname, MAX(C.cost) as MaxCost
FROM Suppliers S, Parts P, Catalog C
WHERE C.pid = P.pid 
	AND C.sid = S.sid
GROUP BY S.sname, S.sid
HAVING ANY (P.color = 'green') 
	AND ANY (P.color = 'red');


Q4.

Foreign key 정의 시 “on delete cascade” 와 “on delete set null” 의 의미를 example 사용하여 설명하시오. (15 points)


A4.

  1. ON DELETE(UPDATE) SET NULL
    • 부모테이블에서 primary 값이 삭제(수정)될 경우, 하위테이블의 reference값은 존재할 수 없습니다.
    • 1-1) 옵션이 없을 경우는 에러가 발생하고….
    • 1-2) 옵션 SET NULL 로 정의되면 …
      • 하위테이블의 reference값이 NULL 값으로 변경되면서 참조무결성을 유지
  2. ON UPDATE CASCADE
    • 부모테이블에서 primary 값이 수정될 경우, 옵션 CASCADE 로 정의되면 하위테이블의 reference값은 변경된 상위테이블의 수정된 값을 가지면서 참조무결성을 유지
  3. ON DELETE CASCADE
    • 부모테이블에서 primary 값이 삭제될 경우, 하위테이블의 reference값은 삭제되면서 참조무결성을 유지


ALTER TABLE 자식테이블명
    ADD FOREIGN KEY (기준컬럼명)
               REFERENCES 부모테이블명 (기준컬럼명)
               ON DELETE CASCADE;


Q5.

Variable-length records 가 발생하는 이유를 설명하고 slotted page structure 에 대해 기술하시오. (15점)

A5.

가변 길이 레코드는 데이터베이스 시스템에서 몇 가지 경우에 가끔 사용된다

  • 한 파일에 여러 타입의 레코드를 저장할 때

  • 레코드의 타입이 한 개 이상의 필드에서 가변 길이를 허용할 때


가변 길이 레코드는 Slotted Page Structure를 가진다

  • 파일은 페이지의 집합이다
  • Slotted Page의 헤더는 다음의 정보를 포함한다
    • 레코드 엔트리의 개수
    • 블록의 빈 공간의 마지막의 위치
  • 각 레코드의 위치와 크기
  • 페이지 안에서 레코드의 위치가 옮겨질 수 있다. 이렇게 하면 각 레코드 사이에 빈 공간이 없이 유지할 수 있다. 이를 위해 헤더의 레코드에 대한 진입점이 업데이트 되어야 한다
  • 포인터는 레코드를 직접적으로 가리키면 안 된다 대신 레코드는 헤더에 저장된 레코드에 대한 진입점을 가리켜야 한다


Q6.

Primary index 와 secondary index 의 차이점을 기술하고,

secondary index 에 bucket structure 가 필요한 이유를 설명하시오. (15 points)


A6.

**primary index **

  • index whose search key also defines the sequential order of the file
  • also called clustering index


secondary index

  • Index whose search key specifies an order different from the sequential order of the file
  • also called non-clustering index


Q7.

Write the definitions of the following terms:

  • (a) 1NF
  • (b) 2NF
  • (c) 3NF


A7.

제 1 정규형 (1NF)

  • relation의 모든 속성이 더는 분해 되지 않는 원자값 (atomic value) 만을 가질 경우!
  • 이를 만족해야, 관계 DB의 relation이 될 자격이 있음


제 2 정규형 (2NF)

  • 릴레이션이 제 1정규형에 속하고,

    (기본키가 아닌) 모든 속성이 기본키에 완전 함수 종속되는 경우!

  • 제 1정규형이 제 2정규형을 만족하게 하려면?

    • 부분 함수 종속을 제거 !!


제 3 정규형 (3NF)

  • relation이 제 2 정규형에 속하고,

    (기본키가 아닌) 모든 속성이 기본키에 이행적 함수 종속이 되지 않는 경우

  • 제 2정규형 -> 제 3정규형 위해…

    • 모든 속성이 기본키에 “이행적 함수 종속이 되지 않도록” 분해 해야!


Q8.

Determine whether the below relation is in the 2NF. If not, explain the reasons.

customer_id customer_class discount_rate
apple gold 10%
banana vip 20%
carrot gold 10%
orange silver 5%

customer_id → customer_class

customer_id → discount_rate

customer_class → discount_rate


A8.

정답 : 만족한다!


풀이 : 위의 테이블은…

  • 제 1정규형 (O)
  • 제 2정규형 (O)
  • 제 3정규형 (X)

근거 : 릴레이션이 제 1정규형에 속하고, (기본키가 아닌) 모든 속성이 기본키에 완전 함수 종속되므로!


참고 :

  • (1) 완전 함수 종속 (Full FD)

    • Y가 “X 전체”에 함수적으로 종속되어 있지만,

      “X의 일부분”에는 종속되어 있지는 않음

    • 일반적으로, 함수종속 = 완전함수종속

    • ex) 당첨여부 : {고객 아이디, 이벤트 번호}에 FFD

  • (2) 부분 함수 종속 (Partial FD)

    • Y가 “X 전체”뿐만 아니라, “X의 일부분”에도 함수적으로 종속
    • ex) 고객 이름 : {고객 아이디, 이벤트 번호}에 PFD


Q9.

Student table 이 (학번, 주민번호, 이름, 학과) 의 구조를 가질 때, 가능한 모든 superkey 와 candidate key 를 나열하시오.


A9.

( super key는 Q1 참고 )

( candidate key )

  • (sid)
  • (ssn)


Q10.

Set intersection “r ∩ s” 를 basic 한 relational algebra operator 를 사용하여 다시 표현하시오.

(힌트: basic relational algebra operator 에는 select, project, union, set difference, Cartesian product, rename 이 있음)


A10.

\[r\cup s = r-(r-s)\]


Q11.

Instructor table 이 (ID, name, dept_name, salary) 의 구조를 가질 때, 다음 문장을 SQL 구문으로 표현하시오.

“Select the names and average salaries of all departments whose average salary is greater than the average salary of all instructors in the “Computer Science” department.”


A11.

SELECT dept_name, AVG(salary)
FROM Instructor
GROUP BY dep_id
HAVING avg(salary) <(SELECT AVG(salary)
                     FROM employees
                     WHERE dept_name = 'Computer Science');


Q12.

Referential integrity constraint 가 subset dependency 로 불리는 이유를 설명하시오.

A12.

개념 : Referential integrity

  • Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation
  • also called “subset dependency”


Q13.

Relational algebra 의 six basic operators 를 나열한 후, basic operator 를 사용해서 r ∩ s 를 표현하시오.


A13.

(1) Select

ex) \(\sigma_{A=B} \wedge D>5(r)\)


(2) Project

ex) \(\prod_{A,C}(r)\)

( + drop duplicate data )


(3) Union

ex) \(r \cup s\)

( Add rows ( but, drop duplicates ) )


(4) Set Difference

ex) \(r-s\)


(5) Cartesian Product

ex) \(r\times s\)


(6) Rename

  • name the results of relational-algebra expressions

  • allow us to refer to a relation by more than one name
  • \(\rho_x(E)\) : expression \(E\) under name \(x\)
  • \(\rho_{x(A_1, \cdots A_n)}(E)\) . : expression \(E\) under the name \(x\) & with attributes renamed to \(A_1 \cdots A_n\)


(7) Additional Operations

do not add any power to the relational algebra

( just simplify common queries )

  • set intersection : \(\mathrm{r} \cap \mathrm{s}=\mathrm{r}-(\mathrm{r}-\mathrm{s})\)
  • natural join : \(r \bowtie S\)
  • division : \(r \div S\)
  • assignment : temp1 \(\leftarrow \prod_{\mathrm{R}-\mathrm{s}}(r)\)


Q14.

Student(sid, ssn, sname, did) 와 Department(did, dname, budget, building) 에 대해, 다음 SQL query 를 relational algebra 로 표현하시오. (15 points)

SELECT sid, sname
FROM Student, Department
WHERE Student.did = Department.did **and** dname = ‘CS’


A14.

Notation

  • Student relationship = \(r_1\)

  • Department relationship = \(r_2\)


\[\sigma_{r_1.did=r_2.did} \wedge \sigma_{r_2.dname=\text{'CS'}}(r_1, r_2)\]


Q15.

Block 이 무엇인지를 기술하고, instructor 테이블과 department 테이블의 join 연산이 빈번하게 발생하는 경우에 block-access time 을 줄이기 위한 방안을 제시하시오.


A15.

Block 이 무엇인지를 기술하고 :

Block ( = record들의 모음 )

  • a contiguous sequence of sectors from a single track

  • data is transferred between “disk” & “main memory” in blocks

    ( 데이터 전송의 단위 )

  • size : 512 bytes ~ xxx kilobytes


join 연산이 빈번하게 발생하는 경우에 ~

\(\rightarrow\) Multitable Clustering File Organization을 사용한다!!

simple file structure : stores each relation in a separate file

↔ store several relations in one file, using “MULTITABLE clustering” file organization


ex) Multitable clustering organization of department and instructor:

  • GOOD for queries joining department and instructor relations
  • BAD for queries involving only department


Q16.

dept_name 에 대한 index 와 salary 에 대한 index 가 각각 존재할 때, 조건 dept_name = “Finance” and salary = 8000 을 만족하는 모든 instructor 를 index 를 사용하여 검색하는 3 가지 방법을 기술하시오.


A16.

시험범위아닌듯?


기타 참고 자료

Tags:

Categories: ,

Updated:

You May Also Enjoy

less than 1 minute read

2 minute read

4 minute read

1 minute read