1.3.2 Databases Flashcards
Database and Parts
Database : Persistent Organised store of related data
Table - Collection of records
Record - All fields/data relating to one entity
Field - Attribute/data relating to entity under field name
Field name - Identifier for single piece of data
Flat file database
Flat file database : Database consisting of a single file / table.
- Many to many entity relationship
Disadvantages :
- Leads to a-lot of data redundancy
- Leads to data inconsistency when data updated
- Harder to update and search
Advantages :
- Easier to create (no specialist knowledge needed) so easier to use for inexperienced user
- Easier to import into another system
Relational Data Base
Relational Data Base : Database with Multiple tables that link together each relating to single entity.
- One to many entity relationship
Advantages :
- decreased data redundancy (less repeated data)
- Improved data consistency
- easier to update and remove data
Key Fields
Primary Key : Field used to uniquely identify each record of a table. (no repeats or blank values)
Secondary Key : Fields that can be indexed to speed up searching and sorting data. (repeats and blank values allowed)
Foreign Key : field that refers to a Primary key from another table used to link data together between tables. (repeats and blank values allowed)
Composite Key : Where multiple fields used together as primary key.
Referential Integrity
Referential Integrity : You cannot delete any data being linked to by other tables
- Each foreign key must link to existing valid primary key.
- Ensures Database relationships between tables are consistent
- If record is removed so are all references
- Enforced by DBMS
Data Integrity
Data Integrity : Data should be protected by stopping incomplete or inconsistent transactions causing data corruption.
- Involves Referential Integrity.
- Process that ensures the accuracy, completeness, consistency, and validity of an organization’s data
SQL Commands
Delete record :
DELETE FROM table name WHERE condition
Insert a record :
INSERT INTO table name (field1, field2, …) VALUES (values, value2, …)
Delete table from a database :
DROP TABLE table name
Combine data from two or more tables :
SELECT table name. field , table name. field FROM table name JOIN other table name ON table name. field=other table name.field WHERE condition
Update data within fields :
UPDATE table name SET field name = value, … = … WHERE field name = condition
ORDER BY field ASC (at end to return in alphabetical order)
LIKE (“A%”) (Wildcard after where that returns only records starting with “A”)
Normalisation
Normalisation : Process used to produce the best possible design for a relational database.
- Process of removing redundancy - makes accessing and maintaining data more efficient and easier.
- To maintain Integrity and avoid duplicates or data errors.
Normalisation Stages
Unnormalized form :
- Data in any format or configuration (Flat file database)
1st Normal Form :
- Primary Key Identified
- Data in attributes must be atomic (Can’t be split up further e.g. Name –> FN and SN)
- No repeated attributes / fields (multiple items in an attribute)
- Unique attribute names (field names)
2nd Normal Form :
- Be in 1st normal Form
- Every field must be dependant on whole primary key (No partial dependencies)
3rd Normal Form :
- Be in 2nd normal form
- No transitive dependencies (non-key dependencies)
(one to many relationship at this point)
Indexing Database
Indexing : Method to store position of each record ordered by certain attribute to look up and access data quickly.
- Primary key automatically indexed but not usually remembered.
- Secondary Key indexed so table faster and easier to search and query through on particular attributes - searching and sorting.
- However Index takes up extra space in database
Capturing Data Techniques
Techniques to input data into databases :
- Forms (e.g. for surveys from pedestrians or web forms)
- Optical Mark Recognition (e.g. MCQ on test)
- Optical Character Recognition (e.g. Reading Reg plates)
- Magnetic ink character recognition (e.g. Cheques)
- Barcode Reader (e.g. Shopping items)
- Sensors (e.g. Light sensor)
- Data Mining (Raw data turned into valuable information e.g. Marketing: Customer segmentation, market basket analysis, and targeted advertising)
Selecting and Managing Data
Query By Example : Search or sort or join data in database by providing example of desired output.
- Easy to use and learn + user friendly but less powerful + flexible than SQL
SQL - Structured Query Language - for databases to manage data.
Exchanging Data
Methods to Exchange data :
- Electronic Data Interchange - no human interaction, steam-lines to increase efficiency.
- CSV - Comma separated values - simple and widely supported.
- JSON - Lightweight and common in web applications.
Transaction processing and ACID
Transaction Processing : A Transaction is a single operation performed on data in a database, Whole process consists of multiple transactions
ADID : Ensures Integrity of database is maintained by ensuring all transactions are processed correctly and reliably to prevent inconsistencies.
ACID Components
Atomicity : Ensures a transaction (single indivisible unit of work) is processed entirely or not at all - to prevent partially completed transactions that lead to data corruption.
- Rolls back transaction if fails
Consistency : Ensures that no transaction can make changes that break any rules of the database e.g. Referential integrity.
- Ensures data remains in consistent state
- Validates and verifies data entered.
Isolation : Ensures that each transaction doesn’t effect/overwrite other transactions concurrently being processed and updates to data are not affected.
- so data remains consistent.
- Crucial in multi-user database
- Through Record Locking
Durability : One a transaction has been processed it remains so no matter what and results are stored permanently, even if power or system failure.
- Can be achieved by storing on secondary storage (Redundancy) so no data lost once transaction complete
