데이터베이스 과제 #02

학번 : 20021558	성명 : 경규성

1. 약개체 집합 (Weak Entity Set)이 소유 개체 집합과 식별 관계 집합을 이루는 예를 들어 보시오. (수업시간에 예를 든 것 이외의 것을 하기 바람)

name

name

number

소속학급

Class

학생

Student-In

Student

식별관계집합              소유개체집합

Class(classname)

Student(studentnumber, classname, studentname)

Student-In(studentnumber, classname)

2. N:1 관계에 있는 ER 다이어그램을 관계 데이터 모델로 변환할 때 N쪽에 있는 개체 집합에 1쪽의 개체 집합의 키를 포함시키면 ER 다이어그램의 관계 집합을 없앨 수 있다. 이것을 반대로 1쪽에 N쪽의 키를 포함시켜 ER 다이어그램의 관계 집합을 없애면 무엇이 문제인가?

개수비율이 1쪽인 개체에서는 N쪽인 개체의 키 에트리뷰트를 모두 포함시켜 표현할 수 없다. 예를 들어 Employee (N) 와 Department (1) 의 관계를 생각해보면 Employee에 Department의 키 에트리뷰트인 dep_Num을 Employee의 에트리뷰트로 포함시켜 표현 할 수 있다. 하지만 하나의 Department에 다수의 Employee의 키 에트리뷰트인 ssn을 전부 포함시켜 구별할 수 없다.

3. 교과서 2.6

전산학과에서 비행기 여행을 자주 하는 사람들이 Dane Country Airport 당국에게 그 공항의 서투른 조직 구성에 대하여 불평을 해왔다. 이 결과로, 공항 당국은 그 공항에 관련된 모든 정보가 DBMS를 이용하여 구성되어야 한다고 결정했고, 여러분은 그 데이터베이스를  설계하도록 고용되었다. 여러분의 첫 번째 임무는 그 공항에 배치되어 관리되는 모든 비행기에 대한 정보를 구성하는 것이다. 관련 정보는 다음과 같다.

∎ 비행기마다 등록번호가 있으며, 각 비행기는 특정한 모델에 속한다.

∎ 그 공항은 여러 비행기 모델을 수용하는데, 각 모델은 모델번호(예, DC-10)로 식별되며 용량(좌석 수)과 중량이 있다.

∎ 여러 기술자들이 그 공항에 근무한다. 각 기술자의 이름, SSN, 주소, 전화번호, 봉급을 저장한다.

∎ 각 기술자는 하나 이상의 비행기 모델의 전문가이며, 그(그녀)의 전문 분야는 다른 기술자들의 전문 분야와 중복될 수 있다. 기술자들에 대한 이러한 정보도 기록되어야 한다.

∎ 항공 관제사들은 매년 의료 진단을 받아야 한다. 각 항공 관제사에 대하여, 가장 최근의 진단일자를 저장해야 한다.

∎ (기술자를 포함하여) 모든 공항 직원들은 노조에 소속된다. 각 직원의 노조 회원 번호를 저장해야 한다. 각 직원은 SSN으로 유일하게 식별된다고 가정한다.

∎ 그 공항에서는 비행기들이 여전히 안전한 항공에 적합하다는 것을 보장하기 위해 주기적으로 실시되는 몇 가지의 검사가 있다. 각 테스트는 FAA(Federal Aviation Authority:미연방 항공관리국) 검사번호, 이름, 만점이라는 난을 가지고 있다.

∎ FAA에서는 그 공항의 특정 비행기가 정해진 기술자에 의해 어떤 검사가 수행될 때마다 그 정보를 유지관리 하도록 요구한다. 각 검사별로 필요한 정보는 검사 일자, 검사 소요시간, 검사 정수이다.

1. 이 공항 데이터베이스 ER 다이어그램을 작성하시오. 각 개체집합과 관계집합에 대해 여러 가지 애트리뷰트들을 분명히 표시하시오; 또한 각 관계집합의 키와 참여 제약조건을 명시하시오. 또한 중첩과 포괄 제약조건을 필요한 대로 명시하시오(한글로).

2. FAA는 한 비행기에 대한 검사들이 그 비행기 모델의 전문 기술자에 의해 수행되어야 하는 규정을 배포한다. 이 제약조건을 어떻게 ER 다이어그램으로 표현하겠는가? 표현할 수 없다면, 그 이유를 간단히 설명하시오.

표현할 수 없다.

검사와 항공기와 기술자, 세 개의 개체는 검사정보라는 관계집합으로 묵여있고, 기술자와 모델 개체는 전문가라는 관계집합으로 따로 표현해야 한다.

모든 항공기는 특정 모델에 속한다는 제약조건은 표현 할 수 있으나,

한 항공기에 대한 검사들이 그 항공기 모델의 전문 기술자에 의해 수행되어야만 한다는 제약 조건은 표현할 수 없다.

4. 교과서 3.8

이 릴레이션 스키마를 기초로 하여 다음의 질문들에 간단히 답하시오.

Emp(eid:integer, ename:string, age:integer, salary:real)

Works(eid:integer, did:integer, pct_time:integer)

Dept(did:integer, dname:string, budget:real, managerid:integer)

1. Dept 릴레이션을 포함하는 외래키 제약조건의 예를 들어 보시오. 사용자가 Dept의 한 투플을 삭제하려고 할 때 이 제약조건을 집행하는 옵션에는 어떠한 것들이 있는가?

교제 68page의 Student와 Enrolled의 관계와 같은 경우로 Emp와 Works의 관계는 아래와 같이 예를 들 수 있다. (did 삭제의 경우 CASCADE, 갱신의 경우 NO ACTION)

CREATE TABLE Works (	eid	INTEGER NOT NULL ,

did	INTEGER NOT NULL ,

pct_time	INTEGER,

PRIMARY KEY (eid, did),

FOREIGN KEY (eid) REFERENCES Emp

FOREIGN KEY (did) REFERENCES Dept

ON DELETE CASCADE

ON UPDATE NO ACTION )

Dept의 행이 삭제되면 4가지 옵션들 중 하나로 대처 할 수 있다.

1) 삭제된 Dept 행을 참조하는 모든 Works 행들을 삭제한다. (CASCADE)

2) 어떤 Works행이 그 Dept 행을 참조하고 있으면 그 행의 삭제를 금지한다.

(NO ACTION)

3) 삭제된 Dept 행을 참조하고 있는 모든 Works 행들에 대해서 did 열을 어떤 ‘디폴트’ 부서의 did로 설정한다. (DEFAULT '.......')

4) 삭제된 Dept 행을 참조하고 있는 모든 Works 행들에 대해서, did 열의 값을 NULL로 설정한다.

2. 이 릴레이션들을 생성하는 SQL 문장들을 작성하시오. 여기에 기본키와 외래키 무결성 제약조건들을 포함하시오.

CREATE TABLE Emp (	eid	INTEGER DEFAULT '12345',

ename	CHAR(10),

age	INTEGER,

salary	REAL,

PRIMARY KEY (eid) )

CREATE TABLE Works (	eid	INTEGER,

did	INTEGER,

pct_time	INTEGER,

PRIMARY KEY (eid, did),

FOREIGN KEY (did) REFERENCES Dept

ON DELETE CASCADE,

FOREIGN KEY (eid) REFERENCES Emp

ON DELETE CASCADE )

CREATE TABLE Dept (	did	INTEGER,

budget	REAL,

managerid	INTEGER ,

PRIMARY KEY (did),

FOREIGN KEY (managerid) REFERENCES Emp (eid),

ON DELETE SET DEFAULT )

3. 모든 부서는 관리자를 갖도록 Dept 릴레이션을 SQL로 정의하시오.

CREATE TABLE Dept (	did INTEGER,

budget REAL,

managerid INTEGER NOT NULL ,

PRIMARY KEY (did),

FOREIGN KEY (managerid) REFERENCES Emp (eid) )

4. eid = 101, age = 32, salary = 15,000의 값을 가지는 John Doe라는 직원을 추가하는 SQL문을 작성하시오.

INSERT

INTO Emp (eid, ename, age, salary)

VALUES (101, ’John Doe’, 32, 15000)

5. 모든 직원들의 급여를 10% 인상하는 SQL문을 작성하시오.

UPDATE Emp E

SET E.salary = E.salary * 1.10

6. Toy부서를 삭제하는 SQL문을 작성하시오. 이 스키마를 위해 선택한 참조 무결성 제약조건에 따라, 이 문장이 실행될 때 어떤 일이 일어나는지 설명하시오.

DELETE

FROM	Dept D

WHERE D.dname = ’Toy’

5. 교과서 2.6 (위의 3번 문제)에서 작성한 ER 스키마 다이어그램을 관계 스키마로 변환하시오.

교과서 문제 2.6  ER 다이어그램의 관계스키마

항공기 (	등록번호)

모델종류 (등록번호, 모델번호)

모델 (모델번호, 용량, 중량)

전문가 (모델번호	, SSN)

기술자 (SSN, 노조번호, 이름, 주소, 전화번호, 봉급)

직원(SSN, 노조번호)

항공관제사(SSN, 최근진단일)

검사(검사번호, 이름, 만점)

검사정보(등록번호, SSN, 검사번호, 검사일자, 소요시간, 검사점수)

문제 3 (교과서 2.6) ER 다이어그램

검사정보

검사

전화번호

모델

모델번호

용량

중량

기술자

전문가

봉급

주소

이름

모델종류

항공기

항공관제사

직원

검사번호

이름

만점

검사일자

소요시간

검사점수

등록번호

최근진단일

ISA

노조번호

SSN