1 / 15

Candidate Keys

Candidate Keys. Objectives of the Lecture :. To consider Integrity Constraints; To consider the principles of Candidate Keys; To consider Candidate Keys in SQL. What are Integrity Constraints ?.

xuxa
Download Presentation

Candidate 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. Candidate Keys Objectives of the Lecture : • To consider Integrity Constraints; • To consider the principles of Candidate Keys; • To consider Candidate Keys in SQL.

  2. What are Integrity Constraints ? Definition: an Integrity Constraint is a constraint on the values that a given DB relation is permitted to hold. Thus it is a validation check that the DBMS automatically applies when a relation’s value is altered. • The requirement that a data type be assigned to every attribute is itself an integrity constraint. • However more integrity constraints are possible. Purpose of integrity constraints :to try to ensure that the relations in the DB only hold data that is true, accurate and up-to-date; i.e. in DB parlance “has integrity” or “is valid”.

  3. Applying Constraints in SQL • In SQL, integrity constraints are usually applied when the relation is created using the Create Tablestatement.Example : Create TableEMPLOYEE ( Insert constraints here,with other definitions. ) ; • SQL also has an AlterTable statement, which can be used for altering the integrity constraints applicable to a table. • For simplicity, all the integrity constraints will be described via the Create Tablestatement. The AlterTable statement uses similar syntax to Create Table in order to apply the constraints.

  4. Categories of Integrity Constraints There are four different kinds of integrity constraint : 1. Attribute type constraints; already considered. 2. Candidate Key constraints; these apply to an individual relation. 3. Referential Integrity constraints: these correlate 2 relations. 4. Ad hoc constraints; these apply to one or more relations.

  5. Candidate Keys • There are no duplicate tuples in a relation, because it is a set of tuples.So every tuple must be unique. • Often, indeed typically, the values of only one attribute, or a small number of attributes, in a relation are sufficient to make each tuple in it unique. • Whether it requires one attribute, several attributes, or all the attributes in a relation to make each tuple unique, that set of 1 or more attribute(s) is called a CandidateKey. • The candidate key attribute(s) can also be considered as uniquely identifying each tuple in the relation. • A relation may contain two or more candidate keys.

  6. Properties of a Candidate Key • Uniqueness. No two distinct tuples may contain the same key value. • Irreducibility. No attribute can be removed from the set forming the key, and the remaining attributes still possess the uniqueness property. Example:- Relation ( A, B, C, D, E, F, G ) Reducible if ( A, B )are unique per tuple. Benefits of these 2 properties are : • Candidate keys provide (the only) guaranteed way to find a particular tuple. • Checks on the uniqueness of tuples can be limited to the candidate key attribute(s), giving greater efficiency.

  7. Candidate Keys : Example (1) Example : CAR holds details of employees’ cars that are entitled to park in the company’s car park CAR( RegNo, Owner, Type ) • RegNo = registration number of car, • Owner = car owner, identified by an employee number, • Type = type of car. • Each car could equally well be identified by its ‘RegNo’ or its ‘Owner’. So they each individually form a candidate key. Example :- EMPLOYEE ( ENo, EName, M-S, Sal ) • The relation has just one candidate key, ‘ENo’.

  8. CAR( RegNo, ENo, Type ) One 2-attribute key, indicated byboth attributes having thesame level of underlining. Candidate Keys : Example (2) • Re-consider CAR. Let us change the assumptions. The company now has a “share & park” scheme whereby a group of employees can share a car to work; a group may use several of the members’ cars. • Now neither ‘RegNo’ or ‘ENo’ on its own is sufficient to identify a tuple in CAR. • However both together will identify any tuple. Therefore they jointly become the only candidate key.

  9. Candidate Keys in SQL • SQL has Primary and Alternate Keys. • If there is only one candidate key, it becomes the primary key; there are no alternate keys. • If there is more than one candidate key, choose one as the primary key; the rest become the alternate keys. • Any candidate key can become the primary key. So choose one that makes the most practical sense. (Usually the shortest - easiest for the user, most efficient for the computer). • While SQL defines a Primary Key using the phrase Primary Key, it defines an Alternate Key using the word Unique ! • SQL does not make specifying at least one candidate key mandatory !

  10. Entity Integrity • This is an additional constraint for relational DBs that allow nulls. Definition :Entity Integrity requires that no attribute in a primary key ever be null. • The rationale for this is that : • each tuple represents an entity in the real world; • each entity must be identifiable by definition; • primary keys serve as identifiers of tuples; • therefore a primary key can never be partly or wholly null, to ensure that it does identify each tuple. • Entity integrity does not apply to alternate keys (which can be null, either wholly or in part).So are alternate keys real keys ?

  11. Constraint Names • Most relational DBMSs give integrity constraints a name when they store the constraint in the DB. Thus a candidate key constraint would receive a name. • If the user does not supply a name when assigning the constraint, a unique default name is created for it by the DBMS. • The naming of integrity constraints can be of great practical use : • It helps users find out about what integrity constraints have been assigned to relations. • It allows more meaningful error messages to be provided to the user if there is an attempt to break an integrity constraint.

  12. Specifying Candidate Keys in SQL • Keys (primary and alternate) can be assigned in two places : • in the same sub-statement in which an attribute is assigned its type, • in a separate sub-statement at the end of a Create Table statement. • The former method is only possible if the SQL key consists of one attribute. The latter method must be used if the key consists of two or more attributes. • In either case, starting the assignment of an SQL key with the phraseConstraintconstraint-nameassigns a name to the key constraint as well.

  13. Examples of SQL Keys (1) Create TableEMPLOYEE (ENo Char(2)Primary Key,EName Varchar2(30),M-S Char(1)Check( M-S In (‘S’, ‘M’, ‘W’, ‘D’ ) ),Sal NumberCheck( Sal >999 And Sal < 100000 ) ) ; These 2 versions of EMPLOYEEhave a default constraint name. Create TableEMPLOYEE (ENo Char(2),EName Varchar2(30),M-S Char(1)Check( M-S In (‘S’, ‘M’, ‘W’, ‘D’ ) ),Sal NumberCheck( Sal >999 And Sal < 100000 ),Primary Key( ENo ) );

  14. Examples of SQL Keys (2) Create TableEMPLOYEE (ENo Char(2) Constraint PKEYPrimary Key,EName Varchar2(30),M-S Char(1)Check( M-S In (‘S’, ‘M’, ‘W’, ‘D’ ) ),Sal NumberCheck( Sal >999 And Sal < 100000 ) ) ; These 2 versions of EMPLOYEEhave the constraint name “PKEY”. Create TableEMPLOYEE (ENo Char(2),EName Varchar2(30),M-S Char(1)Check( M-S In (‘S’, ‘M’, ‘W’, ‘D’ ) ),Sal NumberCheck( Sal >999 And Sal < 100000 )Constraint PKEY Primary Key( ENo ) ) ;

  15. Relation CAR with two 1-attribute keys :- Create TableCAR (RegNo Char(9) Constraint PKEYPrimary Key,Owner Char(2)Constraint AKEYUnique,Type Varchar2(30) ) ; Relation CAR with one 2-attribute key :- Create TableCAR (RegNo Char(9),Owner Char(2),Type Varchar2(30),Constraint BKEYPrimary Key( RegNo, Owner ) ) ; Examples of SQL Keys (3) Either attribute couldhave been the primaryor alternate key.

More Related