CS 3200 Introduction to Database Management Assignment #3

The assignment uses the following data design:

Apartment Data Model

The Argo UML file is apartment.zargo.

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.
  2. 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.
  3. Give the user 'Mary' read-only access to all of her apartment information.
  4. 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.
  5. 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'
               );