Environment Configuration: Altova XMLSpy

You will design and implement an XML database system to support the following operations of a simplified social networking Web site and you will use HTML for the user interface. Users, posts, etc. will now each be represented by separate 1-column DB2 tables whose columns will have the type XML. Each particular user, post, etc. will be represented by a separate XML document (and each such document will be a tuple in an appropriate table). XQuery can create the entire output in the desired HTML format, so you do not need iterators. Instead, you can just extract the HTML snippet created by XQuery into a string and send it to JSP.

Since, in our design, data is split into several independent XML documents, we cannot specify any interesting schematic aspects, like foreign keys, for these data snippets. Instead, after you are done with the database, pull all the pieces of data
together in one consolidated XML document (you might need to add additional tags to group users, walls, etc., together). Then create the appropriate schema (keys, keyrefs, and all the types) and validate your document against the schema using XML Spy: http://www.xmlspy.com

Implement and test the following queries:

1. For each user list the posts he has on the wall and all replies to these posts (including replies to replies – represent 2 levels down). Make the output clear, but not fancy. Something like:
* John Doe has the posts:
– “A photo with a day in my life”
reply by Mary John: “Is that you?”
reply to Mary John’s post by John Doe: “Of course, I was fishing”
reply to Mary John’s post by Mary John: “Did you catch anything?”
reply to Mary John’s post by John Doe: “Only a cold”
reply by Anne Chen: “Excellent photo”
reply by Bill Pop: “Lets go fishing again next week”
– “Got married”
reply by Mary John: “Congratulations”

2. For each user list the posts he liked and the other users who also liked that post.
* John Doe likes the posts:
– “I like potatoes” on Anne Chen’s wall, also liked by: Mary Doe and Joe Public.
– “Photo with Grand Canyon” on Bob Gia’s wall, also liked by: Joe Public and Bill Pop.

3. Find the user with the highest number of friends.

4. List all the connections up to three levels down of a user: his friends, his friends’ friends and their friends.

5. List all friends of a user with their number of friends.

6. Construct the mailbox of a given user: list all emails with Message ID, Date, Subject, Sender and Receivers, and the number of replies to each message.

7. Produce a summary listing of revenue generated by a particular user (you need only to implement a query that lists the set of products bought by a user and their total).


I. XML Schema:

1. Namespace

[Bookface.xsd] describe structure

<schema <!default namespace-no need prefix>

. xmlns=”http://www.w3.org/2001/XMLSchema” <!xmlns-default namespace, namespace for keywords>


<xs:schema <!xs-root element need prefix>

. xmlns:xs=http://www.w3.org/2001/XMLSchema&#8221; <!xs-prefix of xmlns(xml×)>



. xmlns:xs=”http://www.w3.org/2001/XMLSchema&#8221;

. xs:targetNamespace=”http://bookface.com“> <!xs-attribute need prefix, namespace defined by this schema, fake URL>

. <include xs:schemaLocation=”http://bookface.com/UserType.xsd”/&gt; <!include other schemas, must has same targetNamespace>


[BookfaceDB.xml] dataset

<DB xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance

<DB xmlns=”http://bookface.com<!defualt namespace=xs:targetNamespace in .xsb>

.        xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance&#8221; <!namespace for keywords>

.        xsi:schemaLocation=”http://bookface.com http://bookface.com/DB.xsd”> <!.xsd file location>

2. Simple Type

decimal, integer, boolean, string, float, date

.    (1) list

<simpleType name=”myIntList”>

.    <list itemType=”integer”/>


.    (2) union

<simpleType name=”phoneNumber”>

.    <union memberTypes=”phone7digits phone10digits”/>


.    (3) restriction

<simpleType name=”phone7digits”>

.    <restriction base=”integer”>

.        <minInclusive value=”1000000″/>

.        <maxInclusive value=”999999″/>

.        <pattern value=”s[0-9]{9}“/>

.        <enumeration value=”1″/>

.        <enumeration value=”2″/>

.    </restriction>


3. Complex Type

.    (1) sequence

<complexType name=”User”> <!included in element: no name>

.    <sequence> | <all>

.        <element name= , type= >

.        <element name= , type= >

.    </sequence>

.    <attribute name= , type= >


.    (2) all

.    (3) choice

<complexType name=”addressType”>

.    <sequence> <!no need>

.        <choice>

.            <element name= , type= > <!a>

.            <sequence> <!b>

.                <element name= , type= >

.                <element name= , type= >

.            </sequence>

.        </choice>

.    </sequence>

.    <attribute name= , type= >


4. Key

.    (1) key

<xs:key name=”UserPK”>
.    <xs:selector xpath=”DB/Users/User”/>
.    <xs:field xpath=”@UserID”/>

.    (2) keyref

<xs:keyref name=”Friend_OwnerFK” refer=”UserPK”>
.    <xs:selector xpath=”DB/Friends/Friend”/>
.    <xs:field xpath=”@OwnerID”/>

5. Design

.    (1) attribute vs. element

.          attribute: Primary Key <xs:attribute name=”UserID” type=”xs:integer”/>

.          element: other attributes <xs:element name=”FirstName” type=”xs:string”/>

.    (2) schema hierarchy structure: DB/Users/User

<xs:element name=”DB”>
.    <xs:complexType>
.        <xs:sequence>

.            <xs:element ref=”Users” maxOccurs=”unbounded”/>

.    (3) error: duplication of name

.    (4) prefix: xs:integer(schema defined)  addressType(user defined)

II. XQuery:

1. general structure: [Bookface1.xquery]

for $u2 in doc(“BookfaceDB.xml“)/DB/Users/User (:keywords-lowercase, doc(“current path”)/start from root element:)

where $cc/OwnerID = $u2/@UserID

return <ul>{data($u2/FirstName)}<ul>

2. well-formed:

.    <ul>{…}</ul> (:file start with tag:)

.    nested/stand side by side: within tag [Bookface4.xquery]

3. return form:

.    within {}: function, without {}: words

.    return output×<ul>output√{for…}</ul> (:output place within tag:) [Bookface1.xquery]

.    {data($u/FirstName)} (:function data()-only return data:)

.    <ul><il>tag, used in html

4. let: [Bookface3.xquery]

.    let $fs := for…where…return $u [Bookface]

5. aggregation:

.    sum: [Bookface7.xquery]

.    count: can’t use within for  [Bookface3.xquery]

let $fs := for…where…retun…

return count($fs)

.    max: [Bookface3.xquery]

let $maxf := max(for…where…return…) (:get maxvalue:)

if(count($fs) eq $maxf) then <ul>output</ul> else() (:search max argument:)

6. if then else: [Bookface3.xquery]

return (:must after return:)


then <ul>output</ul> (:can’t use return:)


7. >gt =eq

8. newline:

.    let $nl := “”

.    return <ul>{$nl}</ul>

9. comment: (:🙂

10. error:

.    result is empty-name typo

.    global variable vs. local variable

Database Structure