CIS 671, Winter 2002    Solutions
Homework 5: Object Query Language (ODL)         

1. (10 points) Give the graphical ODL schema for this database using
the notation shown in Figure 12.5.

          See separate document.


2. Translate the following queries into OQL.

2.1. (10 points) List the names of all resources and their types.


             RT -> ResourceType

             ^
             |
             v
             v

             R -> ResourceName

select r.ResourceName, r.TypeOfResource.ResourceType
from   r in Resources

----

select r.ResourceName, rt.ResourceType
from   r in Resources,
       rt in r.TypeOfResource


------------------------------------------------------------------
2.2. (10 points) List the customer name of all customers that have
scheduled a resource of type projector or laptop.

                                 RT <- ResourceType = projector or laptop

                                 ^
                                 |
                                 v
                                 v

                                 R

                                 ^
                                /
                               /
                              /
                             /
                            /
                           v
                           v

CompanyName <-  C          S

                ^          ^
                 \         |
                  \        |
                   \       |
                    v      v
                     v     v
                          SD

Two of many possible solutions.  It is OK to use ContactName in place
of CompanyName.
-----
select distinct c.CompanyName
from   c in Customers
where exists sd in c.CustSchedDetails :
      sd.FromSchedule.ResourceScheduled.TypeOfResource.ResourceType
                = 'projector'
        or
      sd.FromSchedule.ResourceScheduled.TypeOfResource.ResourceType
                = 'laptop'

-----
select distinct c.CompanyName
from   c  in Customers,       sd in c.CustSchedDetails,
       s  in sd.FromSchedule,  r in sd.ResourceScheduled,
       rt in r.TypeOfResource
where :
      rt.ResourceType = 'projector'
        or
      rt.ResourceType = 'laptop'


-------------------------------------------------------------------------------
2.3. (10 points) List the resource names and resource types of all
resources scheduled by Lazy K Kountry Store on 5/22/95 OR (emphasis
added) Let's Stop & Shop on 5/23/95.

                                         RT ->ResourceType

                                         ^
                                         |
                                         v
                                         v

                                         R ->ResourceName

                                         ^
                                        /
                                       /
                                      /
                                     /
                                    /
exists {[(Let's S&S) &  (5/23/95)] v
   or [(Lazy K CS) &  (5/22/95)]}  v

                        C          S

                        ^          ^
                         \         |
                          \        |
                           \       |
                            v      v
                             v     v
                                  SD

select distinct r.ResourceName, rt.ResourceType
from   r  in Resources,         rt in r.TypeOfResource
where exists 
       s  in r.SchedOfResource, sd in s.DetailsOfSchedule,
       c  in sd.CustRequesting :
       (c.CompanyName = "Lazy K Country Store" and s.date = '5/22/95')
       or
       (c.CompanyName = "Let's Stop & Shop" and s.date = '5/23/95')

-------------------------------------------------------------------------------
2.4. (10 points) List the resource names and resource types of all
resources scheduled by Lazy K Kountry Store on 5/22/95 AND (emphasis
added) Let's Stop & Shop on 5/23/95.

       Note: There must be two different ScheduleDetail objects with
             corresponding Schedule and Customer objects.

                                         RT ->ResourceType

                                         ^
                                         |
                                         v
                                         v

                                         R ->ResourceName

                                         ^
                                        /
                                       /
                                      /
                                     /
                                    /
  exists (Let's S&S) &  (5/23/95)   v
& exists (Lazy K CS) &  (5/22/95)   v

                        C          S

                        ^          ^
                         \         |
                          \        |
                           \       |
                            v      v
                             v     v
                                  SD

select distinct r.ResourceName, rt.ResourceType
from   r  in Resources,         rt in r.TypeOfResource
where exists 
       s  in r.SchedOfResource, sd in s.DetailsOfSchedule,
       c  in sd.CustRequesting :
       c.CompanyName = "Lazy K Country Store" and s.date = '5/22/95'
                     and
      exists 
       s  in r.SchedOfResource, sd in s.DetailsOfSchedule,
       c  in sd.CustRequesting :
       c.CompanyName = "Let's Stop & Shop" and s.date = '5/23/95'

------

select distinct r.ResourceName, rt.ResourceType
from   r  in Resources,          rt in r.TypeOfResource,
       s1 in r.SchedOfResource, sd1 in s1.DetailsOfSchedule,
       c1 in sd1.CustRequesting,
       s2 in r.SchedOfResource, sd2 in s2.DetailsOfSchedule,
       c2 in sd2.CustRequesting
where
       c1.CompanyName = "Lazy K Country Store" and s1.date = '5/22/95'
                     and
       c2.CompanyName = "Let's Stop & Shop"    and s2.date = '5/23/95'


-------------------------------------------------------------------------------
2.5. (10 points) List each resource type and the number of times it
has been reserved.

                                 RT ->ResourceType

                                 ^
                                 |
                                 v
                                 v

                                 R

                                 ^
                                /
                               /
                              /
                             /
                            /
                           v
                           v

                C          S

                ^          ^
                 \         |
                  \        |
                   \       |
                    v      v
                     v     v
                          SD -> count

select distinct resType, count(partition)
from   sd in ScheduleDetails
group by resType: sd.FromSchedule.ResourceScheduled.TypeOfResource.ResourceType


-------------------------------------------------------------------------------
2.6. (10 points) List the names of any resource that has been
scheduled by all customers in California (CA).

                                    R -> ResourceName
                                    ^
                                   /
                                  /
                                 /
                                /
                               /
                              v
                             v
for all in 'CA'->  C        S   exists SD, S, RR matching R
                   ^        ^   (OR exists S,SD, CC matching C)
                    \       |
                     \      |
                      \     |
                       v    v
                        v   v
                          SD 

select distinct r.ResourceName
from   r in Resources
where  for all ca in 
           (select ca 
            from c in Customers
             where c.State = 'CA'):
               (exists sd in ca.CustSchedDetails,
                      s in sd.FromSchedule,
                      rr in s.ResourceScheduled
                    : r = rr)



select distinct r.ResourceName
from   r in Resources
where  for all ca in 
           (select ca 
            from c in Customers
             where c.State = 'CA'):
               (exists rr in ca.CustSchedDetails.FromSchedule.ResourceScheduled
                    : r = rr)