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.
