Advanced DBMS (CSC-302) Instructor: Huda Sarfraz
Midterm Test No. 1 Max Marks: 50
Date: 23-Oct-2017 Time Allowed: 1 hr
Name: _________________________________________
Multiple choice questions should be answered on the question paper. All other questions should be answered on the answer booklet.
1. This question is in the context of the big data documentary and the data sets on Kaggle. Based on these two and optionally additional research, you were required to come up with a big data solution. Write down responses to the following in order to explain your idea. Each response should be 4-5 lines long at the maximum (10 marks).
a. Clearly define the problem for which you are going to propose a big data solution (2). b. Briefly describe your solution. Note that the details are required in the next two questions
and write your response accordingly (2).
c. Either identify a data source to implement your solution, or explain how you will build a data set to implement your solution (2).
d. Describe how the data will be used to solve the problem? Mention any existing tools and technologies that you think can be used to analyze the data and draw useful inferences from it (2).
e. How would you justify that your solution qualifies as a big data solution (2)?
2. Create a JSON data instance (make your own data sample) that validates against the following schema (5 marks).
{
"type": "object", "properties": {
"street_address": { "type": "string" }, "city": { "type": "string" }, "state": { "type": "string" } },
"required": ["street_address", "city", "state"] }
{
"street_address": "1600 Pennsylvania Avenue NW", "city": "Washington",
"state": "DC" }
3. Given the following XML Schema and information for a purchase order, create XML data for the order that conforms to the Schema. Note that the <xs:annotation> is specifying Schema
comments and should be ignored (like comments in code). The pattern used at the end (\d{3}-[A-Z]{2}) indicates strings of the form 526-DE (three digits, a hyphen, and then two letters). <xs:Schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://tempuri.org/po.xsd"
xmlns="http://tempuri.org/po.xsd" elementFormDefault="qualified"> <xs:annotation>
<xs:documentation xml:lang="en">
Purchase order Schema for Example.com.
Copyright 2000 Example.com. All rights reserved. </xs:documentation>
</xs:annotation>
<xs:element name="purchaseOrder" type="PurchaseOrderType"/> <xs:element name="comment" type="xs:string"/>
<xs:complexType name="PurchaseOrderType"> <xs:sequence>
<xs:element name="shipTo" type="USAddress"/> <xs:element name="billTo" type="USAddress"/> <xs:element ref="comment" minOccurs="0"/> <xs:element name="items" type="Items"/> </xs:sequence>
<xs:attribute name="orderDate" type="xs:date"/> </xs:complexType>
<xs:complexType name="USAddress"> <xs:annotation>
<xs:documentation>
Purchase order Schema for Example.Microsoft.com.
Copyright 2001 Example.Microsoft.com. All rights reserved. </xs:documentation> <xs:appinfo> Application info. </xs:appinfo> </xs:annotation> <xs:sequence>
<xs:element name="name" type="xs:string"/> <xs:element name="street" type="xs:string"/> <xs:element name="city" type="xs:string"/> <xs:element name="state" type="xs:string"/> <xs:element name="zip" type="xs:decimal"/> </xs:sequence>
<xs:attribute name="country" type="xs:NMTOKEN" fixed="US"/>
</xs:complexType>
<xs:complexType name="Items"> <xs:sequence>
<xs:element name="item" minOccurs="0" maxOccurs="unbounded"> <xs:complexType>
<xs:element name="productName" type="xs:string"/> <xs:element name="quantity"> <xs:simpleType> <xs:restriction base="xs:positiveInteger"> <xs:maxExclusive value="100"/> </xs:restriction> </xs:simpleType> </xs:element>
<xs:element name="USPrice" type="xs:decimal"/> <xs:element ref="comment" minOccurs="0"/>
<xs:element name="shipDate" type="xs:date" minOccurs="0"/> </xs:sequence>
<xs:attribute name="partNum" type="SKU" use="required"/> </xs:complexType>
</xs:element> </xs:sequence> </xs:complexType>
<!-- Stock Keeping Unit, a code for identifying products --> <xs:simpleType name="SKU"> <xs:restriction base="xs:string"> <xs:pattern value="\d{3}-[A-Z]{2}"/> </xs:restriction> </xs:simpleType> </xs:Schema> Purchase Order
Shipping Address Billing Address Alice Smith 123 Maple Street Mill Valley CA 90952 US Robert Smith 8 Oak Avenue Old Town PA 95819 Comment: Hurry, my lawn is going wild!
Item 1 Item 2
Part number: 872-AA Product name: Lawnmower Quantity: 1
Price: 148.95 USD
Comment: Confirm this is electric
Part number: 926-AA Product name: Baby Monitor Quantity: 1
Price: 39.98 USD
<?xml version="1.0"?>
<purchaseOrder xmlns="http://tempuri.org/po.xsd" orderDate="1999-10-20"> <shipTo country="US">
<name>Alice Smith</name>
<street>123 Maple Street</street> <city>Mill Valley</city>
<state>CA</state> <zip>90952</zip> </shipTo>
<billTo country="US">
<name>Robert Smith</name> <street>8 Oak Avenue</street> <city>Old Town</city>
<state>PA</state> <zip>95819</zip> </billTo>
<comment>Hurry, my lawn is going wild!</comment> <items>
<item partNum="872-AA">
<productName>Lawnmower</productName> <quantity>1</quantity>
<USPrice>148.95</USPrice>
<comment>Confirm this is electric</comment> </item>
<item partNum="926-AA">
<productName>Baby Monitor</productName> <quantity>1</quantity> <USPrice>39.98</USPrice> <shipDate>1999-05-21</shipDate> </item> </items> </purchaseOrder>
4. Write a DTD for a collection of recipes, where
a. recipes consist of ingredients, steps for preparation, possibly some comments, and a specification of its nutrition
b. an ingredient can be simple or composite
c. a simple ingredient has a name, an amount (possibly unspecified), an a unit (unless amount is dimensionless)
d. a composite ingredient is recursively a recipe with ingredients and preparation
A sample recipe which will be marked up using your DTD is given in Figure 1 below. This markup is not required as part of the answer, but it is recommended that you do it for your own
understanding. The DTD/XML file used as the example during class is given Figure 2 as a syntax reference.
(20 marks) Beef Parmesan with Garlic Angel Hair Pasta
Ingredients
1 onion, sliced into thin rings 1 green bell pepper, sliced in rings 1 cup Italian seasoned bread crumbs 1/2 cup grated Parmesan cheese 2 tablespoons olive oil
1 (16 ounce) jar spaghetti sauce 1/2 cup shredded mozzarella cheese 12 ounces angel hair pasta
2 teaspoons minced garlic 1/4 cup butter
Directions
Preheat oven to 350 degrees F (175 degrees C).
Cut cube steak into serving size pieces. Coat meat with the bread crumbs and parmesan cheese. Heat olive oil in a large frying pan, and saute 1 teaspoon of the garlic for 3 minutes. Quick fry (brown quickly on both sides) meat. Place meat in a casserole baking dish, slightly overlapping edges. Place onion rings and peppers on top of meat, and pour marinara sauce over all.
Bake at 350 degrees F (175 degrees C) for 30 to 45 minutes, depending on the thickness of the meat. Sprinkle mozzarella over meat and leave in the oven till bubbly.
Boil pasta al dente. Drain, and toss in butter and 1 teaspoon garlic. For a stronger garlic taste, season with garlic powder. Top with grated parmesan and parsley for color. Serve meat and sauce atop a mound of pasta! HINT: make the meat ahead of time, and refrigerate over night, the acid in the tomato sauce will tenderize the meat even more. If you do this, save the mozzarella till the last minute.
Calories 1167 Protein 71g Fat 52g
Carbohydrates 101g
Figure 1: Sample recipe <?xml version="1.0" encoding="UTF-8"?>
<!-- Bookstore with DTD --> <!-- Try changes: --> <!-- Make Edition required --> <!-- Swap order of First_Name, Last_Name --> <!-- Add empty Remark --> <!-- Add Magazine, omit closing tag --> <!DOCTYPE Bookstore [
<!ELEMENT Bookstore (Book | Magazine)*> <!ELEMENT Book (Title, Authors, Remark?)> <!ATTLIST Book ISBN CDATA #REQUIRED
Price CDATA #REQUIRED Edition CDATA #REQUIRED> <!ELEMENT Magazine (Title)>
<!ATTLIST Magazine Month CDATA #REQUIRED Year CDATA #REQUIRED> <!ELEMENT Title (#PCDATA)>
<!ELEMENT Authors (Author+)> <!ELEMENT Remark (#PCDATA)>
<!ELEMENT Author (First_Name, Last_Name)> <!ELEMENT First_Name (#PCDATA)>
<!ELEMENT Last_Name (#PCDATA)> ]>
<Bookstore>
<Magazine Month="jan" Year="2011"> <Title>National Geographic</Title> </Magazine>
<Book ISBN="ISBN-0-13-713526-2" Price="85" Edition="3rd"> <Title>A First Course in Database Systems</Title> <Authors> <Author> <First_Name>Jeffrey</First_Name> <Last_Name>Ullman</Last_Name> </Author> <Author> <First_Name>Jennifer</First_Name> <Last_Name>Widom</Last_Name> </Author> </Authors> <Remark></Remark> </Book>
<Book ISBN="ISBN-0-13-815504-6" Price="100" Edition="2nd"> <Title>Database Systems: The Complete Book</Title> <Authors> <Author> <First_Name>Hector</First_Name> <Last_Name>Garcia-Molina</Last_Name> </Author> <Author> <First_Name>Jeffrey</First_Name> <Last_Name>Ullman</Last_Name> </Author> <Author> <First_Name>Jennifer</First_Name> <Last_Name>Widom</Last_Name> </Author> </Authors> <Remark>
Buy this book bundled with "A First Course" - a great deal! </Remark>
</Bookstore>
Figure 2: Sample DTD/XML (for syntax reference)
Marking scheme
Multiple recipes in a collection 2 Recipe includes title, ingredients,
preparation, comments and nutrition
1
Two types of ingredients 1
Simple Ingredient has a name, amount and unit
1 Indications of ingredients which are
recursively recipes
1 Preparation is made up of multiple steps 1
Nutrition has some breakdown 1
Title of recipe ends in PCDATA 1
