• Home
• Exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
  • Reference 1
  • Reference 2
• Classic Models
• Cases
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
• Test questions
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
  • Reference 1
  • Reference 2
• Lab exercises
  • Data integration
  • Exams

4 - One-to-Many Relationship

A continuation of the case from chapter 3.

The club has asked you to extend the initial database to include some more detail.

The club has asked you to extend the initial database to include some more detail. There are seven committees, each of which undertakes between three and seven events per year. All club members must join one, and only one, committee. Each committee has a name and specific mission and works on several projects during the year. These projects are given names, take place in specific locations, and have a begin date and time as well as an end date and time. There is also a short description for each event, which is run by one, and only one, committee.

Create the data model and write the following queries.

Answers

CREATE TABLE club_member (
	 member_number 		CHAR(5) NOT NULL,
	 first_name			   VARCHAR(12),
	 last_name			    VARCHAR(12),
	 community_name		VARCHAR(12),
	 street_address		VARCHAR(50),
	 city					       VARCHAR(12),
	 state				       CHAR(2),
	 zip_code				    CHAR(5),
	 phone_number			 VARCHAR(12),
	 fax_number			   VARCHAR(12),
	 email_address			VARCHAR(25),
	 newsletter_delivery_method VARCHAR(10),
	 member_type			  VARCHAR(12),
	 committee_code		CHAR(5) NOT NULL,
		   PRIMARY KEY (member_number)
		   CONSTRAINT fkcomname FOREIGN KEY(committee_code)
		      REFERENCES committee ON DELETE RESTRICT);

CREATE TABLE committee (
  committee_code	 CHAR(5)
  committee_name	 VARCHAR(20) NOT NULL,
	 mission			      VARCHAR(60),
		   PRIMARY KEY (committee_name));

CREATE TABLE project (
  project_name		  VARCHAR(20) NOT NULL,
	 location 			    VARCHAR(20),
	 begin_date_time	DATE/TIME,
	 end_date_time		 DATE/TIME,
	 description 		  CHAR(100),
	 committee_code	 CHAR(5) NOT NULL,
		   PRIMARY KEY (project_name),
     CONSTRAINT fkcommittename FOREIGN KEY(committee_code) 
        REFERENCES committee ON DELETE RESTRICT);

How many events did each committee undertake in 2001?

SELECT committee_name, COUNT (project_code)
  FROM project, committee
  WHERE project.committee_code = committee.committee_code
  AND begin_date_time BETWEEN ’2001-01-01’ AND ’2001-12-31’
     GROUP BY committee_name;

List the names of all club members by committee membership. List out the committee name, the lastname and the first name of each member in that committee as well as that person's phone number.

SELECT committee_name, last_name, first_name, phone_number
   FROM club_member, committee
   WHERE club_member.committee_code = committee.committee_code
      ORDER BY committee_name, last_name;

List the committees that have fewer than 10 people. Order the report by number of committee members descending.

SELECT committee_name, COUNT (member_number)
   FROM club_member, committee
   WHERE club_member.committee_code = committee.committee_code
      GROUP BY committee_name
      HAVING COUNT (member_number) < 10
         ORDER BY COUNT (member_number) DESCENDING;

Find all of the missions (these are the committee's missions) that had an event taking place in the current month.

SELECT mission FROM committee
    WHERE committee_code IN 
    (SELECT committee_code FROM project
        WHERE MONTH(begin_date_time) = MONTH(sysdate) 
           GROUP BY committee_code
           HAVING COUNT(project_code) > 0);

To make this a more general SQL query you may want to give the students a month (like June) and have them use the like command.

Find all committees that had an event that took place in “JRs Park”.

SELECT UNIQUE committee_name 
   FROM project, committee
      WHERE project.committee_code= committee.committee_code
      AND location = 'JRs Park';

Date revised: 10-Dec-2021