Cơ sở dữ liệu - Chapter 4: Relational model

Chapter 4 Relational Model Relational Model Concept  Relational database: a set of relations  Each relation resembles a table of values  Relation: made up of 2 parts ◦ Instance: a table, with rows and columns. ◦ Schema: specifies name of relation, plus name and type of each column.  The terms commonly used User M del Programmer Row Tuple Record Column Attribute Field Table Relation File Relational Data Model  The relational model uses a collection of tables to represent both data and the relationships among those data.  Each relation resembles a table of values  Tables are logical structures maintained by the database manager.  Ex: STUDENT (NO., NAME, DATE_OF_BIRTH, GENDER) Relation Set of attributes Relational Database  Ex: ◦ A row is called tuple. ◦ A column is called attribute. ◦ The table is called relation. Relational Database  For each column of a table, there is a set of possible values called its domain.  Domain is the set of valid values for an attribute. Relation Schema  Relation Schema R, denoted by R(A1, A2,, An), is made up of relation name R and a list of attributes A1, A2, ,An  Each attribute Ai is the name of a role played by some domain D in the relation schema R.  D is called the domain of Ai and is denoted by dom(Ai)  R is called the name of the relation  The degree of a relation is the number of attributes n of its relation schema Relational Algebra  Symbol ◦ A1, A2, A3, , An: attribute ◦ D1, D2, D3, , Dn : domain ◦ R (A1:D1, A2:D2, , An:Dn): relational model ◦ r(R): relation  r = {t1, t2, t3, , tn}  ti : tuple Relational Data Model  The relational model is a combination of three components: ◦ Structural Part: the database as a collection of relations. ◦ Integrity Part: maintained in the relational model using primary and foreign keys ◦ Manipulative Part: The relational algebra and relational calculus are the tools used to manipulate data in the database. Concept of Key  An attribute or group of attributes, which is used to identify a row in a relation.  Can be classified into 3 types: ◦ Super key ◦ Candidate key ◦ Primary key Concept of Key  Super key: ◦ a subset of attributes of an entity-set that uniquely identifies the entities. ◦ Represent a constraint that prevents two entities from ever having the same value for those attributes. Concept of Key  Candidate key: ◦ a minimal super key. ◦ a minimal set of attributes whose values uniquely identify tuples in the corresponding relation.  Primary key: ◦ a designated candidate key. ◦ not be null  Foreign key: ◦ Set of fields or attributes in one relation that is used to “refer” to a tuple in another relation. Concept of Key  Ex: Employee characterized by these attributes: ◦ employee ID ◦ employee name ◦ employee age ◦ employee experience ◦ employee salary, etc.  Super keys can be: employee ID, employee name, employee age, employee experience, etc.  Candidate keys can be: employee ID, employee name, employee age.  Primary key: employee ID. Concept of Key Relational Query Languages  A major strength of the relational model: supports simple, powerful querying of data.  Queries can be written intuitively, and the DBMS is responsible for efficient evaluation.  The SQL Query Language is developed by IBM in the 1970s Relational Integrity  Four types of data integrity constraints: ◦ Entity ◦ NULL ◦ Domain Integrity Constraint ◦ Referential integrity. Entity Integrity:  Imply that a primary key cannot accept null value  Ex: ◦ The attributes of the entity PLAYER are Name, Age, Nation, and Rank. ◦ If PLAYER’s name’s used as the primary key  cannot insert any data in the relation PLAYER without entering the name of the player. Null Integrity  Imply that the data on attributes can be NULL value are or are not permitted  Ex: ◦ The relation PERSON must have a valid, non- NULL value for the Name attribute, then Name of PERSON is constrained to be NOT NUL Relational Integrity  Domain Integrity Constraint ◦ are based upon the semantics of the real world enterprise that is being described in the database relations. ◦ are used in the relational model to define the characteristics of the columns of a table.  Ex: ◦ The age of the person cannot have any letter from the alphabet. The age should be a numerical value. Relational Integrity  Referential Integrity ◦ a database must not contain any unmatched foreign key values. ◦ not imply a foreign key cannot be null. ◦ Either each foreign key value must match a primary key value in another relation or the foreign key value must be null. ER design to Relational  Entity sets to tables  EMPLOYEE (Emp_ID, Name, Age) CREATE TABLE Employees (Emp_ID CHAR(11), Name CHAR(20), Age INTEGER, PRIMARY KEY (Emp_ID)) EMPLOYEE Emp_ID Name Age Relational Algebra  Query languages: Allow manipulation and retrieval of data from a database.  Relational model supports simple, powerful QLs: ◦ Strong formal foundation based on logic. ◦ Allows for much optimization.  Query Languages != programming languages! ◦ QLs not intended to be used for complex calculations. ◦ QLs support easy, efficient access to large data sets. Relational Algebra Relational algebra Set Operations * Union () * Intersection () * Difference (-) * Rename (ρ) Combined set * Cartesian (x) * Join ( ) Database Operations * Selection * Projection Selection  Work on a single relation R  Define a relation that contains only those tuples of R that satisfy the specified condition  Syntax: ◦ In selection condition:  Comparison operator : >, >=, <, <=, =, ≠  Relational operator: ,, ¬ σ (R) Selection  Ex:  Selection operation is associative. Selection _ example  List all students who get GPA mark > 8 Student Roll. No Name GPA 001 Aravind 7.2 002 Anand 7.5 003 Balu 8.2 004 Chitra 8 005 Deepa 8.5 006 Govind 7.2 007 Hari 6.5 Projection  Define a relation that contains a vertical subject of R, extracting the values of specified attributes and elimination duplicates.  Syntax: pa1,a2,..,an(R) Projection _ example Staff No Name Gender Date of birth Salary SL21 Raghavan M 1/5/1976 15,000 SL22 Raghu M 1/5/1977 12,000 SL55 Babu M 1/6/1976 12,500 SL66 Kingsly M 1/8/1978 10,000  Produce the list of salaries for all staff showing only the Name and salary detail  pName, salary (STAFF)  Give the name and Date of birth of the all the staff  pName, Date of birth (STAFF) Union  Define a relation that contains all the tuples in both R and S  Eliminate all duplicate tuples.  Expression: R  S  Ex: R  S = { t | tR  tS }  Define a relation consisting of the set of all tuples that are in both R and S  Expression: R S  Ex: Intersection R  S = { t | tR  tS }  Define a relation consisting of the tuples that arein relation R but not in S  Expression: R - S  Ex: Intersection R - S = { t | tR  tS } Rename  Return an existing relation under a new name ◦ Expression: ◦ Relation B will be renamed to A  Rename an attribute ◦ Expression: Original relation: R(A, B, C) ◦ Attribute A will be change into X ρS(R) ρX,B,C(R) Rename  Give the first name and last name of all employees working in the 4th department ◦ pFName, LName (σDepNo=4 (STAFF))  ◦ Emp_Dep4  σDepNo=4 (STAFF)) ◦ R  pFName, LName (Emp_Dep4) ◦ ρFirstName, LastName(R) Cartesian Product  Define a relation that is the concatenation of every tuples of relation R with every tuples of relation S.  The result of Cartesian product contains all attributes from both relations R and S.  Expression: R x S Cartesian Product  Ex: Cartesian Product  Ex: Cartesian Product  Ex: Join  Combine two relations to form a new relation.  The tables should be joined based on a common column. The common column should be compatible in terms of domain  3 types of join operation: ◦ Natural Join ◦ Equi Join ◦ Theta Join Equi Join  A special case of condition joins where the condition C contains only equality.  Expression: Equi Join Natural Join  Input: Two relations (tables) R a  Notation: R S  Purpose: Relate rows from second table and ◦ Enforce equality on all column attributes ◦ Eliminate one copy of common attribute Natural Join Theta Join  A conditional join in which we impose condition other than equality condition. Outer Join  In outer join, matched pairs are retained unmatched values in other tables are left null.  Full outer join  Left outer join  Right outer join Outer Join Outer Join Outer Join Outer Join