Relational algebra. Lecture 8 - презентация, доклад, проект скачать

Нажмите для полного просмотра!

Relational algebra. Lecture 8, слайд №10

Relational algebra. Lecture 8, слайд №11

Relational algebra. Lecture 8, слайд №12

Relational algebra. Lecture 8, слайд №13

Relational algebra. Lecture 8, слайд №14

Relational algebra. Lecture 8, слайд №15

Relational algebra. Lecture 8, слайд №16

Relational algebra. Lecture 8, слайд №17

Relational algebra. Lecture 8, слайд №18

Relational algebra. Lecture 8, слайд №19

Relational algebra. Lecture 8, слайд №20

Relational algebra. Lecture 8, слайд №21

Relational algebra. Lecture 8, слайд №22

Relational algebra. Lecture 8, слайд №23

Relational algebra. Lecture 8, слайд №24

Relational algebra. Lecture 8, слайд №25

Relational algebra. Lecture 8, слайд №26

Содержание ▲

Вы можете ознакомиться и скачать презентацию на тему Relational algebra. Lecture 8. Доклад-сообщение содержит 26 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Mypresentation Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1

Описание слайда:

Database Management Systems LECTURE 8 Relational algebra IITU, ALMATY, 2019

Слайд 2

Описание слайда:

SQL Structure DDL (Data Definition Language) DML (Data Manipulation Language) TCL (Transaction Control Language) DCL (Data Control Language)

Слайд 3

Описание слайда:

Review of last lecture: SQL A DML is a language which enables to access and manipulate data. DML commands: INSERT UPDATE DELETE

Слайд 4

Описание слайда:

Querying Data From Tables Query operations facilitate data retrieval from one or more tables. The result of any query is a table. The result can be further manipulated by other query operations.

Слайд 5

Описание слайда:

Querying Data From Tables SQL allows to query data using SELECT statement. Syntax: SELECT attribute(s) FROM table(s) WHERE selection condition(s);

Слайд 6

Описание слайда:

Relational algebra Relational algebra, first described by E.F. Codd, is a family of algebras with a well-founded semantics used for modelling the data stored in relational databases, and defining queries on it. Once the data is normalized in sets of data (entities), the main operations of the relational algebra can be performed. The main application of relational algebra is providing a theoretical foundation for relational databases.

Слайд 7

Описание слайда:

Relational algebra Similar to normal algebra, except we use relations as values instead of numbers, and the operations and operators are different. Not used as a query language in actual DBMSs (SQL instead). We need to know about relational algebra to understand query execution in a relational DBMS.

Слайд 8

Описание слайда:

Querying Data From Tables The operations for querying data: projection selection union difference intersection join

Слайд 9

Описание слайда:

Projection Projection, referred to as Π (pi) Selects a set of attributes from a table The attributes are subscripts to Π and the table is in parenthesis Π stud_id (Students) Projection is represented in a SQL SELECT statement’s attribute list. The above projection is synonymous to the following SQL query: SELECT stud_id FROM Students;

Слайд 10

Описание слайда:

Selection Selection, referred to as σ (sigma) Selects a set of rows from a table that satisfy a selection condition The selection condition is the subscript to σ and the table is in parenthesis. σ stud_id=01 (Students)

Слайд 11

Описание слайда:

Selection In SQL, selection is represented in the WHERE clause of a select statement. Translate σ stud_id=01 (Students) to SQL: SELECT * FROM Students WHERE stud_id=01; What does SELECT * mean? It means that we are selecting all data – all attributes - from a table.

Слайд 12

Описание слайда:

Union (R1 U R2) is the relation containing all tuples that appear in R1, R2, or both. Union (R1 U R2) is the relation containing all tuples that appear in R1, R2, or both. Set difference (R1 - R2) is the relation containing all tuples of R1 that do not appear in R2. Intersection (R1 ∩ R2) is the relation containing all tuples that appear only in both R1 and R2.

Слайд 13

Описание слайда:

Union-compatible Two tables must be union-compatible for the operations to work: Tables need to have same number of attributes The domain of each attribute must also be the same.

Слайд 14

Описание слайда:

Union-compatible: example

Слайд 15

Описание слайда:

Support in SQL For Union SQL supports the UNION operator. For Difference (or Set Difference) SQL supports the EXCEPT operator. For Intersection SQL supports the INTERSECT operator.

Слайд 16

Описание слайда:

Combining Queries The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is query1 UNION [ALL] query2 query1 INTERSECT [ALL] query2 query1 EXCEPT [ALL] query2 query1 and query2 are queries that can use any of the features discussed up to this point.

Слайд 17

Описание слайда:

Combining Queries Set operations can also be nested and chained, for example query1 UNION query2 UNION query3 which is executed as: (query1 UNION query2) UNION query3 In order to calculate the union, intersection, or difference of two queries, the two queries must be "union compatible", which means that they return the same number of columns and the corresponding columns have compatible data types.

Слайд 18

Описание слайда:

Union / UNION The UNION operation on relation A UNION relation B designated as A∪B, includes all tuples that are in A or in B, eliminating duplicate tuples. To include duplicates, use the UNION ALL operator. SQL Syntax: SELECT * From A UNION SELECT * From B

Слайд 19

Описание слайда:

UNION SELECT * From R UNION SELECT * From S

Слайд 20

Описание слайда:

UNION ALL SELECT * From R UNION ALL SELECT * From S

Слайд 21

Описание слайда:

Set Difference / EXCEPT The DIFFERENCE operation includes tuples from one relation that are not in another relation. Let the Relations be A and B, the operation A EXCEPT B is denoted by A – B, that results in tuples that are A and not in B. SQL Syntax: SELECT * FROM A EXCEPT SELECT * FROM B

Слайд 22

Описание слайда:

EXCEPT SELECT * FROM R EXCEPT SELECT * FROM S

Слайд 23

Описание слайда:

EXCEPT SELECT * FROM S EXCEPT SELECT * FROM R

Слайд 24

Описание слайда:

Intersection / INTERSECT The INTERSECTION operation on a relation A INTERSECT relation B, designated by A ∩ B, includes tuples that are only in A and B. In other words only tuples belonging to A and B, or shared by both A and B are included in the result. SQL Syntax: SELECT * FROM A INTERSECT SELECT * FROM B

Слайд 25

Описание слайда:

INTERSECT SELECT * FROM R INTERSECT SELECT * FROM S

Слайд 26

Описание слайда:

Books Connolly, Thomas M. Database Systems: A Practical Approach to Design, Implementation, and Management / Thomas M. Connolly, Carolyn E. Begg.- United States of America: Pearson Education Garcia-Molina, H. Database system: The Complete Book / Hector Garcia-Molina.- United States of America: Pearson Prentice Hall Sharma, N. Database Fundamentals: A book for the community by the community / Neeraj Sharma, Liviu Perniu.- Canada