7/25/2019 DBD tugas

1/18

1.

Design an ER schema for this application, and draw an ER diagram for the schema. Specify key

attributes of each entity type, and structural constraints on each relationship type. Note any

unspecified requirements, and make appropriate assumptions to make the specification

complete.

DEPARTMENT

COURSE

STUDENT

Sname

Snumber SosSecurity_number

C_Address

C_Phone

P_Addre

ssP_Phone Birth_

date

Sex

Class

Deggre_Program

State

City

ZIP_code

FN

LN

Name

Dept_CodeOffice_num

Office_Phone

College

CName

DescriptionCnumber

Semester_hours

level

Instructor

Semester

year

Course

Minor_DeptMajor_Dept

nn

1 1COURSE_SECT

DEPT_COURSE

nn

GRADE

NumericGradeLetterGrade

STUD_SECT

n

n

n

n

n

SECTION

Section_num

STUD_GRADE

n

n

2.

Consider the ER diagram below, which shows a simplified schema for an airline reservations

system. Extract from the ER diagram the requirements and constraints that produced this

schema. Try to be as precise as possible in your requirements and constraints specification.

Entity

-

In Entity AIRPORT,airport is identified by AirportCode. We need to keep track of the Name,

City, and State for each airport.

-

In Entity AIRPLANE TYPE,each airplane type is identified by TypeName.We need to keep

track of the Max-seats for each airplane type

-

In Entity AIRPLANE, each airplane is identified by AirplaneId.We need to keep track of the

Total-no-of-seats for each airplane

-

In Entity FLIGHT LEG,We need to track of the LegNo for each flight leg.

-

In Entity Flight , each flight is identified by Number. We need to keep track of the Weekdays

and Airline for each flight.

-

In Entity LEG INSTANCE ,We need to keep track of the Date and No-of-avail-seats for each

leg instance. Each leg instance must be assigned to at least one airplane.Each leg instance

must be an instance of a flight leg.

Relationships, attributes, and constraints

7/25/2019 DBD tugas

2/18

- Relationship between FLIGHT and FARES

a.

Each instance of FLIGHT may offer multiple instances of FARE.

b.

Instances of FARE must be sold for a specific instance of FLIGHT.

-

Relationship between FLIGHT and FLIGHT LEG

a.

Each instance of FLIGHT may consist of multiple instances of FLIGHT LEG.

b.

Instances of FLIGHT LEG must belong to a specific instance of FLIGHT

- Relationship between FLIGHT LEG and AIRPORT (ScheduledDepTime)

a.

Each instance of AIRPORT may be scheduled for the departure of multiple instances of

FLIGHT LEG.

b.

Instances of FLIGHT LEG must be scheduled for departure from a specific instance of

AIRPORT.

- Relationship between FLIGHT LEG and AIRPORT (ScheduledArrTime)

a.

Each instance of AIRPORT may be scheduled for the arrival of multiple instances of

FLIGHT LEG

b.

Instances of FLIGHT LEG must be scheduled for arrival at a specific instance of AIRPORT

-

Relationship between FLIGHT LEG and LEG INSTANCEa.

Each instance of FLIGHT LEG may consist of instances of LEG INSTANCE.

b.

Instances of LEG INSTANCE must be instances of FLIGHT LEG.

-

Relationship between LEG INSTANCE and AIRPORT (ArrTime)

a.

Each instance of AIRPORT may accommodate the arrival of multiple instances of LEG

INSTANCE.

b.

Instances of LEG INSTANCE must arrive at a specific instance of AIRPORT.

- Relationship between LEG INSTANCE and AIRPORT (DepTime)

a.

Each instance of AIRPORT may accommodate the departure of multiple instances of LEG

INSTANCE.

b.

Instances of LEG INSTANCE must depart from a specific instance of AIRPORT.

-

Relationship between LEG INSTANCE and SEAT (CustomerName, CPhone)a.

Each instance of LEG INSTANCE may have multiple instances of SEAT for reservation.

b.

Instances of SEAT must be reserved for a specific instance of LEG INSTANCE.

- Relationship between AIRPLANE and LEG INSTANCE

a.

Each instance of AIRPLANE may be assigned to multiple instances of LEG INSTANCE.

b.

Instances of LEG INSTANCE must have a specific instance of AIRPLANE assigned to them.

-

Relationship between AIRPLANE TYPE and AIRPLANE

a.

each instance of AIRPLANE TYPE may consist of multiple instances of AIRPLANE.

b.

Instances of AIRPLANE must belong to a specific instance of AIRPLANE TYPE.

- Relationship between AIRPORT and AIRPLANE TYPE

a.

Multiple instances of AIRPORT may be available for multiple instances of AIRPLANE TYPE

for landing.

b.

Multiple instances of AIRPLANE TYPE may land at multiple instances of AIRPORT.

7/25/2019 DBD tugas

3/18

3.

Draw an EER schema diagram for this application. Discuss any assumptions you make, and that

justify your EER design choices.

7/25/2019 DBD tugas

4/18

COLLECTION

EXHIBITION

OTHER

PAINTING

ART OBJECT

PERMANENT

ARTIST

STATUE SCULPTURE

BORROWED

Epoch

Main Style

Country

Aname

Date

Died Born

Creates

Style

Material

Height

Weight

Material Style

Style

Type

Start Date

Ename End Date

Name

Address

Phone

Decription

Type

Collection

DateBorro

wed

Status

Date

Acquired

Cost

Title

Year

Descriptio

n

Id No

From

Collection

d

Shown At

d

Painttype

DateReturn

ContactPerson

1

n

n

m

n

1

4.

Design an Enhanced Entity-Relationship diagram for the ONLINE_AUCTION database.

7/25/2019 DBD tugas

5/18

1.

ITEM

SELLER

BUYER

MEMBER

BID

Place

FEEDBACK

o

MNumber

Email

Name

Password

Homeaddre

ssPhoneNum

ber

Shipping

Address

BankAccou

nt Number

Routing

Number

ItemTitle

ItemNumb

er

DescriptionStarting Bid

Price

Bidding

Increment

Start date

of Auction

End date of

Auction

Category

Bid Price

Bid Time

Rating

Comment 1

N

M

N

N

M

7/25/2019 DBD tugas

6/18

Untuk skema ER dalam soal nomor 1 di bawah ini, lakukan pemetaan skema ER

tersebut enjadi satu set skema relasional. Tunjukkan semua primary keys dan foreign

keys dari hasil pemetaan menggunakan diagram referensial.

Jawab :

AIRPORT

Aiport_code City State Name

CAN_LAND

Airport_Code Type_name

AIRPLANE_TYPE

Type_name Max_seats Company

AIRPLANE

Airplane_id Total_no_of_seats Type_name

FLIGHT

FLIGHT_LEG

FNumber Leg_no Airport_code

_Depart

Scheduled_

dep_time

Airport_code

_Arrival

Scheduled_

arr_time

FARE

FNumber Code Amount Restrictions

LEG_INSTANCE

FNumber Airlane Weekdays

FNumber Leg_no Date No_of_avail_

seats

Airport_co

de_Depart

Dep_time Airport_code

_Arrives

Arr_tim

e

Airplane

_id

7/25/2019 DBD tugas

7/18

SEAT

FNumber Leg_no Date Seat_no Customer_name Cphone

2.

Lakukan pemetan skema relasional yang ditunjukkan dalam gambar soal ini menjadi sebuah

skema ER. Proses ini dikenal sebagai bagian dari proses reverse engineering, dimana sebuah

skema konseptul dibuat dari sebuah basis data yang telah diimplementasikan sebelumnya

7/25/2019 DBD tugas

8/18

BOOK

PUBLISHER

Publish by

LIBRARY_BRANCH

BORROWER

Author_name

Name

Address

Phone

Title

Book_id

Card_no

Name

Address

Phone

Branch_id

Branch_name

Address

BOOK_LOANS

COPIES

No_of_copies

Date_out

Due_date

n

1

m

n

1

n

1

3.

Untuk setiap skema EER dalam soal nomor 3, 4 dan 5 di bawah ini, lakukan pemetaan masing-

masing skema EER gersebut menjadi satu set skema relasional. Tunjukkan semuaprimary keys

danforeign keys dari hasil pemetaan menggunakan diagram referensial. Selain itu, nyatakan

alasan pilihan yang digunakan dala memetakan konsep generalisasi dan spesialisasi yang ada

7/25/2019 DBD tugas

9/18

CAR

Vin Engine_size

TRUCK

Vin Tonnage

SUV

Vin Np_seats

VEHICLE

Vin Price Model

SALE

Vin Sid Ssn Date

SALESPERSON

Sid Name

Customer

Ssn Name State Street City

-

Menggunakan Option 8A karena VEHICLE memiliki Multiple Relationship yaitu antara

Salesperson dan Customer

7/25/2019 DBD tugas

10/18

4.

Untuk setiap skema EER dalam soal nomor 3, 4 dan 5 di bawah ini, lakukan pemetaan masing-

masing skema EER gersebut menjadi satu set skema relasional. Tunjukkan semuaprimary keys

danforeign keys dari hasil pemetaan menggunakan diagram referensial. Selain itu, nyatakan

alasan pilihan yang digunakan dala memetakan konsep generalisasi dan spesialisasi yang ada

PLANE TYPE

Model Capacity Weight

AIRPLANE

Reg# Model Number

HANGAR

Number Location Capacity

EMPLOYEE

Ssn Salary Shift PersonFlag

PILOT

Ssn Restr Lic_num PersonFlag

SERVICEReg# Date Workcode Hours

OWNS

Reg# OwnerId Pdate

WORK_ON

Model Ssn

FLIESModel Ssn

MAINTAIN

Ssn Reg# Date Workcode

7/25/2019 DBD tugas

11/18

OWNER

OwnerId

CORPORATION

Name Address Phone

PERSON

Ssn Name Address Phone

-

Pada OWNER Menggunakan Mapping of Union Types karena terdapat superclass yang

mempunyai keys yang berbeda dengan subclassnya, Sedangkan pada Employee dan Pilot

menggunakan Mapping of shared subclasses karena berbagi subclass(PERSON)

5.

FACULTY

Ssn Rank Foffice Fphone Salary

STUDENT

Ssn Class Dname_Minor Dname_Major Grad_StudentFLag

GRAD_STUDENT

GSsn

DEGREES

GSsn College Degree Year

GRANTNo Title Agency St_date Ssn

INSTRUCTOR_RESEARCHER

Ssn

DEPARTMENT

Dname Dphone Office Chairs_Ssn Cname

COLLEGE

Cname Dean Coffice

COURSE

C# Cname Cdesc Dname

7/25/2019 DBD tugas

12/18

CURRENT_SECTION

Sec#

SECTION

Sec# Year Qtr C# Ssn

COMMITTE

Ssn

BELONGS

Ssn Dname

SUPPORT

No Ssn Start Time End

REGISTERED

Ssn Sec#

TRANSCRIP

Ssn Sec#

-

Menggunakan Mapping of Union Types karena terdapat superclass yang mempunyai keys yang

berbeda dengan subclassnya, menggunakan Mapping of shared subclasses karena ada yang

berbagi subclass, dan menggunakan option 8B karena hanya multiple relationship dan hanya

membutuhkan subclass

HW4 Answer Key

1. Exercise 7.2: List all functional dependencies satisfied by the relation

of Figure 7.18.

Answer:

Non-trivial functional dependencies:

A -> B

C -> B

2. Exrercise 7.6: Compute the closure of the following set F of functionaldependencies for relation schema R = {A, B, C, D, E}.

A -> BC

CD -> E

B -> D

E -> A

List the candidate keys for R.

7/25/2019 DBD tugas

13/18

Answer:

A -> BC, B -> D so A -> D so A -> DC -> E

therefore A -> ABCDE

E -> A, A -> ABCDE, so E -> ABCDE

CD -> E, so CD -> ABCDE

B -> D, BC -> CD, so BC -> ABCDE

Attribute closure:

A -> ABCDE

B -> BD

C -> C

D -> D

E -> ABCDE

AB -> ABCDE

AC -> ABCDE

AD -> ABCDE

AE -> ABCDE

BC -> ABCDE

BD -> BD

BE -> ABCDECD -> ABCDE

CE -> ABCDE

DE -> ABCDE

ABC -> ABCDE

ABD -> ABCDE

ABE -> ABCDE

ACD -> ABCDE

ACE -> ABCDE

ADE -> ABCDE

BCD -> ABCDE

BDE -> ABCDE

CDE -> ABCDE

ABCD -> ABCDE

ABCE -> ABCDE

ABDE -> ABCDE

ACDE -> ABCDE

BCDE -> ABCDE

The candidate keys are A, E, CD, and BC

Any combination of attributes that includes those is a superkey.

3. Exercise 7.18: Why are certain functional dependencies called trivial

functional dependencies?

Answer:

Because the right hand side is a subset of the left hand side. Therefore itis obvious that the right hand side is dependent on the left hand side.

More opaque book definition: An FD is trivial if it is satisfied by all

instances of a relation.

4. Exercise 7.17: Explain what is meant by repetition of information and

inability to represent information. Explain why each of these properties may

indicate a bad relational database design.

7/25/2019 DBD tugas

14/18

Answer:

- Repetition of Information is a condition in a relational database where the

values of one attribute are determined by the values of another attribute in

the same relation, and both values are repeated throughout the relation. This

is a bad relational database design because it increases the storage required

for the relation and it makes updating the relation more difficult.

- Inability to represent information is a condition where a relationship

exists amoung only a proper subset of the attributes in a relation. This is

bad relational database design because all the unrelated attributes must be

filled with null values otherwise a tuple without the unrelated information

cannot be inserted into the relation.

5. Consider a relation R(A,B,C,D,E) with the following dependencies:

{AB-> C, CD -> E, DE -> B}

Is AB a candidate key of this relation? If not, is ABD? Explain your answer.

No. The closure of AB does not give you all of the attributes of the

relation.

If not, is ABD? Explain your answer.

A -> A

B -> B

C -> C

D -> D

E -> E

AB -> ABC

AC -> AC

AD -> AD

AE -> AE

BC -> BC

BD -> BD

BE -> BE

CD -> BCDE

CE -> CE

DE -> BDE

ABD -> ABCDE

Yes, ABD is a candidate key. No subset of its attributes is a key.

6. Consider a relation with schema R(A,B,C,D) and FDs {AB -> C, C -> D, D ->

A}.

a. What are some of the nontrivial FDs that can be inferred from the given

FDs?

Some examples:

C -> ACD

D -> AD

AB -> ABCD

AC -> ACD

BC -> ABCD

BD -> ABCD

CD -> ACD

ABC -> ABCD

7/25/2019 DBD tugas

15/18

ABD -> ABCD

BCD -> ABCD

b. What are all candidate keys of R?

By calculating an attribute closure we can see the candidate keys are:

AB, BC, and BD

Attribute closure:

A -> A

B -> B

C -> ACD

D -> AD

AB -> ABCD

AC -> ACD

AD -> AD

BC -> ABCD

BD -> ABCD

CD -> ACD

ABC -> ABCD

ABD -> ABCDACD -> ACD

BCD -> ABCD

c. Indicate all BCNF violations for R.

C->D and D->A

d. Decompose the relations into collections of relations that are in BCNF.

(ABCD)

| \

C->D |

| |

(CD) (ABC)

| \

C->A |

| |

(CA) (AC)

So you get: R1(CD), R2(AC), and R3(BC)

If we split on D->A

(ABCD)

| \

D->A |

| |(AD) (BCD)

| \

C->D |

| |

(CD) (BC)

So you get: R1(AD), R2(CD), and R3(BC)

7/25/2019 DBD tugas

16/18

e. Indicate which dependencies if any are not preserved by the BCNF

decomposition.

If we start to decompose on C->D then D->A and AB->C

If we start to decompose on D->A then AB->C

7. Consider a relation R(A,B,C,D,E) with FDs {AB -> C, DE ->C, and B -> D}

a. Indicate all BCNF violations for R.

Logically, since C and D are the only attributes that can be determined via

other attributes, we can deduce that the keys will contain the other

attributes, thus we prefrom a smaller attribute closure:

ABE -> ABCDE

ABCD -> ABCDE

ABCE -> ABCDE

Candidate keys: ABE

Violations:

B->D, AB->C, DE->C

b. Decompose the relations into collections of relations that are in BCNF.

(ABCDE)

Break down (ABCDE) with AB->C

(ABC) and (ABDE)

Break down (ABDE) with B->D

(BD) And (ABE)

So we get R1(ABC), R2(BD) and R3(ABE)

c. Indicate which dependencies if any are not preserved by the BCNF

decomposition.

DE->C

8. Prove or disprove the following inference rules for functional

dependencies.

Note: Read "|=" as implies

a. {X->Y, Z->W} |= XZ ->YW

XZ -> XZ

XZ -> XW (Z -> W)

XZ -> W (decomposition rule)

XZ -> XZ

XZ -> YZ (X -> Y)

XZ -> Y (decomposition rule)

XZ -> YW (union rule)

b. {X->Y, XY -> Z} |= X -> Z

7/25/2019 DBD tugas

17/18

Y -> Z (pseudotransitivity rule)

X -> Z (transitivity)

c. {XY -> Z, Y->W} |= XW->Z

W -> W

X -> X

Y -> YW

Z -> Z

WX -> WX

WY -> WY

WZ -> WZ

XY -> WXYZ

XZ -> XZ

YZ -> WYZ

Therefore WX -> Z is not true

You can also find the attribute closure for WX and show that closure set does

not contain Z.

Use Armstrong's Axioms or Attribute closure to prove or disprove.

9. Consider a relation R(A,B,C,D) with FDs {A ->B, B ->C, C-> D}

a. Indicate all BCNF violations for R.

Logically, since B, C, and D are the only attributes that can be determined

via other attributes, we can deduce that the keys will contain the other

attributes, thus we prefrom a smaller attribute closure:

A -> ABCD

AB -> ABCD

AC -> ABCD

AD -> ABCD

ABC -> ABCD

ABD -> ABCD

ACD -> ABCD

Violations:

B->C, C->D

b. Decompose the relations into collections of relations that are in BCNF.

Breakdown based on B->C

(BC), (ABD)

Breakdown based on B->D

(AB), (BD)

So we get R1(BC), R2(AB), R3(BD)

c. Indicate which dependencies if any are not preserved by the BCNF

decomposition.

C->D

10. For the same example relation R with the two tuples as in the notes

above, decompose it as R1(A,B) and R2(A,C). Try and merge them back using

7/25/2019 DBD tugas

18/18

natural join and see if the resulting relation is the same as R. Do you think

this decomposition is a lossless join decomposition?

Suppose R contains two tuples (1, 2, 3) and (2, 2, 4)

R1 contains (1, 2), (2, 2)

R2 contains (1, 3), (2, 4)

Natural Join on A and we have:

(1, 2, 3), (2, 2, 4)

As you can see, we have gotten the original relations back.

Yes, it is lossless because the dependency A->B is not broken.