Overview of Various Kinds of KeysA relation R2 consistingofabunchofattributesA relation R1 consistingofabunchofattributesFindsetsofR2'sattributesthatareFind all possiblekeysprimarykeysinotherrelationsA bunch of ncandidate keysForeignkeysPick a winnerHowmanyforeign keys can a relationhave?Can a foreign key of RbetheprimarykeyOneprimaryKeyof R itself?KRUSTSlide 1
Department of Computer Science and Engineering, HKUST Slide 1 Overview of Various Kinds of Keys ▪ How many foreign keys can a relation have? ▪ Can a foreign key of R be the primary key of R itself? ▪ Is a primary key still a candidate key? A relation R1 consisting of a bunch of attributes Find all possible keys A bunch of n candidate keys Pick a winner One primary Key A relation R2 consisting of a bunch of attributes Find sets of R2’s attributes that are primary keys in other relations Foreign keys
QuestionsCan I answer the following queries by checking a single row in thetable or a pair of rows in two tables (in case the operation has twooperands)?R1R2SelectiononRProjectonRR1 join R2ExistsFor AllR1intersect/union/differenceR2DepartmentofComputer Scienceand Engineering,HKUSTSlide 2
Department of Computer Science and Engineering, HKUST Slide 2 Questions • Selection on R • Project on R • R1 join R2 • Exists • For All • R1 intersect/union/difference R2 R2 R1 Can I answer the following queries by checking a single row in the table or a pair of rows in two tables (in case the operation has two operands)?
Summaryof Key Concepts(aboutTupleRelationalCalculus)Loant e loanamountnamet is atuplevariable which canhold a tuple of loant[amount]: is the value under theTsalary attributet[amount]>1200?What is the difference betweent[salary] and the πsalary Loan ?Departmentof Computer Science andEngineering,HKUSTSlide3
Department of Computer Science and Engineering, HKUST Slide 3 Summary of Key Concepts (about Tuple Relational Calculus) t loan • t is a tuple variable which can hold a tuple of loan t[amount]: is the value under the salary attribute What is the difference between t[salary] and the salary Loan ? Loan name amount . . t t[amount]>1200?
Summary of Key Concepts (about TupleRelationalCalculus)Loant e loanamountnamet is a tuple variable which canhold a tuple of loant[salary]: is the value under thesalary attributett[amount]>1200?tt[amount]>1200?What is the difference betweent[amount]>1200?t[salary] and the 元salary Loan ?DepartmentofComputer Scienceand Engineering,HKUSTSlide4
Department of Computer Science and Engineering, HKUST Slide 4 Summary of Key Concepts (about Tuple Relational Calculus) t loan • t is a tuple variable which can hold a tuple of loan t[salary]: is the value under the salary attribute What is the difference between t[salary] and the salary Loan ? Loan name amount . . t t t[amount]>1200? t[amount]>1200? t t[amount]>1200?
Re-Visit the JOiNOperationin RelationalAlgebraloanbranch-nameamountloan-numberborrowerloan-numbercust-nameGeneralBorrowerLoan △Loan.loan-number=T cust-name,notation:branch-name,Borrower.loan-numberLoan.loan-number,amountThe"dot" notation tells which table does an attribute come fromThe attributes and conditions to be matched in Join are explicitly statedWecouldspecify"Loan.loan-number>Borrower.loan-number"oreven"branch-name > cust-name"for the Join operation (if they make sense!!!)The attributes in the join results are explicitly specifiedDepartmentofComputer Scienceand Engineering,HKUSTSlide5
Department of Computer Science and Engineering, HKUST Slide 5 Re-Visit the JOIN Operation in Relational Algebra • The “dot” notation tells which table does an attribute come from • The attributes and conditions to be matched in Join are explicitly stated − We could specify “Loan.loan-number > Borrower.loan-number” or even “branch-name > cust-name” for the Join operation (if they make sense!!!) • The attributes in the join results are explicitly specified loan branch-name loan-number amount borrower cust-name loan-number Loan Borrower Borrower.loan-number Loan.loan-number amount Loan.loan-number, branch-name, cust-name, = General notation: