Chapter 3 The Relational Data Model and Relational

Component Values Notation n We refer to of a tuple t by: n n

Component Values Notation n t 1. Name OR t 1[Name] = “Dick Davidson” t

Relational Integrity Constraints n n Constraints are conditions that must hold on all valid

What is a key n n n We want to be able to retrieve

Relational keys Super key (SK) n Candidate key (CK) n Primary key (PK) n

Super Key n Is a set of attributes SK of R with the following

$Super Key n n n SK 1 = {Name, Ssn, Home_phone, address, office_phone, Age,$

Candidate Key n n A superkey may contains additional attributes that are not necessarily

Candidate Key n A candidate key, k, for a relation R has two properties

Candidate Key n n Given the value of city , we can determine several

Primary Key n n n A primary key is a candidate key that is

Primary Key n Example: Consider the CAR relation schema: n CAR(State, Reg#, Serial. No,

Slides: 13

Download presentation

Component Values Notation n We refer to of a tuple t by: n n n t[Ai] or t. Ai This is the value vi of attribute Ai for tuple t Similarly, t[Au, Av, . . . , Aw] refers to the subtuple of t containing the values of attributes Au, Av, . . . , Aw, respectively in t Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 2

Relational Integrity Constraints n n Constraints are conditions that must hold on all valid relation states. There are three main types of constraints in the relational model: n n Key constraints Entity integrity constraints Referential integrity constraints Another implicit constraint is the domain constraint n Every value in a tuple must be from the domain of its attribute (or it could be null, if allowed for that attribute) Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 4

What is a key n n n We want to be able to retrieve every single piece of data that we putted into the database So, it is important to specify one attribute or more than one attributes which make it possible to uniquely identify every row in a table This attribute (s) is called a key This uniqueness ensure that we are retrieving exactly the row we want. The key can not be NULL Selecting a good key, is considered a challenge in the database design Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 5

Super Key n Is a set of attributes SK of R with the following condition: n n No two tuples in any valid relation state r(R) will have the same value for SK That is, for any distinct tuples t 1 and t 2 in r(R), t 1[SK] t 2[SK] This condition must hold in any valid state r(R) Every relation has one default super key which is the set of all its attributes Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 7

$Super Key n n n SK 1 = {Name, Ssn, Home_phone, address, office_phone, Age,$

Candidate Key n n A superkey may contains additional attributes that are not necessarily for unique identification We are interested in identifying superkeys that contain only the attributes necessary for unique identification So, a candidate key is a superkey such that no proper subset is a superkey within the relation Example n SK 2 = {Name, Ssn} Proper Subset = {Ssn} n SK 3 = {Address, Ssn} Proper Subset = {Ssn} n Then SK 2 and SK 3 is not candidate key Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 9

Candidate Key n A candidate key, k, for a relation R has two properties n Uniqueness n n Irreducibility n n In each tuple of R, the values of k uniquely identify that tuple No proper subset of k has the uniqueness property For example suppose we choose CK 1 = {A 1, A 2, A 3} as a candidate key and we discovered that CK 2 CK 1 = {A 1, A 2} achieve uniqueness then CK 1 is not a candidate key Identifying the candidate key requires that we know the meaning of attributes in the real world so that we can decide whether duplicates are possible Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 10

Candidate Key n n Given the value of city , we can determine several branch office, So this attribute cannot be candidate key But given a branch. No we can determine at most one tuple Also, given a postcode we can determine at most one tuple Then branch. No and postcode are candidate keys Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 11

Primary Key n n n A primary key is a candidate key that is selected to identify tuples in the relation If a relation has several candidate keys, the minimal one is chosen arbitrarily to be the primary key. In ER the primary key attributes are underlined. Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 12

Primary Key n Example: Consider the CAR relation schema: n CAR(State, Reg#, Serial. No, Make, Model, Year) n CAR has two keys: n n Key 1 = {State, Reg#} Key 2 = {Serial. No} Both are also candidate keys of CAR n So a serial. No is selected as a primary key because it is minimal n CAR(State, Reg#, Serial. No, Make, Model, Year) In general: n Any key is a superkey (but not vice versa) n n for example {Serial. No, Make} is a superkey but not a key. Any set of attributes that includes a key is a superkey Copyright © 2011 Ramez Elmasri and Shamkant B. Navathe 13