数据库系统基础教程答案ch4 下载本文

4.9.7

class Students (key email) { attribute string email; attribute string name; relationship Courses isTA inverse Courses::TA;

relationship Courses Takes inverse Courses::TakenBy; };

class Professors (key email) { attribute string email; attribute string name;

relationship Departments WorksFor inverse Department::Works; relationship Courses Teaches inverse Courses::TaughtBy; };

class Courses (key (no,semester,section)) { attribute string no;

attribute string semester; attribute string section; relationship Students TA inverse Students::isTA;

relationship Students TakenBy inverse Students::Takes;

relationship Professors TaughtBy inverse Professors::Teaches;

relationship Departments OfferedBy inverse Departments::Offer; };

class Departments (key name) { attribute name;

relationship Courses Offer inverse Courses::OfferedBy; relationship Professors Works inverse Professors::WorksFor; };

4.9.8

A relationship is its own inverse when for every attribute pair in the

relationship, the inverse pair also exists. A relation with such a relationship is called symmetric in set theory. e.g. A relationship called SiblingOf in Person relation is its own inverse.

4.10.1 a)

Customers(ssNo,name,addr,phone) Account(number,type,balance) Owns(ssNo,accountNumber) b)

Accounts(number,balance,type,owningCustomerssNo) Customers(ssNo,name)

Addresses(ownerssNo,street,state,city)

Phones(ownerssNo,street,state,city,phonearea,phoneno)

We can remove Addresses relation since its attributes are a subset of relation Phones.

c)

Fans(name,colors)

RootedBy(fan_name,teamname) Admires(fan_name,playername)

Players(name,teamname,is_captain)

Teams(name)--remove subset of teamcolor Teamcolors(name,colorname) Colors(colorname) d)

class Person { attribute string name; relationship Person motherOf inverse Person::childrenOfFemale; relationship Person fatherOf inverse Person::childrenOfMale; relationship Set children inverse Person::parentsOf; relationship Set childrenOfFemale inverse Person::motherOf; relationship Set childrenOfMale inverse Person::fatherOf; relationship Set parentsOf inverse Person::children; };

Person(name,mothername,fathername)

The children relationship is many-many but the information can be deduced from Person relation. Hence below relation is redundant. Parent-Child(parent, child)

4.10.2

First consider each struct as if it were an atomic value i.e. key and value

association pairs can be treated as two attributes. After applying normalization, the attributes can be replaced by the fields of the structs.

4.10.3 (a)

Struct Card { string rank, string suit }; (b)

class Hand {

attribute Set theHand; }; (c)

Hands(handId, rank, suit)

Each tuple corresponds to one card of a hand. HandId is required key to identify a hand.

(d) Hand contains an array of 5 elements

class PokerHand{attribute Array Hand(Card card1,Card card2,Card card3,Card card4,Card card5)}

PokerHandS(handId,rank1,suit1,,rank2,suit2,rank3,suit3,rank4,suit4,rank5,suit5) (e)

class Deal {

attribute Set theDeal; }

(f) PokerDeal consist of a player and array of five card deal.

class PokerDeal{string Player,attribute Array Hand(Card card1,Card card2,Card card3,Card card4,Card card5)}

(g) Above can similarly be represented by key player and a value consisting of five element array. (h)

dealID is a key for Deals. Thus the relations for classes Deals and Hands are: Deals(dealID, player, handID) Hands(handID, rank, suit)

A simpler relation Deals below can also represents the classes: Deals(dealID, player, rank, suit) (i)

The relation Deals(dealID,card) cannot identify the hand to which a card belongs. Also two attributes are required for a card;its rank and suit.

Deals(dealID, handID, rank, suit)

4.10.4 (a)

C(a, f, g) (b)

C(a, f, g, count) (c)

C(a, f, g, position) (d)

C(a, f, g, i, j)