relational operators n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Operators PowerPoint Presentation
Download Presentation
Relational Operators

Loading in 2 Seconds...

play fullscreen
1 / 55

Relational Operators - PowerPoint PPT Presentation


  • 169 Views
  • Uploaded on

Relational Operators. Relational Operators. Properties. Relational operations are specified using Structured Query Language (SQL) -- a standard for relational database access.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

Relational Operators


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
relational operators1
Relational Operators

Properties

  • Relational operations are specified using Structured Query Language (SQL) -- a standard for relational database access.
  • Relational operations are set level, meaning that they operate on multiple rows, rather than one record at a time.
  • SQL is non-procedural, meaning that the user specifies what data is to be retrieved rather than how to retrieve the data.
relational operators2
Relational Operators

Properties

  • Each operator takes one or more tables as it operand(s) and produces a table as its result.
  • Any column value in a table can be referenced, not just keys.
  • Operations can be combined to form complex operations.
operations on a dbms
Operations on a DBMS

Can be specified using

  • Relational Algebra operations (what we learn now)
    • Are usually divided into two groups
      • Set theory operations
      • Operations specifically developed for relational databases
    • But are considered too technical for ordinary users, hence the birth of SQL
    • They are written as a sequence of steps, when executed produce the results
    • Hence the user must give say ”how” and not “what” is needed
operations on a dbms1
Operations on a DBMS

Can be specified using

  • Relational calculus
      • Another formal query language which gives ‘what’ is required, and not how.
      • Eg:- {t.FNAME,t.LNAME|EMPLOYEE(t) and t.SALARY>500}

SELECT T.FNAME, T.LNAME

FROM EMPLOYEE AS T

WHERE T.SALARY>500

  • SQL
relational operators3

Employee

E-No E-Name D-No

179 Silva 7

857 Perera 4

342 Dias 7

Sales Employee

E-No E-Name D-No

179 Silva 7

342 Dias 7

Relational Operators

Selection:horizontal subset of a table

Sales-Emp = D-No=7 (Employee)

slide7

Employee

E-No E-Name D-No

179 Silva 7

857 Perera 4

342 Dias 7

Employee Names

E-No E-Name

179 Silva

857 Perera

342 Dias

Projection:vertical subset of a table

Emp-Names = E-No, E-Name (Employee)

slide8

Department

D-No D-Name M-No

4 Finance 857

7 Sales 179

Employee

E-No E-Name D-No

179 Silva 7

857 Perera 4

342 Dias 7

Emp-Info

E-No E-Name D-No D-No D-Name M-No

179 Silva 7 4 Finance 857

857 Perera 4 4 Finance 857

342 Dias 7 4 Finance 857

179 Silva 7 7 Sales 179

857 Perera 4 7 Sales 179

342 Dias 7 7 Sales 179

Cartesian Product:Creates a single table from two tables.

Emp-Info = Employee  E.D-No=D.D-No Department

slide9

Employee

E-No E-Name D-No

179 Silva 7

857 Perera 4

342 Dias 7

Emp-Info

E-No E-Name D-No D-No D-Name M-No

179 Silva 7 7 Sales 179

857 Perera 4 4 Finance 857

342 Dias 7 7 Sales 179

Join:Creates a single table from two tables.

Department

D-No D-Name M-No

4 Finance 857

7 Sales 179

EquiJoin

Emp-Info = Employee E.D-No=D.D-No Department

joins
Joins…
  • The most common join is where we only use the ‘equal’ operator , and is known as equijoin.
  • We can use other operator (=,<,>,<=, etc…) for the join condition also
  • The natural join (*) can be used to get rid of the additional attribute in an equijoin condition.
joins1
Joins…
  • In a natural join only the matching tuples are displayed. The ‘left outer join’ and ‘right outer join’ and ‘full outer join’can be used to find even non matching tuples
slide12

Department

Employee

D-No D-Name M-No

4 Finance 857

7 Sales 179

E-No E-Name D-No

179 Silva 7

857 Perera 4

342 Dias 7

Emp-Info

E-No E-Name D-No D-Name M-No

179 Silva 7 Sales 179

857 Perera 4 Finance 857

342 Dias 7 Sales 179

Natural Join:Creates a single table from two tables.

Emp-Info = Employee  E.D-No=D.D-No Department

relational operators4

Union

Intersection

Difference

Relational Operators

Other operators

Set operations from mathematical set theory

set operators

Student

Union

Stu-Inst

Fname

Kapila

Nimal

Ajith

Rohan

Lname

Dias

Perera

Silva

Mendis

Fname

Kapila

Nimal

Ajith

Rohan

Sunil

Kamal

Saman

Lname

Dias

Perera

Silva

Mendis

De Silva

Soysa

Silva

Instructor

FN

Sunil

Kamal

Saman

Kapila

Nimal

LN

De Silva

Soysa

Silva

Dias

Perera

Set Operators

Stu-Inst = Student  Instructor

set operators1

Student

Intersection

Stu-Inst

Fname

Kapila

Nimal

Ajith

Rohan

Lname

Dias

Perera

Silva

Mendis

Fname

Kapila

Nimal

Lname

Dias

Perera

Instructor

FN

Sunil

Kamal

Saman

Kapila

Nimal

LN

De Silva

Soysa

Silva

Dias

Perera

Set Operators

Stu-Inst = Student Instructor

set operators2

Student

Difference

Stu-Inst

Fname

Kapila

Nimal

Ajith

Rohan

Lname

Dias

Perera

Silva

Mendis

Fname

Ajith

Rohan

Fname

Sunil

Kamal

Saman

Lname

Silva

Mendis

Lname

De Silva

Soysa

Silva

Instructor

FN

Sunil

Kamal

Saman

Kapila

Nimal

LN

De Silva

Soysa

Silva

Dias

Perera

Inst-Stu

Set Operators

Stu-Inst = Student -Instructor

Inst-Stu = Instructor - Student

complete set of relational algebra operations
Complete Set of Relational Algebra Operations

It has been proved that {, , , , } is a complete set.

Any other relational algebra operator can be expressed

in terms of the above operators.

E.g. R Ç S = (R È S)  ( ( R  S) È (S  R) )

division operator
Division operator

Rename operator

R(FirstName,LastName,Salary) = Fname,Lname,Sal (Employee)

Can be useful for set related operations.

relational operators5
Relational Operators

Because the result of every relational operation is a table, operators can be combined to create complex operations. For example:

Select + Project

A B

B

A

+

Project + Select + Join

relational operators6
Relational Operators

Get course names thought by lecturer ‘Dr Kodikara’

course(cno, cname, lecturer)

employee(empno, ename, designation)

Emp_Kodi eneme=’Dr. Kodikara’ Employee

Courses cname, lecturer Course

Kodi_courses Emp_Kodi * empno = lecture Courses

Kodi_courses

Employee

Course

+

Select + Project + N-Join

slide22

Normalisation

Is derivation of data as a set of

Non-Redundant,

Consistent and

Inter-Dependent Relations

slide23

Normalisation

  • Normalisation is a set of data design standards.
  • It is a process of decomposing unsatisfactory relations into smaller relations.
  • Like entity–relationship modelling were developed as part of database theory.
slide24

Normalisation - Advantages

Reduction of data redundancy within tables:

  • Reduce data storage space
  • Reduce inconsistency of data
  • Reduce update cost
  • Remove many-to-many relationship
  • Improve flexibility of the system
slide25

Normalisation - Disadvantages

Reduction in efficiency of certain data retrieval as relations may be joined during retrieval.

  • Increase join
  • Increase use of indexes: storage (keys)
  • Increase complexity of the system
slide26

Normal Forms

A state of a relation that results from applying simple rules regarding functional dependencies (or relationships between attributes) to that relation.

0NF multi-valued attributes exists

1NF any multi-valued attributes have been removed

2NF any partial functional dependencies have been removed

3NF any transitive dependencies have been removed

slide27

Functional Dependencies and Keys

Functional dependency:A constraint between two attributes or two sets of attributes

The functional dependency of B on A is represented by an arrow: A  B

e.g.

NID (SSN)  Name, Address, Birth date

VID  Make, Model, Colour

ISBN  Title, First Author

slide28

Functional Dependencies and Keys

Functional dependency (definition)

For any relation R (e.g. book), attribute B (e.g. title) is functionally dependent on attributes A (e.g. ISBN), if for every valid instance of A (e.g. 981-235-996-6), that value of A uniquely determines the value of B (e.g. Modern Database Management)

slide29

Input for the Normalisation Process

Database Design process (phase 1)

data requirements and data analysis

entity types(e.g. Supplier, Order)

attributes describing each entity type with its meaning (e.g. supplier name and part name)

attributes relationships to other attributes. (e.g.supplier no of Supplier to supplier no of purchase Order)

slide30

ATTRIBUTE

PO-NO

PO-DATE

EMP-CODE

SUPP-NO

SUPP-NAME

PART-NO

PART-DESC

PART-QTY

TYPE

N

D

C

N

C

N

C

N

LEN-GTH

3

8

2

3

20

2

10

2

DESCRIPTION

Unique purchase order (PO) number. Many parts can be ordered in one PO

DDMMYYYY date when PO written

Unique code of employee who wrote the PO

Unique number assigned to supplier

Supplier name

Unique number assigned to each part

Part description

Quantity of parts ordered in given PO

Purchase Order - Attribute Analysis

KeyPO-NO

slide31

Purchase Order Relation in 0NF

PO-NO

111

112

113

114

115

116

PO-DATE

01012001

01012001

02012001

02012001

03012001

04012001

EMP-CODE

M2

S3

S1

M2

S1

S1

SUPP-NO

222

105

111

150

222

100

SUPP-NAME

AC Stores

I Hardware

BC Trading

DO Service

AC Stores

LM Centre

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Bolt

Screw

Nut

Nail

Plug

Pin

Fuse

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

slide32

Normalisation Process

  • Apply a set of normalisation rules to all the attributes of the entity types identified in the data requirement step.

0NF Relations

1NF Relations

2NF Relations

3NF Relations

Optimised Relations

slide33

Output of the Normalisation Process

  • A list of normalised entity types in at least third normal form (3NF), such that all non-key attributes of each entity type fully depend on the whole key and nothing but the key
slide34

First Normal Form - 1NF

A relation is in First Normal Form (1NF) if ALL its attributes are ATOMIC.

ie. If there are no repeating groups.

If each attribute is a primitive.

e.g. integer, real number, character string,

but not lists or sets

non-decomposable data item

single-value

slide35

Purchase Order Relation in 0NF

PO( PO-NO, PO-DATE, EMP-CODE, SUPP-NO, SUPP-NAME, PARTS-ORDERED{PART-NO, PART-DESC, PART-QTY})

Within a single purchase order we could find several part numbers, part descriptions and part quantities. Hence, parts ordered can be decomposed.

slide36

Purchase Order Relation in 0NF

PO-NO

111

112

113

114

115

116

PO-DATE

01012001

01012001

02012001

02012001

03012001

04012001

EMP-CODE

M2

S3

S1

M2

S1

S1

SUPP-NO

222

105

111

150

222

100

SUPP-NAME

AC Stores

I Hardware

BC Trading

DO Service

AC Stores

LM Centre

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Bolt

Screw

Nut

Nail

Plug

Pin

Fuse

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

slide37

First Normal Form - 1NF

  • 1NF deals with the shape of a record type
  • All occurrences of a record type must contain the same number of fields
  • A relational schema is at least in 1NF
slide38

1NF - Actions Required

1) Examine for repeat groups of data

2) Remove repeat groups from relation

3) Create new relation(s) to include repeated data

4) Include key of the 0NF to the new relation(s)

5) Determine key of the new relation(s)

slide39

PO

PO-PART

PO-NO

111

112

113

114

115

116

PO-DATE

01012001

01012001

02012001

02012001

03012001

04012001

EMP-CODE

M2

S3

S1

M2

S1

S1

SUPP-NO

222

105

111

150

222

100

SUPP-NAME

AC Stores

I Hardware

BC Trading

DO Service

AC Stores

LM Centre

PO-NO

111

111

111

111

112

112

113

113

114

115

116

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Bolt

Screw

Nut

Nail

Plug

Pin

Fuse

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

Purchase Order Relations in 1NF

slide40

Problems - 1NF

1. INSERT PROBLEM

cannot know available parts until an order is placed (e.g. P4 is bush)

2. DELETE PROBLEM

loose information of part P7 if we cancel purchase order 115 (i.e. Delete PO-PART for Part No P7)

3. UPDATE PROBLEM:

to change description of Part P3 we need to change every tuple in PO-PART containing Part No P3

slide41

Second Normal Form - 2NF

A relation is in 2NF if it is in 1NF and every non-key attribute is dependent on the whole key

i.e. Is not dependent on part of the key only.

slide42

PO-PART Relation (Parts Ordered)in 1NF

PO-PART( PO-NO, PART-NO, PART-DESC, PART-QTY)

Part Description is depended only on Part No, which is part of the key of PO-PART.

slide43

Parts Ordered Relation in 1NF

PO-NO

111

111

111

111

112

112

113

113

114

115

116

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Bolt

Screw

Nut

Nail

Plug

Pin

Fuse

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

slide44

Second Normal Form - 2NF

Deals with the relationship between non-key and key fields

A non-key field cannot be a fact about a subset of a key

It is relevant when the key is composite, i.e. consists of several fields

slide45

2NF - Actions Required

If entity has a concatenated key

1) Check each attribute against the whole key

2) Remove attribute and partial key to new relation

3) Optimise relations

slide46

Parts Ordered Relations in 2NF

PO-PART

PO-NO

111

111

111

111

112

112

113

113

114

115

116

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

PART

PART-NO

P1

P2

P3

P5

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Plug

Pin

Fuse

slide47

PO

PO-NO

111

112

113

114

115

116

PO-DATE

01012001

01012001

02012001

02012001

03012001

04012001

EMP-CODE

M2

S3

S1

M2

S1

S1

SUPP-NO

222

105

111

150

222

100

SUPP-NAME

AC Stores

I Hardware

BC Trading

DO Service

AC Stores

LM Centre

PART-NO

P1

P2

P3

P5

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Plug

Pin

Fuse

Purchase Order Relations in 2NF

PART

PO-PART

PO-NO

111

111

111

111

112

112

113

113

114

115

116

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

slide48

Problems - 2NF

1. INSERT PROBLEM

cannot know available suppliers until an order is placed (e.g. 200 is hardware stores)

2. DELETE PROBLEM

loose information of supplier 100 if we cancel purchase order 116 (i.e. Delete PO for Supplier No 100)

3. UPDATE PROBLEM

to change name of Supplier 222 we need to change every tuple in PO containing Supplier No 222

slide49

Third Normal Form - 3NF

A relation is in 3NF if it is in 2NF and each non-key attribute is only dependent on the whole key, and not dependent on any non-key attribute.

i.e. no transitive dependencies

slide50

PO Relation in 2NF

PO( PO-NO, PO-DATE, EMP-CODE, SUPP-NO, SUPP-NAME)

Supplier name is a non-key field depended on another non-key field (i.e. the supplier no) in addition to be depended on the key purchase order no

slide51

Third Normal Form - 3NF

Deals with the relationship between non-key fields

A non-key field cannot be a fact about another non-key field

slide52

3NF - Actions Required

1) Check each non-key attribute for dependency against other non-key fields

2) Remove attribute depended on another non-key attribute from relation

3) Create new relation comprising the attribute and non-key attribute which it depends on

4) Determine key of new relation

5) Optimise

slide53

PO and SUPPLIER Relations in 3NF

PO

SUPPLIER

PO-NO

111

112

113

114

115

116

PO-DATE

01012001

01012001

02012001

02012001

03012001

04012001

EMP-CODE

M2

S3

S1

M2

S1

S1

SUPP-NO

222

105

111

150

222

100

SUPP-NO

100

105

111

150

222

SUPP-NAME

LM Centre

I Hardware

BC Trading

DO Service

AC Stores

slide54

PART-NO

P1

P2

P3

P5

P6

P7

P8

PART-DESC

Nut

Bolt

Nail

Screw

Plug

Pin

Fuse

Purchase Order Relations in 3NF

SUPP-NO

222

105

111

150

222

100

SUPP-NAME

AC Stores

I Hardware

BC Trading

DO Service

AC Stores

LM Centre

SUPPLIER

PO-PART

PART

PO-NO

111

111

111

111

112

112

113

113

114

115

116

PART-NO

P1

P2

P3

P5

P2

P5

P1

P3

P6

P7

P8

PART-QTY

10

5

3

6

2

1

3

4

5

8

2

PO

PO-NO

111

112

113

114

115

116

PO-DATE

01012001

01012001

02012001

02012001

03012001

04012001

EMP-CODE

M2

S3

S1

M2

S1

S1

SUPP-NO

222

105

111

150

222

100

further normalization
Further Normalization
  • BCNF or Boyce–Codd Normal form
  • 4th Normal form
  • 5th Normal form

In a normal situation normalization up-to 3NF is quite sufficient.Certain relations may even be de-normalized on account of efficiency. The Normalizations which are discussed next are not practically enforced most of the time.

  • But a relation in 3NF does not guarantee that all anomalies have been removed, hence the additional normalizations.