3. Consider the DTD below: <!ELEMENT Parties (Party*)> <!ELEMENT Party (Event, O

Question

3. Consider the DTD below:

<!ELEMENT Parties (Party*)>

<!ELEMENT Party (Event, OrganizerPhone?, ((Time, Place) | Recurring), Participant*)>

<!ELEMENT Participant (Name, Phone*,Email*)>

(Elements that are not defined are PCDATA.)

The order of all elements in the document is irrelevant.

Consider an instance (and XML document) X with 10 Party elements, each with one OrganizerPhone, a Time, a Place, and 20 Participants, each with exactly one Phone and one Email. Also consider the XQuery Q below:

for x in /Parties/Party[Place/text()="Tom’s house"],

     y in x/Participant[Phone/text() = x/OrganizerPhone/text()]

return y/Email/text()

(a) Design a relational schema S to store such XML data. Choose a good database design that takes advantage of the DTD.

(b) For each table in S indicate how many tuples it contains when we populate the database with X.

(c) Translate query Q into a SQL query over the schema S.

Explanation / Answer

a. There are two possible tables/entities: 1. Entity/Table Name: Parties Event, OrganiserPhone, Time, Place Primary Key: Event 2. Entity/Table Name: Participants Name, Phone, Email, Event. Unique Key:(Phone, Email) Foreign Key(references Parties): Event b. Given Number of Party elements = 10 => Number of rows in Parties table = 10 Given Number of Participants for each organiser phone = 20 => Number of rows in Participants table = 20 * 10 = 200 c. Equivalent query in SQL. SELECT Email, Phone from Participants, Parties where Participants.Phone = Parties.OrganiserPhone

Related Questions

Navigate

• Browse (All)
• Browse 3
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.