CSE 132B, Spring 2000
Midterm Exam
Name:
Solve as many problems as possible. The answers to the first two problems can (and should) be brief.
Consider the ODMG schema of Figure 1 representing a vehicle company and its employees. Note the various kinds of relationships that exist between the entities, e.g. president, employees, cars, and manufacturer (in the sequel inverse references are not considered).
Problem 1: Relational Representation (15 points)
Translate the ODMG schema of Figure 1 into a relational schema with appropriate attribute types. Denote the keys of the relations and explain why we may need additional attributes and relations.
Problem 2: N1NF Representation & Complex Values (50 points)
1. Translate the ODMG schema of Figure 1 into a N1NF relational schema with one nested relation. For this purpose we can ignore the entity Person and consider only the entity Employee with all the inherited attributes (10 points).
2. Give the algebraic form (for complex objects) of the following query: “Find the names of the employees residing and working in the same city” (10 points)
3.
Consider the following relations: R1(Company, Ename) is a standard relation and S1(Company, Employees) where the attribute Company has atomic values while the attribute Employees is set valued with tuples having one attribute Ename (30 points).
3.1 Is the following equality true for every relation R(Company, Ename) i.e. for all possible instances of the relation schema (Company, Ename) (justify your answer)?
unest Employees ( nest Employees = Ename (R) ) = R
3.2 Is the following equality true for every relation S(Company, Employees) i.e. for all possible instances of the relation schema (Company, Employees) (justify your answer)?
nest Employees = Ename ( unest Employees (S) ) = S
3.3 Assuming now that we have a relation K resulting from the query: K=s Employees != {}(S)
where S is a nested relation with schema (Company, Employees) and != {} denotes the condition not equal to the empty set. Is the following equality true for every relation S (justify your answer)?
nest Employees = Ename ( unest Employees (K) ) = K
Problem 3: Object-Oriented Representation (105 points)
1. Translate the ODMG schema of Figure 1 into an O2 schema (give the name and the type of each class). Assuming that a company has a number of subsidiaries how we can represent this information in the corresponding class? What would be a more interesting representation of the address information for both companies and persons? How are we able to declare in O2 that the created objects will become persistent? (20 points).
2. Write the signature and the body of O2C methods for each of the following manipulations:
a) Create a new company and add a new employee in a given company (10 points).
b) Find the car models of company employees whose salary is greater than a certain amount (10 points).
c) Find the subsidiaries (and sub-subsidiaries) of a given company (15 points).
3. Give the OQL expressions for the following queries:
a) Find the complete information about employees (i.e. their values) sharing the same car with the president of their company (10 points).
b) Find the name of presidents having at least one car, which is not manufactured by their company (10 points).
c) Find the smallest salary of Ford employees (10 points).
4. Consider the following query:
select e->name
from COMPANIES c, c->employees e, e->cars v
where v->manufacturer->name=“Mercedes and c->name=“Ford”
a) What is the above query supposed to compute? (5 points)
b) Give the algebraic form (see course lecture notes) of the above query (15 points).