Transcript Powerpoint slides - Dynamic Connectome Lab

Database Systems
Marcus Kaiser
School of Computing Science
Newcastle University
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

Database Management
Systems: Structure
DBMS
Software
Databas
e
Query Language
Application
Program
Pre-Defined
Applications:
Report Generator
Query by Forms
User
Query
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.
Database Structures
Database Structures

Databases consist of a set of Tables
 A Table holds a set of Fields for each Record
Columns or fields
 e.g. Staff Table
Surname
Initial
Title
Department
Smith
A
Mr
Sales
Smith
K
Ms
Marketing
Brown
M
Mrs
Engineering
Record
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)
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
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
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
The SQL Language
SQL: Basic Select

SELECT retrieves information from a table
 Simplest form:
SELECT columns FROM table
Which columns or fields to display
The name of the table
Select Example
we want to get the names of all members of staff.
SELECT Surname, Initial FROM Staff
Staff
Surname
Initial
Title
Department
Smith
A
Mr
Sales
Smith
K
Ms
Marketing
Brown
M
Mrs
Engineering
Gives:
Surname
Initial
Smith
A
Smith
K
Brown
M
Personnel Example Revisited (1)

Recall

a Companies Personnel Address Database for the employees:
Personnel

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
What SQL command would give you all:
 The telephone numbers?
 The addresses?
Answer (1)

The telephone numbers?

The addresses?
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’
Personnel Example Revisited (2)

Recall

a Companies Personnel Address Database for the employees:
Personnel

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
What SQL command would give you:
 The telephone number for A Smith?
 The address for C.A. Jones?
Answer (2)

The telephone number for A Smith?

The address for C.A. Jones?
Sorting

We can sort the result of the query:
SELECT Initial, Surname
FROM Staff
ORDER BY Surname ASC, Initial ASC
(ASC = ascending; DESC = descending)
Personnel Example Revisited (3)

Recall

a Companies Personnel Address Database for the employees:
Personnel
Nam
e
Init
Street
Town
Post_Code
Tel_Number
73 Dover St
Newcastle
NE2 3SR
01912234554
Jones C.A. 25 Spring Rd Sunderland S12 7DD
01914566547
Smith A

What SQL command would give you:
 Name and Town sorted in reverse alphabetical order
of Town?
Answer (3)

Name and Town sorted in reverse alphabetical order
of Town?
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
Column or field
 WHERE Department = ‘Sales’

returns:
Number
1

count(*) returns the number of records in a table
Personnel Example Revisited (4)

Recall

a Companies Personnel Address Database for the employees:
Personnel
Nam
e
Init
Street
Town
Post_Code
Tel_Number
73 Dover St
Newcastle
NE2 3SR
01912234554
Jones C.A. 25 Spring Rd Sunderland S12 7DD
01914566547
Smith A

What SQL command would give you:
 The number of members of staff who live in
Newcastle?
Answer (4)

The number of members of staff who live in
Newcastle?
Renaming Fields

Sometimes we want to rename a field
 We can use the AS operation
SELECT Surname AS Family_Name
FROM staff

Column or field
returns:
Family_Name
Smith
Smith
Brown
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’
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
Marketing
Engineering
Marketing
Sales
Sales
Marketing
Engineering
Sales
Marketing
Engineering
Marketing
Marketing
Marketing
Sales
Sales
Sales
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
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
Inserting Rows
INSERT INTO Staff (Name,Initial,Dept)
VALUES (‘Green’,’D’,’Eng’)
INSERT INTO Place (Dept,City)
VALUES (‘Manufacturing’,’Bristol’)
Updating Rows
UPDATE Place
SET
City = ‘Coventry’
WHERE
Dept = ‘Sales’
UPDATE Staff
SET
Dept = ‘Systems’
WHERE
Dept = ‘Eng’
Deleting Rows
DELETE
FROM Staff
WHERE Name = ‘Smith’
AND
Initial = ‘C’
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)