Question

1. Draw a class diagram for a real estate firm that lists property for sale. The following describes the organization:

• The firm has a number of sales offices in several states; location is an attribute of sales office.
• Each sales office is assigned one or more employees. Attributes of employee include employeeID and employeeName. An employee must be assigned to only one sales office.
• For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he or she is assigned.
• The firm lists property for sale. Attributes of property include propertyName and location.
• Each unit of property must be listed with one (and only one) of the sales offices.
• A sales office may have any number of properties listed, or may have no properties listed.
• Each unit of property has one or more owners. Attributes of owner are ownerName and address. An owner may own one or more units of property. For each property that an owner owns, an attribute called percentOwned indicates what percentage of the property is owned by the owner.

2. Consider a database schema with a relation EMP whose attributes are as shown below, with types specified for multivalued attributes. (Oracle database for writing SQL)

EMP= (ename, ChildrenSet multiset(Children), SkillSet multiset(Skills))

Children = (name, birthday)

Skills = (type, ExamSet setof(Exams))

Exams = (year, city)

1. Define the above schema in SQL, with appropriate types for each attribute.
2. Using the above schema, write the following queries in SQL.
   1. Find the names of all employees who have a child born on or after January 1, 2000.
   2. Find those employees who took an examination for the skill type “typing” in the city “Dayton”.
   3. List all skill types in the relation EMP.

Answer 1

a)

create type Exams( year char(4), city varchar(20) )

create table exams of type Exams;- CREATE TABLE exams as Exams

create type Skills ( type varchar(20), ExamSet Exams multiset )

create table skills of type Skills :-CREATE TABLE skills as Skills

create type Birthday ( day int(2), month char(3), year int(4) )

same ->CREATE TABLE birthaday as Birthday

create type Children (name varchar(50), Birthday birthday )

same -> CREATE TABLE children  as Children

create type Emp( ename varchar(50), ChildrenSet Children multiset, SkillSet Skills multiset )

create table emp of type EMP-> CREATE TABLE emp as Emp

b)

Queries in SQl

i) select ename from emp as e ,e.ChildrenSet as c ,c.birthday as b    where b.year>=2000)

ii) select e.ename from emp as e ,e.SkillSt as s ,s.ExamSet as x where s.type='typing' and x.city='Dayton'

ii) select distinct s.type from emp as e ,e.SkillSet as s