Name  ___________________

 

CPTR349  Spring '04 (100 total points)                                                                  Exam 1

 

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.