the first two files are the examples for this assignment, the next three photos are the assignment
Microsoft PowerPoint - dbm F2022 L10 Q&A.pptx 95 - 703, Database Management Janusz Szczypula, CMU 1 Relational Algebra Examples 95 – 703 Janusz Szczypula, CMU Relational Algebra Operations Selection: predicate (R) Projection: col1, . . . , coln (R) Union: R S Intersection: R S Set Difference: R – S Cartesian Product: R × S Rename: ρ Join Operations: ⨝ Theta-join, Equi-join, Natural join, Outer join, Semi-join Division: R ÷ S 95-703, DBM 2 Exercise 1 List book ID and book title for every book in computer category π ISBN, Title σ Category ‘COMPUTER’ Books 95-703, DBM 3 Exercise 2 List title, price, and cost of books that have expected profit of less than 20% of the book’s cost π Title, Retail, Cost σ Retail – Cost 0.2 *Cost Books 95-703, DBM 4 JTW JTW JTW 95 - 703, Database Management Janusz Szczypula, CMU 2 Exercise 3 Find the book code and title for every book published by “Reed-N-Rite” or book with price over $25 π ISBN, Title σ Retail 25 OR Name ‘REED-N-RITE’ Books ⨝ Publisher 95-703, DBM 5 Exercise 4 List author IDs of books that are in either children or family life categories. Use union operation. π AuthorID σ Category ‘FAMILY LIFE’ Books ⨝ BookAuthor ∪ π AuthorID σ Category ‘CHILDREN’ Books ⨝ BookAuthor 95-703, DBM 6 Exercise 5 List customer number of those customers who did not place any order in April 2009. Use set difference. π Customer# Customers – π Customer# σ ordderdate ‘01-Apr-2009’AND ordderdate ‘30-Apr-2009’ Orders 95-703, DBM 7 Exercise 6 List the ID, title, and price for “Family Life” books with price that is less than $50. Use a set operation. π ISBN, Title, Retail σ Retail 50 Books ∩ π ISBN, Title, Retail σ Category ‘FAMILY LIFE’ Books 95-703, DBM 8 JTW JTW 95 - 703, Database Management Janusz Szczypula, CMU 3 Exercise 7 Find any book that cost more than $15 and is either published by “Reading Materials Inc.” or is in the “Family Life” category. List title, publisher ID, cost, and category. π Title, PubID, Cost, Category σ Category ‘FAMILY LIFE’ OR Name ‘Reading Materials Inc.’ AND Cost 15 Books ⨝ Publisher 95-703, DBM 9 Exercise 8 Find any information we have in the database on authors who wrote computer books. Include only the information in Author table. Author ⊳ BookAuthor⨝ σ Category ‘COMPUTER’ Books 95-703, DBM 10 Exercise 9 List book code and book title for every pair of books that have the same publisher and are of the same category π B1.ISBN, B1.Title, B2.ISBN, B2.Title σ B1.PubID B2.PubID AND B1.Category B2.Category AND B1.ISBN B2.ISBN ρ B1 Books ρ B2 Books 95-703, DBM 11 Exercise 10 List book id and title for books in cooking category. For books that were ordered include the order# and quantity of the book on that order. π ISBN, Title, Order#, Quantity σ Category ’COOKING’ Books OrderItems 95-703, DBM 12 JTW JTW 95 - 703, Database Management Janusz Szczypula, CMU 4 Exercise 11 Do we get all our books in the cooking category from a single publisher? If we do, then provide the name and ID of the publisher. π PubID, Name, ISBN Books ⨝ Publisher π ISBN σ Category ’COOKING’ Books 95-703, DBM 13 JTW JTW Microsoft Word - Tables of JustLee Books.doc BOOKS ISBN TITLE PUBDATE PUBID COST RETAIL DISCOUNT CATEGORY 1059831198 BODYBUILD IN 10 MINUTES A DAY 21-JAN-05 4 18.75 30.95 FITNESS 0401140733 REVENGE OF MICKEY 14-DEC-05 1 14.20 22.00 FAMILY LIFE 4981341710 BUILDING A CAR WITH TOOTHPICKS 18-MAR-06 2 37.80 59.95 3 CHILDREN 8843172113 DATABASE IMPLEMENTATION 04-JUN-03 3 31.40 55.95 COMPUTER 3437212490 COOKING WITH MUSHROOMS 28-FEB-04 4 12.50 19.95 COOKING 3957136468 HOLY GRAIL OF ORACLE 31-DEC-05 3 47.25 75.95 3.8 COMPUTER 1915762492 HANDCRANKED COMPUTERS 21-JAN-05 3 21.80 25.00 COMPUTER 9959789321 E-BUSINESS THE EASY WAY 01-MAR-06 2 37.90 54.50 COMPUTER 2491748320 PAINLESS CHILD-REARING 17-JUL-04 5 48.00 89.95 4.5 FAMILY LIFE 0299282519 THE WOK WAY TO COOK 11-SEP-04 4 19.00 28.75 COOKING 8117949391 BIG BEAR AND LITTLE DOVE 08-NOV-05 5 5.32 8.95 CHILDREN 0132149871 HOW TO GET FASTER PIZZA 11-NOV-06 4 17.85 29.95 1.5 SELF HELP 9247381001 HOW TO MANAGE THE MANAGER 09-MAY-03 1 15.40 31.95 BUSINESS 2147428890 SHORTEST POEMS 01-MAY-05 5 21.85 39.95 LITERATURE AUTHOR AUTHORID LNAME FNAME S100 SMITH SAM J100 JONES JANICE A100 AUSTIN JAMES M100 MARTINEZ SHEILA K100 KZOCHSKY TAMARA P100 PORTER LISA A105 ADAMS JUAN B100 BAKER JACK P105 PETERSON TINA W100 WHITE WILLIAM W105 WHITE LISA R100 ROBINSON ROBERT F100 FIELDS OSCAR W110 WILKINSON ANTHONY BOOKAUTHOR ISBN AUTHORID 0132149871 S100 0299282519 S100 0401140733 J100 1059831198 P100 1059831198 S100 1915762492 W100 1915762492 W105 2147428890 W105 2491748320 B100 2491748320 F100 2491748320 R100 3437212490 B100 3957136468 A100 4981341710 K100 8117949391 R100 8843172113 A100 8843172113 A105 8843172113 P105 9247381001 W100 9959789321 J100 PUBLISHER PUBID NAME CONTACT PHONE 1 PRINTING IS US TOMMIE SEYMOUR 000-714-8321 2 PUBLISH OUR WAY JANE TOMLIN 010-410-0010 3 AMERICAN PUBLISHING DAVID DAVIDSON 800-555-1211 4 READING MATERIALS INC. RENEE SMITH 800-555-9743 5 REED-N-RITE SEBASTIAN JONES 800-555-8284 CUSTOMERS CUSTOMER# LASTNAME FIRSTNAME ADDRESS CITY STATE ZIP REFERRED REGION EMAIL 1001 MORALES BONITA P.O. BOX 651 EASTPOINT FL 32328 SE
[email protected] 1002 THOMPSON RYAN P.O. BOX 9835 SANTA MONICA CA 90404 W 1003 SMITH LEILA P.O. BOX 66 TALLAHASSEE FL 32306 SE 1004 PIERSON THOMAS 69821 SOUTH AVENUE BOISE ID 83707 NW
[email protected] 1005 GIRARD CINDY P.O. BOX 851 SEATTLE WA 98115 NW
[email protected] 1006 CRUZ MESHIA 82 DIRT ROAD ALBANY NY 12211 NE
[email protected] 1007 GIANA TAMMY 9153 MAIN STREET AUSTIN TX 78710 1003 SW
[email protected] 1008 JONES KENNETH P.O. BOX 137 CHEYENNE WY 82003 N
[email protected] 1009 PEREZ JORGE P.O. BOX 8564 BURBANK CA 91510 1003 W
[email protected] 1010 LUCAS JAKE 114 EAST SAVANNAH ATLANTA GA 30314 SE 1011 MCGOVERN REESE P.O. BOX 18 CHICAGO IL 60606 N
[email protected] 1012 MCKENZIE WILLIAM P.O. BOX 971 BOSTON MA 02110 NE
[email protected] 1013 NGUYEN NICHOLAS 357 WHITE EAGLE AVE CLERMONT FL 34711 1006 SE
[email protected] 1014 LEE JASMINE P.O. BOX 2947 CODY WY 82414 N
[email protected] 1015 SCHELL STEVE P.O. BOX 677 MIAMI FL 33111 SE
[email protected] 1016 DAUM MICHELL 9851231 LONG ROAD BURBANK CA 91508 1010 W 1017 NELSON BECCA P.O. BOX 563 KALMAZOO MI 49006 N
[email protected] 1018 MONTIASA GREG 1008 GRAND AVENUE MACON GA 31206 SE
[email protected] 1019 SMITH JENNIFER P.O. BOX 1151 MORRISTOWN NJ 07962 1003 NE 1020 FALAH KENNETH P.O. BOX 335 TRENTON NJ 08607 NE
[email protected] PROMOTION GIFT MINRETAIL MAXRETAIL BOOKMARKER 0.00 12.00 BOOK LABELS 12.01 25.00 BOOK COVER 25.01 56.00 FREE SHIPPING 56.01 999.99 ORDERS ORDER# CUSTOMER# ORDERDATE SHIPDATE SHIPSTREET SHIPCITY SHIPSTATE SHIPZIP SHIPCOST 1000 1005 31-MAR-09 02-APR-09 1201 ORANGE AVE SEATTLE WA 98114 2 1001 1010 31-MAR-09 01-APR-09 114 EAST SAVANNAH ATLANTA GA 30314 3 1002 1011 31-MAR-09 01-APR-09 58 TILA CIRCLE CHICAGO IL 60605 3 1003 1001 01-APR-09 01-APR-09 958 MAGNOLIA LANE EASTPOINT FL 32328 4 1004 1020 01-APR-09 05-APR-09 561 ROUNDABOUT WAY TRENTON NJ 08601 1005 1018 01-APR-09 02-APR-09 1008 GRAND AVENUE MACON GA 31206 2 1006 1003 01-APR-09 02-APR-09 558A CAPITOL HWY. TALLAHASSEE FL 32307 2 1007 1007 02-APR-09 04-APR-09 9153 MAIN STREET AUSTIN TX 78710 7 1008 1004 02-APR-09 03-APR-09 69821 SOUTH AVENUE BOISE ID 83707 3 1009 1005 03-APR-09 05-APR-09 9 LIGHTENING RD. SEATTLE WA 98110 1010 1019 03-APR-09 04-APR-09 384 WRONG WAY HOME MORRISTOWN NJ 07960 2 1011 1010 03-APR-09 05-APR-09 102 WEST LAFAYETTE ATLANTA GA 30311 2 1012 1017 03-APR-09 1295 WINDY AVENUE KALMAZOO MI 49002 6 1013 1014 03-APR-09 04-APR-09 7618 MOUNTAIN RD. CODY WY 82414 2 1014 1007 04-APR-09 05-APR-09 9153 MAIN STREET AUSTIN TX 78710 3 1015 1020 04-APR-09 557 GLITTER ST. TRENTON NJ 08606 2 1016 1003 04-APR-09 9901 SEMINOLE WAY TALLAHASSEE FL 32307 2 1017 1015 04-APR-09 05-APR-09 887 HOT ASPHALT ST MIAMI FL 33112 3 1018 1001 05-APR-09 95812 HIGHWAY 98 EASTPOINT FL 32328 1019 1018 05-APR-09 1008 GRAND AVENUE MACON GA 31206 2 1020 1008 05-APR-09 195 JAMISON LANE CHEYENNE WY 82003 2 ORDERITEMS ORDER# ITEM# ISBN QUANTITY PAIDEACH 1000 1 3437212490 1 19.95 1001 1 9247381001 1 31.95 1001 2 2491748320 1 85.45 1002 1 8843172113 2 55.95 1003 1 8843172113 1 55.95 1003 2 1059831198 1 30.95 1003 3 3437212490 1 19.95 1004 1 2491748320 2 85.45 1005 1 2147428890 1 39.95 1006 1 9959789321 1 54.5 1007 1 3957136468 3 72.15 1007 2 9959789321 1 54.5 1007 3 8117949391 1 8.95 1007 4 8843172113 1 55.95 1008 1 3437212490 2 19.95 1009 1 3437212490 1 19.95 1009 2 0401140733 1 22 1010 1 8843172113 1 55.95 1011 1 2491748320 1 85.45 1012 1 8117949391 1 8.95 1012 2 1915762492 2 25 1012 3 2491748320 1 85.45 1012 4 0401140733 1 22 1013 1 8843172113 1 55.95 1014 1 0401140733 2 22 1015 1 3437212490 1 19.95 1016 1 2491748320 1 85.45