____ 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.