# B561-mideterm-2012-session-2-solution

Document Sample

```					                                                                                                11:15 session

B561 Midterm Exam - Fall 2012
Solutions

Part 1 (60 points)
Design an ER model for an online insurance broker. Assume that the following information is
what you gathered by interviewing staffs in the insurance office:
 Each vehicle has a unique vehicle identification number (VIN) that uniquely identify this
vehicle in the world. A person may own more than one vehicle.
 Auto insurance policies are sold to a person, for driver(s), with respect to a specific vehicle.
The person who purchases auto insurance is not necessarily the driver of this vehicle. For
each driver who is to be insured, for insurance company to determine the insurance premium
for auto insurance, it needs to know about the driver’s driving history, for example, numbers
of accidents, numbers of traffic tickets, etc.
 For each insurance policy, the customer can chose to pay the lump sum, or by monthly
payment
Your model should be as simple and concise as possible. You need to make some decision in
your design. Any “non-obvious” choices you make in your model should be explained.

pID
firstName                                                                                        VIN
lastName           People                              Own                                      make
birthDate                                                                                       model
year
color

Purchase
Vehicles
ISA                           _policies

accidentNum
payMethod
Drivers

Some explanations:

1. Ternary relationship for "purchase_policies": we choose to model "purchase_policies" as a ternary
relationship, because it is implied in the question that it is an activity involving people(customer), drivers
and vehicles. Another choice would be modeling it as an entity, but since it does not participate in any
other relationships, we do not do it here.
2. Hierarchy: The ISA hierarchy here models naturally the real world relationship between people and
drivers, since we do not require the one who buys the policy to be a driver, but a driver must be present in
the "purchase_policies" relationship.
3. Participation constraint: Although not specified directly in the question, the real world situation
requires that each vehicle that has activity in the online broker should have an owner.

1
11:15 session

Part 2 (40 points) Consider a relational schema R = <U, F>, where
U= (M, N, P, S, T), and F = {NST, MNPS, MNPS}.

Question 2.1    Compute Fc. (20 points)

Step 1 result: {N→S, N→T, M→N, M→P, M→S, MN→P, MN→S}

Step 2: remove MN→P and MN→S, because M→N
result: {N→S, N→T, M→N, M→P, M→S}

Step 3: remove M→S because M→N and N→S.
result: {N→S, N→T, M→N, M→P}

Fc ={N→S, N→T, M→N, M→P}

Question 2.2    Determine whether relation R satisfies the 3rd normal form. Explain why (20 points)

R does not satisfy the 3rd normal form.

M is the only candidate key.

N→S (or N→T) is not trivial;
N is not a super key;
S (or T) is not a primary attribute.

2
11:15 session

Part 3 (60 points + 20 bonus points)
Given the following relational schema:

Articles (aid, title, language, year, conf)
Conf: Char(20) is an attribute that describes the conference in which the article is published.
Universities (uid, name, state, country, rank)
Rank:integer is an attribute that describes the ranking of a university.
People (pid, name, gender, age, university)
Gender:Char(1) is an attribute of type Char(1), taking values “F” or “M”
University is a foreign key, referring to Universities(uid).
Write (pid, aid)
Pid is a foreign key, referring to People(pid)
Aid is a foreign key, referring to Article(aid)

Please write queries in the query languages specified to satisfy the following query requests. (15 points
per query)

Question 3.1 Find the articles in the 2012 ICDE conference such that a male author and a female
author are not from Indiana University.

RA:

)

(    (            (                                                                                     )))))
)
∩

)

(    (            (                                                                                     )))))
)

Question 3.2 Find the universities that have employee(s) who published article(s) in the SIGMOD
conference before 2009.

TRC:

))

3
11:15 session

Question 3.3    Find those who published at least three conference articles in a year, return the names.
SQL:
SELECT P.name
FROM People P, Write W, Articles A
WHERE P.pid = W.pid AND W.aid = A.aid
GROUP BY A.year, P.pid, P.name
HAVING COUNT(A.aid) >= 3

Question 3.4 Find the articles that are (1) published in a conference in 2012 and (2) all male authors of
the article, if any, are from the Indiana University, return the titles.
SQL:
SELECT A1.title
FROM Articles A1
WHERE A.year = 2012 AND A1.aid NOT IN(
SELECT A2.aid
FROM Articles A2, Universities U, People P, Write W
WHERE A2.aid = W.aid
AND W.pid = P.pid
AND P.university = U.uid
AND P.gender = “M”
AND U.name <> “Indiana University”)

TRC: (20 bonus points)

)))

Part 4 (40 points)
<a id = “a1”>
Consider the XML document on the right. Please give the answers to the           <a id = “a1”>
following XPath queries (10 points each)                                            <a id = “a3”>
<b id = “b1”/>
Question 4.1    //a/b/@id                                                              <b id = “b2”/>
b1, b2, b3, b4,                                                                    </a>
Question 4.2 /a//b/@id                                                              <b id = “b3”/>
</a>
b1, b2, b3, b4, b5, b6                                                          <b id = “b4”>
Question 4.3 //a[a]/b/@id
<a id = “a4”/>
b3,b4                                                                              <b id = “b5”/>
Question 4.4    //a[b/a]//b/@id                                                     <b id = “b6”/>
b1, b2, b3, b4, b5, b6                                                         </b>
</a>

4

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 0 posted: 5/8/2013 language: Latin pages: 4
yan tingting