데이터베이스의이해와활용 첫번째 과제 답안

   


  1. (1점) 다음 예시 관계 대수로 표현된 쿼리들을 아래 데이터베이스 상태에 적용한 결과를 보여주세요. Figure 5.6

Q1 Q2 Q3 Q4

답:

Q1 Result:

FNAME LNAME ADDRESS
John Smith 731 Fondren, Houston, TX
Franklin Wong 638 Voss, Houston, TX
Ramesh Narayan 975 Fire Oak, Humble, TX
Joyce English 5631 Rice, Houston, TX

Q2 Result:

PNUMBER DNUM LNAME ADDRESS BDATE
10 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31
30 4 Wallace 291 Berry, Bellaire, TX 20-JUN-31

Q3 Result: (empty because no tuples satisfy the result).

Q4 Result:

PNO
1
2

   


  1. (2점) 다음은 주어진 COMPANY 관계형 데이터베이스 스키마를 사용하여 특정 쿼리를 수행하는 내용입니다. 아래 그림을 참조하세요. Figure 5.5

각 쿼리에 대한 결과를 다음 데이터베이스 상태에 적용하여 보여주세요. Figure 5.6

a. 부서 5에서 일주일에 10시간 이상 ProductX 프로젝트에 참여하는 모든 직원의 이름을 검색하세요.

b. 'Franklin Wong'에게 직접 감독받는 모든 직원의 이름을 찾으세요.

c. 모든 프로젝트에 참여하는 모든 직원의 이름을 검색하세요.

d. 각 부서별로, 해당 부서에서 일하는 모든 직원의 평균 급여와 부서 이름을 검색하세요.

e. 부양 가족이 없는 모든 부서 관리자의 성을 나열하세요.

답:

a. Result:

LNAME FNAME
Smith John
English Joyce

b. Result (empty)

c. Result:

LNAME FNAME
Smith John
Narayan Ramesh
English Joyce

d. Result:

PNAME TOT_HRS
ProductX 52.5
ProductY 37.5
ProductZ 50.0
Computerization 55.0
Reorganization 25.0
Newbenefits 55.0

e. Result (empty)

   


  1. (2점) 다음 UNIVERSITY 데이터베이스를 표현한 ER 다이어그램을 관계형 데이터베이스 스키마로 변환해보세요. Figure 3.20

답:

INSTRUCTOR(Id, IName, Rank, IOffice, IPhone, DCode, DName); foregin key(DCode, DName) references DEPT(DCode, DName)

COLLEGE(CName, COffice, CPhone, DeanID); foreign key(DeanID) references INSTRUCTOR(Id)

DEPT(DCode, DName, DOffice, DPhone, ChairID, CStartDate); foreign key(ChairID) references INSTRUCTOR(Id)

COURSE(CCode, CoName, Level, Credits, CDesc, DCode, DName); foreign key(DCode, DName) references DEPT(DCode, DName)

SECTION(CourseId, SecId, Semester, Year, CRoom, SecNo, DaysTime, Id); foreign key(CourseId) references COURSE(CCode) and (DCode) references COURSE(DCode), foreign key(Id) references INSTRUCTOR(Id)

STUDENT(SId, SName, Address, Phone, DOB, Major, DCode, DName); foreign key(DCode, DName) references DEPT(DCode, DName)

TAKES(SId, CourseId, SecId, Semester, Year, Grade); foreign key(SId) references STUDENT(SId), foreign key(CourseId, SecId, Semester, Year) references SECTION(CourseId, SecId, Semester, Year)

TEACHES(Id, CourseId, SecId, Semester, Year); foreign key(Id) references INSTRUCTOR(Id), foreign key(CourseId, SecId, Semester, Year) references SECTION(CourseId, SecId, Semester, Year)

DEPT_LOCATIONS(DCode, DName, COffice); foreign key(DCode, DName) references DEPT(DCode, DName)

   


  1. (1점) 다음 그림에 나타난 관계형 스키마를 ER 스키마로 매핑해 보세요. 이는 기존에 구현된 데이터베이스에 대해 개념적 스키마를 생성하는 과정, 즉 역공학(reverse engineering)의 일부입니다. 어떤 추가적인 가정을 하게되는 경우 반드시 명시해 주세요. Figure 6.14

답: Figure 6.14

참고: BOOK_AUTHORS 를 BOOK 엔티티의 multi-valued attribute 로 표현했음

   


  1. (2점) 학생들의 성적표를 추적하기 위해 사용되는 대학 데이터베이스에 대한 다음과 같은 요구사항이 있다고 가정해 봅시다:

    a. 대학은 각 학생의 이름(Sname), 학번(Snum), 사회보장번호(Ssn), 현재 주소(Sc_addr)와 전화번호(Sc_phone), 영구 주소(Sp_addr)와 전화번호(Sp_phone), 생년월일(Bdate), 성별(Sex), 학년(Class) (‘신입생’, ‘2학년’, …, ‘대학원생’), 전공 부서(Major_code), 부전공 부서(Minor_code) (해당되는 경우), 학위 프로그램(Prog) (‘b.a.’, ‘b.s.’, …, ‘ph.d.’)을 기록합니다. Ssn과 학번은 각 학생마다 고유한 값을 가집니다.

    b. 각 학과는 이름(Dname), 학과 코드(Dcode), 사무실 번호(Doffice), 사무실 전화(Dphone), 단과대학(Dcollege)으로 설명됩니다. 이름과 코드는 각 학과마다 고유한 값을 가집니다.

    c. 각 과목은 과목명(Cname), 설명(Cdesc), 과목 번호(Cnum), 학기당 학점 수(Credit), 수준(Level), 개설 학과(Cdept)를 가집니다. 과목 번호는 각 과목마다 고유합니다.

    d. 각 섹션은 강사(Iname), 학기(Semester), 연도(Year), 과목(Sec_course), 섹션 번호(Sec_num)를 가집니다. 섹션 번호는 같은 학기/연도에 가르치는 동일 과목의 다른 섹션을 구별합니다; 그 값은 1, 2, 3, …, 각 학기에 가르치는 섹션의 총 수까지입니다.

    e. 성적 기록은 학생(Ssn), 특정 섹션, 그리고 성적(Grade)을 참조합니다.

이 데이터베이스 애플리케이션을 위한 관계형 데이터베이스 스키마를 설계하세요. 먼저 모든 속성 간에 유지되어야 할 함수적 종속성(functional dependency)을 보여주세요. 그런 다음 데이터베이스의 관계 스키마를 각각 3NF 또는 BCNF로 설계하세요. 각 관계의 키 속성을 명시하세요. 만약 스키마를 완전히 만들기 위해 추가적인 요구조건이 필요한 경우에는 적절한 가정을 추가하여 만들어보세요.

답:

주어진 설명에서 다음과 같은 기능적 종속성이 성립한다고 가정할 수 있습니다.

FD1: {SSSN} -> {SNAME, SNUM, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS, MAJOR, MINOR, PROG} FD2: {SNUM} -> {SNAME, SSSN, SCADDR, SCPHONE, SPADDR, SPPHONE, BDATE, SEX, CLASS, MAJOR, MINOR, PROG} FD3: {DEPTNAME} -> {DEPTCODE, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE} FD4: {DEPTCODE} -> {DEPTNAME, DEPTOFFICE, DEPTPHONE, DEPTCOLLEGE} FD5: {CNUM} -> {CNAME, CDESC, CREDIT, LEVEL, CDEPT} FD6: {SECCOURSE, SEMESTER, YEAR, SECNUM} -> {INSTRUCTORNAME} FD7: {SECCOURSE, SEMESTER, YEAR, SECNUM, SSSN} -> {GRADE}

이러한 기본 기능적 종속성들은 주어진 요구사항에서 유도할 수 있으며, IR1부터 IR3의 추론 규칙을 사용하여 다른 종속성들을 유추할 수 있습니다. FD1과 FD2는 학생 속성을 나타내며, STUDENT 관계에 대한 기본 키로 SSSN 또는 SNUM 중 하나를 선택할 수 있습니다. 마찬가지로, FD3과 FD4는 학과 속성을 나타내며 DEPTNAME 또는 DEPTCODE 중 하나를 기본 키로 선택할 수 있습니다. FD5는 COURSE 속성을 정의하고, FD6은 SECTION 속성을 정의하며, 마지막으로 FD7은 GRADES 속성을 정의합니다. 이러한 정보를 바탕으로 아래와 같이 STUDENT, DEPARTMENT, COURSE, SECTION 및 GRADES 관계를 생성할 수 있으며, 각 관계의 기본 키는 밑줄로 표시되었습니다. COURSE, SECTION 및 GRADES 관계는 다른 종속성이 없는 한 3NF 및 BCNF 상태이며, STUDENT 및 DEPARTMENT 관계는 일반적인 정의에 따라 3NF 및 BCNF 상태이지만 Section 18.3의 정의에 따르면 이차키가 있는 상태입니다.

Figure 6.14

외래키는 다음과 같습니다.

STUDENT.MAJOR -> DEPARTMENT.DEPTCODE STUDENT.MINOR -> DEPARTMENT.DEPTCODE COURSE.CDEPT -> DEPARTMENT.DEPTCODE SECTION.SECCOURSE -> COURSE.CNUM GRADES.(SECCOURSE, SEMESTER, YEAR, SECNUM) -> SECTION.(SECCOURSE, SEMESTER, YEAR, SECNUM) GRADES.SNUM -> STUDENT.SNUM

   


  1. (1점) 아래 주어진 EMP_DEPT와 EMP_PROJ에서 데이터 갱신 시 발생할 수 있는 이상 현상(update anomaly)들이 어떤 것들이 있는지 설명해주세요. 이상 현상들을 제거하기 위해 어떤 방법을 사용할 수 있을지 설명해주세요.

Figure 14.3

답:

EMP_PROJ에서의 부분 종속성 {SSN}->{ENAME} 및 {PNUMBER}->{PNAME, PLOCATION}은 이상을 발생시킬 수 있습니다. 예를 들어, 어떤 프로젝트에 일하는 직원이 없는 경우, 해당 프로젝트 정보 (PNAME, PNUMBER, PLOCATION)는 데이터베이스에 나타나지 않을 것입니다. 이 경우, 마지막으로 해당 프로젝트에서 근무하는 직원이 삭제될 때 (삭제 이상) 문제가 발생할 수 있습니다. 또한 새로운 프로젝트를 추가하려면 적어도 하나의 직원이 할당되어 있어야 합니다 (삽입 이상). 기존 EMPLOYEE를 기존 PROJECT와 관련시키는 새로운 튜플을 삽입하려면 부분 종속성을 모두 확인해야 합니다. 예를 들어, PNUMBER 값이 동일한 다른 튜플들과 다른 PLOCATION 값을 입력하면 업데이트 이상이 발생할 수 있습니다. EMP_PROJ는 EMPLOYEE와 PROJECT 간의 관계를 나타내는 동시에 EMPLOYEE 및 PROJECT 엔터티에 관한 정보를 나타냅니다.

EMP_DEPT에서의 추이 종속성 {SSN}->{DNUMBER}->{DNAME, DMGRSSN}은 이상을 발생시킬 수 있습니다. 예를 들어, 어떤 부서에 임시로 근무하는 EMPLOYEE가 없는 경우, 해당 부서 정보 (DNAME, DNUMBER, DMGRSSN)는 데이터베이스에 나타나지 않을 것입니다. 이 경우, 해당 부서에 일하는 마지막 EMPLOYEE가 삭제될 때 (삭제 이상) 문제가 발생할 수 있습니다. 또한 새로운 DEPARTMENT를 추가하려면 적어도 하나의 EMPLOYEE가 할당되어 있어야 합니다 (삽입 이상). 새로운 EMPLOYEE를 기존 DEPARTMENT와 관련시키는 새로운 튜플을 삽입하려면 추이 종속성을 확인해야 합니다. 예를 들어, DNUMBER 값이 동일한 다른 튜플들과 다른 DMGRSSN 값을 입력하면 업데이트 이상이 발생할 수 있습니다. EMP_DEPT는 EMPLOYEE와 DEPARTMENT 간의 관계를 나타내는 동시에 EMPLOYEE 및 DEPARTMENT 엔터티에 관한 정보를 나타냅니다.

   


  1. (1점) 아래 그림의 LOTS 관계 스키마가 기본 키만을 고려하는 제한적인 정규형 해석에 따라 어떤 정규형에 해당하는지 알려주세요. 만약 일반적인 정규형 정의를 사용한다면 여전히 같은 정규형에 해당하게 될까요? Figure 14.12a

답:

만약 우리가 기본 키만을 고려한다면, LOTS 관계 스키마 (a)는 주 키에 대한 부분 종속성이 없으므로 2NF에 해당합니다. 그러나 주 키에 대한 두 개의 추이 종속성이 있기 때문에 3NF가 아닙니다. 이러한 두 가지 추이 종속성은 다음과 같습니다: PROPERTY_ID# -> COUNTY_NAME -> TAX_RATE 및 PROPERTY_ID# -> AREA -> PRICE. 이제 모든 키를 고려하고 2NF와 3NF의 일반적인 정의를 사용한다면, LOTS 관계 스키마는 2차 키 {COUNTY_NAME, LOT#}에 대한 부분 종속성 COUNTY_NAME -> TAX_RATE이 있으므로 2NF를 위반하여 LOTS 관계 스키마는 1NF에만 해당할 것입니다.