Databases Flashcards
what is a database
An organised collection of data enabling efficient:
- Adding
-Modification
-Deleting
-Searching of data
Primary key
Field that holds unique value for each record in a database.
Foreign key
A field that links to tables together.
Secondary key
A unique identifier which is indexed to allow fast searching
Composite primary key
2 or more attributes uniquely identifying a record.
Flat file database
Contains one table. Easy to set up and maintain, but can be inefficient and have repeated data
Referential integrity
Ensures all foreign keys represent a valid and existing primary key in parent table
SQL to extract field
SELECT …
FROM …
WHERE…
ORDER BY….
ASC/DESC
WILDCARD (*) = ALL FIELDS
SQL to combine rows from different tables
JOIN table2
ON table1.attribute = table2.attribute
SQL to create a new database
CREATE TABLE table1
(
Attribute 1 [Datatype,
Null/not null, Primary key]
…
)
Data types for creating tables
CHAR(n)
VARCHAR(n)
BOOLEAN
INTEGER
FLOAT
DATE
TIME
CURRENCY
Adding a column
ALTER TABLE table1
ADD attribute and data types
Deleting a column
ALTER TABLE table1
DROP Attribute
Modifying a column
ALTER TABLE table1
MODIFY COLUMN Attribute NEW DATATYPE
Insert a new record
INSERT INTO COLUMN 1, COLUMN 2,…
VALUES(Value 1, Value2…)
Updating a record
UPDATE table1
SET column1 = value1,….
WHERE columnX = value
Deleting a record
DELETE FROM table1
WHERE columnX = value
Normalisation
Coming up with the best possible layout for a relational database.
Aims of Normalisation
No redundancy
Consistent data
Records added/removed without issue
Complex queries can be carried out
1st Normal Form
No attributes that contain more than 1 value. Fixed by making more records but causes redundancy
2nd Normal Form
Must be in 1st Normal Form
Must be no partial dependencies (no attributes must depend on part of a composite key.)
Fix partial dependency by creating another table with field which has the partial dependency and field it is dependent on.
3rd Normal Form
Must be in 2nd Normal Form
Contains no non key dependencies (All attributes must depend on only the primary key)
Fixed by splitting table until each attribute in a table is only dependent on primary key
Transaction
A single logical operation performed on data (Create, Read, Update, Delete)
ACID
A set of rules that ensure database validation rules are met.
Atomicity, Durability, Isolation, Consistency
Atomicity
A transaction must be completed entirely or not at all.
Consistency
A transaction must maintain referential integrity rules
Isolation
Simultaneous execution of transactions should produce same result as if done one after another
Durability
Once a transaction has been executed it will remain so regardless of circumstances like power cuts.
Record Locking and issue
Is a method to counter loss of updates due to simultaneous access to records.
A record is locked when a user is editing it until the changes are saved.
Issue: If two users need to update the same two records simultaneously, they will end up in a situation where they will wait for each other to stop updating a record, which means records dont become available and users wait endlessly. DEADLOCK.
Serialisation
Ensures transactions do not overlap in time so they cannot interfere with each other. One transaction cannot start unless the previous is complete
Timestamp ordering
Transactions are given timestamps whenever it starts. If multiple transactions affect the same object the transaction with earliest timestamp is applied first.
Each object has a read/write timestamp to ensure transactions are not lost.
Commitment ordering
Transactions ordered in terms of their dependencies on each other as well as time of initiation.
Types of data capture
Magnetic Ink Character Recognition [used in banks scanning cheques]
Optical Mark Recognition [ used in MCQ’s for a test]
Optical character Recognition [used in forms]
