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.

1b.
Car: A car has a manufacturer, range name, and style code (e.g., a Honda Accord DX, where Honda is the manufacturer, Accord is the range, and DX is the style). A car also has a vehicle identification code, registration code, and color.

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

1d.
Cow: A dairy cow has a name, date of birth, breed (e.g., Holstein), and a numbered plastic ear tag.

2.
Do the following queries using SQL:
2a.
List a share's name and its code.
SELECT shrfirm, shrcode FROM shr;
2b.
List full details for all shares with a price less than one dollar.
SELECT * FROM shr 
   WHERE shrprice < 1.00;
shrcode shrfirm shrprice shrqty shrdiv shrpe
BE Burmese Elephant $0.07 154,713 $0.01 3
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;
2d.
Create a report showing firm name, share price, share holding, and total value of shares held. (Value of shares held is price times quantity.)
SELECT shrfirm, shrprice, shrqty, shrqty*shrprice
   FROM shr;
shrfirm
 shrprice shrqty expr1003
Abyssinian Ruby $31.82 22,010 $700,358.20
Burmese Elephant $0.07 154,713 $10,829.91
Bolivian Sheep $12.75 231,678 $2,953,894.50
Canadian Sugar $52.78 4,716 $248,910.48
Freedonia Copper $27.50 10,529 $289,547.50
Indian Lead & Zinc $37.75 6,390 $241,222.50
Nigerian Geese $35.00 12,323 $431,305.00
Patagonian Tea $55.25 12,635 $698,083.75
Royal Ostrich Farms $33.75 1,234,923 $41,678,651.25
Sri Lankan Gold $50.37 32,868 $1,655,561.16
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;
2f.
Report the total dividend payment of Patagonian Tea. (The total dividend payment is dividend times quantity.)
SELECT shrdiv*shrqty FROM shr 
   WHERE shrfirm = 'Patagonian Tea';
expr1000
$31,587.50
2g.
Find all shares where the price is less than 20 times the dividend.
SELECT shrcode, shrfirm FROM shr 
   WHERE shrprice < 20*shrdiv;
3h.
Find the share(s) with the minimum yield.
SELECT shrcode, shrfirm, shrdiv/shrprice*100 FROM shr
   WHERE shrdiv/shrprice = 
      (SELECT MIN(shrdiv/shrprice) FROM shr);
shrfirm
 expr1001
Abyssinian Ruby 4.14833438089252
2i.
Find the total value of all shares with a PE ratio > 10.
SELECT SUM (shrprice*shrqty) FROM shr  
   WHERE shrpe > 10;
2j.
Find the share(s) with the maximum total dividend payment.
SELECT shrcode, shrfirm FROM shr 
   WHERE shrdiv*shrqty = 
      (SELECT MAX(shrdiv*shrqty) FROM shr);
shrfirm
Royal Ostrich Farms
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');
2l.
Find the yield of all firms except Bolivian Sheep and Canadian Sugar.
SELECT shrcode, shrfirm, shrdiv/shrprice*100 FROM shr 
   WHERE shrfirm NOT IN ('Bolivian Sheep', 'Canadian Sugar');
shrfirm
 expr1001
Freedonia Copper 6.69090909090909
Patagonian Tea 4.52488687782805
Abyssinian Ruby 4.14833438089252
Sri Lankan Gold 5.3206273575541
Indian Lead & Zinc 7.94701986754967
Burmese Elephant 14.2857142857143
Nigerian Geese 4.8
Royal Ostrich Farms 8.88888888888889
2m.
Find the total value of the portfolio.
SELECT SUM (shrprice*shrqty) FROM shr;
2n.
List firm name and value in descending order of value.
SELECT shrfirm, shrprice*shrqty FROM shr 
   ORDER BY shrprice*shrqty DESC;
shrfirm
 expr1001
Royal Ostrich Farms $41,678,651.25
Bolivian Sheep $2,953,894.50
Sri Lankan Gold $1,655,561.16
Abyssinian Ruby $700,358.20
Patagonian Tea $698,083.75
Nigerian Geese $431,305.00
Freedonia Copper $289,547.50
Canadian Sugar $248,910.48
Indian Lead & Zinc $241,222.50
Burmese Elephant $10,829.91
2o.
List shares with a firm name containing "Gold."
SELECT shrcode,shrfirm FROM shr 
   WHERE shrfirm REGEXP 'Gold';
2p.
Find shares with a code starting with "B."
SELECT shrcode, shrfirm FROM shr 
   WHERE shrcode REGEXP '^B';
shrfirm
Burmese Elephant
Bolivian Sheep
3.
Run the following queries and explain the differences in output. Write each query as a manager might state it.
Question Answer
a. SELECT shrfirm FROM shr WHERE shrfirm NOT REGEXP 's' List shares with a firm name containing no lower case s.
b. SELECT shrfirm FROM shr WHERE shrfirm NOT REGEXP 'S' List shares with a firm name containing no upper case s.
c. SELECT shrfirm FROM shr WHERE shrfirm NOT REGEXP 's' AND shrfirm NOT REGEXP 'S' List all shares except those with a firm name containing an upper and a lower case s.
d. SELECT shrfirm FROM shr WHERE shrfirm NOT REGEXP 's' OR shrfirm NOT REGEXP 'S' List all shares with a firm name containing no upper or lower case s.
e. SELECT shrfirm FROM shr WHERE (shrfirm NOT REGEXP 's' AND shrfirm NOT REGEXP 'S') OR shrfirm REGEXP '^S' List all shares for firms whose name does not contain an upper and lower case s and list those firms whose names begin with an S.
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
4b.
Which alien has the most heads?
SELECT alname, alheads FROM alien 
   WHERE alheads = (SELECT MAX(alheads) FROM alien);
4c.
Are there any aliens with a double o in their names?
SELECT alname FROM alien 
   WHERE alname REGEXP '[o]{2}';
alname
Foozelgiga
Koofoopsiepus
4d.
How many aliens are chartreuse?
SELECT COUNT(*) FROM alien 
   WHERE alcolor = 'chartreuse';
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.

5b.
How should Eduardo physically organize his books to permit fast retrieval of a particular one?

Eduardo could physically organize his books on shelves. There are two alternatives to arranging these books. Eduardo could simply arrange them by ISBN number. This solution would help him quickly find a book when he is using the database. However, if he knows something about the book he is searching for, it may be more practical to order the books by author. This way he would be able to quickly find the book by Watson, Richard.

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.

6.
What is an identifier? Why does a data model have an identifier?

An identifier is an attribute of an entity which uniquely describes an instance of this entity. In other words, the value for this identifier must be different for each instance of the entity.

A data model has an identifier so that each instance of the entity can be identified uniquely. For example, customer last name would not be a good identifier since there could be two customers with the same name, e.g., Miller. If one of these customers named Miller complains about his transaction, it is impossible for the system to know which Miller has filed the complaint unless some additional information is available.

7.
What are entities?

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

8.
What is the entity integrity rule?

The entity integrity rule states that each instance of an entity must have a unique identifier, which is not null.

 

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