Banner

 

3 - The one-to-many Relationship
Skill Builders

A ship has a name, registration code, gross tonnage, and a year of construction. Ships are classified as cargo or passenger. Draw a data model for a ship.

ship

Create a relational database for the ship entity you modeled previously. Add some rows to the table.

CREATE TABLE ship (
   regcode        VARCHAR(15),
   shipname       VARCHAR(30),
   grosston       INTEGER,
   yearconstruct  SMALLINT,
   classification VARCHAR(10),
   PRIMARY KEY (regcode));

List those shares where the value of the holding exceeds one million.

SELECT shrfirm, shrqty, shrprice, shrqty*shrprice 
   AS shrvalue FROM shr 
      WHERE shrqty*shrprice > 1000000;

Find the name of the firm for which the value of the holding is greatest.

SELECT shrfirm FROM shr 
   WHERE shrqty*shrprice = (select MAX (shrqty*shrprice) FROM shr);

List the firms containing "ian" in their name.

SELECT shrfirm FROM shr WHERE shrfirm REGEXP 'ian';

Create a single table database using the data in the preceding table.

CREATE TABLE track (  
   trkid    INTEGER, 
   tracknum INTEGER, 
   title    VARCHAR(20), 
   length   DECIMAL(4,2), 
     PRIMARY KEY (trkid));

Justify your choice of data type for each of the attributes.

1. Write SQL commands for the following queries:

  1. Report all tracks between 4 and 5 minutes long.
  2. On what track is "Naima "?
  3. Sort the tracks by descending length.
  4. What tracks are longer than the average length of tracks on the CD?
  5. How many tracks are less than 5 minutes?
  6. What tracks start with "Cou "? 
  1. SELECT * FROM track
       WHERE length < 5 AND length > 4; 
  2. SELECT trkid FROM track
       WHERE title = 'Naima'; 
  3. SELECT * FROM track
       ORDER BY length DESC; 
  4. SELECT * FROM track
       WHERE length > (SELECT AVG(length) FROM track); 
  5. SELECT COUNT(trkid) FROM track
       WHERE length < 5; 
  6. SELECT trkid FROM track
       WHERE title REGEXP '^Cou';

3. Write SQL to answer the following queries:

  1. What is the longest track on Swing?
  2. What tracks on Swing include the word Java?
Both queries cannot be written because no information on a CD's name is contained in the database.

4. What is the data model missing?

As one can see from the answers to question 3, some important pieces of information are missing.
  1. The database does not give information on which CD a track is found.
  2. The artists of the tracks are also not included. Thus, one cannot find all songs by Manhattan Transfer with this database.

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: 17-Oct-2022