Solutions to the Homework 2, CSE444, fall 2003

1. a. Insurance (name, phone)

Vehicle (licencePlate, year, InsuranceCo.name, maxLiability, maxLossDamage, Person.ssn)

Car(Vehicle.licencePlate, make)

Truck(Vehicle.licencePlate, capacity, ProfessionalDriver.isa)

Person(ssn, name)

Driver(licenceNo, Person.ssn)

ProfessionalDriver (Driver.licenceNo, medicalHistory)

NonProfessionalDriver (Driver.licenceNo)

Drives(licencePlate, Driver.licenceNo)

b. The Insures relationship is many-one and its attributes along with the attributes of the InsuranceCo entity can be included in the Vehicle relation. If a vehicle entity does not have insurance, these attributes will be NULL in the Vehicle table.

c. Drives is many-many relationship and Operates is many-one. Drives requires an additional relation, while Operates can be encoded into the Truck relation.

d. Keys are represented in the schema for part A.

2. a. BCNF decomposition

i. R(A,B,C,D,E); D->B, CE->A

DCE is a minimal key

R violates BCNF since D is not a key

D+=DB and we split by D->B. The result is DB, DCEA

DCEA violates BCNF since CE is not a key.

CE+=CEA and we split by CE->A. The result is CEA, CED

CEA, CED do not violate BCNF.

Answer: DB, CEA, CED

ii. S(A,B,C,D,E); A->E, BC->A, DE->B

DCE, DCB, DCA are minimal keys

S violates BCNF since A is not a key

A+=AE and we split by A->E. The result is AE, ABCD

ABCD violates BCNF since BC is not a key

BC+=BCA and we split by BC->A. The result is BCA, BCD

BCA, BCD do not violate BCNF

Answer: AE, BCA, BCD

b. 3NF decomposition

i. R(A,B,C,D,E); D->B, CE->A

DCE is a minimal key

R violates 3NF since B is not a prime (part of a key)

D+=DB and we split by D->B. The result is DB, DCEA

DCEA violates 3NF since A is not a prime.

CE+=CEA and we split by CE->A. The result is CEA, CED

CEA, CED do not violate 3NF.

Answer: DB, CEA, CED

1. S(A,B,C,D,E); A->E, BC->A, DE->B

DCE, DCB, DCA are minimal keys

S does not violate 3NF since each right side of given FDs is a prime (part of a key)

Answer: ABCDE

3. b. select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.name = h2.name and h1.discount <> h2.discount)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.name = h2.name and h1.month <> h2.month)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.name = h2.name and h1.price <> h2.price)

(0 row(s) affected)

FD: name -> price

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.name <> h2.name and h1.discount = h2.discount)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.month <> h2.month and h1.discount = h2.discount)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.price <> h2.price and h1.discount = h2.discount)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.name <> h2.name and h1.month = h2.month)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.month = h2.month and h1.discount <> h2.discount)

(0 row(s) affected)

FD: month -> discount

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.price <> h2.price and h1.month = h2.month)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.name <> h2.name and h1.price = h2.price)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.discount <> h2.discount and h1.price = h2.price)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on (h1.month <> h2.month and h1.price = h2.price)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on

(h1.month = h2.month

and h1.price = h2.price

and h1.name <> h2.name)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on

(h1.month <> h2.month

and h1.discount = h2.discount

and h1.name = h2.name)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on

(h1.month <> h2.month

and h1.discount = h2.discount

and h1.price = h2.price)

(36 row(s) affected)

select count(distinct h1.name)

from hwk2 as h1

join hwk2 as h2 on

(h1.name <> h2.name

and h1.discount = h2.discount

and h1.name = h2.name)

(36 row(s) affected)

Answer: name -> price, month -> discount

c. create table NamePrice (name varchar(20) primary key, price money)

create table MonthDiscount (month varchar(20) primary key, discount varchar(10))

create table NameMonth (name varchar(20) foreign key references NamePrice,

month varchar(20) foreign key references MonthDiscount)

d. insert into NamePrice(name,price)

select distinct Sales.name , Sales.price from Sales

insert into MonthDiscount(month,discount)

select distinct Sales.month , Sales.discount from Sales

insert into NameMonth(name, month)

select Sales.month, Sales.name from Sales

group by month, name

4. a. <result>

{ for $x in document("mondial-3.0.xml")/mondial/country[@name="Peru"]//city/name/text()

order by $x

return

<c> { $x } </c>

}

</result>

- <result>

<c>Abancay</c>

<c>Arequipa</c>

<c>Ayacucho</c>

<c>Cajamarca</c>

<c>Callao</c>

<c>Cerro de Pasco</c>

<c>Chachapoyas</c>

<c>Chiclayo</c>

<c>Chimbote</c>

<c>Chincha Alta</c>

<c>Cusco</c>

<c>Cuzco</c>

<c>Huancavelica</c>

<c>Huancayo</c>

<c>Huanuco</c>

<c>Huaraz</c>

<c>Ica</c>

<c>Iquitos</c>

<c>Juliaca</c>

<c>Lima</c>

<c>Moquegua</c>

<c>Moyobamba</c>

<c>Piura</c>

<c>Pucallpa</c>

<c>Puerto Maldonado</c>

<c>Puno</c>

<c>Sullana</c>

<c>Tacna</c>

<c>Talara</c>

<c>Trujillo</c>

<c>Tumbes</c>

</result>

b. <result>

{ for $x in document("mondial-3.0.xml")/mondial/country[count(province)>20]/@name

order by $x

return

<c> { fn:string($x) } </c>

}

</result>

- <result>

<c>Argentina</c>

<c>Brazil</c>

<c>China</c>

<c>Colombia</c>

<c>Egypt</c>

<c>France</c>

<c>Hungary</c>

<c>India</c>

<c>Iran</c>

<c>Japan</c>

<c>Kazakstan</c>

<c>Mexico</c>

<c>Peru</c>

<c>Poland</c>

<c>Romania</c>

<c>Russia</c>

<c>Sweden</c>

<c>Switzerland</c>

<c>Taiwan</c>

<c>Tanzania</c>

<c>Thailand</c>

<c>Turkey</c>

<c>Ukraine</c>

<c>United Kingdom</c>

<c>United States</c>

<c>Venezuela</c>

<c>Vietnam</c>

</result>

c. <result>

{ for $m in document("mondial-3.0.xml")/mondial,

$e in distinct-values($m/country/ethnicgroups/text())

where count($m/country[ethnicgroups/text()=$e]) > 10

order by $e

return

<e> { $e } </e>

}

</result>

- <result>

<e>African</e>

<e>Arab</e>

<e>Chinese</e>

<e>European</e>

<e>Indian</e>

<e>Russian</e>

</result>

d. <result>

{ let $z:= for $x in document("mondial-3.0.xml")/mondial,

$s in $x/sea[@name="Pacific Ocean"],

$c in $s/located/@country,

$y in $x/country[@id=$c]

return xs:string($y/@name),

$u:= distinct-values($z)

for $r in $u

return <pacificCountry> { $r } </pacificCountry>

}

</result>

- <result>

<pacificCountry>Russia</pacificCountry>

<pacificCountry>Japan</pacificCountry>

<pacificCountry>Colombia</pacificCountry>

<pacificCountry>Costa Rica</pacificCountry>

<pacificCountry>Honduras</pacificCountry>

<pacificCountry>Panama</pacificCountry>

<pacificCountry>Peru</pacificCountry>

</result>

e. <result>

{ for $x in document("mondial-3.0.xml")/mondial,

$c in $x/country

where count($x/mountain[@height>2000][located/@country=$c/@id]) > 2

return <c> <name> { xs:string($c/@name) } </name>

{ for $y in $x/mountain[located/@country=$c/@id]

return <mountain>

<name> {xs:string($y/@name)}

</name>

<height> {xs:string($y/@height)}

</height>

</mountain>

}

</c>

}

</result>

- <result>

- <c>

<name>Sweden</name>

- <mountain>

<name>Kebnekaise</name>

<height>2114</height>

</mountain>

- <mountain>

<name>Sarektjokko</name>

<height>2090</height>

</mountain>

- <mountain>

<name>Portefjaellen</name>

<height>2021</height>

</mountain>

- <mountain>

<name>Tarrekaise</name>

<height>1850</height>

</mountain>

- <mountain>

<name>Sulitjelma</name>

<height>1914</height>

</mountain>

</c>

- <c>

<name>Tajikistan</name>

- <mountain>

<name>Pik Kommunizma</name>

<height>7494</height>

</mountain>

- <mountain>

<name>Pik Revoluzija</name>

<height>6974</height>

</mountain>

- <mountain>

<name>Pik Moskva</name>

<height>6785</height>

</mountain>

</c>

</result>

f. <user>

{for $x in document("mondial-3.0.xml")/mondial,

$r in $x/river[@length>2000]

return <river> <name> { xs:string($r/@name) } </name>

{ for $c in $x/country[@id=$r/located/@country]

return <country> {xs:string($c/@name)} </country>

}

</river>

}

</user>

- <user>

- <river>

<name>Volga</name>

<country>Russia</country>

</river>

- <river>

<name>Paraguay</name>

</river>

- <river>

<name>Parana</name>

</river>

- <river>

<name>Zaire</name>

<country>Zaire</country>

</river>

- <river>

<name>Amazonas</name>

<country>Colombia</country>

<country>Peru</country>

</river>

- <river>

<name>Jenissej</name>

<country>Russia</country>

</river>

- <river>

<name>Lena</name>

<country>Russia</country>

</river>

- <river>

<name>Ob</name>

<country>Russia</country>

</river>

- <river>

<name>Amur</name>

<country>Russia</country>

</river>

- <river>

<name>Kolyma</name>

<country>Russia</country>

</river>

- <river>

<name>Irtysch</name>

</river>

- <river>

<name>Ischim</name>

</river>

- <river>

<name>Donau</name>

<country>Hungary</country>

<country>Romania</country>

</river>

- <river>

<name>Dnepr</name>

</river>

- <river>

<name>Syrdarja</name>

<country>Kazakstan</country>

</river>

- <river>

<name>Ural</name>

</river>

- <river>

<name>Jangtse-Kiang</name>

</river>

- <river>

<name>Mekong</name>

</river>

- <river>

<name>Orinoco</name>

<country>Venezuela</country>

</river>

- <river>

<name>Mississippi</name>

</river>

- <river>

<name>Missouri</name>

</river>

- <river>

<name>Arkansas</name>

</river>

- <river>

<name>Colorado</name>

</river>

- <river>

<name>Rio Grande</name>

</river>

- <river>

<name>Ubangi</name>

<country>Zaire</country>

</river>

</user>