CSE3241Final SELECT-ANS-Selectsalltuplesthatsatisfytheselectionconditionfromarelation Union-ANS-ProducesarelationthatincludesALLofthetuplesintworelations,iftheyare unioncompatible NaturalJoin-ANS-Producesallthecombinationsoftuplesfromtworelationsthatsatisfyonly equalityconditions,butiftwoattributeshavethesamename,theydonothavetobespecified PROJECT-ANS-Producesanewrelationwithonlysomeoftheattributes,andremoves duplicatetuples OuterJoin-ANS-Producesarelationwithallofthetuplesofonerelationandalsothematching tuplesofanotherrelation Difference-ANS-Producesarelationthatincludesallofthetuplesinonerelation,thatarenot alsoinanotherrelation,iftheyareunioncompatible Intersection-ANS-ProducesarelationthatincludesallofthetuplesthatareinBOTHoftwo relations,iftheyareunioncompatible ThetaJoin-ANS-Producesallcombinationsoftuplesfromtworelationsthatsatisfythejoin condition CartesianProduct-ANS-Producesarelationthathasalloftheattributes,andallofthetuplesin allpossiblecombinations,oftworelations,eveniftheyarenotunioncompatible Equijoin-ANS-Producesallthecombinationsoftuplesfromtworelationsthatsatisfyonly equalityconditions Theunaryrelationaloperationsare-ANS-SELECT,PROJECT,RENAME ThebenefitsofusingtheThreeSchemaArchitecture-ANS-Changethephysicaldiskstorage withoutchangingconceptualschema,givesdifferentusersdifferentviewsofthesamedata, dataindependence Typesofattributes-ANS-Composite,multi-valued,atomic Weakentities-ANS-Arerelatedtospecificentitiesbyidentifyingrelationships 1 / 3
Ternaryrelationships-ANS-Canadequatelyberepresentedasaseparaterelationwithan artificialkey Allaretrueaboutdisjointsubclasses-ANS-Anentitycanbeamemberofatmostoneofthe subclassesofthespecialization,asuperclasscanhavepartialparticipationindisjoint subclasses,asuperclasscanhavetotalparticipationindisjointsubclasses
Atupleis:-ANS-Anorderedsetofattributes
Akey:-ANS-Isasuperkey,canhavemorethanoneinatuple,uniquelyidentifiesatuple
Whenmappingaregularentitytoarelationalmodelwhichofthefollowingapply:-ANS-Every
simpleattributemapstoanattributeoftherelation Aprimarykeyofaweakentityiscomposedof-ANS-BothPrimaryKeyoftheidentifyingentity asaforeignkeyasthepartialkey Binary1:1relationshipsaremappedtoarelationalmodelby:-ANS-Aforeignkeyontheentity thatfullyparticipatesintherelationship
InsertingaNULLfortheprimarykeyofatuplerepresentswhatkindofviolation:-ANS-Entity
Constraint Atomicity-ANS-thetransactionshouldeitherbeperformedinitsentiretyornotperformedatall Consistencypreservation-ANS-Ifatransactioniscompletelyexecutedfrombeginningtoend withoutinterferencefromothertransactions,itshouldtakethedatabasefromoneconsistent statetoanother Isolation-ANS-Atransactionshouldappearasthoughitisbeingexecutedinisolationfrom othertransactions,eventhoughmanytransactionsareexecutingconcurrently Durabilityorpermanency-ANS-Thechangesappliedtothedatabasebyacommitted transactionmustpersistinthedatabase;thesechangesmustnotbelostbecauseofanyfailure ArelationschemaRisin2NFifeverynon-primeattributeAinRisfunctionallydependent(FD) ontheprimarykey-ANS-False.FullfunctionaldependencymeansFDY-->ZwhereremovalofanyattributefromZmeansthe FDdoesnotholdanymore-ANS-False.ArelationschemaRisgeneralizedin3NFifwhenaFDX-->AholdsinR,thenXisasuperkey ofRandAisaprimeattributeofR-ANS-False. 2 / 3
Arelationwhereallattributesareatomicisalwaysin1NF-ANS-True.AbinaryrelationisinBCNF-ANS-True.APrimeattributemustbeamemberofSOMECANDIDATEKEY-ANS-True.TransitivefunctionaldependencymeansFDX-->ZthatcanbederivedfromtwoFDsX-->Y andY-->Z-ANS-True.ArelationschemaRisin3NFifitisin2NFANDnonon-primeattributeinRistransitively dependentontheprimarykey-ANS-True.ABCNFrelationisalwaysin3NF-ANS-True X-->YholdsifwhenevertwotupleshavethesamevalueforX,theyMUSTHAVEthesame valueforY-ANS-True.IfarelationRisdecomposedinto{R1,R2,...,Rn}andthedecompositionislosslessthen- ANS-ThenaturaljoinofR1,R2,...,Rnwillhavethesamenumberoftuplesastheoriginal relationR Thefunctionaldependency{A}-->{B}fortherelationschemaR(A,B,C,D)impliesthat- ANS-AnytwotuplesinRthathavethesamevalueforAmusthavethesamevalueforB GiventheFDs,{A,B}-->{C,D,E}and{A}-->{E},forrelationschemaR=(A,B,C,D,E)wecan
inferthefollowing:-ANS-{A,B}isakeyforR
Atablethatdisplaysdataredundanciesyieldsthefollowinganomalies:-ANS-Update
anomalies,Insertionanomalies,Deletionanomalies Atablewhereeverydeterminantisacandidatekeyissaidtobein-ANS-BCNF ArelationschemaRisin3NFif-ANS-Rsatisfies2NFandnononprimeattributeofRis transitivelydependentontheprimarykey If{A,B}-->{C,D}isoneFDthatholdsfortherelationschemaR(A,B,C,D),then-ANS-{A,B}isa candidatekeyforR,NotwotuplesinRcanhavethesamevaluesforbothAandB,{A,B}isa primarykeyforR GivenonlytherelationschemaBooks(TITLE,AUTHOR,Year,Publisher),wecaninferthe
followingFD:-ANS-Author,Title-->Publisher
IfthefollowingFDs,({A}-->{B},{B}-->{C})holdforthedatabaseschemaR(A,B)andS(B,C), thenthejoinofRandSwillbe-ANS-Lossless
- / 3