Database Theory

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)
• Remember the example from the last lecture

4
Relational Database - Example

• BRANCH relation
• STAFF relation

5
Set Theory

• Codds 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

6
Mathematical Relation

• Cartesian product
• The cartesian product gives us all possible
  ordered pairs between the two sets
• e.g. ('John White', 30000), ('Ann Beech',
  30000), ('David Ford', 30000), ('John White',
  12000), ...
• Relation - remember Venn diagrams!
• A subset of a cartesian product is a relation,
  e.g.
• ('John White', 30000), ('Ann Beech', 12000),
  ('David Ford', 18000)
• And so we can see that John White earns 30000

7
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 in the STAFF table
• why do you think this is?

8
Keys

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

9
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

10
Example Queries 1

• Retrieve branch numbers and the city for all
  branches
• SQL
• select branchNo, city
• from branch
• Relational Algebra
• ? branchNo, city (BRANCH)

11
Example Queries 2

• Retrieve all information about branch B003
• SQL
• select
• from branch
• where branchNo B003
• Relational Algebra
• ?branchNo, street, city, postcode(?branchNo
  B003(BRANCH))
• The project operation is not strictly necessary
  here, why?

12
Example Queries 3

• Get the branch number and staff names at each
  branch
• SQL
• select branch.branchNo, staff.name
• from branch, staff
• where branch.branchNo staff.branchNo
• Relational Algebra
• ?BRANCH.branchNo, STAFF.name(BRANCH ?
  BRANCH.branchNo STAFF.branchNo STAFF)
• This is the same as the ordered pairs operation
  shown earlier

13
SQL

• What is it?
• Historical Background
• Internal structure
• Commands

14
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

15
Internal Structure

• DIVIDED INTO 2 MAIN ELEMENTS
• Data Definition Language ( DDL )
• Data Manipulation Language ( DML )

16
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

17
Data Definition (DDL)

• USED TO
• define the structure of data CREATE
• which columns are in which tables
• what indexes are maintained
• change existing tables ALTER
• delete existing tables DROP

18
Data Manipulation (DML)

• USED TO
• retrieve/query data SELECT
• insert data INSERT
• delete data DELETE
• update data UPDATE

19
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.

20
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
• Remember the BRANCH, STAFF example 3

21
Views

• A view is a virtual table.
• It can be queried in the same manner as a real
  table.
• Conceptually, can be thought of as a mask
  overlaying one or more tables.
• Uses no physical database space to store data.

22
Views

• USING VIEWS HAS TWO MAIN
• ADVANTAGES
• 1 can be used to conceal complex operations
• 2 can provide additional security, as users only
  need access to the view and not the underlying
  data structures.

23
Views

• CREATING A VIEW
• create view sales as
• select empno, empname
• from emp
• where upper(job) SALES
• This command set will be stored in the Oracle
  data dictionary.

24
Views

• USING A VIEW
• select from sales
• where upper(name) like SMITH

25
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
• Be careful with this command what do you think
  happens if you type
• DELETE FROM emp

26
Protecting Data

• Use GRANT command to set privileges on data
• An Access Control Command
• e.g.
• GRANT SELECT ON emp TO ALL
• GRANT UPDATE ON emp TO personnel

27
Summary

• Relational data model based on set theory and
  included a number of rules to allow consistency
  in the data model and manipulation
• Relational algebra is the basis of relational
  query languages, based on set manipulation
  operations
• SQL is the most successful relational query
  language
• Structured Query Language
• Similarities to relational algebra operators

28
Further Reading

• Relational Model
• Connelly and Begg, chapter 4
• SQL
• Connelly and Begg, chapter 5
• Next week
• Database Application Lifecycle