File (table)
A collection of related records which contain the same type of information as all the other records
Record
All of the data relating to one item or “thing”.
Field
Stores one item of data in a record
Flat file databases
Usually only has one table (it may have more than one table, but with no links between them)
Advantages of Flat file databases
Easy to set up and understand – no specialist knowledge needed
Disadvantages of Flat file databases
May have redundant data
Potential duplication of data
Harder to update the data due to duplication
Records are not unique
Security is poor as if access is gained all data is readily available
Harder to perform complex queries
Relational databases
This type of database has more than one table which are linked together by primary and foreign keys.
Primary key
Used to uniquely identify a record
Foreign key
A foreign key is a primary key in one table which is sued as an attribute in another table to provide a link between table.
A foreign key in one table is always linked to a key field in another table.
Compound key
A combination of fields that makes a record unique (but breaks 2 Normal Form)
Secondary key
Made on a field that you would like to be indexed for faster searches
They may not be unique
They are used for sorting or searching data
Advantages of relational data bases
Data is only stored once – this avoids data duplication and means that data is only stored once.
Data integrity is maintained
Easy to modify the format
Security is better as sections of the data can be protected individually using authorisation
More “future proof” as it is easier to update and expand the basic structure than a flat file.
More complex queries can be run over the data
Disadvantages of relational databases
They are difficult to set up
Data integrity
The correctness of the data over the lifetime of that data.
This means that the data is correct when it enters the database, as it is queried and updated up until the point it is removed from the system.
Data must be complete, accurate and consistent.
Entity integrity
Every table must have a primary key which is unique and not empty
Referential integrity
Foreign keys must point to the primary key of another table but can be empty (meaning that there is no relationship)
Domain integrity
All attributes in the database are related to the overall domain that the database is working on.
Data redundancy
This refers to the unnecessary duplication of data.
Data consistency
When data is held in more than one file it should be stored in a consistent way.
A date field could be stored as a text field in one table but as a date/time field in another, meaning the data would be incompatible.
In a relational database because the attributes of any one entity are contained within one table, there is no risk of it being stored in a different format in a different file
Data independence
This refers to the separation of the data from the programs that use it.
This means that users can view data without having to be aware of the basic structure of the database, and allows them to manipulate it in many different ways.
User can also not make changes to the underlying structure of the database.
Flat file vs Relational data bases
Flat file
May have redundant data, which increases the overall size of the database and reduces data integrity
Harder to update the database design
No specialist knowledge needed to set one up
Relational Databases
Data integrity is maintained
Easier to update the database design
Provides security features e.g. individual tables can be protected
Need specialist knowledge to set up and maintain
ERD
Entity relationship diagram
Attributes
Describes the facts, details or characteristics of an entity.
These are the fields within a table
Relationships
The links between entities.
They can be one to one, one to many, or many to many.
One to one relationship
One thing to one thing
e.g A Husband can only have one wife and a wife can only have one husband
One to Many relationship
One thing to many things
e.g A mother can have many children but a child can only have one mother.
Many to many relationships
Many things to many things
e.g A book can be loaned by many customers in a library and a customer can loan many books
How can a many to many relationship be changed to a one to many relationship?
This is usually achieved by adding another entity which acts as a link table and:
Creates one to many relationships
Makes the relationship in to 3NF
Reduces data inconsistencies
What is the standard notation for describing entities and attributes?
The entity is always written in capital letters
The primary key is underlined
Foreign keys are overlined
Normalisation
The process of refining the structure of a database to minimise redundancy and improve integrity
When a database has been normalised, it is said to be in normal form
Helps organise the data efficiency, eliminate redundant data, ensure that only related data are stored in a table
UNF
Un-normalised Form
Data that has not been normalised
It potentially has:
repeated data
inconsistent data
1NF - First Normal Form
A table is in this form if it contains no repeating attributes or groups of attributes, data is atomic, AND has a primary key
Atomic data
Information which cannot be logically broken down in to smaller parts.
E.g. Full name is not atomic as it can be split in to first name and surname
First name is atomic as it cannot be split any further without losing detail.
2NF - Second Normal Form
In 1NF AND it contains no partial key dependencies
If a table has a single attribute for a primary key it is usually in 2NF as all of the fields depend on that key.
It is when there is a compound primary key (a key made up of 2 or more fields) that something needs to be changed.
3NF - Third Normal Form
A table is in this form if data items are dependent on the key, the whole key and nothing but the key
Many-many relationships are not allowed in this form as it causes duplication of data.
Partial key dependencies
Occurs when one primary key determines some other attribute/attributes
Serial Access
The records are stored chronologically
New records are appended onto the end of the file
There is no way to find a particular record except by searching through all of the preceding records
When is Serial file organisation used?
The order of the data doesn’t really matter
The total number of records is small
Data MUST be stored in the order it is received
Sequential Access
Records are stored one after another
Ordered in a logical sequence based on a key field (usually primary key)
In order to find a record, the whole file may still need to be searched (like in serial), however, it tends to be faster and more efficient than serial
Name two types of random access methods
Indexed file
Hashed file
How do you delete records in a serial file?
Deleting records in a serial file is complex
The file is scanned until the record is found
Everything is copied except for the file you want to delete, the copy then replaces the old list
How do you add/delete a record in a sequential file?
All records are in order of the key field so you cannot just add a new record to the end of the file
The file is copied up to the point of insertion, the data is inserted and then the remainder of the file is copied
The file is copied up to the point of the data to be deleted, this record is skipped and then the remainder of the file is copied
Indexed Sequential File Organisation
Records are sorted according to a primary key but a separate index is also kept.
The index is an additional file that contains information about where records are stored and allows them to be accessed directly.
This could mean that groups of related records are physically stored in groups
How do you add/delete records in an indexed sequential file?
Every time a record is added or deleted, it can affect the start and end point of the indexed “chunks” and so all indexes from that point onwards need to be updated.
The index needs to be re-created after each addition or deletion, which is performed as per standard sequential files.
Random (direct) Access
A hash algorithm (a mathematical algorithm) is used on key fields to generate an address for the file - this calculates a disk address/location of a record based on its key field.
The algorithm MUST produce the same address every time a key is used so that the data doesn’t get lost!
Sometimes several records are linked to the same location, which means you have to search sequentially through those records
Collision
When too simple a hashing algorithm is used, several records could hash to the same address.
RDBMS
Relational Database Management System
Used to maintain data integrity
Uses SQL which allows you to crate, delete, and alter the database
Has a way of documenting all the internal structures that make up the database
The ability to view the database from different viewpoints according to the requirements of the user
Provide some level of security and access control to the data
Data dictionary
Used by database managers
Is a file containing descriptions of the data stored in tables in a database
Provides metadata (information about data and how it is stored)
Describes the structure and attributes of data 'items' and includes the names and descriptions of tables and the fields contained in each table, as well as information about the data type, field length etc.
Used when altering the structure of the database
Field valuation
Presence checks, lookup, range checks, picture check which are applied to a field
Indexes
Any field that has been indexed to improve search speed
Access rights
Which users can change / edit / modify etc. data
Data Manipulation Language (DML)
Provides a means of manipulating data within the database
Includes operations such as CRUD (Create, Read, Update, Delete)
Many systems allow the user to set a task by using ‘Query by Example’
Used to:
access/query/sort/search data
store/insert data
change/update/delete data
Query by Example
Users are presented with a graphical view of the tables and use a number of icons such as 'filter' to manipulate the data.
With some systems you can drag fields graphically into appropriate areas on the screen to set up the query.
Behind the scenes, the QBE tool is compiling and running the required DML commands.
This means that users do not need to have a sophisticated understanding of DMLs to use the database.
Data Definition Language (DDL)
Helps to provide a method of creating a database from scratch
Used to write schema (designs) which describes data items to be stored and the relationships between them.
Allows tables to be created and primary/foreign keys to be defined.
Also defines validation rules
Name three views of data
Physical view
Logical view
User view
Physical view
This view exposes all the internal details of how the data is stored on the physical computer
For example the data structures used in memory, the nature of the indexes used for searching, what physical devices are used to store the database and so on.
At this level the operating system is interacting with the database
Logical view
It shows:
a model of all the information within the database
The kind of records it holds
The type of authorisation and validation methods used.
This is the kind of view the database designer is using to set out the whole database
The data dictionary is produced at this level.
External view
This is the view that sets out what an authorised user can see of the database.
It sets out the data they are allowed to see and their authority level to change the records.
Opening a file
myFile = openRead(“sample.txt”)
x = myFile.readLine()
myFile.close()
Opening a file and printing its contents
myFile = openRead(“sample.txt”)
while NOT myFile.endOfFile()
print(myFile.readLine())
endwhile
myFile.close()
Writing to a file
myFile = openWrite(“sample.txt”)
myFile.writeLine(“Hello World”)
myFile.close()
Indexing
A method used to store the position of each record ordered by a certain attribute.
This is used to look up and access data quickly.
The primary key is automatically indexed; however, the primary key is almost never queried since it is not normally remembered.
This is why secondary keys are used. Secondary keys are indexed to make the table easier and faster to search through on those particular attributes.
Name three ways of capturing data
Optical Mark Recognition (OMR)
Optical Character Recognition (OCR)
Manually entering it
What is exchanging data?
The process of transferring the collected data to a database
Selecting and Managing data
Selecting the correct data is an important part of data preprocessing
This could involve only selecting data that fits a certain criteria to reduce the volume of input.
Collected data can alternatively be managed using SQL to sort, restructure and select certain sections.
Things to know about SQL commands
SQL commands are in capital letters
Multiple fields are separated by commas
Alter and drop are to do with structure
Update and delete from are to do with data
How do you add in a new field?
ALTER TABLE tablename ADD columnname datatype
How do you remove an existing field?
ALTER TABLE tablename DROP COLUMN columnname
How do you create a table within a database? (e.g Clubs)
CREATE TABLE Clubs ( ClubID INT NOT NULL, ClubName VARCHAR(50) NOT NULL, PRIMARY KEY (ClubID))
How do you insert data into a database?
INSERT INTO databasename.tablename (field1, field2) VALUES ('1', 'The Pullman Club');
How do you change a record?
UPDATE databasename.tablename Fieldname = ‘data’ WHERE tablename.primarykeyname = “primarykey”
How do you extract a sub-set of the table records?
SELECT * FROM tablename WHERE primarykey>1
How do you return a single record?
SELECT * FROM tablename WHERE primarykey=2
How do you include more than one table in the search?
SELECT fieldname(s) FROM table1
JOIN table2
ON table1.primarykey = table2.foreignkey
How do you delete records?
DELETE FROM tablename WHERE primarykey = 1
Methods of exchanging data
Data can be transferred:
Automatically using the DBMS software
By typing it in using a customised form
Importing it from another type of file
Using EDI (Electronic Data Interchange) this is used to transfer data between one computer system and another
APIs (application programming interfaces) uses web services to communicate using the HTTP protocol
SOAP a standardised protocl that sends messages using HTTP and SMTP
JSON (JavaScript Object Notation)
XML (Extensible Markup Language) used to separate data from its presentation on web pages
No matter which method is used, validation should occur to ensure that the data is sensible along with verification to ensure that the data entered matches the original
Transaction Processing
A single logical operation on data (e.g a customer booking a cinema ticket online, even though it involves multiple actions)
The database must ensure that it is not possible to only complete part of a transaction.
e.g. if seats have been successfully reserved, but payment fails, the seats should not stay reserved or change status to booked.
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that transactions are processed reliably.
Atomicity
Ensures that a transaction must be processed in its entirety or not at all
It must guarantee that in any situation, including power cuts or hard disk failure, it is not possible to only process part of a transaction.
Transactions should either succeed or fail but never partially succeed.
Consistency
The transaction should only change the database according to the rules of the database in order to maintain its integrity
Changes to data in one table must take into account data in linked tables
This means that it will not be possible to create or delete a record if related records exist in linked tables e.g.
an employee cannot be deleted if they have salary details in another table
you can’t create a salary for an employee that doesn’t exist
Isolation
This property ensures that concurrent execution of transactions leads to the same results as if the transactions were processed one after the other.
i.e. Each transaction should not overwrite other transactions that are being processed at the same time
This is particularly relevant in a multi-user database where each transaction must not affect/overwrite other transactions being processed at the same time
Durability
Ensures that once a transaction has been committed, it will remain so, even in the result of a power cut.
e.g. in an online cinema ticket purchase, it should not be possible for the number of seats sold to be updated if the credit card transaction fails
As each part of the transaction is completed, it is held in a buffer on disk until all elements of the transaction have been completed.
Only then will the changes to the database be made.
Multi-user Database
These need to allow for concurrent processing
If multiple users need to read the same data, it generally isn't a problem, but allowing multiple users to simultaneously update or write to a database table may cause one of the updates to be lost unless measures are taken to prevent it.
When an item is updated, the entire record will be copied into the user’s own local memory (on their local device).
When the record is saved, the data in memory is then rewritten in its entirety.
This can lead to unexpected results
Record locking
In its simplest form, the record is “locked” when the first user obtains the data and cannot be accessed by any other user until it is “unlocked” (saved).
However, this can cause problems…
If two users try to access a single record at the same time a situation can arise where neither can proceed, known as deadlock.
The DBMS (Database Management System) must recognise if this happens and must deal with the deadlock.
Serialisation
When two or more transactions are executed concurrently, the effect should be the same as if they had been executed serially i.e. one after the other.
It ensures that transactions do not overlap in time and therefore cannot interfere with each other or lead to updates being lost.
A transaction cannot start until the previous one has finished and this can be implemented using:
Timestamp ordering
Commitment ordering
Timestamp Ordering
Whenever a transaction starts, it is given a timestamp, so that it two transactions affect the same object, the transaction with the earliest timestamp should be applied first.
Each transaction is given a read timestamp and a write timestamp.
When a transaction starts, it reads a record of data, causing it to be read timestamped.
When the updated data is written back, the read timestamp is checked.
If it is not the same in the database as the current record in memory steps are taken to solve the problem
Commitment Ordering
Transactions are ordered in terms of their dependencies on one another as well as the time they were initiated.
This involves evaluating the impact of the transactions before they are executed i.e. which data will be updated and how that will potentially affect other transactions
It can be used to prevent deadlock by blocking one request until another is completed.
Transactions are not completed in time order, but in commitment order
Redundancy
This could be a RAID(Redundant Array of Inexpensive Disks) setup or mirroring servers.
In its simplest(!) form, these organisations maintain two or three identical systems in different geographical locations, so that every transaction is written to two or three storage facilities.
This hardware redundancy protects against the loss of data in the event of power failure or other disasters.
If one system fails, one of the backup systems automatically takes over and processing continues.
If part of a database is lost, it can be recovered from one of the other versions.