Banner

 

3 - Single Entity

Answers to exercises

1.
Draw data models for the following entities. In each case, make certain that you show the attributes and identifiers:
1a.
Aircraft: An aircraft has a manufacturer, model number, call sign (e.g., N123D), payload, and a year of construction. Aircraft are classified as civilian or military.

1c.
Restaurant: A restaurant has an address, seating capacity, phone number, and style of food (e.g., French, Russian, Chinese).

2.
Do the following queries using SQL:
2a.
List a share's name and its code.
SELECT shrfirm, shrcode FROM shr;
2c.
List the name and price of all shares with a price of at least $10.
SELECT shrfirm, shrprice FROM shr
   WHERE shrprice >= 10.00;
2e.
List the name of all shares with a yield exceeding 5 percent.
SELECT shrfirm, shrdiv/shrprice*100 FROM shr 
   WHERE shrdiv/shrprice*100 > 5;
2g.
Find all shares where the price is less than 20 times the dividend.
SELECT shrcode, shrfirm FROM shr 
   WHERE shrprice < 20*shrdiv;
2i.
Find the total value of all shares with a PE ratio > 10.
SELECT SUM (shrprice*shrqty) FROM shr  
   WHERE shrpe > 10;
2k.
Find the value of the holdings in Abyssinian Ruby and Sri Lankan Gold.
SELECT shrfirm, shrprice*shrqty FROM shr 
   WHERE shrfirm IN ('Abyssinian Ruby', 'Sri Lankan Gold');
2m.
Find the total value of the portfolio.
SELECT SUM (shrprice*shrqty) FROM shr;
2o.
List shares with a firm name containing "Gold."
SELECT shrcode,shrfirm FROM shr 
   WHERE shrfirm REGEXP 'Gold';
4.
A weekly newspaper, sold at supermarket checkouts, frequently reports stories of aliens visiting Earth and taking humans on short trips. Sometimes a captured human sees Elvis commanding the spaceship. Well, to keep track of all these reports, the newspaper has created the following data model. The paper has also supplied some data for the last few sightings and asked you to create the database and add details of these aliens. When you have created the database, answer the following queries:
CREATE TABLE alien (
   alno     INTEGER, 
   alname   VARCHAR(20), 
   alheads  INTEGER, 
   alcolor  VARCHAR(15), 
   alsmell  VARCHAR(20), 
     PRIMARY KEY (alno));
4a.
What's the average number of heads of an alien?
SELECT AVG(alheads) FROM alien;
expr1000
2.4
4c.
Are there any aliens with a double o in their names?
SELECT alname FROM alien 
   WHERE alname REGEXP '[o]{2}';
alname
Foozelgiga
Koofoopsiepus
4e.
Report details of all aliens sorted by smell and color.
SELECT * FROM alien 
   ORDER BY alsmell, alcolor;
alnum
alname
alheads
alcolor
alsmell
1 Foozelgiga 1 vermillion ambrosial
5 Eop 0 chartreuse new car
4 Meekamuncha 7 lavender old cabbage
3 Bunyippa 1 chartreuse perfumed
2 Koofoopsiepus 3 amethyst putrid
5.
Eduardo, a bibliophile, has a collection of several hundred books. Being a little disorganized, he has his books scattered around his den. They are piled on the floor, some are in bookcases, and others sit on top of his desk. Because he has so many books, he finds it difficult to remember what he has, and sometimes he cannot find the book he wants. Eduardo has a simple personal computer file system that is fine for a single entity or file. He has decided that he would like to list each book by author(s)' name and type of book (e.g., literature, travel, reference). Draw a data model for this problem, create a single entity table, and write some SQL queries.

CREATE TABLE book (
   isbn      CHAR(10), 
   author    VARCHAR(30), 
   title     VARCHAR(30), 
   edition   CHAR(2), 
   type      VARCHAR(20), 
   publisher VARCHAR(20), 
   year      INTEGER, 
     PRIMARY KEY (isbn)

Sample SQL queries:

SELECT isbn, author, title FROM book 
   WHERE author = 'Wesner, Rudy';
SELECT * FROM book 
   WHERE title REGEXP 'Mocking Bird';
5a.
How do you identify each instance of a book? (It might help to look at a few books.)

The ISBN number is a unique identifier for each book. However, it did not appear until around the 1960s so would not be useful for a collection containing some old books.

5c.
Are there any shortcomings with the data model you have created?

One shortcoming of this database is that it does not consider the possibility that Eduardo may actually have two copies of the same book. Also, there is no place to enter any detail information on the author or the publisher of the book.

Also, there is no possibility to enter information on the short story of a particular author published in a book of short stories. All of these shortcomings can be redeemed with a database containing several entities.

7.
What are entities?

Entities are things existing in the real world. Examples of entities are people, cars, and houses.

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: 02-Dec-2022