Transcript Powerpoint slides - Dynamic Connectome Lab

Database Systems
Marcus Kaiser
School of Computing Science
Newcastle University
Security




if a client wishes to access a database it sets up a session
only registered users are allowed to set up sessions
authentication takes place before the user can send queries
the Database Server checks each query to ensure that the user is
allowed to see the data he’s requesting
 e.g. the Head of Personnel connects and issues a query:



what is A.N.Other’s salary
she gets back the answer £20000
e.g. a secretary in Personnel connects and issues a query:


what is A.N.Other’s salary
he is told that he is not allowed to access that information
Security

How to control what users read and write, e.g.  prevent tellers increasing their own bank balance
 restrict who can see salary information
 prevent deletion of information
 Solution: User privileges
 users have usernames that give them privileges
 SQL statements are checked to make sure a user
has the required privileges
User privileges


SQL has 9 types of privilege
The main ones are the ability specify if tables
(base & views) & attributes can be subject to
the following….


Select, Insert, Delete, Update
e.g. Staff(id, name, dept, address, salary)



a user in the mail room may only be allowed to select id,
name, dept,address
a user in HR may only be allowed to select all attributes,
update dept and address, and insert all attributes
head of HR may be allowed to select, insert, delete and
update all attributes
Granting Privileges

Users can grant privileges to other users
GRANT <privilege list>
ON <database element>
TO <user list>

e.g.
GRANT SELECT ON Staff TO npw1,njkw


The database element is usually a table
The privilege list is usually:


select, update, insert, delete
privileges can be qualified with column names
 e.g. GRANT SELECT(name,address) ON Staff TO nadb
Passing on Privileges

Each privilege has an option:
WITH GRANT OPTION
if true then privilege can be passed on to another
user
 For example:
GRANT SELECT ON Staff TO nwp1 WITH
GRANT OPTION



This allows a user to pass on a subset of their own
privileges to another user
The inverse of GRANT is REVOKE
Database Design
Designing a Database

Before creating a Database we need to design it
 Database design cannot be done purely mechanically
but there are techniques which help
 The steps are:
1. Decide on what subjects are covered by the database
2. Decide how they are related to each other
3. Decide on what characteristics they have
4. Derive the database tables from the design
5. Create the database tables using database software
Decide what Subjects are Covered
by the Database
Rules:
 Look at what the subject is about, rather than the
conclusions you want to find
 Think about the subject independently of any particular
database software (or computing at all)
 Don’t design database on computer
 Focus on the data you are likely to collect, rather than
how a computer will organise the data
Decide on what subjects are
covered by the DB
Subjects are called Entities:
 things that hold interest for you in your database
 represent a class of things
 precise definition
 different for every database you create
Example: a Crime database


The police want to track cases
 When a robbery has happened who is involved?
 What was taken?
 Did this go to court?
 What was the verdict?
What are the Entities (subjects) here?
 Police Officer
 Stolen Item
 Case
 Result
Example: a Crime database

e.g. The police want to track cases
 we begin by drawing the Entities:
Decide how the entities are
related to one another



Relationships
 a significant association between the entities
 represented as a diamond
Each relationship has:
 a name
 a degree (more on this later)
Names
 Pick a simple word that encapsulates the relationship
 Concentrate on the main entities first: the rest can
come later when you understand the problem better
Naming relationships
The Degree of Relationship

One-to-One
 One occurrence of an entity is uniquely related to
another entity
 e.g: a crime database: each case has one unique result
The Degree of Relationship

One-to-Many
 an occurrence of one entity can be related many times
to another entity
 e.g: a crime database: many items can be stolen in
one case
The Degree of Relationship

Many-to-Many
 there can be many occurrences of one entity related
to many occurrences of another entity
 e.g: a crime database: many officers can work on a
case, and an officer can work on many cases
Deciding on the Attributes of the
Entities




Attributes are details about the state of an entity
They are things we want to know about an entity
Ensure each has a unique name within the Entity
They are usually drawn as spokes on Entity-Relationship
diagrams:
Attributes: Primary Keys

Make sure that each Entity has an Attribute (or set of
Attributes) which allow it to be uniquely identified

This is called the primary key
(often called ID, use underscore in the ER model)
If there might be some doubt about whether or not a
set of attributes is unique, then add an extra attribute
containing a unique number/code.
e.g. for Officers:
 Name and DOB may be enough: but the id attribute
is guaranteed to be unique


Deriving the Database tables
from the Design






The Entities are Tables
The Attributes are their columns
 Using the database software manual, find out what the possible
column types are and decide on the right one for each attribute
Once this is done, the tables can be created
Next any initial data is entered into the tables
Any standard Queries Forms and Reports are designed and
implemented
Now the database is ready for use
police officer
id station dob
rank
address
name
Representing Relationships
through Foreign Keys


A FOREIGN KEY in one table points to a PRIMARY KEY
in another table.
They are used to represent relationships
Representing 1-1 Relationships

Assume each table has an id column which is its key
 Then we can add a foreign key column to either table
 i.e. Add a CaseId column to Result, or a ResultId
column to Case
Representing 1-many
Relationships

Assume each table has an id column which is its key
 Then we must add a foreign key column to the table at
the many end of the relationship
 i.e. Add a Case Id column to the Stolen Item table
Representing 1-to-Many
Relationships
-- Table `StolenItem`
CREATE TABLE `StolenItem` (
`stolenItemId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`stolenItemName` VARCHAR(45) NOT NULL ,
`caseItemId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`stolenItemId`) );
Representing Many-to-Many
Relationships

To implement in a relational database, these are split up
into two relationships, with a newly created entity
intervening
Entity A
Entity A
Relationship
Many
1
Many
Many
New Entity
Many
1
Entity B
Entity B
Representing Many-to-Many
Relationships

In the Police example we create a new entity which
matches Cases to Officers:
Officer
Officer
Relationship
Many
1
Many
Many
Officer Case
Many
1
Case
Case
Representing Many-to-Many
Relationships
-- Table `Officer`
CREATE TABLE `Officer` (
`officerId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`officerName` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`officerId`) );
-- Table `Case`
CREATE TABLE `Case` (
`caseId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`caseName` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`caseId`) );
-- Table `OfficerCase`
CREATE TABLE `OfficerCase` (
`Case_caseId` INT UNSIGNED NOT NULL ,
`Officer_officerId` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`Case_caseId`, `Officer_officerId`) );
Summary

Security: grant access and pass access to others

Primary and foreign keys: unique identifiers

Degree of a relationship:
1:1
1:Many
or 1:n
Many:Many
or n:m
(create intervening entity)
Quick overview to SQL: http://www.w3schools.com/sql/