Midterm Review Môn Principle of database management | Trường Đại học Quốc tế, Đại học Quốc gia Thành phố Hồ Chí Minh

lOMoAR cPSD| 58504431
ÔN TẬP MIDTERM PRINCIPLES OF DATABASE MANAGEMENT
● Hình thức: Tự Luận.
● Nội dung: RA, Tree, SQL Server, ERD I. RA A. First Schema
Suppliers(sID, sName, address)
Parts(pID, pName, colour)
Catalog(sID, pID, price)
Catalog[sID] Suppliers[sID]
Catalog[pID] Parts[pID] Note:
● In this schema, everywhere we want values to match across relations,
the attributes have matching names. And everywhere the attributes
have matching names, we want values to match across relations.
● This means that natural join will do exactly what we want in all cases. 1.
If sID is a key for the Suppliers relation, could it be a key for the Catalog relation? 2.
Find the names of all red parts. lOMoAR cPSD| 58504431 3.
Find all prices for parts that are red or green. (A part
may have different prices from different manufacturers.) 4.
Find the sIDs of all suppliers who supply a part that is red or green. 5.
Find the sIDs of all suppliers who supply a part that is red and green. 6.
Find the names of all suppliers who supply a part that is red or green. B. Second Schema
Employees(number, name, age, salary)
Supervises(boss, employee)
Supervises[boss] Empploeees[uupber]E Supervises[epploeee] mpploeees[uupber] Note:
● In this schema, wherever we want values to match across relations,
the attributes do not have matching names. This means that natural
join will not force things to match up as we’d like.
● In fact, since there are no attribute names in common across the two
relations, natural join is no different from Cartesian product.
● We are forced to use selection to enforce the necessary matching. lOMoAR cPSD| 58504431 1.
What does it say about our domain that employee is a key for Supervises? 2.
Does the schema allow for an employee with no boss? 3.
How would the world have to be different if boss were a key for Supervises? 4.
How would the world have to be different if both boss and
employee together were a key for Supervises? 5.
Find the names and salaries of all bosses who have an
employee earning more than 100. C. Third Schema
This schema is for a salon. Services could be things like “haircut” or “manicure”.
Clients(CID, name, phone) Staff(SID, name)
Appointments(CID, date, time, service, SID)
Appoiutpeuts[CID] Clieuts[CID]
Appoiutpeuts[SID] Staff[SID] Note:
● In this schema, everywhere we want values to match across relations,
the attributes have matching names. But there are also attributes with lOMoAR cPSD| 58504431
matching names whose values we do not want to match across relations.
● In those cases, that natural join will get rid of many tuples that we
need, so we must use Cartesian product and make any necessary
matching happen using select. (Unless we can remove the problem attributes first.). 1.
Find the appointment time and client name of all
appointments for staff member Giuliano on Feb14.
(Assume that you can compare a date value to “Feb 14”
using “=”). At each step, use projection to pare down to only the attributes you need. 2.
Now solve the same problem but begin by putting all three
relations together in full — with all of their attributes. 3. Which answer is better? II. Tree
Các bạn tự xem file Tree rồi sau đó luyện tập đổi tất cả các RELATIONAL
ALGEBRA các bạn đã làm bên trên thành dạng Tree nhé. III. SQL Server 1.
Consider the following relations: lOMoAR cPSD| 58504431
The meaning of these relations is straightforward; for example, Enrolled has
one record per student-class pair such that the student is enrolled in the class.
Write the following queries in SQL. No duplicates should be printed in any of the answers. a)
FiudEtheEuapesEofEallEJuuiorsE(levelE=EJR)EwhoEareEeurolledE
iuEaEclassEtaughtEbeEI.ETeach. b)
FiudEtheEageEofEtheEoldestEstudeutEwhoEisEeitherEaEHistoreE
pajorEorEeurolledEiuEaEcourseEtaughtEbeEI.ETeach. c)
FiudEtheEuapesEofEallEclassesEthatEeitherEpeetEiuEroopER128E
orEhaveEfiveEorEporeEstudeutsEeurolled. d)
FiudE theE uapesE ofE allE studeutsE whoE areE eurolledE iuE twoE
classesEthatEpeetEatEtheEsapeEtipe. e)
FiudE theE uapesE ofE faculteE pepbersE whoE teachE iuE evereE
roopEiuEwhichEsopeEclassEisEtaught. f)
FiudEtheEuapesEofEfaculteEpepbersEforEwhopEtheEcopbiuedE
eurollpeutEofEtheEcoursesEthatEtheeEteachEisElessEthauEfive. g)
ForE eachE level,E priutE theE levelE audE theE averageE ageE ofE studeutsEforEthatElevel. h)
ForEallElevelsEexceptEJR,EpriutEtheElevelEaudEtheEaverageEageE
ofEstudeutsEforEthatElevel. i)
ForE eachE faculteE pepberE thatE hasE taughtE classesE ouleE iuE
roopER128,EpriutEtheEfaculteEpepber’sEuapeEaudEtheEtotalE
uupberEofEclassesEsheEorEheEhasEtaught. j)
FiudE theE uapesE ofE studeutsE eurolledE iuE theE paxipupE uupberEofEclasses. lOMoAR cPSD| 58504431 k)
FiudEtheEuapesEofEstudeutsEuotEeurolledEiuEaueEclass. l)
ForEeachEageEvalueEthatEappearsEiuEStudeuts,EfiudEtheElevelE
valueE thatE appearsE postE ofteu.E ForE exapple,E ifE thereE areE
poreEFRElevelEstudeutsEagedE18EthauESR,EJR,EorESOEstudeutsE
agedE18,EeouEshouldEpriutEtheEpairE(18,EFR). 2.
The following relations keep track of airline flight information:
Note that the Employees relation describes pilots and other kinds of
employees as well; every pilot is certified for some aircraft, and only pilots
are certified to fly. Write each of the following queries in SQL. (Additional
queries using the same schema are listed in the exercises for Chapter 4.) a)
FiudEtheEuapesEofEaircraftEsuchEthatEallEpilotsEcertifiedEtoE
operateEthepEhaveEsalariesEporeEthauE$80,000. b)
ForEeachEpilotEwhoEisEcertifiedEforEporeEthauEthreeEaircraft,E
fiudEtheEeidEaudEtheEpaxipupEcruisiugraugeEofEtheEaircraftE
forEwhichEsheEorEheEisEcertified. c)
FiudEtheEuapesEofEpilotsEwhoseEsalareEisElessEthauEtheEpriceE
ofEtheEcheapestErouteEfropELosEAugelesEtoEHouolulu. d)
ForEallEaircraftE withEcruisiugraugeE overE1000Epiles,EfiudE
theEuapeEofEtheEaircraftEaudEtheEaverageEsalareEofEallEpilotsE
certifiedEforEthisEaircraft. e)
FiudEtheEuapesEofEpilotsEcertifiedEforEsopeEBoeiugEaircraft. lOMoAR cPSD| 58504431 f)
FiudEtheEaidsEofEallEaircraftEthatEcauEbeEusedEouEroutesEfropE LosEAugelesEtoEChicago. g)
IdeutifeEtheEroutesEthatEcauEbeEpilotedEbeEevereEpilotEwhoE pakesEporeEthauE$100,000. h)
PriutE theE euapesE ofE pilotsE whoE cauE operateE plauesE withE
cruisiugraugeEgreaterEthauE3000EpilesEbutEareEuotEcertifiedE ouEaueEBoeiugEaircraft. i)
AEcustoperEwautsEtoEtravelEfropEMadisouEtoENewEYorkEwithE
uoE poreE thauE twoE chaugesE ofE flight.E ListE theE choiceE ofE
departureE tipesE fropE MadisouE ifE theE custoperE wautsE toE
arriveEiuENewEYorkEbeE6Ep.p. j)
CopputeE theE differeuceE betweeuE theE averageE salareE ofE aE
pilotE audE theE averageE salareE ofE allE epploeeesE (iucludiugE pilots). k)
PriutEtheEuapeEaudEsalareEofEevereEuoupilotEwhoseEsalareE
isEporeEthauEtheEaverageEsalareEforEpilots. l)
PriutE theE uapesE ofE epploeeesE whoE areE certifiedE ouleE ouE
aircraftsEwithEcruisiugEraugeElougerEthauE1000Epiles. p)
PriutE theE uapesE ofE epploeeesE whoE areE certifiedE ouleE ouE
aircraftsEwithEcruisiugEraugeElougerEthauE1000Epiles,EbutE
ouEatEleastEtwoEsuchEaircrafts. u)
PriutE theE uapesE ofE epploeeesE whoE areE certifiedE ouleE ouE
aircraftsEwithEcruisiugEraugeElougerEthauE1000EpilesEaudE
whoEareEcertifiedEouEsopeEBoeiugEaircraft. lOMoAR cPSD| 58504431 3.
Consider the following relational schema and briefly
answer the questions that follow a)
DefiueEaEtableEcoustraiutEouEmppEthatEwillEeusureEthatEevereE
epploeeeEpakesEatEleastE$10,000. b)
DefiueEaEtableEcoustraiutEouEDeptEthatEwillEeusureEthatEallE
pauagersEhaveEageE>E30. c)
DefiueE auE assertiouE ouE DeptE thatE willE eusureE thatE allE
pauagersE haveE ageE >E 30.E CoppareE thisE assertiouE withE theE
equivaleutEtableEcoustraiut.EmxplaiuEwhichEisEbetter. d)
WriteE SQLE statepeutsE toE deleteE allE iuforpatiouE aboutE
epploeeesEwhoseEsalariesEexceedEthatEofEtheEpauagerEofEoueEorE
poreEdepartpeutsEthatEtheeEworkEiu.EBeEsureEtoEeusureEthatEallEtheE
relevautEiutegriteEcoustraiutsEareEsatisfiedEafterEeourEupdates. IV. ERD Tham khảo:
https://www.guru99.com/er-diagram-tutorial-dbms.html
https://course.ccs.neu.edu/cs3200sp18s3/ssl/lectures/lecture_07_erd.pdf
A. Design an ERD for the description below. lOMoAR cPSD| 58504431
B. Design an ERD for the description below. C. YRB.com lOMoAR cPSD| 58504431 1.
How does one determine the price a given customer paid
for a given book? Should an attribute price be added to buys? 2.
Is it possible for two customers to buy the same book but
for different prices? If so, how is this possible? If not, how
does the logic of the E-R diagram prohibit this? 3.
Does the customer always pay the lowest price for which
he or she is eligible (eligible)? If not, is there an easy way
to modify the E-R diagram in order to assure this? 4.
Does the E-R diagram ensure that the Offer under which
a customer buys a book is, in fact, legitimate? That is, an
offer is for a particular club’s members. Are we lOMoAR cPSD| 58504431
guaranteed that the customer belongs to the corresponding club? Why or why not? 5.
There is a serious flaw (at least one) in the design in
Figure 1, at least in as far as any bookseller would be concerned. What is the flaw? 6.
Redesign the E-R from Figure 1 to fix this.
V. Reverse from Schema to ERD lOMoAR cPSD| 58504431 VI. Reference 1.
https://www.eecs.yorku.ca/~papaggel/courses/eecs3421/ docs/tutorials/tut1-ra.pdf 2.
https://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdit ion/qbe.pdf 3.
https://www.eecs.yorku.ca/~papaggel/courses/eecs3421/ docs/tutorials/tut2-ra.pdf 4.
https://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdit
ion/supporting_material.htm#Students%20and%20Instructors