Relational Operators

1. Relational Operators

2. 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.

3. 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.

4. 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

5. 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

6. 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)

7. 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)

8. 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

9. 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

10. 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.

11. 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

12. 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

13. Union Intersection Difference Relational Operators Other operators Set operations from mathematical set theory

14. 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

15. 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

16. 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

17. 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) )

18. Division operator Rename operator R(FirstName,LastName,Salary) = Fname,Lname,Sal (Employee) Can be useful for set related operations.

19. 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

20. 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

21. Logical Database Design - Normalisation

22. Normalisation Is derivation of data as a set of Non-Redundant, Consistent and Inter-Dependent Relations

23. 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.

24. 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

25. 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

26. 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

27. 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

28. 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)

29. 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)

30. 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

31. 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

32. 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

33. 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

34. 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

35. 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.

36. 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

37. 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

38. 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)

39. 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

40. 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

41. 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.

42. 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.

43. 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

44. 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

45. 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

46. 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

47. 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

48. 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

49. 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

50. 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