Do some joins of the pet and event tables.

Assume name is the primary key (PK) of pet and the foreign key (FK) in event.
pet is the parent, event is the child in this relationship.
The FK of event references the PK of pet.
Each row of pet can have its PK as the FK of many rows of event.

[We could make this change now, but we'll do it later so that first
we'll be able to do some "bad" changes to the tables.]


--combine each pet row with each event row: i.e. Cartesian product.  How
  many rows?  See that most are garbage.

--combine each pet with its events. i.e. natural join on name column.

--same as previous query, but only display name, species, and event type

--sex of the pets that had litters (duh)

--owner's whose pets have had birthdays:

--Harold's pets' events:

--age of pets on visits: (hint: subtract 2 Date values)

--pets who visited when no more than one year old:

--the age of oldest pet at the time of its event

--the number of events per species

--the number of events per species, in increasing order

--the species with the most events (hint: top N analysis)

--the average cuteness of the birthday pets

--owners who had events 12 or more years ago (hint: Sysdate)




--do a select that would show any pets who never had any events (hint:
  left outer join)

--do a select that would show any referential integrity problems of an
  event that does not have an existing pet PK as its FK

--do a select that would show any event that does not have a FK value (hint:
  not a join)


Add a row into pet for a new pet.
INSERT INTO pet VALUES('Garfield,'Al','cat','f','1-Dec-2000','12-Dec-2000',12,99);
Now repeat the:
--do a select that would show any pets who never had any events (hint:
  left outer join)

Add a row into event with a name that does not exist in pet.  We can
do this because the tables have not been declared to be in a PK-FK
relationship.
INSERT INTO event VALUES('Sluggo','12-Dec-2000','lobotomy','excellent prognosis');
Now repeat the:
--do a select that would show any referential integrity problems of an
  event that does not have an existing pet PK as its FK






      
Now make these tables somewhat better relational tables by adding a
primary key for pet and a foreign key for event:
(these keys are not actually required to do joins).
ALTER TABLE pet ADD PRIMARY KEY(name);
ALTER TABLE event ADD FOREIGN KEY(name) REFERENCES pet;
(hint: delete Sluggo)
pet is the parent, event is the child in this relationship.
The FK of event references the PK of pet.
Each row of pet can have its PK as the FK of many rows of event.

Now try to add Sluggo back to event.
Try to set a PK of a row of pet to Null.
Try to set a PK of a row of pet to a non-Null name not found in event.