Banner

 

Chapter 6

1.

Draw data models to describe the following situations.

1a(i).

A club has many members and a person can be a member of many clubs. Each club has a president and a treasurer. One person can be the president or treasurer of more than one club.

CREATE TABLE person (
persid	INTEGER,
persfname VARCHAR(20),
perslname VARCHAR(20),
PRIMARY KEY(persid));

CREATE TABLE club (
clubid	INTEGER,
clubname VARCHAR(20),
president INTEGER,
treasurer INTEGER,
PRIMARY KEY(clubid),
CONSTRAINT fkpresident FOREIGN KEY (president) REFERENCES person,
CONSTRAINT fktreasurer FOREIGN KEY (treasurer) REFERENCES person);

CREATE TABLE membership (
memberid INTEGER,
joindate DATE,
persid	INTEGER,
clubid	INTEGER,
PRIMARY KEY(clubid, memberid),
CONSTRAINT fkperson FOREIGN KEY (persid) REFERENCES person,
CONSTRAINT fkclub FOREIGN KEY (clubid) REFERENCES club);

1a(ii).

A club has many members and a person can be a member of many clubs. Each club has a president and a treasurer. One person can be the president or treasurer of only one club.

The 1:m becomes a 1:1 and a unique constraint is specified.

CREATE TABLE person (
persid	INTEGER,
persfname VARCHAR(20),
perslname VARCHAR(20),
PRIMARY KEY(persid));

CREATE TABLE club (
clubid	INTEGER,
clubname VARCHAR(20),
president INTEGER,
treasurer INTEGER,
PRIMARY KEY(clubid),
CONSTRAINT  uniquepresident UNIQUE(president),
CONSTRAINT  uniquetreasurer UNIQUE(treasurer),
CONSTRAINT fkpresident FOREIGN KEY (president) REFERENCES person,
CONSTRAINT fktreasurer FOREIGN KEY (treasurer) REFERENCES person);

CREATE TABLE membership (
memberid INTEGER
joindate DATE,
persid	INTEGER,
clubid	INTEGER,
PRIMARY KEY(clubid, memberid),
CONSTRAINT fkperson FOREIGN KEY (persid) REFERENCES person,
CONSTRAINT fkclub FOREIGN KEY (clubid) REFERENCES club);

1a(iii).

A club has many members and a person can be a member of many clubs. Each club has a president and a treasurer. One person can be the president or treasurer of only one club and can be a member of no other clubs.

Another pair of constraints need to be added to specify that the president and treasurer can belong to only one club (the two 1:1 relationships). A trigger needs to be written to ensure that when a person is elected as president or treasurer (i.e. , MEMBER is updated) a check is done to ensure that this person is not a member of more than one club. It is assumed that a person has to be a member of the club to be elected to a position.

The skeleton for a trigger. The procedure would be written in a procedural language, which is beyond the scope of this text.


CREATE TRIGGER officercheck BEFORE INSERT OR UPDATE
ON membership FOR EACH ROW 
EXECUTE PROCEDURE check_single_membership(persid);

CREATE TABLE person (
persid	INTEGER,
persfname VARCHAR(20),
perslname VARCHAR(20),
PRIMARY KEY(persid));

CREATE TABLE club (
clubid	INTEGER,
clubname VARCHAR(20),
president INTEGER,
treasurer INTEGER,
PRIMARY KEY(clubid),
CONSTRAINT  uniquepresident UNIQUE(president),
CONSTRAINT  uniquetreasurer UNIQUE(treasurer),
CONSTRAINT fkpresident FOREIGN KEY (president) REFERENCES person,
CONSTRAINT fktreasurer FOREIGN KEY (treasurer) REFERENCES person);

CREATE TABLE membership (
memberid INTEGER,
joindate DATE,
persid	INTEGER,
clubid	INTEGER,
president INTEGER,
treasurer INTEGER,
PRIMARY KEY(clubid, memberid),
CONSTRAINT fkperson FOREIGN KEY (persid) REFERENCES person,
CONSTRAINT fkclub FOREIGN KEY (clubid) REFERENCES club,
CONSTRAINT fkpresone FOREIGN KEY (president) REFERENCES person,
CONSTRAINT fktreasone FOREIGN KEY (treasurer) REFERENCES person);

1b.

Researchers are associated with one institution (university, research institution, etc.). Each researcher has specific research interests. Your model should also reflect the most prominent researcher in each area. Assume that a researcher can be the most prominent researcher of only one area. Also, record that teams of researchers often collaborate.

2.

Write the following SQL queries based on the described database for a travel agent.

2a.

What is the average June temperature for cities with more than 300,000 inhabitants?

SELECT AVG(TEMPERATURE) FROM CLIMATE, CITY
   WHERE CITYPOPULATION > 300000
   AND MONTH = "June"
   AND CITY.CITYID = CLIMATE.CITYID

2b.

Which city is the farthest from Amsterdam and how long does it take to travel there?

SELECT CITY2, CITYNAME, TRAVELTIME FROM CITY, DISTANCE
   WHERE CITY1 = "Amsterdam"
   AND CITY.CITYID = DISTANCE.CITY2
   AND DISTANCE =
      (SELECT MAX(DISTANCE) FROM DISTANCE
         WHERE CITY1 = "Amsterdam")

2c.

What is the August precipitation and temperature of cities which lie exactly two hours travel time from New York City?

SELECT CITYNAME, PRECIPITATION, TEMPERATURE
  FROM CITY, CLIMATE, DISTANCE
	 WHERE CITY.CITYID = CLIMATE.CITYID
	 AND CITY.CITYID = DISTANCE.CITY2
	 AND CITY1 = "New York"
	 AND TRAVELTIME = 2
3.

A shoe manufacturer wants to maintain contact information about each employee, the employees title, the hire date, and so forth. Nearly all employees have a mentor and the firm wants to track each employee's current mentor.

4.

You want to transfer your e-mail messages from an e-mail package into a database to mange the messages. You need to follow the sequence of e-mails you have had with a variety of correspondents. Each message may receive many responses and each message may be a response to more than one message.

5.

The gourmet food club sells select food products via home parties. Aimee runs one of these clubs and wants to be able to track who has come to her parties and who has hosted a party for her. She keeps all contact information about her customers and tracks the date and time of all parties she has given. A customer may attend many parties (and of course parties have many attendees) and a customer may also host many parties (but each party is only hosted by one person).

6.

The Parents' Association of a local elementary school wants to record participation of its members. For each member, it wants to record general contact information, the names and dates of birth of all of the members' children, the grades of those children, and the spouse’s name (if there is one). Each member can participate in a number of events put on by the association (bake sales, fashion shows, golf outings, etc.) and can also chair an event. In order to increase the number of people chairing events the association does not allow the same person to chair more than one event. It also has only one chair per event. Members can participate in as many events as they wish.

7.

Using the preceding data model, write SQL to answer the following queries

Answers:

Assume that the foreign keys are inmailid and outmailid for mail going in and out, respectively.

7a:

select count(*) from email where emailid not in (select inmailid from exchange)

7b .

select avg(count(outmailid)) from exchange

7c.

select count(*) from email, exchange where email.emailid = exchange.outmailid and fname = “Ada” and lname = “Ye”

8.

Using the preceding data model answer the following queries

  1. How many employee have been a mentor
  2. How many employees have had a mentor
  3. List the employee's title, and name and the number of employees they have mentored (only include those who have mentored at least 3 employees).

Answers

8a.

Assuming that when the data model is mapped to a relational database, the foreign key is called mentorid.

Select count(distinct (mentor.empid))
  from employee emp, employee mentor
    where emp.mentorid=mentor.empid

8b.

select count(distinct(emp_id))
  from employee
    where mentorid is not NULL

8c.

select mentor.emptitle, mentor.empfname, mentor.emplname, count(*)
  from employee emp, employee mentor
    where emp.mentorid=mentor.empid
    group by mentor.emptitle, mentor.empfname, mentor.emplname 
       having count(*) > 2

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 10-Dec-2021