1 / 22

Database Theory

Database Theory. Objectives. Understanding of relational data model Basic concepts of set theory Learn how to use SQL query language. Relational Data Model. Devised by Codd in 1970, conforming to a simple set of rules Data stored as two dimensional tables ( relations )

shiloh
Download Presentation

Database Theory

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. Database Theory

  2. Objectives • Understanding of relational data model • Basic concepts of set theory • Learn how to use SQL query language

  3. Relational Data Model • Devised by Codd in 1970, conforming to a simple set of rules • Data stored as two dimensional tables (relations) • Every relation must have a unique name • Each column (attribute) has a unique name within a table • Each value of an attribute is from the same domain • Order of rows (tuples) is irrelevant • Order of columns (attributes) is irrelevant • Each value should be single-valued (atomicity)

  4. Relational Database - Example • BRANCH relation • STAFF relation

  5. Keys • Primary Key: • all tables have an attribute or set of attributes which uniquely identifies each separate row in the table • e.g. branchNo in BRANCH table • staffNo in STAFF table • Note that we could not use the staffName attribute as the primary key • why do you think this is?

  6. Keys • Foreign Key: • a set of attributes in a table which match the primary key of some other table • e.g. branchNo in STAFF relation • Allows us to join tables together to relate information in tables.

  7. Relational Data Manipulation • Codd’s definition of the relational model was based on a sound theoretical basis • Set theory • Allows us to specify relations between sets, e.g. • StaffNames = {‘John White’, ‘Ann Beech’, ‘David Ford’} • StaffSalaries = {30000, 12000, 18000}

  8. Relational Data Manipulation • It is fine to be able to store data in a relational database • But we also need to be able to retrieve from it too • The use of set theory provides a basis for all relational query languages such as SQL • Relational algebra contains operations for • RESTRICT - return rows satisfying condition • PROJECT - return specified columns • JOIN - join of two tables on primary key/foreign key

  9. Example Queries 1 • SQL • select branchNo, city from branch; • Relational Algebra This is use of PROJECT

  10. Example Queries 2 • SQL: • select * from branch where branchNo = ‘B003’; • Relational Algebra: • Use of RESTRICT

  11. Example Queries 3 • SQL: • select branchNo,staff_forename, staff_surname from branch, staff where branch.branchNo = staff.branchNo; • Relational Algebra: • Use of JOIN

  12. SQL • What is it? • Historical Background • Internal structure • Commands

  13. Historical Background • Originally called SEQUEL • developed by IBM c1976 • Now called SQL • the ISO standard query language for relational databases • DEVELOPED TO: • Reduce programmers workload • Make programs and data functionally independent • Make data structures more flexible • Allow end-user access to data

  14. Internal Structure • DIVIDED INTO 2 MAIN ELEMENTS • Data Definition Language ( DDL ) • Data Manipulation Language ( DML )

  15. SQL • SYNTAX • Commands may be on one or many lines • Command words cannot be split • SQL commands are not case sensitive • Only one statement can be current within the SQL buffer • SQL statements always end in a semi-colon;

  16. Data Definition (DDL) • USED TO: • define the structure of data (CREATE) • which columns are in which tables • what indexes are maintained • changeexisting rows ( ALTER ) • delete existing rows/tables ( DELETE) (DROP) • grant/revoke access to ORACLE database & its structures

  17. Data Manipulation (DML) • USED TO: • retrieve/query data SELECT • insert data INSERT • delete data DELETE • protect data GRANT

  18. Data Manipulation (DML) • RETRIEVAL • THE SELECT COMMAND • The purpose of this command is to read data from the database and present it in user specified form. • By far the most complex of all SQL commands, with the ability to perform a wide range of tasks.

  19. Table Joins • Used when an SQL query requires data from more than one table in the database • Rows in one table can be joined to rows in another according to common values. • These common values are the primary key of one table and the foreign key of the other table

  20. Adding/Removing Rows • Use the INSERT or DELETE command • Examples: INSERT INTO emp VALUES (1234, ‘Barney Rubble’, ‘Manager’, 30000); DELETE FROM emp WHERE job = ‘Administrator’;

  21. Protecting Data • Use GRANT command to set privileges on data • e.g. GRANT SELECT ON emp TO ALL; GRANT UPDATE ON emp TO personnel;

  22. Further Reading • Relational Model • Connolly & Begg, 3rd and 4th edition, chapter 3 • SQL • Connolly & Begg, 3rd and 4th edition, chapter 5 • Next week: • Database Application Lifecycle

More Related