CS 5200 Introduction to Database Management Solution to Individual Assignment #3

The assignment uses the following data design: Apartment Data Model

The following is the SQL schema:

create table Building (
  id int primary key,
  address varchar(5000) not null
);

create table Apartment (
  id int primary key,
  number varchar(31) not null,
  containedIn int not null references Building(id)
    on update cascade on delete cascade
);

create table RoomType (
  id int primary key,
  description varchar(5000) not null
);

create table RoomTypeName (
  type int references RoomType(id)
    on update cascade on delete cascade,
  name varchar(255),
  primary key(type, name)
);

create table Room (
  id int primary key,
  area double not null,
  type int not null references RoomType(id)
    on update cascade on delete no action,
  containedIn int not null references Apartment(id)
    on update cascade on delete cascade
);

create table ApartmentFloor (
  apartment int references Apartment(id)
    on update cascade on delete cascade,
  floor int,
  primary key(apartment, floor)
);
  
create table Person (
  id int primary key,
  name varchar(5000) not null
);

create table Owner (
  isOwnedBy int references Person(id)
    on update cascade on delete cascade,
  owns int references Apartment(id)
    on update cascade on delete cascade,
  starts date not null,
  ends date,
  primary key(isOwnedBy, owns)
);

create table Error (
  id int primary key auto_increment,
  message varchar(5000) not null
);

  1. Every apartment must be on at least one floor. Express this cardinality constraint as a check constraint.
    alter table Apartment add
      check (exists(select * from ApartmentFloor f where f.apartment = id));
    
  2. A person is not allowed to own more than one apartment in the same building. Express this as an embedded constraint defined by a relational algebra expression. Show the embedded constraint in the UML diagram.

    The UML class diagram is

    .

    count(*)<=1(p.id,b.id (Person p P1(Owner o P2(Apartment a P3 Building b))))

    P1 is p.id = o.isOwnedBy
    P2 is a.id = o.owns
    P3 is b.id = a.containedIn

    There is a shorter form of the constraint that omits the RoomType and uses the join condition r.type = n.type.

  3. Express the ownership constraint above as an assertion.

    create assertion OwnershipConstraint check(
      not exists(
        select *
          from Person p, Owner o, Apartment a, Building b
         where p.id = o.isOwnedBy
           and o.owns = a.id
           and a.building = b.id
         group by p.id, b.id
        having count(*) > 1
      );
    

  4. No apartment can have more than one kitchen. Express this as an embedded constraint defined by a relational algebra expression. Show the embedded constraint in the UML diagram.

    The UML class diagram is:

    count(*)<=1count(*)(a.id(n.name='kitchen' (Apartment a R1(Room r R2 RoomTypeName n))))

    R1 is a.id = r.containedIn
    R2 is r.type = n.type

    One can also include RoomType in which case the relational algebra expression is the following:

    count(*)<=1count(*)((n.name='kitchen' (Apartment a R1(Room r R2(RoomType t R3 RoomTypeName n)))))

    R1 is a.id = r.containedIn
    R2 is r.type = t.id
    R3 is n.type = t.id

  5. Give the user 'Mary' read-only access to all of her apartment information.

    One approach is to create a single view with all of the attributes:

    create view MaryAccess as
      select *
        from Person p, Owner o, Apartment a, Room r, RoomType t, RoomTypeName n
       where p.name = 'Mary'
         and p.id = o.isOwnedBy
         and o.owns = a.id
         and a.id = r.containedIn
         and r.type = t.id
         and t.id = n.type;
    grant select on MaryAccess to 'Mary';
    

    One can omit the RoomType and RoomTypeName tables from this view since it is reasonable to assume that these tables are publicly readable.

    Alternatively, one could create separate views for each table:

    create view MaryApartment as select * from Apartment a where exists(select * from Owner o, Person p where o.isOwnedBy=p.id and o.owns=a.id and p.name='Mary');
    grant select on MaryApartment to 'Mary';
    create view MaryBuilding as select * from Building b where exists(select * from Apartment a, Owner o, Person p where b.id=a.containedIn and o.isOwnedBy=p.id and o.owns=a.id and p.name='Mary');
    grant select on MaryBuilding to 'Mary';
    create view MaryRoom as select * from Room r where exists(select * from Apartment a, Owner o, Person p where r.containedIn=a.id and o.isOwnedBy=p.id and o.owns=a.id and p.name='Mary');
    grant select on MaryRoom to 'Mary';
    

  6. Develop a trigger that will record an error message whenever an apartment acquires more than one kitchen. You may use either the syntax in the textbook or the MySQL syntax.
    create trigger KitchenError after insert on Room for each row
      insert into Error(message)
        select 'An apartment has too many kitchens'
          from RoomType t, RoomTypeName n
         where new.type = t.id -- Only generate an error message for a kitchen (optional)
           and n.room = t.id
           and n.name = 'kitchen'
           and exists(
                -- Compute the number of kitchens in the apartment of the new room
                select *
                  from Room r2, RoomType t2, RoomTypeName n2
                 where t2.id = r2.type
                   and n2.room = r2.id
                   and n2.name = 'kitchen'
                   and r2.containedIn = new.containedIn
                 group by r2.containedIn
                having count(*) > 1
              );
    

    There are many other ways to solve this problem, of course. The only parts that are required are to compute the number of kitchens of the apartment of the room being inserted and to insert a message into the Error table when the number is greater than 1.

    Note that if one uses "before insert", then the query will count the number of kitchens in the apartment before the new room is inserted so the having condition would be count(*) > 0.

  7. Express the following query in the relational algebra:
    select sum(r.area)
      from Apartment a, Owner o, Person p, Room r
     where a.id = o.owns
       and o.isOwnedBy = p.id
       and a.id = r.containedIn
       and not exists(
                select *
                  from Room s, RoomTypeName n
                 where s.containedIn = a.id
                   and s.type = n.type
                   and n.name = 'kitchen'
               );
    

    sum(r.area)(sum(r.area)((Apartment a Q1(Owner o Q2(Person p Q3 Room r))) Q4(n.name='kitchen'(Room s Q5 RoomTypeName))))

    Q1 is a.id = o.owns
    Q2 is o.isOwnedBy = p.id
    Q3 is a.id = r.containedIn
    Q4 is s.containedIn = a.id
    Q5 is s.type = n.type

For scoring see the rubric