2015/11/17 CS 5200 Introduction to Database Management Fall 2015
http://www.ccs.neu.edu/home/kenb/15/fal/5200/5.html 1/3
CS 5200 Introduction to Database
Management Assignment #5
1. For each schedule:
a. Determine whether the schedule is view-serializable, conflict-serializable,
recoverable, cascadeless and/or strict. Note: you must state separately whether
each one of these holds.
b. For each of the 5 answers in the first part above, explain why or why not. In the case
of the two notions of serializability, you must either give an equivalent serial schedule
or a reason why the schedule is not serializable. For the other three concepts, you
must give an example of a violation if the schedule does not have the property.
The following are the schedules:
a. R3
(A), R3
(B), R1
(C), R2
(C), R2
(B), R3
(A), W3
(A), C2
, W1
(C), C1
, W4
(B), W3
(A), W4
(B), C3
,
W4
(A), W4
(C), C4
b. W4
(C), W3
(C), W2
(A), R2
(B), R3
(A), W4
(A), W1
(A), C4
, C2
, W1
(C), R1
(A), R3
(B), C3
,
R1
(B), C1
c. R2
(B), R4
(A), R2
(C), W2
(B), W3
(C), R1
(B), W4
(C), R2
(A), C4
, C2
, R1
(B), W1
(A), W3
(B),
R3
(A), C3
, C1
d. R2
(A), R2
(B), C2
, R3
(B), W4
(B), W1
(A), R4
(C), R3
(A), R1
(A), C1
, R4
(C), W4
(A), W3
(B),
R4
(C), R3
(A), C3
, R4
(A), R4
(C), C4
2. This problem using the following data model:
The following is an XML schema for representing data in the data model above:
<schema xmlns=’http://www.w3.org/2001/XMLSchema’>
<element name=’Company’>
<complexType>
<choice minOccurs=’0′ maxOccurs=’unbounded’>
<element ref=’Factory’/>
<element ref=’ProductType’/>
2015/11/17 CS 5200 Introduction to Database Management Fall 2015
http://www.ccs.neu.edu/home/kenb/15/fal/5200/5.html 2/3
</choice>
</complexType>
</element>
<element name=’ProductType’>
<complexType>
<choice minOccurs=’0′ maxOccurs=’unbounded’>
<element ref=’Repairer’/>
<element ref=’Product’/>
</choice>
<attribute name=’title’ type=’string’ use=’optional’/>
<attribute name=’description’ type=’string’ use=’required’/>
<attribute name=’size’ type=’integer’ use=’required’/>
</complexType>
</element>
<element name=’Repairer’>
<complexType mixed=’true’>
<attribute name=’qualification’ use=’required’>
<simpleType>
<restriction base=’string’>
<enumeration value=’novice’/>
<enumeration value=’intermediate’/>
<enumeration value=’expert’/>
</restriction>
</simpleType>
</attribute>
</complexType>
</element>
<element name=’Product’>
<complexType>
<attribute name=’label’ type=’string’ use=’optional’/>
<attribute name=’started’ type=’date’ use=’required’/>
<attribute name=’completed’ type=’date’ use=’optional’/>
<attribute name=’processedBy’ type=’IDREF’ use=’optional’/>
</complexType>
</element>
<element name=’Factory’>
<complexType>
<sequence minOccurs=’0′ maxOccurs=’unbounded’>
<element ref=’Machine’/>
</sequence>
<attribute name=’address’ type=’string’ use=’required’/>
</complexType>
</element>
<element name=’Machine’>
<complexType>
<attribute name=’id’ type=’ID’ use=’required’/>
<attribute name=’code’ type=’string’ use=’required’/>
</complexType>
</element>
</schema>
Here is an example of an XML document for the XML schema
<Company>
<Factory address=”100 Main Street”>
<Machine id=”M1″ code=”101″/>
<Machine id=”M2″ code=”102″/>
2015/11/17 CS 5200 Introduction to Database Management Fall 2015
http://www.ccs.neu.edu/home/kenb/15/fal/5200/5.html 3/3
</Factory>
<ProductType title=”Carpet” description=”Shag Rug” size=”50″>
<Product started=”2015-10-27″ processedBy=”M1″/>
<Repairer qualification=”novice”>John Doe</Repairer>
<Repairer qualification=”expert”>Divya Bhaskar</Repairer>
<Product started=”2015-10-29″ label=”Green Carpet”/>
</ProductType>
<Factory address=”101 Main Street”>
<Machine id=”N1″ code=”101″/>
<Machine id=”N2″ code=”102″/>
</Factory>
</Company>
Suppose that we have a database with the following schema:
create table Company(
id int primary key auto_increment;
name varchar(200) not null,
factories text
);
Solve the following problems:
a. Find the names of all experts who can repair a product with size at least 50 from the
company named “Acme”.
b. List the labels of all products that have not yet been completed and have a repairer
who is at least at the intermediate level from any company.
c. List the addresses of the factories whose second machine has a code starting with
“10” from companies whose name ends in “Inc”.
d. Find the average size of all products that are currently being produced.
Express your queries using SQL. The queries must correctly retrieve the specified data for
any consistent state of the database (i.e., any state of the database that satisfies all of the
constraints specified in the SQL schema and the XSD schema).
An item is incomplete when the completed date is either unspecified or strictly later than
the current date. A date is current if the started date is earlier than or equal to the current
date and the item is incomplete. The current date is current-date(). Not all XPath
processors support the current-date function. In this case, one can test your query by
using the actual current date. However, in your submission use the current-date function.
A node set is automatically converted to boolean if it occurs in a boolean context. In this
case, it is true when the node set is nonempty. In other words, it acts like the SQL “exists”
operator. One can explicitly convert to boolean by using the boolean() function. In
XPath, true is written true() and false is written false().