CHAPTER 7: RELATIONAL DATABASE DESIGN BY ER- AND EER-TO-RELATIONAL MAPPING

 

Answers to Selected Exercises

 

7.3 Try to map the relational schema of Figure 6.12 into an ER schema. This is part of

a process known as reverse engineering, where a conceptual schema is created for

an existing implemented database. State any assumptions you make.

 

Answer:

 

 

Note: We represented BOOK_AUTHORS as a multi-valued attribute of BOOK in the above

ER diagram. Alternatively, it can be represented as a weak entity type.

 

7.4 Figure 7.7 shows an ER schema for a database that may be used to keep track of

transport ships and their locations for maritime authorities. Map this schema into

a relational schema, and specify all primary keys and foreign keys.

 

Answer:

 

SHIP

SNAME OWNER TYPE PNAME

SHIP_TYPE

TYPE TONNAGE HULL

STATE_COUNTRY

NAME CONTINENT

SEAOCEANLAKE

NAME

SHIP_MOVEMENT

SSNAME DATE TIME LONGITUDE LATITUTE

PORT

S_C_NAME PNAME S_O_L_NAME

VISIT

VSNAME VPNAME STARTDATE ENDDATE

f.k.

f.k.

f.k. f.k. f.k.

f.k.

f.k.

 

7.5 Map the BANK ER schema of Exercise 3.23 (shown in Figure 3.17) into a

relational schema. Specify all primary keys and foreign keys. Repeat for the AIRLINE schema (Figure 3.16) of Exercise 3.19 and for the other schemas for Exercises 3.16 through 3.24.

 

Partial Answer:

 

BANK

CODE NAME ADDR

ACCOUNT

ACCTNO BALANCE TYPE BCODE BNO

CUSTOMER

SSN NAME PHONE ADDR

LOAN

LOANNO AMOUNT TYPE BCODE BNO

BANK_BRANCH

BCODE BRANCHNO ADDR

A_C

SSN ACCTNO

L_C

SSN LOANNO

f.k.

f.k. f.k.

f.k.

f.k. f.k.

f.k.

f.k. f.k.