=========================preview======================
(COMP231)midterm01F_sol.pdf
Back to COMP231 Login to download
======================================================
.
COMP.231.Database.Management.Systems. Name:. .
Midterm.Examination. Student.ID:. .
October.26,.2001.10:00.C.10:45. Lab.section:. .
.

1).[5].Given.a.relation.R,.which.of.the.followings.can.uniquely.identify.the.tuples.in.R?.Circle.the. correct.one(s)..
-. partial.key.of.R.
.

. 2).[30].The.following.relation.records.the.biological.father.and.mother.of.people..If.a.person.has.a. child,.his.person_id.will.be.used.as.the.childs.father_id.or.mother_id,.depending.on.the.sex.of.the. person.. . . Parent.(.person_id,.father_id,.mother_id.). .
i). [8].Write.an.SQL.query.to.retrieval.the.grandfather.of.John.Lee.of.the.person.with.
persion_id=1234..

. In.English,.both.the.father.of.your.father.and.the.father.of.your.mother.are.your.grandfather..The. answer.above.is.enough..I.provide.the.complete.solution.for.your.reference.. .
select.p.father_id.
from.Parent.c,.Parent.p.
where.(.c.father_id=p.person_id.
or......c.mother_id=p.person_id.).
and...c.person_id=1234.

. Alternative,.you.can.get.the.father.of.your.father.and.the.father.of.your.mother.with.two.SQL. statements.and.union.the.result.together:. . (.select...c.father_id=p.person_id.). UNION.. (.select...c.mother_id=p.person_id.). .
ii). [7].Answer.I).using.tuple.relation.calculus..
.

{.p[father_id].|.p..Parent..(..c..Parent).
(c[father_id]=p[person_id]..c[person_id]=1234.}.
.

You.can.use.either.notations:.c[father_id].or.c.father_id..
. iii). [15].Write.an.SQL.query.to.retrieval.all.the.descendents.(children,.grand.children,.great.grand. children,.etc).of.Mary.Cheung..of.the.person.with.persion_id=1234..


Since.this.query.requires.recursion,.you.need.to.use.temporary.variable.and.loop.to.compute.the.result.. That.is,.you.need.embedded.SQL..
. Embedded.SQL.is.used.(regardless.of.correctness):..7.pts. EXEC.SQL. Declaration:.4.pts. ...declare.c.cursor.for. Looping.and.use.of.variables:.4.pts. ...select.person_id. ...from.Parent. ...where.mother_id=:id.
...or....father_id=:id. This.gets.the.sons.and.daughters.of.1234.and. END-EXEC.
recursively.1234s.grand.sons.and.grand.daughter... . I.wont.deduct.any.points.if.the.students.only.gets.the. .
male.branch.only.or.the.female.branch.only..
.
.

id-list=append(1234).
..
while.(.id-list.is.not.empty.).{.
...id=get-element(id-list).
...EXEC.SQL.open.c.END-EXEC..
...EXEC.SQL.fetch.c.into.:descendent-id.END-EXEC.
...while.(.result.tuple.exist.).{.
......printf.(%d\n,.descendent-id);.
......id-list=append(descendent-id).
......EXEC.SQL.fetch.c.into.:descendent-id.END-EXEC.
...}.
}.
EXEC.SQL.close.c.END-EXEC.

.
.
.
I.use.a.combination.of.C.and.pseudo.code.syntax,.but.any.understandable.pseudo.code.is.OK..

.
I.also.assume.that.the.variables.id.and.descendent.are.defined.elsewhere.

.
I.assume.that.a.list.structure.(id-list).has.been.implemented,.the.append.function.appends.an. element.at.the.end.of.the.list.and.the.get-eleme