Databases Flashcards
Relational Database
A database which recognises the difference between entities by creating different tables for each entity
Entity
An item of interest of which information is stored
Attributes
Link tables together and are the charcteristics of the entity
Flat File
A database structure which has a single file and its attributes.
Primary Key
A unique identifier for each record in the table
Foreign Key
A attribute which links two tables together
foreign key will exist in one table as the primary key and act as the foreign key in another
Secondary Key
Allows databases to be searched quickly
{Order and Search}
(Index)
ERM (Entity Relationship Modelling)
Relationships between tables are shown in abstracted view
Relationships
defined by how many records they associate with in each table.
One-to-One
One-to-Many
Many-to-Many
Normalisation
The process of coming up with the best possible layout for a relational database
Normalisation Rules:
- no redundancy (unnecessary duplicates)
- Consistent data throughout linked tables.
- Records can be added and removed without issues.
- Complex queries (requests) can be carried out.
First normal Form
There must be no attribute that has more than 1 value
Second normal Form
A database which doesn’t have any partial dependencies and is in First Normal Form.
This means that no attributes can depend on part of a composite key (multiple keys in one used to identify rows).
Third normal Form
Database is in Second Normal form and contains no non-key dependencies it is in Third Normal Form.
Non-key dependency - the attribute only depends on the value of the primary key and nothing else.
Indexing
method used to store the position of each record ordered by a certain attribute.
Used to look up and access data quickly.
The primary key is automatically indexed
(but primary key is almost never queried as its forgotten. so secondary keys are used. Secondary keys are indexed to make the table easier and faster to search through on those particular attributes.)
Capturing Data
data needs to be inputted. there’s lots of ways of doing this and the way you choose to input data depends on the context.
surveys - data is manually entered
checks scanned using - Magnetic Ink Character Recognition (MICR).
multiple choice questions - Optical Mark Recognition (OMR)
Selecting and Managing Data
selecting correct data is important part of data pre-processing
only selecting data that fits a certain criteria using SQL to sort and manage the data
Exchanging Data
is the process of transferring collected data
EDI (Electronic Data Interchange) transfers data without human interaction from node to node
Referential Integrity
process of ensuring consistency.
ensures data isnt removed if it is required elsewhere in a linked table
Transaction Processing
a single operation executed on data.
ACID
Atomicity
Consistency
Isolation
Durability
Atomicity
A transaction must be processed in its entirety or not at all
Consistency
transaction must maintain the referential integrity rules between linked tables
Isolation
Simultaneous execution of transactions must lead to the same result as if they were executed one after the other.
Durability
Once a transaction has been executed it will remain so
even if the power if cut off
Record Locking
process of preventing simultaneous access of records in a database used to prevent inconsistencies or a loss of updates. can cause deadlocks
DEADLOCK - two or more transactions are waiting for one another to give up locks
(if one person is accessing the data it locks the record so no one else can access the same record )
Redundancy
process of having one or more copies of the data in physically different locations
so if its lost it can be recovered
Composite Key
a key that consists of two or more attributes
