Tirsdag

1. Tirsdag E/R model Enhanced E/R-model (EE/R-model) Relationelle model Relationelle algebra Omformningfra E/R-model tilrelationelle model

2. ER Diagram for the Company Database

3. EE/R-diagram

4. Den relationelle model • Den relationelle model er en datamodel med specielt sigte på relationsdatabaser • Den relationelle model er en logisk datamodel, der beskriver hvordan data struktureres i relationsdatabaser

5. Den relationelle model • Den relationelle model beskrives ved hjælp af en række veldefinerede begreber: • domæner • relationelle skemaer • relationer • attributter • tupler • primærnøgler, fremmednøgler • begrænsninger (constraints)

6. Eksempel på tabeller som repræsentation af relationer

7. Den relationelle model • Data struktureres i et antal tabeller (relationer), som har forskellige navne. • Hver tabel består af et antal (>=1) søjler (attributter). • Attributter er atomiske og defineret over et domæne. • I en tabel er der et antal (evt. ingen) rækker (forekomster, tupler), som ikke har nogen indbyrdes orden • De enkelte forekomster kan identificeres ved hjælp af værdien af nøglen, der findes ikke to helt ens forekomster • En relation (tabel) er en mængde af tupler • Graden af en tabel angiver antal attributter (søjler)

8. Nøglebegrebet • En nøgle er en attributkombination, som entydigt identificerer en forekomst i en tabel. • En nøgle er minimal, dvs.. fjernes een attribut, er den ikke længere entydig. • Alle attributter fra tabellen vil tilsammen altid være en (evt.. ikke-minimal) nøgle, kaldet en supernøgle. • Der kan være flere forskellige kandidatnøgler i en tabel • Der vælges altid en primærnøgle fra mængden af kandidatnøgler

9. Tabelsammenhænge • repræsenteres ved fremmednøgler • en fremmednøgle er een eller flere attributter i en tabel, som svarer til primærnøglen i en anden tabel • en fremmednøgle peger på en forekomst i en anden tabel og fortæller, at her ligger resten af oplysningerne • fremmednøglen og primærnøgleattributterne i den tabel, der refereres til, skal have samme domæne.

10. Integritetsregler • Integritet: at være sammenhængende • Domæneregel: Værdien af en attribut skal være en atomisk værdi fra dom(A) • Entitetsintegritet: En primærnøgle må ikke indeholde NULL-værdier • Referenceintegritet: En fremmednøgle skal enten være NULL eller referere til en forekomst med en tilsvarende primærnøgleværdi • Semantisk integritet: Forskellige regler, der i modsætning til de andre former for integritet, afhænger af den bestemte database.

11. 1 n Konto Kunde Eksempel • I firmaet Minibank registreres der oplysninger om kunder og konti. • Om kunder registreres navn, adresse, cprnr og status (A= særlig gode kunder, B= almindelige kunder eller C= problemkunder). • Om konti registreres kontonr, saldo og rentefod. • En konto hører altid til en og kun en kunde, en kunde kan have 0 eller flere konti. • Model:

12. Eksempel ...-2 • Der er defineres tre tabeller • Kunde • Konto • PNrBy (for at undgå redundans) • For at repræsentere ejerforholdet mellem konto og kunde tilføjes cprnr til konto som fremmednøgle

13. Eksempel …-3 Vi får følgende relationelle skemaer: • Kunde: • cprnr: cprNrType PK • navn varChar • gade varChar • postnrchar[4] FK REF PNrBy(pnr) • status statusType NOT NULL • PNrBy: • pnrchar[4] PK • by varChar NOT NULL

14. Eksempel…-4 • Konto: • kto ktoNrType PK • saldo decimal[>=0] NOT NULL • rentefod interval[0:100] NOT NULL • kCprnr cprNrType NOT NULL FK REF Kunde(cprnr)

15. Eksempel…-5 • domæner: • cprNrType: det skal være muligt at definere gyldige cpr-numre (mulige datoer, modulus 11) • tilsvarende for kontonumre og statusværdier • saldo skal være ikke-negativ (semantisk/problemspecifik) • entitet: • primærnøgler må ikke være NULL • reference: • hvad skal der ske med Konto.kCprNr, hvis kunde slettes?( NULLIFY eller CASCADE) • hvad skal der ske med Kunde.postnr, hvis en by får nyt postnr?

16. Semantiske (Problemspecifikke) integritetsregler (forretningsregler) • Mulighed for at definere fornuftige reaktioner på forsøg på opdateringer, som vil bryde integritetsregler • fx. at hæve et beløb, så saldo vil blive negativ

17. DBMS-understøttelse • DBMS’et bør understøtte: • Domæneintegritet • Entitetsintegritet • Referenceintegritet • Semantisk integritet • Udbredte relationelle DBMS understøtter kun 1 og 4 i begrænset omfang.

18. Foreign Key Constraints

19. Øvelser - papir • Opgave 5.11 - s.162

20. Relationel algebra

21. Det er det, man forstår ved en algebra! Datamanipulation i den relationelle model - relationsalgebraen • Arbejder på hele tabeller dvs. alle operationer tager tabeller som input og returnerer nye tabeller • Hermed kan operationer sammensættes til udtryk (som almindelige regneudtryk) • Operationer: • rækkeudvælgelse (RESTRICT/SELECT) • søjleudvælgelse (PROJECT) • sammensætning af tabeller (JOIN) • mængdeoperationer (UNION, INTERSECTION, MINUS, PRODUCT) • avancerede operationer (OUTER (LEFT/RIGTH) JOIN)

22. Unary Relational Operations • SELECT Operation SELECT operation is used to select a subset of the tuples from a relation that satisfy a selection condition. It is a filter that keeps only those tuples that satisfy a qualifying condition – those satisfying the condition are selected while others are discarded. Example: To select the EMPLOYEE tuples whose department number is four or those whose salary is greater than $30,000 the following notation is used: DNO = 4(EMPLOYEE) SALARY > 30,000(EMPLOYEE) In general, the select operation is denoted by <selection condition>(R) where the symbol  (sigma) is used to denote the select operator, and the selection condition is a Boolean expression specified on the attributes of relation R

23. Unary Relational Operations SELECT Operation Properties • The SELECT operation <selection condition>(R) produces a relation S that has the same schema as R • The SELECT operation  is commutative; i.e., • <condition1>(< condition2> (R)) = <condition2> (< condition1> ( R)) • A cascaded SELECT operation may be applied in any order; i.e., • <condition1>(< condition2> (<condition3> (R)) • = <condition2> (< condition3> (< condition1> ( R))) • A cascaded SELECT operation may be replaced by a single selection with a conjunction of all the conditions; i.e., • <condition1>(< condition2> (<condition3> (R)) • = <condition1> AND < condition2> AND < condition3> ( R)))

24. Figur 6.1 Unary Relational Operations (cont.)

25. Unary Relational Operations (cont.) • PROJECT Operation This operation selects certain columns from the table and discards the other columns. The PROJECT creates a vertical partitioning – one with the needed columns (attributes) containing results of the operation and other containing the discarded Columns. Example: To list each employee’s first and last name and salary, the following is used: LNAME, FNAME,SALARY(EMPLOYEE) The general form of the project operation is <attribute list>(R) where  (pi) is the symbol used to represent the project operation and <attribute list> is the desired list of attributes from the attributes of relation R. The project operation removes any duplicate tuples, so the result of the project operation is a set of tuples and hence a valid relation.

26. Unary Relational Operations (cont.) PROJECT Operation Properties • The number of tuples in the result of projection  <list> (R)is always less or equal to the number of tuples in R. • If the list of attributes includes a key of R, then the number of tuples is equal to the number of tuples in R. •  <list1> ( <list2> (R) ) =  <list1> (R) as long as<list2>contains theattributes in<list2>

27. Figur 6.1 Unary Relational Operations (cont.)

28. Relational Algebra Operations FromSet Theory STUDENT U INSTRUCTOR • UNION Example

29. Relational Algebra Operations From Set Theory (cont.) • INTERSECTION OPERATION The result of this operation, denoted by R S, is a relation that includes all tuples that are in both R and S. The two operands must be "type compatible" Example: The result of the intersection operation (figure below) includes only those who are both students and instructors. STUDENT INSTRUCTOR

30. Relational Algebra Operations From Set Theory (cont.) • Set Difference (or MINUS) Operation The result of this operation, denoted by R - S, is a relation that includes all tuples that are in R but not in S. The two operands must be "type compatible”. Example: The figure shows the names of students who are not instructors, and the names of instructors who are not students. STUDENT-INSTRUCTOR INSTRUCTOR-STUDENT

31. Relational Algebra Operations From Set Theory (cont.) Figur 6.5

32. Binary Relational Operations • JOIN Operation • The sequence of cartesian product followed by select is used quite commonly to identify and select related tuples from two relations, a special operation, called JOIN. It is denoted by a • This operation is very important for any relational database with more than a single relation, because it allows us to process relationships among relations. • The general form of a join operation on two relations R(A1, A2, . . ., An) and S(B1, B2, . . ., Bm) is: R <join condition>S where R and S can be any relations that result from general relational algebra expressions.

33. Binary Relational Operations (cont.) Example: Suppose that we want to retrieve the name of the manager of each department. To get the manager’s name, we need to combine each DEPARTMENT tuple with the EMPLOYEE tuple whose SSN value matches the MGRSSN value in the department tuple. We do this by using the join operation. DEPT_MGR  DEPARTMENT MGRSSN=SSNEMPLOYEE

34. Binary Relational Operations (cont.) • EQUIJOIN Operation The most common use of join involves join conditions with equality comparisons only. Such a join, where the only comparison operator used is =, is called an EQUIJOIN. In the result of an EQUIJOIN we always have one or more pairs of attributes (whose names need not be identical) that have identical values in every tuple. The JOIN seen in the previous example was EQUIJOIN. • NATURAL JOIN Operation Because one of each pair of attributes with identical values is superfluous, a new operation called natural join—denoted by *—was created to get rid of the second (superfluous) attribute in an EQUIJOIN condition. The standard definition of natural join requires that the two join attributes, or each pair of corresponding join attributes, have the same name in both relations. If this is not the case, a renaming operation is applied first.

35. Binary Relational Operations (cont.) • EQUIJOIN Operation The most common use of join involves join conditions with equality comparisons only. Such a join, where the only comparison operator used is =, is called an EQUIJOIN. In the result of an EQUIJOIN we always have one or more pairs of attributes (whose names need not be identical) that have identical values in every tuple. The JOIN seen in the previous example was EQUIJOIN. • NATURAL JOIN Operation Because one of each pair of attributes with identical values is superfluous, a new operation called natural join—denoted by *—was created to get rid of the second (superfluous) attribute in an EQUIJOIN condition. The standard definition of natural join requires that the two join attributes, or each pair of corresponding join attributes, have the same name in both relations. If this is not the case, a renaming operation is applied first.

36. Binary Relational Operations (cont.) Example: To apply a natural join on the DNUMBER attributes of DEPARTMENT and DEPT_LOCATIONS, it is sufficient to write: (B)DEPT_LOCS  DEPARTMENT * DEPT_LOCATIONS

37. Recap of Relational Algebra Operations

38. Additional Relational Operations • Aggregate Functions and Grouping • A type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database. • Examples of such functions include retrieving the average or total salary of all employees or the total number of employee tuples. These functions are used in simple statistical queries that summarize information from the database tuples. • Common functions applied to collections of numeric values include SUM, AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples or values.

39. Additional Relational Operations (cont.)

40. Additional Relational Operations (cont.) Use of the Functional operator ℱ ℱMAX Salary(Employee) retrieves the maximum salary value from the Employee relation ℱMIN Salary(Employee) retrieves the minimum Salary value from the Employee relation ℱSUM Salary(Employee)retrieves the sum of the Salary from the Employee relation DNO ℱCOUNT SSN, AVERAGE Salary(Employee)groups employees by DNO (department number) and computes the count of employees and average salary per department.[ Note: count just counts the number of rows, without removing duplicates]

41. The OUTER JOIN Operation • In NATURAL JOIN tuples without a matching (or related) tuple are eliminated from the join result. Tuples with null in the join attributes are also eliminated. This amounts to loss of information. • A set of operations, called outer joins, can be used when we want to keep all the tuples in R, or all those in S, or all those in both relations in the result of the join, regardless of whether or not they have matching tuples in the other relation. • The left outer join operation keeps every tuple in the first or left relation R in R S; if no matching tuple is found in S, then the attributes of S in the join result are filled or “padded” with null values. • A similar operation, right outer join, keeps every tuple in the second or right relation S in the result of R S. • A third operation, full outer join, denoted by keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with null values as needed.

42. Additional Relational Operations (cont.)

43. Øvelser - papir • 6.16 a, b, c,d

44. Relational Algebra - Overview

45. Design of Relational Tables Transformation from ER Diagram to Relational Schemas

47. Table Design Transformation from E/R-model to Relational Model Eigth Steps Algorithm Does not always yield an optimal design, but provides a good starting point for the final design of tables

48. Step 1: For each regular entity create a table For composite attributes only the components are included. Multi-value attributes are not included (they are considered in step 6). Choose a primary key.

49. Step 2: For each weak entity type create a table All attributes from the weak entity are included. The primary key from the owner is included as foreign key. The primary key is composed by the owner’s primary key and the partial key.

50. Step 3: For each (binary) 1:1-relation type include primary key of one participant as foreign key in the other Any attribute on the relation type is included with the key. If possible, include on a side with total participation.