Database Systems

1
Database Systems

• Marcus Kaiser
• School of Computing Science
• Newcastle University

2
Requirements

• Remove Redundancy or at least control it
• Data Integrity
• Separation of data and program
• Prevent inconsistency when systems fail
• Allow multiple (simultaneous) interactions
• High level of security
• Remote access
• Access by humans and computers

3
Database Management Systems Structure
4
Database Management Systems - Features

• Data Centralization
• all data can be stored in one database
• all users see the same, consistent set of data
• eliminates data redundancy
• increases data integrity
• clients can connect to the database irrespective
  of its location.

5
Database Structures
6
Database Structures

• Databases consist of a set of Tables
• A Table holds a set of Fields for each Record
• e.g. Staff Table

Columns or fields
Record
7
Creating Tables

• Data Definition Language
• Used to create tables
• Allows you to define
• Table Name
• Fields
• Name
• Format (e.g. 20 characters of Text Integer)

8
Populating Tables

• Once the Tables have been defined they can be
  filled
• by bulk loading from text files
• by bulk loading from other databases
• by queries

9
Data Independence

• changes to the data can be made without changing
  the programs which access it
• 2 Views of data
• Physical View
• actual location of the computer holding the data
• actual location of the data on a storage device
• the actual format of the data
• Logical View
• view of the records and fields of data as they
  are seen by the user
• presented via the query language
• independent of the Physical View
• e.g. users can ask for all workers with the
  surname Smith

10
Querying the Database

• Queries are used to
• retrieve data
• update fields
• delete records
• insert records
• The Standard Query Language is called SQL
• Structured Query Language
• Almost all Databases support SQL
• However there are other approaches

11
The SQL Language
12
SQL Basic Select

• SELECT retrieves information from a table
• Simplest form
• SELECT columns FROM table

The name of the table
Which columns or fields to display
13
Select Example
we want to get the names of all members of
staff. SELECT Surname, Initial FROM Staff
Staff
Gives
14
Personnel Example Revisited (1)

• Recall
• a Companies Personnel Address Database for the
  employees
• Personnel
• What SQL command would give you all
• The telephone numbers?
• The addresses?

Name Init Street Town Post_Code Tel_Number
Smith A 73 Dover St Newcastle NE2 3SR 01912234554
Jones C.A. 25 Spring Rd Sunderland S12 7DD 01914566547
15
Answer (1)

• The telephone numbers?
• The addresses?

16
Where Clauses

• SELECT columns FROM table
• can only retrieve whole columns
• a WHERE clause can be used to select only the
  rows (records) of those columns which meet a
  particular qualifier
• e.g. we want to get the names of all staff
  working in Sales
• SELECT Surname, Initial
• FROM Staff
• WHERE Department Sales
• the qualifier can contain OR or AND
• SELECT Surname, Initial
• FROM Staff
• WHERE Department Sales
• AND Surname Smith

17
Personnel Example Revisited (2)

• Recall
• a Companies Personnel Address Database for the
  employees
• Personnel
• What SQL command would give you
• The telephone number for A Smith?
• The address for C.A. Jones?

Name Init Street Town Post_Code Tel_Number
Smith A 73 Dover St Newcastle NE2 3SR 01912234554
Jones C.A. 25 Spring Rd Sunderland S12 7DD 01914566547
18
Answer (2)

• The telephone number for A Smith?
• The address for C.A. Jones?

19
Sorting

• We can sort the result of the query
• SELECT Initial, Surname
• FROM Staff
• ORDER BY Surname ASC, Initial ASC
• (ASC ascending DESC descending)

20
Personnel Example Revisited (3)

• Recall
• a Companies Personnel Address Database for the
  employees
• Personnel
• What SQL command would give you
• Name and Town sorted in reverse alphabetical
  order of Town?

Name Init Street Town Post_Code Tel_Number
Smith A 73 Dover St Newcastle NE2 3SR 01912234554
Jones C.A. 25 Spring Rd Sunderland S12 7DD 01914566547
21
Answer (3)

• Name and Town sorted in reverse alphabetical
  order of Town?

22
Set Functions

• We can also perform operations on sets of data
• count (column) counts the number of entries in a
  column
• SELECT count(Surname)
• FROM staff
• WHERE Department Sales
• returns
• count() returns the number of records in a table

Column or field
23
Personnel Example Revisited (4)

• Recall
• a Companies Personnel Address Database for the
  employees
• Personnel
• What SQL command would give you
• The number of members of staff who live in
  Newcastle?

Name Init Street Town Post_Code Tel_Number
Smith A 73 Dover St Newcastle NE2 3SR 01912234554
Jones C.A. 25 Spring Rd Sunderland S12 7DD 01914566547
24
Answer (4)

• The number of members of staff who live in
  Newcastle?

25
Renaming Fields

• Sometimes we want to rename a field
• We can use the AS operation
• SELECT Surname AS Family_Name
• FROM staff
• returns

Column or field
26
Multi-Table Retrieval

• We can query more than one table
• e.g. Where does Every Member of Staff Work ?
• SELECT Staff.Initial,Staff.Surname,Place.City
• FROM Staff,Place
• WHERE Staff.Dept Place.Dept
• e.g. Who Works at Hull ?
• SELECT Staff.Initial,Staff.Surname
• FROM Staff,Place
• WHERE Staff.Dept Place.Dept
• AND Place.City Hull

27
Why Place.Dept Staff.Dept?

• To distinguish between Fields in different tables
  with the same name we prepend the Field with the
  name of the Table
• Place.Dept
• Staff.Dept
• But why Place.Dept Staff.Dept?
• When we join the two tables together each record
  from Place is matched with each record from Staff
• So we have every member of Staff Matched with
  every Place
• But we only want the ones that represent real
  matches

Sales
Sales
Marketing
Marketing
Marketing
Marketing
Marketing
Sales
Engineering
Marketing
Engineering
Sales
Sales
Marketing
Sales
Engineering
Sales
28
Multi-Table Retrieval

• What does this do ? What is the Result of the
  Query ?
• SELECT Place.City
• FROM Staff,Place
• WHERE Staff.Surname Smith
• AND Staff.Initial C
• AND Staff.Dept Place.Dept
• Write a Query to Find out the Initial and
  Department of Jones, who works in Leeds
• Write a Query to Find out how many people work in
  Leeds

29
Answer (5)

• What does this do ? What is the Result of the
  Query ?
• SELECT Place.City
• FROM Staff,Place
• WHERE Staff.Surname Smith AND Staff.Initial
  C
• AND Staff.Dept Place.Dept
• Write a Query to Find out the Initial and
  Department of Jones, who works in Leeds
• people work in Leeds

30
Inserting Rows

• INSERT INTO Staff (Name,Initial,Dept)
• VALUES (Green,D,Eng)
• INSERT INTO Place (Dept,City)
• VALUES (Manufacturing,Bristol)

31
Updating Rows

• UPDATE Place
• SET City Coventry
• WHERE Dept Sales
• UPDATE Staff
• SET Dept Systems
• WHERE Dept Eng

32
Deleting Rows

• DELETE
• FROM Staff
• WHERE Name Smith
• AND Initial C

33
Summary

• Database Management Systems
• Centralize data
• Provide remote access
• Data is stored in tables
• With fields for each piece of data
• A set of fields makes a record
• Data has physical and logical views
• Access data through SQL (Structured Query
  Language)