Solutions Manual > Kennesaw State University - IS 3280 Workbook 1 Answers 3 - Single Entity Answers to Exercises.

Document Content and Description Below

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 ma ... nufacturer, 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]

Last updated: 3 years ago

Preview 1 out of 22 pages