1 / 6

Overview of Various Kinds of Keys

A relation R1 consisting of a bunch of attributes. A relation R2 consisting of a bunch of attributes. Find all possible keys. Find sets of R2’s attributes that are primary keys in other relations. A bunch of n candidate keys. Foreign keys. Pick a winner. One primary Key.

csteinberg
Download Presentation

Overview of Various Kinds of Keys

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. A relation R1 consisting of a bunch of attributes A relation R2 consisting of a bunch of attributes Find all possible keys Find sets of R2’s attributes that are primary keys in other relations A bunch of n candidate keys Foreign keys Pick a winner One primary Key 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? Department of Computer Science and Engineering, HKUST Slide 1

  2. Questions • Selection on R • Project on R • R1 join R2 • Exists • For All • R1 intersect/union/difference R2 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)? R1 R2 Department of Computer Science and Engineering, HKUST Slide 2

  3. t t[amount]>1200? 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 ? Department of Computer Science and Engineering, HKUST Slide 3

  4. t t t t[amount]>1200? t[amount]>1200? t[amount]>1200? 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 ? Department of Computer Science and Engineering, HKUST Slide 4

  5. loan branch-name loan-number amount borrower cust-name loan-number 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 General notation: Department of Computer Science and Engineering, HKUST Slide 5

  6. loan branch-name loan-number amount borrower cust-name loan-number Natural Join and Equi-Join • Equi-Join: Joins where the match condition between two tuples is “equality” • Natural Join: Loan Borrower • Attributes with the same names in both relations are used for matching the tuples • Conditions are equality • Attributes in both relations, excluding duplicated attributes, are output in the result Department of Computer Science and Engineering, HKUST Slide 6

More Related