Scarica Data Integrity Constraints in Relational Databases e più Sintesi del corso in PDF di Management Theory solo su Docsity!
010 - DBMS AND RELATIONAL MODEL
INTEGRITY CONSTRAINTS
A DBMS must prevent the entry of incorrect information. For a basic pattern of data you can be associated with a set of integrity constraints. Types of constraints: Intra-relational : constraints defined within the context of individual relation Tuple constraints : rules defined within the tuple key constraints. Inter-relational : constraints involving more relations. The most important one is the Referential integrity constraint (or foreign key constraint).
TUPLE CONSTRAINTS
It is a boolean expression on the attributes values within a single tuple (record). In case the condition is apply to a single attribute it is named domain constraint An example of a domain constraint in the Exam relation can be: Mark>= 18 AND Mark<= 30 In the following relation Payment(Date, Gross Amount, Taxes, Net Value) we can define two constraints Gross Amount >= 0 Net Value = Gross Amount – Taxes
KEYS
A key is a set of attributes that uniquely identifies a tuple. The formal definition ... A set of attributes K is a super key of the relation r if r does not include two different tuples such that
t1[K] = t2[K] K is a (candidate) key of r if it is a minimal super key of r, that means no other super key K’ is a subset of K. Example : (Name, Surname, Birth Date) is a super key (Name, Surname) is a key (Surname) is not a key (Nr, Surname) is a super key but it is not a key (Nr) is a key
EACH RELATION HAS AT LEAST ONE KEY
Since relations are sets, a relation can not contain two identical tuples. It follows that each relation has a super key as the set of attributes on which it is defined. Since the set of all attributes is a super key for each relation, every relation schema has at least one super key. It follows that every relation schema has (at least) a key The keys are used to access records and to establish a references between different relations
PRIMARY KEY
It is not necessary that the two sets of attributes have the same name (but the number of attributes and the data types must be compatible). The course identify by code 05 doesn’t exist in the Course relation Without 05 but with 02 : Foreign Key constraint: Exam(Course) is included into Course (Code)
- Misused, an integrity constraint can prevent the storage of database instances that can arise in practice.
- The integrity constraints are based on the semantic organization of the real world that is described in the relations of the database.
- We can control the instance of a database to see if an integrity constraint is violated, but we can NEVER infer that an integrity constraint is true only examining one instance.
- An integrity constraint is a statement about all possible instances!
- Integrity constraints of keys and foreign keys are the most common; more general integrity constraints can be defined and applied as well
RELATIONAL DATA MODEL RECAP
The relational data model is the most widely used because the representation of a relation is achievable through simple and intuitive concept of table The references between tables are based on values without the need to know the underlying physical structures
We can define integrity constraints of the database. The most important are Constraints on tuple Key constraints Referential integrity (foreign keys) constraints : is an inter relation
EXERCISE 1 – 2 – 3 - 4 – 5 – 6 – 7 – 8
Exercise 3 : PLACE YEAR DISCOVERY RESEARCHER
MUSEUM HALL
FINDS : id finds - place – hall – museum – researcher – year
MUSEUM : name - manager - address - city –
country
HALL (hol) : identifier - name - size
RESEARCHER : identification code - name – last name –
date of birth
Finds (researcher ) --- Researcher (I code) Finds (hall) --- Hall (id) Hall (museum) --- Museum (name)
Exercise 5 :
1.TRUE
2.FALSE (but only one primary key)
3.FALSE
4.TRUE
- It is an Algebra. A mathematical structure built by a set of operands and operators with closure property within the set
- It is an Algebra of relations a set of relations on which some operators (unary or binary) closed within the set are defined; that means the result is a relation itself.
- Relational algebra is a procedural formal language (specification is generated as the result) query associated with the relational model.
- Through the relational algebra, complex operations are specified by describing the procedure to be
followed to obtain a result.
R.A. OPERATORS
- Usual set operators - Union - Intersect - Difference **- Rename
- Selection
- Projection
- Cartesian Product
- Join**
- Natural
- Tetha-join
- equi-join SET OPERATORS
- Relations are sets of tuples and then you can apply the usual set operators but with some restrictions
- The two sets, namely the two relations, must have the same number of attributes and attributes compatible (same domain)
- Union di r1 e r2: r1 U r2 contains the tuples that belong to r1 OR to r
- Intersection di r1 e r2: r1 ∩ r2 contains the tuples that belong to r1 AND to r
- Difference di r1 e r2: r1- r2 contains the tuples that belong to r1 AND do not belong to r RENAME
- It is unary operator, renaming the name of an attribute
- It is useful in case of union of two relations that have the same number of attributes but not with the same name
- It indicates with ρ B1, B2,..., Bn ← A1, A2, ..., An (r)
- Where Bn is the new name, An the old name and r the relation the operator is applied to
- Another notation is ρ (R1(A1→B1,... An →Bn),R) Where R1 is the result relation, An the old attribute name Bn the new attribute name R is the relation or another relational algebra expression PROJECTION
- It returns an instance of relation whose schema contains all fields of R (original order) followed by all fields of S (original order).
- The result contains tuples obtained by combining each tuple of R with each tuple of S JOIN
- It is a typical binary operator relational algebra
- It allows to correlate tuples of different relations on the basis of attribute values (typically the keys)
- There are several versions **- Natural join
- Theta join (conditional) NATURAL JOIN** Correlates data in different relations, on the basis of equal values for attributes with the same name Formally it is represented by r1 r r1 r2 = { t on X1 X2 | t[X1] € r1 e t[X2] € r2} In this case the join it is complete because all the rows of the two operands contribute to the result It may happen some value of the common attribute is missing in the other relation
In this case the join it is incomplete because not all the
rows of the two operands contribute to the result
OUTER JOIN
In case the application requires to retrieve all the tuples from the left or from the right relation anyway, we need to use the outer join operator
- Left (outer) join
- Right (outer) join
- Full (outer) join CONDITIONAL JOIN It is a join where the join condition is not necessarily expressed on a basis of two (set of) attributes with the same name, but it is a generic Boolean condition on the attributes Therefore it is equivalent to a Cartesian product in which a specific selection is applied r1 condition r2 = σ condition(r1 X r2) In case the join condition is expressed by equality of attributes (r1.A = r2.B) the join is a Equi-Join Otherwise, with more generic condition, is named Theta-Join In the case of natural join worth the properties : Commutative Associative If the schemas of the two relations are the same (X1 = X2), then the join is equivalent to the intersection NULL VALUES HANDLING We have to be careful when NULL values are present in the relation, because the conditions may be neither true nor false In order to handle NULL values special functions are introduced IS NULL IS NOT NULL
But you can always insert parentheses to force the order you desire. EXPRESSION TREE
- Leaves are operands - either variables standing for relations or particular, constant relations.
- Interior nodes are operators , applied to their child or children. Example: using the relations Bars(name, addr) and Sells(bar, beer, price), find the names of all the bars that are either on Maple St. or sell Bud for less than $3. VIEWS It may be useful to end users or applications to have different representations of the same data via the external schemas This can be done with the views that are derived relations They can be
- materialized views : the query result is materialized in a new relation
- Views: it is a virtual relation (usable by other queries) and coincides with the query definition Views are useful for making more readable very complex queries To decouple the level of data presentation (external schema) from logical data schema Exercise 1 Psalary ( R ) – Psalary (R JOIN ( R.salary > R1.salary) Ro (R1( Sur Sur1 , Sa Sa1) , R ) SLIDE
