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 nu
...
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.
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 datamodel. 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 alno, 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
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 thespaceship. 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 alno, 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 putrid4 - The One-to-Many Relationship
Answers to exercises
1.
Draw data models for the following situations. In each case, make certain that you show the
attributes and feasible identifiers:
1a.
A farmer can have many cows, but a cow belongs to only one farmer.
1b.
A university has many students, and a student can attend at most one university.
1c.
An aircraft can have many passengers, but a passenger can be on only one flight at a time.
1d.
A nation can have many states and a state many cities.
1e.An art researcher has asked you to design a database to record details of artists and the museums
in which their paintings are displayed. For each painting, the researcher wants to know the size
of the canvas, year painted, title, and style. The nationality, date of birth, and death of each artist
must be recorded. For each museum, record details of its location and specialty, if it has one.
2.
Report all values in British pounds:
2a.
Report the value of stocks listed in Australia.
SELECT stkcode, stkfirm, stkprice*stkqty*exchrate
FROM stock, nation
WHERE stock.natcode = nation.natcode
AND natname = 'Australia';
2b.
Report the dividend payment of all stocks.
SELECT stkcode, stkfirm, stkdiv*stkqty*exchrate
FROM stock, nation
WHERE stock.natcode = nation.natcode;
stkfirm expr1001
Freedonia Copper 19373.36
Patagonian Tea 31587.5
Abyssinian Ruby 29053.2
Sri Lankan Gold 88086.24
Indian Lead & Zinc 19170
Burmese Elephant 1547.13
Bolivian Sheep 412386.84
Nigerian Geese 20702.64
Canadian Sugar 11790
Royal Ostrich Farms 3704769
Minnesota Gold 546801.753620505
Georgia Peach 51902.6232928634
Narembeen Emu 20984.7403806746
Queensland Diamond 20527.7303723842
Indooroopilly Ruby 12913.8102342635
Bombay Duck 3816.309664352c.
Report the total dividend payment by nation.
SELECT natname, SUM(stkdiv*stkqty*exchrate)
FROM stock, nation
WHERE stock.natcode = nation.natcode
GROUP BY natname;
2d.
Create a view containing nation, firm, price, quantity, exchange rate, value, and yield.
CREATE VIEW yield(nation, firm, price, qty, exchrate, value, yield)
AS SELECT natname, stkfirm, stkprice, stkqty, exchrate,
stkprice*stkqty*exchrate,stkdiv/stkprice*100*exchrate
FROM stock, nation
WHERE stock.natcode = nation.natcode;
MS Access does not support the SQL CREATE VIEW statement. However, you can create the
equivalent of a view by saving a query and then using the name of the query as a table name. In
other words, in MS Access a saved query is a view. So you might write the following:
2e.
Report the average yield by nation.
SELECT natname, AVG(stkdiv/stkprice*exchrate*100)
FROM stock, nation
WHERE stock.natcode = nation.natcode group BY natname;
2f.
Report the minimum and maximum yield for each nation.
SELECT natname, MIN(stkdiv/stkprice*100), MAX(stkdiv/stkprice*100)
FROM stock,nation
WHERE stock.natcode = nation.natcode
GROUP BY natname;
natname expr1001 expr1002
Australia 3.14070351758794 8.10372771474878
India 3.91389432485323 3.91389432485323
United Kingdom 4.14833438089252 14.2857142857143
United States 1.85632077222944 8.51063829787234
2g.
Report the nations where the average yield of stocks exceeds the average yield of all stocks.
SELECT natname, AVG(stkdiv/stkprice*100)
FROM stock, nation
WHERE stock.natcode = nation.natcode
GROUP BY natname
HAVING AVG(stkdiv/stkprice*100) >
(SELECT AVG(stkdiv/stkprice*100) FROM stock);
7.
An orchestra has four broad classes of instruments (strings, woodwinds, brass, and percussion).
Each class contains musicians who play different instruments. For example, the strings section of
a full symphonic orchestra contains 2 harps, 16 to 18 first violins, 14 to 16 second violins, 12
violas, 10 cellos, and 8 double basses. A city has asked you to develop a database to store detailsof the musicians in its three orchestras. All the musicians are specialists and play only one
instrument for one orchestra.
8.
Answer the following queries based on the following database for a car dealer:
8a.
What is the personid of Sheila O'Hara?
SELECT personid FROM person
WHERE `person first` = 'Sheila'
AND `person last` = "O'Hara";
personid
21
8b.
List sales personnel sorted by last name and within last name, first name.
SELECT * FROM person
ORDER BY `person last`,`person first`;
8c.
List details of the sales made by Bruce Bush.
SELECT cartype, carcost, carsell
FROM person, car
WHERE person.personid = car.personid
AND `person first` = 'Bruce'
AND `person last` = 'Bush';
cartype carcost carsell
coupe 15500.00 14750.00
sports 1255.00 1355.00
sedan 950.00 2000.00
8d.
List details of all sales showing the gross profit (selling price minus cost price).
SELECT `person first`, `person last`, carid, carcost, carsell, cartype,
carsell-carcost
FROM car, person
WHERE person.personid = car.personid;
8e.
Report the number of cars sold of each type.
SELECT cartype, COUNT(cartype)
FROM car GROUP BY cartype;
cartype expr1001
coupe 2
sedan 5sports 3
8f.
What is the average selling price of cars sold by Sue Lim?
SELECT AVG(carSELL)
FROM car, person
WHERE car.personid = person.personid
AND `person first` = 'Sue'
AND `person last` = 'Lim';
8g.
Report details of all sales where the gross profit is less than the average.
SELECT `person first`, `person last`, cartype, carsell-carcost AS profit
FROM car, person
WHERE person.personid = car.personid
AND (carsell-carcost)
< (SELECT AVG(carsell-carcost) FROM car);
person first person last cartype profit
Nolan Haley sedan 295.00
Bruce Bush coupe -750.00
Bruce Bush sports 100.00
Bruce Bush sedan 1050.00
Sue Lim sedan 600.00
Barbara Capelli sedan 500.00
8h.
What was the maximum selling price of any car?
SELECT MAX(carsell) FROM car;
8i.
What is the total gross profit?
SELECT SUM(carsell-carcost) as `Gross profit` FROM car;
Gross profit
11295.00
8j.
Report the gross profit made by each salesperson who sold at least three cars.
SELECT `person first`, `person last`, SUM(carsell-carcost)
FROM car, person
WHERE person.personid=car.personid
GROUP BY `person first`, `person last`
HAVING COUNT(*) > 2;
8k.
Create a view containing all the details in the car table and the gross profit.
CREATE VIEW PROFIT(id, type, cost, sell, profit)
AS SELECT carid, cartype,carcost, carsell,carsell-carcost FROM car;5 - The Many-to-Many Relationship
Answers to exercises
1.
Draw data models for the following situations. In each case, think about the names you give each
entity:
1a.
Farmers can own cows or share cows with other farmers.
1b.
A track and field meet can have many competitors, and a competitor can participate in more than
one event.
1c.
A patient can have many physicians, and a physician can have many patients.1d.
A student can attend more than one class, and the same class can have many students.
1e.
The Marathoner, a monthly magazine, regularly reports the performance of professional
marathon runners. It has asked you to design a database to record the details of all major
marathons (e.g., Boston, London, and Paris). Professional marathon runners compete in several
races each year. A race may have thousands of competitors, but only about 200 or so are
professional runners, the ones The Marathoner tracks. For each race, the magazine reports a
runner's time and finishing position and some personal details such as name, gender, and age.3.
Write the following SQL queries for the database described in this chapter:
3a.
List the names of items for which the quantity sold is greater than one for any sale.
SELECT itemname FROM item, lineitem WHERE item.itemno = lineitem.itemno AND
lineqty > 1;
itemname lineqty
Safari chair 50
Hammock 50
Tent - 8 person 8
3b.
Compute the total value of sales for each item by date.
SELECT itemname, saledate, SUM(lineqty*lineprice)
FROM item, lineitem, sale
WHERE sale.saleno = lineitem.saleno AND item.itemno = lineitem.itemno
GROUP BY itemname, saledate;
itemname expr101
Geo positioning system 2
Hammock 50
Pocket knife - Avon 5
Safari chair 50
Tent - 2 person 2
Tent - 8 person 8
3c.
Report all items of type “F” that have been sold.
SELECT DISTINCT(itemname) FROM item, lineitem
WHERE item.itemno = lineitem.itemno AND itemtype = 'F';
itemname
HammockSafari chair
Tent - 2 person
Tent - 8 person
3d.
List all items of type “F” that have not been sold.
SELECT DISTINCT(itemname) FROM item
WHERE itemtype = 'F'
AND itemname NOT IN
(SELECT itemname FROM item, lineitem
WHERE item.itemno = lineitem.itemno AND itemtype = 'F');
3e.
Compute the total value of each sale.
SELECT sale.saleno, SUM (lineqty * lineprice)
FROM sale, lineitem
WHERE sale.saleno = lineitem.saleno
GROUP BY sale.saleno;
saleno sum
1 4.50
2 72.25
3 502.25
4 712.25
5 5109.00
7.
Answer the following queries based on the described relational database.
7a.
List the phone numbers of donors with last names Hays or Jefts.
SELECT dphone FROM donor
WHERE dlname = 'Hays' OR dlname = 'Jefts';;
dphone
1352
8103
7b.
How many donors are there in the donor table?
SELECT COUNT (*) FROM donor;
7c.
How many people made donations in 1999?
SELECT COUNT(donorno) FROM gift
WHERE year = 1999;
expr1000
10
7d.
What is the name of the person who made the largest donation in 1999?SELECT dlname, dfname FROM donor, gift
WHERE donor.donorno = gift.donorno
AND year = 1999
AND amount = (SELECT MAX(amount) FROM gift WHERE year = 1999);
7e.
What was the total amount donated in 2000?
SELECT SUM (amount) FROM gift
WHERE year = 2000;
expr1000
$6,768.00
7f.
List the donors who have made a donation every year.
SELECT dlname, dfname FROM donor
WHERE NOT EXISTS (SELECT * FROM year
WHERE NOT EXISTS (SELECT * FROM gift
WHERE donor.donorno = gift.donorno
AND year.year = gift.year));
7g.
List the donors who give twice the average.
SELECT dlname, dfname, AVG(amount)
FROM donor, gift
WHERE donor.donorno = gift.donorno
GROUP BY dlname, dfname
HAVING AVG(amount) > (SELECT 2*AVG(amount) FROM gift);
dfname dlname expr1002
Gulsen Beckman $2,405.00
7h.
List the total amount given by each person across all years; sort the report by the donor
SELECT dlname, dfname, SUM (amount)
FROM donor, gift
WHERE donor.donorno = gift.donorno
GROUP BY dlname, dfname
ORDER BY dlname, dfname;
7i.
Report the total donations in 2001 by state.
SELECT DSTATE, SUM(amount)
FROM donor, gift
WHERE donor.donorno = gift.donorno
AND year = 2001 GROUP BY dstate;
dstate expr1001
AK $332.00
AZ $155.00
GA $671.00
MA $499.00
MN $835.00
NC $966.00ND $345.00
WA $5,208.00
7j.
In which years did the total donated exceed the goal for the year?
SELECT year.year, yeargoal, SUM(amount)
FROM year, gift
WHERE year.year = gift.year
AND yeargoal < (SELECT SUM (amount)
FROM gift
WHERE year.year = gift.year)
GROUP BY year.year;
8.
The following table records data found on the side of a breakfast cereal carton. Use these data as
a guide to develop a data model to record nutrition facts for a meal. In this case, a meal is a cup
of cereal and 1/2 cup of skim milk.
Nutrition facts
Serving size 1 cup (30g)
Servings per container about 17
Amount per serving Cereal
with 1/2 cup
of skim milk
Calories 110 150
Calories from Fat 10 10
Total Fat 1g 1% 2%
[Show More]