数据库系统基础教程第四章答案

Solutions

Chapter 4 4.1.1

a)

b)

c)

In c we assume that a phone and address can only bel ong to a sin gle customer (1-

d)

In d we assume that an address can only bel ong to one customer and a phone can exist at only one address.

If the multiplicity of above relati on ships were m-to-n, the en tity set becomes weak and the key ssNo of customers will be needed as part of the composite key of the en tity set.

In c&d, we convert attributes phones and addresses to entity sets. Since entity sets often become relations in relational design, we must con sider more efficie nt alter natives.

In stead of query ing multiple tables where key values are duplicated, we can also modify attributes:

(i) Phones attribute can be con verted into HomePho ne, OfficePh one and CellPh one.

(ii) A multivalued attribute such as alias can be kept as an attribute where a sin gle colu mn can be used in relati onal desig n i.e. con cate nate all values. SQL

4.1.3

4.1.4

a)

c)

The relati on ship "played" betwee n Teams and Players is similar to relati on

ship "plays" betwee n Teams and Players.

b) 1 >: i .-I T mains Oo L UJT 曰 Pla.yor3

uid^r

4.1.6 The in formatio n about childre n can be ascerta ined from motherOf and fatherOf relati on ships. Attribute ssNo is required since n ames are not uni que.

People

4.1.7

4.1.8

a)

(b)

Family

People

4.1.9

Assumpti ons

A Professor only works in at most one departme nt.

A course has at most one TA.

A course is only taught by one professor and offered by one departme nt. Stude nts and professors have bee n assig ned uni que email ids.

A course is uniquely identified by the course no, section no, and semester (e.g. CS157-3 spring 09).

4.1.10

Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one studio.

But stars could be unemployed at a given time. Thus the four-way relationship in fig 4.6 can be easily into converted equivalent relationships.

421

Redu ndan cy: The owner address is repeated in AccSets and Addresses en tity sets. Simplicity: AccSets does not serve any useful purpose and the desig n can be more simply represe nted by creati ng man y-to-ma ny relati on ship betwee n Customers and Accou nts.

Right kind of element: The entity set Addresses has a single attribute address. A

customer cannot have more tha n one address.

Hence address should be an attribute of en tity set Customers.

Faithfulness: Customers cannot be uniquely identified by their names. In real world

Customers would have a unique attribute such as ssNo or customerNo

4.2.2

Studios and Preside nts can be comb ined into one en tity set Studios with Preside

nts beco ming an attribute of Studios un der follow ing circumsta nces: 1. The

Presidents entity set only contains a simple attribute viz. presidentName. Additi onal

attributes specific to Preside nts might justify making Preside nts into an en tity set.

4.2.4 The en tity sets should have sin gle attribute.

a) Stars: starName

b) Movies: movieName

c) Studios: studioName. However there exists a man y-to-ma ny relati on ship

betwee n Studios and Con tracts. Hen ce, in additi on, we n eed more in formati on

about studios in volved. If a con tract always in volves two studios, two attributes

such

as produc in gStudio and starStudio can replace the

Studios en tity set. If a con tact can be associated with at most five studios, it may

be possible to replace the Studios en tity set by five attributes viz. studio1, studio2,

studio3, studio4, and studio5. Alter nately, a composite attribute containing con cate

nati on of all studio n ames in a con tact can be con sidered. A separator character

such as "$" can be used. SQL allows search ing of such an attribute using query like

'%keyword%'

4.2.5

From Augme ntati on rule of Fun cti onal Depe ndency, give n

B -> M (B=Baby, M=Mother)

4.2.3

Movies S匸