Microsoft PowerPoint - dbm F2022 L10 Q&A.pptx
XXXXXXXXXX, Database Management
Janusz Szczypula, CMU 1
Relational Alge
a
Examples
95 – 703
Janusz Szczypula, CMU
Relational Alge
a 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
XXXXXXXXXX, 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 ⨝ Publishe
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 ⨝ BookAutho
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
XXXXXXXXXX, 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 ⨝ Publishe
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
ooks 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 XXXXXXXXXXOrderItems
95-703, DBM 12
JTW
JTW
XXXXXXXXXX, 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 ⨝ Publishe
π 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
XXXXXXXXXXBODYBUILD IN 10 MINUTES A DAY 21-JAN XXXXXXXXXXFITNESS
XXXXXXXXXXREVENGE OF MICKEY 14-DEC XXXXXXXXXXFAMILY LIFE
XXXXXXXXXXBUILDING A CAR WITH TOOTHPICKS 18-MAR XXXXXXXXXXCHILDREN
XXXXXXXXXXDATABASE IMPLEMENTATION 04-JUN XXXXXXXXXXCOMPUTER
XXXXXXXXXXCOOKING WITH MUSHROOMS 28-FEB XXXXXXXXXXCOOKING
XXXXXXXXXXHOLY GRAIL OF ORACLE 31-DEC XXXXXXXXXX8 COMPUTER
XXXXXXXXXXHANDCRANKED COMPUTERS 21-JAN XXXXXXXXXXCOMPUTER
XXXXXXXXXXE-BUSINESS THE EASY WAY 01-MAR XXXXXXXXXXCOMPUTER
XXXXXXXXXXPAINLESS CHILD-REARING 17-JUL XXXXXXXXXX5 FAMILY LIFE
XXXXXXXXXXTHE WOK WAY TO COOK 11-SEP XXXXXXXXXXCOOKING
XXXXXXXXXXBIG BEAR AND LITTLE DOVE 08-NOV XXXXXXXXXXCHILDREN
XXXXXXXXXXHOW TO GET FASTER PIZZA 11-NOV XXXXXXXXXX5 SELF HELP
XXXXXXXXXXHOW TO MANAGE THE MANAGER 09-MAY XXXXXXXXXXBUSINESS
XXXXXXXXXXSHORTEST POEMS 01-MAY XXXXXXXXXXLITERATURE
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
XXXXXXXXXXS100
XXXXXXXXXXS100
XXXXXXXXXXJ100
XXXXXXXXXXP100
XXXXXXXXXXS100
XXXXXXXXXXW100
XXXXXXXXXXW105
XXXXXXXXXXW105
XXXXXXXXXXB100
XXXXXXXXXXF100
XXXXXXXXXXR100
XXXXXXXXXXB100
XXXXXXXXXXA100
XXXXXXXXXXK100
XXXXXXXXXXR100
XXXXXXXXXXA100
XXXXXXXXXXA105
XXXXXXXXXXP105
XXXXXXXXXXW100
XXXXXXXXXXJ100
PUBLISHER
PUBID NAME CONTACT PHONE
1 PRINTING IS US TOMMIE SEYMOUR XXXXXXXXXX
2 PUBLISH OUR WAY JANE TOMLIN XXXXXXXXXX
3 AMERICAN PUBLISHING DAVID DAVIDSON XXXXXXXXXX
4 READING MATERIALS INC. RENEE SMITH XXXXXXXXXX
5 REED-N-RITE SEBASTIAN JONES XXXXXXXXXX
CUSTOMERS
CUSTOMER# LASTNAME FIRSTNAME ADDRESS CITY STATE ZIP REFERRED REGION EMAIL
1001 MORALES BONITA P.O. BOX 651 EASTPOINT FL XXXXXXXXXXSE XXXXXXXXXX
1002 THOMPSON RYAN P.O. BOX 9835 SANTA MONICA CA XXXXXXXXXXW
1003 SMITH LEILA P.O. BOX 66 TALLAHASSEE FL XXXXXXXXXXSE
1004 PIERSON THOMAS 69821 SOUTH AVENUE BOISE ID XXXXXXXXXXNW XXXXXXXXXX
1005 GIRARD CINDY P.O. BOX 851 SEATTLE WA XXXXXXXXXXNW XXXXXXXXXX
1006 CRUZ MESHIA 82 DIRT ROAD ALBANY NY XXXXXXXXXXNE XXXXXXXXXX
1007 GIANA TAMMY 9153 MAIN STREET AUSTIN TX XXXXXXXXXXSW XXXXXXXXXX
1008 JONES KENNETH P.O. BOX 137 CHEYENNE WY XXXXXXXXXXN XXXXXXXXXX
1009 PEREZ JORGE P.O. BOX 8564 BURBANK CA XXXXXXXXXXW XXXXXXXXXX
1010 LUCAS JAKE 114 EAST SAVANNAH ATLANTA GA XXXXXXXXXXSE
1011 MCGOVERN REESE P.O. BOX 18 CHICAGO IL XXXXXXXXXXN XXXXXXXXXX
1012 MCKENZIE WILLIAM P.O. BOX 971 BOSTON MA XXXXXXXXXXNE XXXXXXXXXX
1013 NGUYEN NICHOLAS 357 WHITE EAGLE AVE CLERMONT FL XXXXXXXXXXSE XXXXXXXXXX
1014 LEE JASMINE P.O. BOX 2947 CODY WY XXXXXXXXXXN XXXXXXXXXX
1015 SCHELL STEVE P.O. BOX 677 MIAMI FL XXXXXXXXXXSE XXXXXXXXXX
1016 DAUM MICHELL XXXXXXXXXXLONG ROAD BURBANK CA XXXXXXXXXXW
1017 NELSON BECCA P.O. BOX 563 KALMAZOO MI XXXXXXXXXXN XXXXXXXXXX
1018 MONTIASA GREG 1008 GRAND AVENUE MACON GA XXXXXXXXXXSE XXXXXXXXXX
1019 SMITH JENNIFER P.O. BOX 1151 MORRISTOWN NJ XXXXXXXXXXNE
1020 FALAH KENNETH P.O. BOX 335 TRENTON NJ XXXXXXXXXXNE XXXXXXXXXX
PROMOTION
GIFT MINRETAIL MAXRETAIL
BOOKMARKER XXXXXXXXXX
BOOK LABELS XXXXXXXXXX
BOOK COVER XXXXXXXXXX
FREE SHIPPING XXXXXXXXXX
ORDERS
ORDER# CUSTOMER# ORDERDATE SHIPDATE SHIPSTREET SHIPCITY SHIPSTATE SHIPZIP SHIPCOST
XXXXXXXXXXMAR-09 02-APR XXXXXXXXXXORANGE AVE SEATTLE WA XXXXXXXXXX
XXXXXXXXXXMAR-09 01-APR XXXXXXXXXXEAST SAVANNAH ATLANTA GA XXXXXXXXXX
XXXXXXXXXXMAR-09 01-APR-09 58 TILA CIRCLE CHICAGO IL XXXXXXXXXX
XXXXXXXXXXAPR-09 01-APR XXXXXXXXXXMAGNOLIA LANE EASTPOINT FL XXXXXXXXXX
XXXXXXXXXXAPR-09 05-APR XXXXXXXXXXROUNDABOUT WAY TRENTON NJ 08601
XXXXXXXXXXAPR-09 02-APR XXXXXXXXXXGRAND AVENUE MACON GA XXXXXXXXXX
XXXXXXXXXXAPR-09 02-APR-09 558A CAPITOL HWY. TALLAHASSEE FL XXXXXXXXXX
XXXXXXXXXXAPR-09 04-APR XXXXXXXXXXMAIN STREET AUSTIN TX XXXXXXXXXX
XXXXXXXXXXAPR-09 03-APR XXXXXXXXXXSOUTH AVENUE BOISE ID XXXXXXXXXX
XXXXXXXXXXAPR-09 05-APR-09 9 LIGHTENING RD. SEATTLE WA 98110
XXXXXXXXXXAPR-09 04-APR XXXXXXXXXXWRONG WAY HOME MORRISTOWN NJ XXXXXXXXXX
XXXXXXXXXXAPR-09 05-APR XXXXXXXXXXWEST LAFAYETTE ATLANTA GA XXXXXXXXXX
XXXXXXXXXXAPR XXXXXXXXXXWINDY AVENUE KALMAZOO MI XXXXXXXXXX
XXXXXXXXXXAPR-09 04-APR XXXXXXXXXXMOUNTAIN RD. CODY WY XXXXXXXXXX
XXXXXXXXXXAPR-09 05-APR XXXXXXXXXXMAIN STREET AUSTIN TX XXXXXXXXXX
XXXXXXXXXXAPR XXXXXXXXXXGLITTER ST. TRENTON NJ XXXXXXXXXX
XXXXXXXXXXAPR XXXXXXXXXXSEMINOLE WAY TALLAHASSEE FL XXXXXXXXXX
XXXXXXXXXXAPR-09 05-APR XXXXXXXXXXHOT ASPHALT ST MIAMI FL XXXXXXXXXX
XXXXXXXXXXAPR XXXXXXXXXXHIGHWAY 98 EASTPOINT FL 32328
XXXXXXXXXXAPR XXXXXXXXXXGRAND AVENUE MACON GA XXXXXXXXXX
XXXXXXXXXXAPR XXXXXXXXXXJAMISON LANE CHEYENNE WY XXXXXXXXXX
ORDERITEMS
ORDER# ITEM# ISBN QUANTITY PAIDEACH
XXXXXXXXXX19.95
XXXXXXXXXX31.95
XXXXXXXXXX85.45
XXXXXXXXXX55.95
XXXXXXXXXX55.95
XXXXXXXXXX30.95
XXXXXXXXXX19.95
XXXXXXXXXX85.45
XXXXXXXXXX39.95
XXXXXXXXXX54.5
XXXXXXXXXX72.15
XXXXXXXXXX54.5
XXXXXXXXXX8.95
XXXXXXXXXX55.95
XXXXXXXXXX19.95
XXXXXXXXXX19.95
XXXXXXXXXX22
XXXXXXXXXX55.95
XXXXXXXXXX85.45
XXXXXXXXXX8.95
XXXXXXXXXX25
XXXXXXXXXX85.45
XXXXXXXXXX22
XXXXXXXXXX55.95
XXXXXXXXXX22
XXXXXXXXXX19.95
XXXXXXXXXX85.45