�������� ��������������������� � The Relational Model slide 64 � Operators of Relational Algebra slide 77 ������������������� � Query examples slide 94 ��� � Optimization slide 99 63 ��������������������� ��������������� The Relational Model The Relational Model � Type or domain � Definition � Cartesian Product � Set of values (defined in intension or in extension) � Relation � Examples � Attributes � Integer, Real, String, Boolean � Key � Salary = 1000…100000 ( � ) � Relational Schema and Database Schema � Color = {‘blue’, ‘white’, ‘red’} � Foreign Key � Data dictionary 64 65 Relational data model - 1
����������������� The Relational Model The Relational Model �������� � Definition � Definition � The cartesian product of D 1 , ...., D n is the set of tuples <V 1 ,.., � Subset of the cartesian product of a list of domains V n > where V i ∈ D i Notation � Characterized by a name � � D 1 X ....X D n � Example � Example : � D 1 = Modules D1 X D2 D1 D2 � D 1 = {‘DB’, ‘OO’} (Modules) � D 2 = Teachers DB Lecocq � D 2 = {‘Lecocq’, ‘Bac’} (Teachers) DB Bac ModulesCo D1 D2 ord OO Lecocq DB Lecocq OO Bac OO Bac 66 67 The Relational Model The Relational Model ���������� ! "#��������$���������� � More simply, a relation is a 2-dimensional table , Student StudentI Name Address Age d composed of rows and columns 1 Bélaïd Maisel 20 � A row is called a tuple 2 Millot CROUS 20 3 Meunier Maisel 21 � A name is associated to each column in order to find it without order information = attribute Registration StudentId ModuleId Mark � takes its values in a given domain ModulesCo ModuleId coord 2 DB 10 ord � Example : ModuleId 1 DB 20 DB Lecocq 2 OO 17 OO Bac 3 OO 18 68 69 Relational data model - 2
�����������&�'���� The Relational Model The Relational Model %�� Definition � Definition � � A relational schema is composed of : � A key is a minimal set of attributes that determines a unique A name � tuple in a relation (at every moment) A list of attributes and their associated domains � Example � The list of attributes composing the key (the key is underlined) � � Key of Student ? Example � � Key of Module ? � Student (StudentId : integer, name : string, address : string, � Key of Registration ? age : integer between 18 and 35) Integrity Constraint � Intension vs. Extension � � Every relation must have a key that is filled (without an unknown � Relational Schema : the intension of the relation or null value) � Table : the extension of the relation � Relational Database: a set of Relational Schemas 70 71 (�������%�� (�������%���� ! The Relational Model The Relational Model � Updates and Foreign Keys � Definition � Insertion: the value of attributes must exist in the � A Foreign Key is a set of attributes appearing as key in referenced relation. another relation � Insertion of the tuple (4, ‘DB’, 15) in Registration ? R1(A1, A2, .... , Ap, Ap+1 , ...., An) � deletion in the referenced relation is possible iff there are no referencing tuples. R2(B1, B2, ......, Bn) � deletion of Student number 2 of Student ? � Role � Foreign Keys reflect the relationships of the E/R � Foreign Keys define referential integrity constraints model between relations 72 73 Relational data model - 3
��������������� (�������%�� The Relational Model The Relational Model Definition � Examples � � A “database” containing the set of schemas and Student(StudentId, name, address, age) correspondence rules associated to a database Principle � Module(ModuleID, nbh, coord) � A database describing other databases, that is: relations � Registration(StudentId, ModuleId, mark) attributes � domains � Book(BookId, title, StudentId, lendingDate) keys ..... � Room(RoomId, price, StudentId) � A particular Database managed by the Database Manager 74 75 &��������$�������� )�������� ���������� ����'� ���������� The Relational Model ����� Schema Relation1 attribute 1 attribute 2 attribute n Intension � Languages for Data Definition (DDL) : DB RelName attribute v1 w1 Schem � Definition /updates of relational schemas v1 w2 a Relation attribute � Query Languages (QL) : allow data manipulation 1 1 Table Tuple and retrieval from a database Relation attribute v3 w2 1 2 � Queries : data retrieval vn … � Updates : insertion, deletion, updates Key Relation2 attribute a attribute b � 2 classes of languages : Foreign Key w1 � Algebraic � SQL w2 � Predicative � QBE DB DD (Data Dictionary) xi wn 76 77 Relational data model - 4
Relational Algebra Relational Algebra &�������� ���������������������� Students registered in DB : � σ ModuleId=‘DB’ (Registration) � Goal � Every result of an operation is a relation; it can thus be to "select" a subset of rows � given as input to a new operator (composition). (tuples) satisfying a condition Result StudentId ModuleId mark The Selection Operator reduces � � Operators can be classified as follows : the “vertical” size of the relation 2 DB 10 � set operators / relational operators Constraints � 1 DB 20 Unary � � basic operators / derived operators requires a condition � � unary operators / binary operators Notation � Major Students (mark >15) in DB: � Textual Notation : T σ cond (R) Unary : Selection , Projection, � � σ ModuleId=‘DB’ and Graphical Notation : Binary : Union, Intersection, Set-difference, cartesian Product, Join, � � mark>15 (Registration) Division R Result StudentId ModuleId mark Cond. 1 DB 20 T 78 79 ���*������ Relational Algebra +���� Relational Algebra 1st step: Teachers’ and Students’ � Goal Goal � � Names Students’ Addresses : � "selecting" attributes � allows to union 2 relations � Prof Name Student Name Π Address (Student) The projection operator reduces � Constraints � the “horizontal “ size of the Bac Bélaïd Binary � relation Lecocq Millot Result Address The 2 input relations must Constraints � � Millot Meunier have the same schema No Unary � Maisel Same number of attributes doubles � requires a list of attributes � CROUS “Corresponding” attributes People’s Names at INT : Prof ∪ Student � � Notation � have the same type (domain) Result Name Textual Notation : T Π attributes (R) � Id and nb. of hours of Modules : � Notation � Bac Graphical Notation : � Π ModuleID,nbh (Module) Textual Notation : T R ∪ S � R Lecocq Result ModuleID nbh Graphical Notation : � attributes. Millot R S OO 45 Bélaïd No ∪ doubles DB 15 Meunier T 80 81 T Relational data model - 5
������������ ��$$������ Relational Algebra Relational Algebra Goal � Goal � Obtaining the set of tuples � 1st step: Teachers’ and Students’ � � 1st step: Teachers’ and allows to obtain the tuples � being in Relation1, but not in Names belonging to 2 relations at the Students’ Names Relation2 same time Prof Name Student Name Prof Name Student Name Constraints � Constraints � Bac Bélaïd Bac Bélaïd Binary � Binary � Lecocq Millot Lecocq Millot Same schema for the 2 input � Same schema for the 2 input � Millot Meunier Millot Meunier relations relations Non commutative � Notation � Notation � Textual Notation : T R ∩ S � Teachers’ and Students’ names in � Textual Notation : T R - S Students that do not have the � � Graphical Notation : � common : Prof ∩ Student name of a Teacher : Student- Graphical Notation : � R S R S Prof Result Name Result Name ∩ - Millot Bélaïd Meunier 82 83 T T ����������������� ������������������� ! Relational Algebra Relational Algebra Student StuId Name Address Age Mod ModId Nbh Coord Goal � � to combine 2 relations : by concatenation of each tuple of R with each 1 Bélaïd Maisel 20 OO 45 Bac tuple of S 2 Millot CROUS 20 DB 15 Lecocq Constraints � 3 Meunier Maisel 21 � Binary � Resulting Schema : Student X Mod StuId Name Address Age ModId Nbh Coord R(a1, a2, ...., an), S(b1, b2, ..., bp) � T R X S, T(a1, a2, ...., an, b1, b2, ..., bp) � 1 Bélaïd Maisel 20 OO 45 Bac R S � Card (R X S) = Card (R) * Card (S) 2 Millot CROUS 20 OO 45 Bac � Notation 3 Meunier Maisel 21 OO 45 Bac � Textual Notation : T R X � S X 1 Bélaïd Maisel 20 DB 15 Lecocq � Graphical Notation : 2 Millot CROUS 20 DB 15 Lecocq T 3 Meunier Maisel 21 DB 15 Lecocq 84 85 Relational data model - 6
Recommend
More recommend
