1 / 18

CM036: Advanced Database

CM036: Advanced Database. Lecture 3 [Self Study] Relational Algebra and SQL. 3 Structured Query Language – SQL. A standard language for working with relational databases; mixture of DDL, DML and DCL constructs

cheri
Download Presentation

CM036: Advanced Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. CM036: Advanced Database Lecture 3 [Self Study] Relational Algebra and SQL

  2. 3 Structured Query Language – SQL • A standard language for working with relational databases; mixture of DDL, DML and DCL constructs • It is a specific database language, not general-purpose programming language; all its constructs are primarily for manipulating tables, rows, columns, database schemes and users - not for direct processing of the data • There are several SQL standards; among them SQL-92 (SQL2) and SQL-99 (SQL3) are the most widely used, and SQL-92 is still regarded up-to-date; Different vendors implement them to certain levels of compliance – for example, Oracle 8i is SQL2 compliant, while Oracle 9i is SQL3 compliant • SQL is purely declarative; procedural extensions of SQL exist, but they are vendor-specific (e.g. Oracle PL/SQL, Microsoft Transact SQL, Informix 4GL, etc.) CM036: Advanced Databases Lecture 3: Relational Languages

  3. 3.1 SQL DML For querying and manipulating relational databases; its constructs are recognised by the first keyword - SELECT, INSERT, UPDATE or DELETE • The SELECT statements implement the relational algebra operations; All relational operations can be expressed in a standard SQL implementation using SELECT statements only • INSERT statements are used to add tuples to the relations, defined by the relational schema • DELETE statements exclude tuples from the relations • UPDATE change some of the attributes of the existing tuples CM036: Advanced Databases Lecture 3: Relational Languages

  4. 3.2 SELECT- statement • The general form of this statement in SQL is SELECT <list-of-columns> FROM <list-of-tables> WHERE <conditions-on-the-tables> The result from executing the statement is directed to the system output by default. It corresponds to the following form  <algebraic expression> Example: The following statement SELECT e.name, a.address FROM EMP e, ADDR a WHERE a.no = e.no corresponds to the algebraic expression • name,address(EMP * ADDR) CM036: Advanced Databases Lecture 3: Relational Languages

  5. 3.2 SELECT- statement … • In some cases (e.g. in Oracle extended SQL), the query result can be stored SELECT <list-of-columns> INTO <storage-place> FROM <list-of-tables> WHERE <conditions-on-the-tables> In this case, the result is stored into <storage-place> and the statement corresponds to the following algebraic expression <relation>  <algebraic expression> where <relation> is the relation calculated as a result of the expression CM036: Advanced Databases Lecture 3: Relational Languages

  6. 3.2 SELECT- statement … • There are variations of these templates, which support the implementation of relational operations through varying of different clauses in the statement: • SELECT clause can explicitly point at the required columns; alternatively, it can contain special symbol *, which projects full rows without skip of any columns in them • FROM clause can contain more then one table, which allows to implement both unary and binary operations • WHERE clause in the statement can be used to restrict the rows to be selected using additional conditions on the columns • INTO clause, when present, points to a data storage which should store the result from the query; it should have the same structure, as the expected result CM036: Advanced Databases Lecture 3: Relational Languages

  7. 3.3 Simulating RA operations • All relational algebra operations are simulated in SQL using SELECT statements only. For this purpose the following variations of different parameters in the SELECT clauses can be used: • The list of attributes to be selected in the SELECT clause • The number of tables to be looked into in the FROM clause • The conditions specified in the WHERE clause • The resulting relation given INTO clause is present • There is no structural correspondence between the expressions of relational algebra and the SQL expressions; in a single SQL statement the following combinations are possible: • single unary operator applied to one single relation • single binary operator applied to pair of relations • sequence of unary operators applied inner side out to one relation and all the intermediate results • combinations of binary and unary operators applied to several relations and the intermediate results according nesting rules CM036: Advanced Databases Lecture 3: Relational Languages

  8. Restriction and Projection • The SELECT statements of SQL when applied to one table only correspond to three possible combinations of the relational algebra operatorss (RESTRICT) and  (PROJECT) Examples: • List of all student ids, names and addresses SELECT StudentId, Name, Address FROM Student  StudentId, Name, Address(STUDENT) • Full information about the students in Business Computing SELECT * FROM Student WHERE CourseName = ‘Business Computing’ sCourseName = ‘Business Computing’(STUDENT) • List of ids and names for students in Business Computing SELECT StudentId, Name FROM Student WHERE CourseName = ‘Business Computing’  StudentId, Name(sCourseName = ‘Business Computing’(STUDENT)) CM036: Advanced Databases Lecture 3: Relational Languages

  9. Cartesian Product • When applied to two tables simultaneously without WHERE clause, the SELECT statements correspond to combinations of the unary relational algebra operator  with the binary operator  Examples: • Full information about students and courses SELECT * FROM Student, Course STUDENT  COURSE • List of ids for both students and lecturers SELECT StudentId, LecturerId, FROM Student, Lecturer  StudentId, LecturerId(STUDENT  LECTURER) Note:When more than one tables are in a single SELECT statement, to avoid ambiguity when referring to columns with the same names we precede them with the names of respective tables. • Names of all students and lecturers SELECT STUDENT.Name, LECTURER.Name FROM STUDENT, LECTURER CM036: Advanced Databases Lecture 3: Relational Languages

  10. Inner Joins • When applied to two tables simultaneously, the SELECT statements with WHERE clause correspond to combination of the two unary relational algebra operatorss (RESTRICT) and  (PROJECT) with proper binary operators Examples: • Full information about the students and their courses SELECT * FROM Student, Course WHERE Student.Course = Course.Name OR SELECT * FROM Student INNER JOIN Course ON Student.Course = Course.Name STUDENT ⋈ Course = Name COURSE • List of student names with their course leaders SELECT StudentName, CourseLeader FROM Student NATURAL JOIN Course  StudentName,CourseLeader(STUDENT * COURSE) CM036: Advanced Databases Lecture 3: Relational Languages

  11. Full information about students from Business computing with details about the course SELECT * FROM Student INNER JOIN Course ON Student.Course = Course.Name WHERE Course.CourseName = ‘Business Computing’ sCourseName = ‘Business Computing’ (STUDENT ⋈Course = Name COURSE) Note:In order to avoid ambiguity when referring to columns from different tables which have the same names, we should qualify them explicitly in the WHERE clause. This can be done through preceding their names with aliases of the respective tables. • The names of all unit leaders of current units together with the names of the units themselves SELECT l.Name, u.Name FROM LECTURER l INNER JOIN UNIT u ON u.Leader = l.Name WHERE u.Status = ‘Current’ CM036: Advanced Databases Lecture 3: Relational Languages

  12. Set Operations • The tuples returned as answers of two independent queries can be combined in a single relation using the set operators  (UNION) - for the union of two relations and  (DIFFERENCE or MINUS) - for the difference between them Examples: (using Oracle SQL Syntax) • Full details for students in both Mathematics and Computing SELECT * FROM Student WHERE Course = ‘Computing’ UNION SELECT * FROM Student WHERE Course = ‘Mathematics’ sCourse = ‘Computing’(STUDENT) sCourse = ‘Mathematics’(STUDENT) • Full details for students in Computing after first year SELECT * FROM Student WHERE Course = ‘Computing’ MINUS SELECT * FROM Student WHERE Level = 1 sCourse = ‘Computing’(STUDENT) sLevel = 1(STUDENT) CM036: Advanced Databases Lecture 3: Relational Languages

  13. Outer Joins (Oracle 8i) • Outer joins are not always directly expressible in the commercial SQL implementations. But they can be modelled if needed Examples:(using Oracle SQL syntax) • List of unit leaders with their units, including leaders still without assigned units SELECT LecturerName, UnitName FROM Lecturer, Unit WHERE Lecturer.UnitName = Unit.UnitName (+) LECTURER UnitName = UnitNameUNIT • List of units with their leaders, including units still without appointed leaders SELECT UnitName, LecturerName FROM Lecturer, Unit WHERE Lecturer.UnitName (+) = Unit.UnitName LECTURER UnitName = UnitNameUNIT CM036: Advanced Databases Lecture 3: Relational Languages

  14. Combined list of units and leaders, including units without leaders and leaders without units (full outer join) SELECT Lecturer.UnitName, Unit.UnitName FROM Lecturer, Unit WHERE Lecturer.UnitName = Unit.UnitName(+) UNION SELECT Lecturer.UnitName, Unit.UnitName FROM Lecturer, Unit WHERE Lecturer.UnitName (+) = Unit.UnitName LECTURER UnitName = UnitNameUNIT CM036: Advanced Databases Lecture 3: Relational Languages

  15. Outer Joins (Oracle 9i) • Outer joins are not always directly expressible in the commercial SQL implementations. But they can be modelled if needed Examples:(using Oracle SQL syntax) • List of unit leaders with their units, including leaders still without assigned units SELECT LecturerName, UnitName FROM Lecturer LEFT OUTER JOIN Unit ON Lecturer.UnitName = Unit.UnitName LECTURER UnitName = UnitNameUNIT • List of units with their leaders, including units still without appointed leaders SELECT UnitName, LecturerName FROM Lecturer RIGHT OUTER JOIN Unit WHERE Lecturer.UnitName = Unit.UnitName LECTURER UnitName = UnitNameUNIT CM036: Advanced Databases Lecture 3: Relational Languages

  16. Combined list of units and leaders, including units without leaders and leaders without units (full outer join) SELECT Lecturer.UnitName, Unit.UnitName FROM Lecturer FULL OUTER JOIN Unit ON Lecturer.UnitName = Unit.UnitName • When more than two tables are queried, the SQL operator corresponds to combination of unary and binary operations Examples: (using Oracle SQL syntax) • List of students in Computing after first year SELECT StudentId, StudentName FROM Student WHERE CourseName= ‘Computing’MINUS SELECT StudentId, StudentName FROM Student WHERE CourseLevel = 1 sCourseName = ‘Computing’(StudentId,StudentName (STUDENT))  sCourseLevel = 1(StudentId,StudentName (STUDENT)) LECTURER UnitName = UnitNameUNIT CM036: Advanced Databases Lecture 3: Relational Languages

  17. Note:According to relational algebra theory, the relations do not contain duplicated tuples. However, the result from an SQL query could contain several identical rows. If we wish to have a true relation as a result instead, the keyword DISTINCT should be specified after SELECT to indicate eliminating of possible duplications. • All units which have been subscribed by the students without duplicates SELECT DISTINCT e.Unit FROM STUDENT s, ENROLMENT e WHERE e.Student = s.Name CM036: Advanced Databases Lecture 3: Relational Languages

  18. Summary Relational Algebra • Formal languages have unambiguous syntax and clear semantics, which makes them good for specifications • Formal languages hide the details of implementation and can’t give very deep insight into the real DBMS • Formal languages formalize adequately only part of the database operations, which have semantics inherited from the relational model;they do not cover DCL at al. SQL • SQL syntax is ambiguous about the order of operations,which requires knowledge of the way it is interpreted • SQL is not a programming language, but language for communication with DBMS and still can’t give very deep insight into the real data processing • SQL as a mixture of DDL, DML and DCL is the ultimate choice for practical relational databases CM036: Advanced Databases Lecture 3: Relational Languages

More Related