1.Terminology:Select the word the best matches the description given.Each word will be used exactly once.(2 points each)

a.attributes

b.database management system

c.domain

d.entity

e.integrity constraints

f.key

g.log

h.relationship

i.schema

j.transaction

____ software package designed to store and manage
databases

____ details the relation’s
name, the name of each field (or column) and the domain of each field

____ an atomic sequence of database
actions (reads/writes)

____ a file containing the
history of all actions carried out by a DBMS, used for crash recovery

____ a real-world object
distinguishable from other objects

____ the set of
characteristics used to describe an object

____ a minimal set of
attributes whose values uniquely identify an entity in the set

____ the set of possible
values for an attribute

____ an association among
two or more entities

____ conditions that must be
true for any valid instance of a database

2.Consider the following ER
diagram.For each statement below,
indicate if the statement is true (T), false (F), or undeterminable from the
diagram (U).(2 points each)

T, F, or U

____Every employee must have purchased a policy.

____An employee can purchase at most one policy

____Every policy must be purchased by exactly one
employee

____Every policy has a unique policy id

____Every policyid-pname combination must be
unique

____Every dependent must be a beneficiary on a
policy

____The “cost” attribute is represented as a real
number

____The name of each employee must be unique

____The name of each dependent (pname) must be
unique

3.The Susque Handy Health
System wants to store information about the physicians employed in its network.Draw the ER diagram that captures the
following information.(15 points)

·Each physician is identified by a unique doctor id number.In addition, the doctor’s name, office
number, and phone number are to be recorded.

·Each physician specializes in exactly one field of medicine.The various fields are uniquely identified by
their names, such as primary care, cardiac care, opthamology, etc.Each field of specialty also has an
associated pay scale (an integer).

·For every field of medicine, the health system has at least one
physician that specializes in it.

·Some doctors have formed groups (so that one can be on call all weekend
while the rest go golfing.)No doctor
can belong to more than one group.Every
group must have at least one doctor in it.Each group is uniquely identified by its name.Additional information about each group is
their location and telephone number.

4.Give the SQL statements (see
handout for samples) to create the relations (tables) necessary for the
following ER diagram.Select appropriate
domain, key, and participation constraints.Only the date of birth of the author may be null.All book titles and publisher names should be
unique.No entity may be deleted if it
is involved in either a “wrote” or “published” relationship.For full credit, use only 4 relations.(16 points)

5.Answer the questions below
based on the following SQL commands.

a.Suppose that we want to
automatically delete a customer’s orders if that customer is deleted.What changes should be made to the SQL
statements above? (4 points)

b.For
each statement below, indicate if the statement is true (T), false (F), or
undeterminable from the SQL statements (U). (2 points each)

____An order cannot be placed without a card number
(cardnum)

____An order cannot be placed without a order
number (ordernum)

____A customer can place more than one order in
one day

6.Consider the
following tables:

Emp(eid: integer, ename: string, salary:
real, lot: string)

Works(eid: integer, did:
integer, pct_time: integer)

Dept(did: integer, dname: string,
budget: real, eid: integer)

Clarification:In the Works relation, eid and did
are foreign keys referencing Emp and Dept respectively.In the Dept relation, eid is a
foreign key referencing Emp and is the employee id of the department
manager.

did

dname

budget

eid

20

CEO staff

25000

1

99

Parking

25

3

Emp:Dept:

eid

name

salary

lot

1

Smith

100000

B

2

Jones

75000

A

3

Doe

82000

B

4

Schmuck

1200

Z

eid

did

pct_time

1

20

100

2

20

100

3

20

95

3

99

5

4

99

100

Works:

Draw the tables that result from the following RA (relational algebra) queries:(3 points each)

7.Consider the
following schema (same as on the previous page):

Emp(eid: integer, ename: string, salary:
real, lot: string)

Works(eid: integer, did:
integer, pct_time: integer)

Dept(did: integer, dname: string,
budget: real, eid: integer)

Clarification:In the Works relation, eid and did
are foreign keys referencing Emp and Dept respectively.In the Dept relation, eid is a
foreign key referencing Emp and is the employee id of the department
manager.

Write RA (relational algebra) queries for the following– do NOT draw
the tables: (3 points each)

a.Find all employee information (that is a table with
the same schema as Emp) for all those who park in parking lot B.

b.Find the names of the departments with a budget greater
than $10,000.

c.Find the names of the managers of all departments with
a budget greater than $10,000.

d.Find the department name (dname) of the employee whose
employee id (eid) is 3.

e.Give the name(s) of the department(s) in which
employee “Doe” works.