Relational Data Model and Integrity: Concepts and Examples, Slides of Database Management Systems (DBMS)

Download Relational Data Model and Integrity: Concepts and Examples and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

The Relational Model

Relational Data Structure

• Data is stored in

relations (tables)

• Each relation has a

scheme (heading)

• The scheme defines

the relation’s

attributes (columns)

• Data takes the form

of tuples (rows)

Name Age John 23 Mary 20 Mark 18 Jane 21

Attributes

Scheme

Tuples

Unnamed and named tuples

Name Age John 23 Mary 20 Mark 18 Jane 21

Attributes

Scheme

Tuples

John 23 Mary 20 Mark 18 Jane 21

Just numbers of columns

Tuples

A tuple is

<John, 23>

A tuple is

{(Name,John), (Age,23)}

Not a big difference!

• There is no fundamental difference between

named and unnamed perspectives on relations

• We could have written tuples <a,b,c> as sets

of pairs {(1,a), (2,b),(3,c)}, only we know

anyway in which order 1,2,3 go, so we can

skip the numbers.

• Written as sets of pairs (partial functions),

tuples can be written in any order, e.g.

{(3,c),(2,b),(1,a)}.

Relations

ID Name Salary Department

M139 John Smith 18,000 Marketing M140 Mary Jones 22,000 Marketing A368 Jane Brown 22,000 Accounts P222 Mark Brown 24,000 Personnel A367 David Jones 20,000 Accounts

Attributes are ID, Name, Salary, and Department Degree is 4

Cardinality is 5

Tuples, e.g. { (ID, A368), (Name, Jane Brown), (Salary, 22,000), (Department, Accounts) }

Scheme is {ID, Name, Salary, Department}

Relational Data Integrity

• Data integrity controls what data can be

in a relation

• Domains restrict the possible values a tuple

can assign to each attribute

• Candidate and Primary Keys identify tuples

within a relation

• Foreign Keys link relations to each other

Candidate Keys

• A set of attributes in

a relation is called a

candidate key if, and

only if,

• Every tuple has a

unique value for the

set of attributes

( uniqueness )

• No proper subset of

the set has the

uniqueness property

( minimality )

ID First Last S139 John Smith S140 Mary Jones S141 John Brown S142 Jane Smith

Candidate key: {ID}; {First,Last} looks plausible but we may get people with the same name {ID, First}, {ID, Last} and {ID, First, Last} satisfy uniqueness, but are not minimal {First} and {Last} do not give a unique identifier for each row

Choosing Candidate Keys

• Important: don’t look just on the data in

the table to determine what is a candidate

key

• The table may contain just one tuple, so

anything would do!

• Use knowledge of the real world – what is

going to stay unique!

NULLs and Primary Keys

• Missing

information can

be represented

using NULLs

• A NULL indicates

a missing or

unknown value

• More on this

later...

• Entity Integrity :

Primary Keys

cannot contain

NULL values

Foreign Keys

• Foreign Keys are used to link data in

two relations. A set of attributes in the

first ( referencing ) relation is a Foreign

Key if its value always either

• Matches a Candidate Key value in the

second ( referenced ) relation, or

• Is wholly NULL

• This is called Referential Integrity

Foreign Keys - Example

Employee

ID Name Manager

E1496 John Smith E E1497 Mary Brown E E1498 Mark Jones E E1499 Jane Smith NULL

{ID} is a Candidate Key for Employee, and {Manager} is a Foreign Key, which refers to the same relation - every tuple’s Manager value is either NULL or matches an ID value

Referential Integrity

• When relations are

updated, referential

integrity can be

violated

• This usually occurs

when a referenced

tuple is updated or

deleted

• There are a number

of options:

• RESTRICT - stop the

user from doing it

• CASCADE - let the

changes flow on

• NULLIFY - make

values NULL

RESTRICT

• RESTRICT stops any

action that violates

integrity

• You cannot update or

delete Marketing or

Accounts

• You can change

Personnel as it is not

referenced

Department DID DName 13 Marketing 14 Accounts 15 Personnel

Employee EID EName DID 15 John Smith 13 16 Mary Brown 14 17 Mark Jones 13 18 Jane Smith NULL

CASCADE

• CASCADE allows the

changes made to flow

through

• If Marketing’s DID is

changed to 16 in

Department, then the

DIDs for John Smith and

Mark Jones also change

• If Accounts is deleted

then so is Mary Brown

Department DID DName 13 Marketing 14 Accounts 15 Personnel

Employee EID EName DID 15 John Smith 13 16 Mary Brown 14 17 Mark Jones 13 18 Jane Smith NULL