/*** Query 1:find the organization unit that has the most people ***/
define function max_count() returns integer
{
let $b := ( distinct-values(document("SampleData.xml")//OrgUnit) )
return
max(
for $c in $b
return
count(
for $p in document("SampleData.xml")//Person
where $p/OrgUnit = $c
return $p
)
)
}
let $maxnum := max_count()
let $ou := ( distinct-values(document("SampleData.xml")//OrgUnit) )
for $o in $ou
let $num := count(
for $p in document("SampleData.xml")//Person
where $p/OrgUnit = $o
return $p
)
where $num = $maxnum
return $o
/* Result of Query 1 for sample data */
NY Company
/*** Query 2:list all information of people who are not members of any project or authors of any product ***/
let $a := document("SampleData.xml")//Author
let $m := document("SampleData.xml")//Member
for $p in document("SampleData.xml")//Person
where not($p/@SSN = $a/@SSN or $p/@SSN = $m/@SSN)
return $p
/* Result of Query 2 for sample data */
JudyNY Companyjudy@nyc.com505 East Loop RoadJetBoston Companyjet@bc.com502 East Loop Road
/*** Query 3:find the total amount of fundings of a project (assuming that the amount is divided equally among multiple projects with the same funding) ***/
define function get_fundamount() returns element*
{
for $f in document("SampleData.xml")//Funding
let $num := count(
for $proj in document("SampleData.xml")//Project
where some $mf in $proj/projFunding satisfies $mf/@id = $f/@id
return $proj
)
return
}
let $fs := get_fundamount()
for $proj in document("SampleData.xml")//Project
let $g := sum(
for $f in $fs
where some $my in $proj/projFunding satisfies $my/@id = $f/@id
return $f/@amount
)
return
/* Result of Query 3 for sample data */
/*** Query 4:list names of all products of any project that has a funding with the most recent start date ***/
define function get_maxdatefund() returns element*
{
for $f in document("SampleData.xml")//Funding
where $f/Startdate = max(for $s in document("SampleData.xml")//Startdate
return $s)
return $f
}
let $maxf := get_maxdatefund()
for $proc in document("SampleData.xml")//Product
let $g := (
for $p in document("SampleData.xml")//Project
where some $mp in $p/projProduct/@id satisfies $mp = $proc/@id
return $p
)
where some $my in $g satisfies $my/projFunding/@id = $maxf/@id
return
/* Result of Query 4 for sample data */
/*** Query 5:find the source that funded every project whose all products' names are contained in the general description ***/
define function get_descprocs() returns element*
{
for $proc in document("SampleData.xml")//Product
for $g in document("SampleData.xml")//gendescript
where contains($g/text(), $proc/@id)
return $proc
}
define function get_projs() returns element*
{
let $procs := get_descprocs()
for $proj in document("SampleData.xml")//Project
where every $my in $proj/projProduct satisfies $my/@id = $procs/@id
return $proj
}
let $projs := get_projs()
for $f in document("SampleData.xml")//Funding
where every $p in $projs satisfies $f/@id = $p/projFunding/@id
return $f
/* Result of Query 5 for sample data */
3000001995-11-021996-07-02
/*** Constraint 4:each product of a project has at least one author that is a member of the project ***/
for $pj in document("SampleData.xml")//Project
for $pr in (
for $prod in document("SampleData.xml")//Product
where $pj/projProduct/@id = $prod/@id
return $prod
)
where not(some $a in $pr/Author satisfies $a/@SSN = $pj/Member/@SSN)
return
/* Result of Constraint 4 for sample data */
/* The output tells us that the sample data violate the constraint 4. The product "prod3" in the project
"P3" doesn't satisfy the constraint */