1 / 30

Logical Layer

Logical Layer. The Relational Data Model + SQL. Abstraction Layers. WHAT. Conceptual What data is held An Image and its meta-data Entity-Relationship model (ERM) Logical How data is organised in storage Block and Directory structure Tables, keys Physical How data is stored in bits

colman
Download Presentation

Logical Layer

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. Logical Layer The Relational Data Model + SQL

  2. Abstraction Layers WHAT • Conceptual • What data is held • An Image and its meta-data • Entity-Relationship model (ERM) • Logical • How data is organised in storage • Block and Directory structure • Tables, keys • Physical • How data is stored in bits • JPEG as a stream of bytes • A Database as files and records stored in a DBMS-specific format Realisation (Refinement Reification) (Engineering, Model-Driven development Abstraction (Reverse Engineering) HOW

  3. The Relational Data Model • The Theory underlying Relational Databases – Access, Oracle, MySQL.. • E.F Codd A Relational Data Model for Large Shared Data Banks (1970) • All Relational DBMSs depart from the basic model

  4. Components • The concepts available to represent the UoD • Relations (tables) of • Tuples (rows) , of • Columns (fields) containing values drawn from a Domain • Base Relations - facts • Derived Relations - • Relations constructed by extracting, combining base relations

  5. EMP-DEPT example (from SQL workbook) Two relations: Department : DEPT Employee : EMP

  6. DEPT Table

  7. EMP - table Empno Ename Mgr Sal Deptno 7369 SMITH 7902 £ 800.00 20 7499 ALLEN 7698 £1,600.00 30 7521 WARD 7698 £1,250.00 30 7566 JONES 7839 £2,975.00 20 7654 MARTIN 7698 £1,250.00 30 7698 BLAKE 7839 £2,850.00 30 7782 CLARK 7839 £2,450.00 10 7788 SCOTT 7566 £3,000.00 20 7839 KING £5,000.00 10 7844 TURNER 7698 £1,500.00 30 7876 ADAMS 7788 £1,100.00 20 7900 JAMES 7698 £ 950.00 30 7902 FORD 7566 £3,000.00 20 7934 MILLER 7782 £1,300.00 10

  8. Relation Column (field) Relation (table) Deptno Dname Loc 10 Accounting New York 20 Research Dallas 30 Sales Chicago 40 Operations Boston Tuple (row) general integer string domain 0<int<99 city name specific

  9. Relations are everything • There is only one data structure in the relational data model - the relation • Every relation in a database must have a distinct name. • Every column in a relation must have a distinct name within the relation. • All entries in a column must be of the same kind • The ordering of columns in a relation is not significant. • Each row in a relation must be distinct • Leads to Primary Key in a Relational Database • The ordering of rows is not significant. • Each cell or column/row intersection in a relation should contain only a so-called atomic value.

  10. Relational Algebra • A group of operations on relations which yield only other relations – “Closed” • A single tuple (row) is a relation • A single value is also a relation • Base operations • RESTRICT, PROJECT, PRODUCT • Convenience operations • EQUI-JOIN, (Natural) JOIN, Outer Joins • Set operations • UNION, INTERSECTION, DIFFERENCE, DIVISION

  11. The Relational Algebra Restrict Union Project Intersect Product

  12. SQL (SeQueL) • There are a number of languages for manipulating relations, but the one most commonly implemented is SQL • SQL1 - 1986 • SQL2 - 1992 • better support for Algebraic operations • SQL3 - 1999 Post-Relational • row and column types, stored procedures, triggers, references, large objects

  13. SQL • DATA MANIPULATION (DML) - factbase • QUERY • SELECT • UPDATE • UPDATE • DELETE • INSERT • DATA DEFINITION (DDL) -schema • CREATE, ALTER, DROP • DATA CONTROL (DCL) - access control • GRANT,REVOKE

  14. RESTRICT:SELECT * FROM EMP WHERE sal > 2000 Empno Ename Mgr Sal Deptno 7369 SMITH 7902 £ 800.00 20 7499 ALLEN 7698 £1,600.00 30 7521 WARD 7698 £1,250.00 30 7566 JONES 7839 £2,975.00 20 7654 MARTIN 7698 £1,250.00 30 7698 BLAKE 7839 £2,850.00 30 7782 CLARK 7839 £2,450.00 10 7788 SCOTT 7566 £3,000.00 20 7839 KING £5,000.00 10 7844 TURNER 7698 £1,500.00 30 7876 ADAMS 7788 £1,100.00 20 7900 JAMES 7698 £ 950.00 30 7902 FORD 7566 £3,000.00 20 7934 MILLER 7782 £1,300.00 10

  15. Project:Select Empno,Mgr,Deptno from Emp Empno Ename Mgr Sal Deptno 7369 SMITH 7902 £ 800.00 20 7499 ALLEN 7698 £1,600.00 30 7521 WARD 7698 £1,250.00 30 7566 JONES 7839 £2,975.00 20 7654 MARTIN 7698 £1,250.00 30 7698 BLAKE 7839 £2,850.00 30 7782 CLARK 7839 £2,450.00 10 7788 SCOTT 7566 £3,000.00 20 7839 KING £5,000.00 10 7844 TURNER 7698 £1,500.00 30 7876 ADAMS 7788 £1,100.00 20 7900 JAMES 7698 £ 950.00 30 7902 FORD 7566 £3,000.00 20 7934 MILLER 7782 £1,300.00 10

  16. Restrict - Project:Select Empno,Mgr,Deptno from Emp where sal > 2000; Empno Ename Mgr Sal Deptno 7369 SMITH 7902 £ 800.00 20 7499 ALLEN 7698 £1,600.00 30 7521 WARD 7698 £1,250.00 30 7566 JONES 7839 £2,975.00 20 7654 MARTIN 7698 £1,250.00 30 7698 BLAKE 7839 £2,850.00 30 7782 CLARK 7839 £2,450.00 10 7788 SCOTT 7566 £3,000.00 20 7839 KING £5,000.00 10 7844 TURNER 7698 £1,500.00 30 7876 ADAMS 7788 £1,100.00 20 7900 JAMES 7698 £ 950.00 30 7902 FORD 7566 £3,000.00 20 7934 MILLER 7782 £1,300.00 10

  17. Restrict - Project:Select Empno,Mgr,Deptno from Emp where Sal > 2000 and Sal < 3000; Empno Ename Mgr Sal Deptno 7369 SMITH 7902 £ 800.00 20 7499 ALLEN 7698 £1,600.00 30 7521 WARD 7698 £1,250.00 30 7566 JONES 7839 £2,975.00 20 7654 MARTIN 7698 £1,250.00 30 7698 BLAKE 7839 £2,850.00 30 7782 CLARK 7839 £2,450.00 10 7788 SCOTT 7566 £3,000.00 20 7839 KING £5,000.00 10 7844 TURNER 7698 £1,500.00 30 7876 ADAMS 7788 £1,100.00 20 7900 JAMES 7698 £ 950.00 30 7902 FORD 7566 £3,000.00 20 7934 MILLER 7782 £1,300.00 10

  18. Some queries to write: • http://www.cems.uwe.ac.uk/~cjwallac/sql/mysql/queryemp.php • List the names of the employees whose manager is 7698 • List the empnos of the employees in Department no 20 whose salary is over $2500

  19. Product:Select * from EmpX , Dept;

  20. Product : • DEPT has 4 records • EMPX has 3 records • so DEPT x EMPX has 12 records • but not very useful

  21. Product – Project - RestrictSelect * from EmpX ,Dept where Emp.Deptno=Dept.Deptno; Empno Mgr Deptno Deptno Dname Location 7566 7839 20 10 Accounting New York 7566 7839 20 20 Research Dallas 7566 7839 20 30 Sales Chicago 7566 7839 20 40 Operations Boston 7698 7839 30 10 Accounting New York 7698 7839 30 20 Research Dallas 7698 7839 30 30 Sales Chicago 7698 7839 30 40 Operations Boston 7782 7839 10 10 Accounting New York 7782 7839 10 20 Research Dallas 7782 7839 10 30 Sales Chicago 7782 7839 10 40 Operations Boston

  22. Restrict – Project – Product – Restrict – Project :Select Empno,Mgr,Deptno,Dname from Emp Natural Join Dept where Sal > 2000 and Sal < 3000; Product – Project - RestrictSelect * from EmpX natural join Dept; Empno Mgr Deptno Deptno Dname Location 7566 7839 20 10 Accounting New York 7566 7839 20 20 Research Dallas 7566 7839 20 30 Sales Chicago 7566 7839 20 40 Operations Boston 7698 7839 30 10 Accounting New York 7698 7839 30 20 Research Dallas 7698 7839 30 30 Sales Chicago 7698 7839 30 40 Operations Boston 7782 7839 10 10 Accounting New York 7782 7839 10 20 Research Dallas 7782 7839 10 30 Sales Chicago 7782 7839 10 40 Operations Boston

  23. Join queries • List the names of all staff in department number 10

  24. SQL Functions vary with RDBMS • STRINGS • LIKE, CONCAT, SUBSTR… • DATE • SYSDATE.. • STATISTICAL FUNCTIONS • COUNT, AVG, MIN, MAX, SUM • GENERATE AGGREGATE VALUES • SELECT SUM(sal) FROM emp; • shows total salary over all emps

  25. Sorting Rows (tuples) • Select ename, sal from emp order by sal; • Grouping Rows • Select deptno, count(*) from emp group by deptno; • Limiting the number of Rows • Select ename, sal from emp order by sal desc limit 1;

  26. RDMS • Relational Database Management System • Maps Relations and values into the Physical Layer • Interprets SQL statements and executes the requested updates on the physical data • Controls access to data, recovery from errors..

  27. MySQL • Free – pay for support • Command line interface or use PHPMyAdmin • Installed for student use on shares – usual Unix login • Personal copy easily be installed • Multiple databases can be created – you get just one • Several different file systems for physical storage (ISAM, INNODB) • ISAM • Three files per table • definition (schema) .FRM • Data .MYD • Index .MYI

  28. What would you expect to see at the physical layer?

  29. Department Table data file Dept no length ASCII String Big or Little Endian? Record header

  30. Learning SQL • Workbook • MySQL, Oracle and MS Access • We will be using MySQL with PHP • http://www.cems.uwe.ac.uk/~cjwallac/sql/mysql • SQL/PHP Textbook • Chapter 10 of Welling and Thomson

More Related