Banner

 

Chapter 4

1.

Draw data models for the following situations:

1a.

A hotel has many rooms but each room is in only one hotel.

1b.

A train can have many wagons but one wagon belongs to only one train.

1c.

An exam is about one course but one course can have many exams.

1d.

A person has one main address but many people can have the same address as their main address.

2.

Write the following SQL queries based on the described database for a student exchange program coordinator. To simplify the model, assume that each student can only participate in one exchange program and that each university teaches only one language.

2a.

How many students visited the University of Amsterdam in Amsterdam, the Netherlands in 2005?

SELECT COUNT(SSN) FROM STUDENT, UNIVERSITY             
   WHERE UNIVERSITY.UNIID = STUDENT.UNIID
   AND UNINAME = "University of Amsterdam"               
   AND UNICOUNTRY = "The Netherlands" 
   AND YEAR = 2005

2b.

List the name of the female student at Stavanger College in Norway in 2005.

SELECT SFNAME, SONAME, SLNAME FROM STUDENT, UNIVERSITY
   WHERE UNIVERSITY.UNIID = STUDENT.UNIID
   AND UNINAME = "Stavanger College" 
   AND UNICOUNTRY = "Norway"
   AND YEAR = 2005   
   AND GENDER = "female"

2c.

What is the university ID of the University of Graz, Austria?

SELECT UNIID FROM UNIVERSITY
   WHERE UNINAME = "University of Graz"
   AND UNICOUNTRY = "Austria"

2d.

Report the total stipends given to students in 2003.

SELECT SUM(STIPEND) FROM STUDENT
   WHERE YEAR = 2003
3.

A meteorologist wants to build a database to store data on some weather indicators. The data he wants to include in this database is collected at 25 weather stations. Hourly measurements on temperature, humidity, precipitation, and wind speeds will be entered into the database. In case of potentially erroneous data, the meteorologist needs to call the technician responsible for the station. Each technician can be responsible for up to five weather stations. Draw the data model for this database.

4.

Use the database given in exercise 3 to answer the following queries:

4a.

Where and when was the hottest time, day, and place?

SELECT DATE, HOUR, STATIONCITY FROM MEASUREMENT, STATION
   WHERE STATION.STATIONID = MEASUREMENT.STATIONID
   AND TEMPERATURE = (SELECT MAX(TEMPERATURE) FROM MEASUREMENT)

4b.

Who is the technician for the station in New York City?

SELECT TECHID, TECHNAME FROM STATION, TECHNICIAN
   WHERE STATION.TECHID = TECHNICIAN.TECHID
   AND STATIONCITY = 'New York City'

4c.

Report the average wind speeds of each station.

SELECT AVG(WINDSPEED, STATIONNAME FROM STATION, MEASUREMENT
   WHERE STATION.STATIONID = MEASUREMENT.STATIONID
      GROUP BY STATIONNAME

4d.

Report the average rainfall in Seattle.

SELECT AVG(PRECIPITATION) FROM MEASUREMENT, STATION
   WHERE STATION.STATIONID = MEASUREMENT.STATIONID
   AND STATIONCITY = 'Seattle'

5.

A used car dealership deals with a wide range of car companies.  Each company  makes many different types of cars.    It needs a database that contains contact information for all of the car companies with which it works (in case it needs to call them for additional maintenance/recall information). It also wants to keep general information about all the models of cars it handles.  This information should include, number of doors, gas mileage, year (note it considers the 1967 Ford Mustang to be a different model then the 1968 Ford mustang), and horse power .

 6.

A new sports magazine wants to keep a simple database that tracks what sports teams are in what city.  It wants to know the city’s name, state, and population and the team’s name, sport type, and stadium.

Note: the formation of an identifier for TEAM presents a problem because there a multiple teams with the same name, but none of these teams plays at the same stadium.

7.

Charlie’s book store wants to determine what publisher’s tend to sell the most popular books.  To do this he wants to create a database that contains contact information about the various publisher’s with which he works .  For the books, he wants to have the book’s title, the type it is (fiction, non-fiction, how-to, etc.) and the name of the first author on the book.

Find the errors in the following model:

 Answer:

  1. The relationship is reversed
  2. The identifier for book will not work.  If you use author as the identifier then no author could write more than one book!
  3. There is no way to phone, fax or email the publishing company

8.

MegaMerger owns nine different fast food chains.  They want to track information about each chain (the name, and headquarters information, and a description of the type of food they carry).  They also want to track address information for each store associated with each chain (note that a specific store can only belong to one of the fast food chains), and finally they would like to track information about each employee that works at the fast food chain (note: that an employee is assigned to one and only one store).

Answer

 

Using the data model in the Chapter find the errors in the following SQL queries:

9. 

Find the minimum value stkpe for each nation.  Print out the stock price and the nations name and the exchange rate for each nation. Put it in order by the stock price

Select min(stkprice), nation, exchrate
		From stock
		Where stock.natcode=nation.natcode
		Order by stkprice
  1. Nation should be in the from statement
  2. The group by statement is missing between the where and the order statement
  3. Stkprice should be min(stkprice) in the order statement

10.

List the stock dividends received from all stock in a nation for all nations that earn at least 170,000 (note to figure out what the stock dividends are you need to multiple the stkqty by the stkdiv and sum them up for each nation.  Print out the total dividends earned and the nations name

Select count(stkqty*stkdiv), natname
		From stock, nation
		Where sum(stkqty*stkdiv)>170,000
		Group by natcode
  1. Count should be sum
  2. After the from statement there needs to be: Where stock.natcode=nation.natcode
  3. The current where statement should be in a having clause.
  4. Natcode should be natname in the group by statement.

 

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