Please enable JavaScript.
Coggle requires JavaScript to display documents.
Database Management Term 2 (7. Database Security (Views Restricting the…
Database Management Term 2
Data Validation and Referential Integrity
FORMATTING
Formatting is the term used for controlling the appearance of your data
speed up data entry as it can prompt the user to follow the correct format
CHECK CONSTRAINTS
validation rules for an attribute to ensure that only appropriate values are accepted
DEFAULT VALUE
Automatically entered - user must overtype if different.
RECORD LEVEL VALIDATION
Check how the value of an attribute compares with another value in the record
E.g. The date of an Order must be before its date of Delivery.
Properties:
(Name) CK_tblOrder_OrderDate_Before_DeliveryDate
Check Constraint Expression [OrderDate] < [DeliveryDate]
FIELD LEVEL VALIDATION
[OrderDate] DATE DEFAULT (getdate()) NOT NULL
PRIMARY KEY CONSTRAINTS (UNIQUENESS)
The database will ensure that no row is added to a table if its primary key is the same as an existing one
For composite keys it is possible for one of the columns to contain duplicate values but the combination of values must be unique.
FK REFERENCIAL INTEGRITY CONSTRAINTS
Enforced using foreign keys, which link tables together, and enforce the relationships between the entities.
The DBMS will check, a FK value entered exists as an associated primary key in the corresponding table
UPDATE
the value of PK, the DBMS will check there is a value PK otherwise it will reject it
DELETING
a record from the hall table. The DBMS will reject the delete if that hall no is in use
If we are sure we wish the related records to be deleted we can set it up automatically to delete all the records with that PK – known as a cascade delete.
(ON DELETE CASCADE)
ADD
a new row - the DBMS will check there is a record for the FK entered otherwise it will reject it
INTEGRITY CONSTRAINTS
NOT NULL
– there must be a value for this attribute
CHECK CONSTRAINT
- uses a simple logic statement to work out whether the value of an attribute is acceptable.
2. Introduction to Normalisation
NORMALISATION
This is a process to enable us to design a database that works well
GOOD DESIGN
No row order significance
- resolved by making each row complete
No column order significance
- resolved by naming each column
No repeating groups
(a field only contains one value)
No redundant data
REDUNDANCY
Is data that we
don't need
, we can
work it out
from the data we have
Functional Determinancy
Normalisation and E-R modelling are different approaches to designing well structured relational tables with no redundant data
Worked Examples of Normalisation
PROCESS OF NORMALISATION
1st Normal Form (remove repeating groups)
2nd Normal Form (attributes must depend on whole key)
3rd Normal Form. (and nothing but the key)
UN-NORMALISED FORM (UNF)
List all attributes from source document(s)
Give all attributes their full name
Create extra attributes where necessary e.g. when there are several phone numbers phone1, phone2 etc.
Add any obvious missing attributes
Show repeating groups in brackets
Find a unique identifier if there is one
Add a unique identifier if necessary
Name table - in this case Order.
Example:
Order (OrderNo, CustomerNumber, CustomerName, CustomerAddress, CustomerTel, DeliveryDate, DateofOrder, (SandwichNumber, SandwichPrice, Quantity, SandDescription, TotalCostfItem)TotalCostofOrder).
FIRST NORMAL FORM
Identify each repeating group
Remove the attributes of the repeating group from the existing table. Put them in a new table. Post the PK as a FK into the old table
Place the attributes in brackets:
(SandwichNumber, SandwichPrice, Quantity,SandwichDescription, TotalCostofItem)
As it is a new table, give it a name (SandwichOrder)
Add the unique identifier from the original table to the new table (as a foreign key - OrderNo)
Decide on a unique identifier:
A composite key: foreign key and another attribute from the table
An entirely new attribute
Examples of tables in 1NF:
SandwichOrder (SandwichNumber, OrderNo, SandwichPrice, Quantity, SandDescription, TotalCostofItem)
Order (OrderNo, CustomerNumber, CustomerName, CustomerAddress, CustomerTel, DeliveryDate, DateofOrder, TotalCostofOrder).
SECOND NORMAL FORM
All attributes in the table must be determined by the whole unique identifier
Look at each table with a composite unique identifier
Check that each attribute can only be determined from the whole unique identifier
In other words is the value of the attribute associated with just one part of the composite identifier? If so, create a new table.
e.g. is SandwichPrice determined by the whole key or part of it?
Do I need to know the OrderNo and the SandwichNumber to know the price of a sandwich?
Quantity - OrderNo and SandwichNumber
SandDescription - SandwichNumber Only
TotalCostOfItem - OrderNo and SandwichNumber
SandwichPrice - SandwichNumber only
7. Database Security
Protects the system from:
Hackers
Unhappy employees
Fraud
Mistakes
Disasters
System crashes
Policies & Procedures
Prevent problems
Procedures for when problems occur
log user access and transactions
security compromise - longer to gain access, transactions are slowed
Physical Security Steps
Keeping equipment under
lock and key
CCTV/alarms
Restricted access
(e.g. use of swipe cards to gain access for authorised personnel)
Biometric
devices
No lone worker policy
Policies which
prevent the removal or installation
of hardware and software
Network Security
Steps taken to isolate the database from the Internet:
Firewalls
VPN
(Virtual private network)
Strong
encryption
of passwords only
On wireless networks use
MAC
(Media Access Control) addresses. Every network device has a unique MAC address. This allows you to specify the addresses of devices which are valid on your network.
System level security
Install as little of the DBMS
Use
minimal OS
facilities
Install
patches
ASAP
Change all
default passwords
.
Database client security
A client is any computer system/workstation
Username/Password
Logins
Data encryption
switch off JavaScript
and Cookies in web browsers
virus scanners
No unauthorised software
Test for security breaches
Database access security
Prevent unauthorised access to the database
(access rights for user, administrator, developer)
granting
privileges
for each table e.g. can delete but not access
Views
Restricting the columns or rows of a database that a user can see
A view is the result of a (table or table join) query
Hiding columns and/or rows - If someone does not know the data is there they will not be tempted to access it* Hiding complex database operations from the user
improve the performance (speed) of a query that the user carries out on the view
Logical data independence - users may not be affected by changes
CREATE VIEW Emp_1
AS
SELECT employeeNumber, employeeName, skill, departmentName
FROM employee;
Design of a basic menu control security system
This can be done by restricting access to the menu forms using a security profile based on binary strings.Then each user is allocated a binary string of ones and zeros which is loaded into memory when they log in. One indicates permission granted to see this menu screen zero indicates permission denied.
8. More database security
User Error
Keyboard error - dealt with by:
validation and verification
application design
Drop down lists of acceptable values
Format checking
Errors in process:
training, manuals, online help
System Failure
power failure
hardware failure
software failure
Recovering from Failure
backups and recovery procedures
Transactions
Transaction log
DBMS keeps log of transaction started and changes it has made to the database data. This is known as the transaction log
Defined as a
unit of work
, -
do all or none of it
The important point about a
transaction
is that the user sees it as
one operation
Commit
When the transaction is completed a command called COMMIT is issued which makes all the changes permanent, and records the transaction as complete in the log.
Rollback
A system failure - DBMS looks through the transaction log and identifies any transactions started but do not have a COMMIT. These transactions are then undone using the transaction log to put the database back to where it was before the failure - known as ROLLBACK.
Disaster Types
Contingency Planning
or Disaster Recovery
Plan to allow an organisation to recover if they completely lose their system
Flood, Terrorist activity, Fire, Theft, Any natural disaster
Disaster recovery plan
Locates alternative hardware (local or another Co)
Remotely held
- Up to date
soft-copy back-ups
of all data/software/system docs and manuals
List of all equipment, alternative accommodation for all users, Prioritise business functions
Additionally plan needs to be
tested
(regularly) Key personnel contactable. Everyone must know what they are expected to do.
Locking
During the process of updating a data field in a database, the data field is 'locked' locking out access to other users. This is so that if another user access the data before the data has been updated they do not receive an incorrect/un-updated value
e.g. correct stock level
Database locking
Table locking
Block or Page locking
An operating system puts blocks or pages of data into memory.
The part of the data in the database, accessed by user, is not read into memory at the same time will not be available to other users.
Record or row locking
Column or attribute level locking
Deadlock
This is when two users cannot complete their transactions (and release the lock) because they are each waiting for the other to release the lock on another record
Assuming column or attribute level locking
[Time] [---------------------------Database User A-----------------------------] [----------------------------Database User B------------------------------]
[--T1--] [---------select /update quantity in stock (lock attribute)---------] [-----------------------------------------------------------------------------------]
[--T2--] [--------------------------------WAIT----------------------------------------] [------------------Select price of item(attribute locked)-----------------]
[--T3--] [Select /update price of item (must wait due to user B’s lock)] [-----------------------------------------------------------------------------------]
[--T4--] [----------------------------WAIT-------------------------------------------] [Select/update quantity in stock (must wait due to user A’s lock)]
[--T5--] [----------------------------WAIT-------------------------------------------] [----------------------------------WAIT------------------------------------------]
In this situation both users could wait forever for the other to release the lock, so DBMS looks for deadlock situations and usually
terminates one
of the transactions involved and rolls back the transaction
9. Database Models &
Database System Testing
Flat Files
Ordinary operating system files. Just an electronic
extension of paper files
no information
to communicate the
file structure
or any
relationship between files
No physical data independence
– this means that any change to the file requires all programs that use the file to be re-worked
Data redundancy
. Same data stored in many places
No data integrity
– frequently have slight variations in data e.g. name changed for student in one record but not another
Would need to read whole file to find required record so therefore much
slower
than a databases which use indexes to quickly locate data items.
Indexed files
Index was created which had
pointers to the records
Quicker to read the index than whole file
Other problems of redundancy and lack of
integrity
still remained
Database Model
DB requires:
Data
Hardware (physical data storage)
Software (manages HW)
to store data
standardised method for retrieving/manipulating data
Users
Hierarchical Model
Records are connected using
‘pointers’
Each pointer establishes a
‘parent-child relationship’ (1:M relationship).
No M:M relationships
There is less redundant data but some may still remain e.g. where a customer may have more than 1 bank account
Searching for data is more efficient. Quicker to search a
tree structure
than a list
The Network Model
Allows child tables to have
more than one parent
, but this leads to complexity
Improvement on hierarchical: e.g. include
Total and IDMS
In some ways similar to the hierarchical model in that
pointers are used to link files
Relational Model
Flexible, no pre-defined pathways
Ideal for ‘ad hoc’ queries
Instead of pointers, foreign keys are used to identify logical links between records
Relates records as they are needed, using their data alone. Instead of pointers, foreign keys are used to identify logical links between records
Examples include
ORACLE, MySQL and SQL Server.
NoSQL Model
MongoDB (NoSQL) Model
JavaScript Object Notation (JSON): a flexible shared structure on web.
A document database of JSON objects organised into collections.
Analytical v Operational Databases
Analytical Database
Also known as
On Line Analytical Processing (OLAP)
Static, read-only
. You can view the data but not change it
Historical data for analysis
Used to analyse marketing strategies
On the Web used for inventory catalogues e.g. Amazon. Web pages generated dynamically from customer queries
Operational Databases
On Line Transaction Processing (OLTP)
Allow you to do more than view data. You can change the data
Used to track real-time information
Testing Database Systems
Test Plan
Test Phases
User test
finally test the system with a number of
real users
and do some final
‘tweaking’
of the design.
Beta test
test the system as a
whole
including menus, user defined input and computer reports
Systems test
make sure the system ‘runs hot’ in a
realistic environment
together with the client’s computers and network configuration.
Component testing
test
each database object
you create (table, form etc.) is fit for purpose, stores/displays the required information