1.3.2 Databases Flashcards
Whats a database?
An organised collection of related data
What’s a flat file database?
A database in 0NF, where all data is stored in a single table, about a single entity. It’s useful for static (non-changing) data
What’s an entity
A category about which data needs to be recorded e.g. a person, or thing. (A table in the conceptional phase)
What’s a table
A Two-dimensional representation of data that is stored in rows and columns
What’s a field
A single piece of data about an entity
What’s a record?
A group of fields about a specific entity (AKA a row)
What’s a relational database?
Where data is held in multiple tables that are linked via relationships.
What’s a primary key?
A field used to uniquely identify each record in the table
What’s a feature of a primary key field?
It’s automatically indexed
What’s a composite primary key
When multiple fields are used to uniquely identify each record in the table
What are the three types of relationships between entities?
One-to-one,
One-to-many,
Many-to-many
What’s a foreign key?
A field that creates a relationship between two tables, appearing as the primary key in another table.
What are the benefits of foreign keys?
They maintain the relationship between tables
Why do we use junction tables?
To eliminate many-to-many relationships
What are junction tables?
A junction table contains the primary key columns of the two or more tables you want to relate.
What is data integrity
Maintaining and ensuring the reliability of data
What needs to be considered when reinforcing data integrity in a database
Accuracy, completeness, consistency over lifecycle
What is Entity integrity
Stipulating that every table must have a primary key
What is domain integrity
Ensuring every attribute in a relational database is associated with a domain
What’s a domain
The set of allowed values that an attribute can contain, e.g. numerical
What is referential integrity
It’s a data quality concept that prevents data redundancy and inaccuracies
It a rule that foreign keys in one table can only refer to primary keys in
another table.
What’s data validation?
The process of ensuring the accuracy and quality of data.
What’s data verification?
The process of checking data for inconsistencies after it has been moved
What are the two types of data verification?
Visual check and Double entry
What’s Visual Check data verification?
When a person checks that the data being entered from one document to another is the same
What’s Double Entry data verification?
Requires same info to be input twice into 2 separate fields
The two values are then compared to check they’re the same
What’s data accuracy?
Refers to how valid data is, or the processes used to ensure that data is trustworthy
What’s normalization?
The concept of splitting tables in a database and arranging the data to move it from 1NF - 3NF
At what point is a database considered normalized
When it meets third normal form
What’s 0NF
A flat file prior to normalization
Define 1NF
When…
Each record has a primary key
Data is of a single value type (atomic)
There are no repeating groups of attributes
Values in fields are from the same domain
Define 2NF
No partial dependencies- each field depends on primary key
Data in 1NF already
Define 3NF
Data already in 2NF
No non-key (transitive) dependencies
All fields depend on the key, the whole key and nothing but the key
What are the steps in eliminating many-to-many relationships?
Create a junction table
Assign primary keys to initial tables
Create a composite key for junction table (and provide foreign keys)
Initial tables will both have one to many relationships with junction table
What does SQL stand for?
Structured Query Language
What are the different SQL commands and their uses?
SELECT- List of fields to be displayed
FROM- List the table(s) data will come from
WHERE- List of the search criteria (operators used here)
ORDER BY- List the fields that data is to be sorted in (e.g. price ASC)
- / (Wild Card) = ‘All’
LIKE- Used to search for a pattern
BETWEEN- Used to search in an inclusive range
IN- Used to search in a specific column/ field
Why is a join clause used in SQL?
To combine data from two or more tables into one data set, by identifying the primary-to-foreign key relationship.
Basic syntax for inserting new records into tables?
INSERT INTO “table” (field1, field2,…)
VALUES (value1,value2,…);
Basic syntax for deleting data from a table?
DELETE FROM table
WHERE criteria;
if criteria involves primary key- whole record deleted
Datatypes?
CHAR
VARCHAR
BOOLEAN
INT
FLOAT- decimals
DATE- dd/mm/yy (enlosed in hashtags)
TIME
CURRENCY
Define a transaction
A single logical unit of work. A transaction can be a single operation but most transactions have multiple steps.
Transaction feature?
All of the required steps need to be successfully executed before the transaction can be considered complete. Either all of the steps must happen, or none of them must happen.
Ideal carrying out of transactions?
Transactions would be carried out serially (one after another)
Serialization?
Multiple transactions are executed one after another.
A transaction can only be serialized if it can be carried out irrespective of any other transaction.
Concurrent processing?
The simultaneous execution of multiple tasks or transactions.
Improves performance and efficiency of multi-user databases.
What methods enable concurrent processing to be usable?
Record locking, Timestamp Ordering
What is record locking?
A method of preventing simultaneous access to the same record by different users of the database.
The DBMS locks the affected record until the update is completed
- an update can be part of a larger transaction
Several records may need to be locked to ensure system integrity- causing potential deadlocks
What can be used to fix deadlocks?
Commitment ordering
Commitment ordering?
A serialization technique
Ensures NO transactions are lost if two clients are trying to update a record at the same time
Transactions are ordered according to when they were initiated and by their dependencies on one another
Can prevent a deadlock by blocking one request until another is completed
Timestamp ordering?
Each record has two time stamps: Time last read, Time last updated.
Each transaction is assigned a unique timestamp when it starts
Newer transactions have higher timestamps
The database system executes transactions in the order of their timestamps
If a transaction tries to read or write a data item with a lower/ older timestamp than the existing one, the operation is rejected.
SQL Commands for transaction processing?
SET TRANSACTION- Initiates transaction
COMMIT- Once all steps are written down it makes changes permanent
ROLLBACK- Reverts interim changes to the point before the transaction was started
ACID stands for?
Atomicity
Consistency
Isolation
Durability
Atomicity?
Transaction must be process entirely or not at all
Consistency?
Transaction execution must maintain referential integrity rules
Isolation?
Simultaneous transactions results remain unaffected; as if they were executed sequentially
Durability?
Ensure data is saved once a transaction is completed- irrespective of hardware (multiple copies in different physical locations)
What is ACID?
The set of properties of database transactions that will guarantee the integrity of data
If a database system is good, it’s ACID compliant
Issues caused by multi-user databases?
Race conditions
Deadlocks
Data corruption
Loss of Isolation
How to solve issues caused by multi-user databases?
Locking mechanisms
Different isolation levels
Atomic transactions
What is electronic data interchange?
The computer-to-computer exchange of documents such as purchase orders, between two companies/ entities.
Replaces post or emails.
All docs must be in standard form so that a computer can understand them.
