Databases Flashcards
Entity Relationship Database:
Allows to visual how data will be organised
One to one relationship:
A record in one table relates to only one record in another.
One to many relationship:
A record in one table relates to multiple records in another table
Many to many relationship:
Not desirable, means a table isn’t fully normalised, not to 3NF
Primary Key:
Entity identifiers which are used to uniquely identify each entity in a table. (represented with a line)
Composite Primary Keys:
Multiple fields combined to form a composite primary key
Foreign Keys:
When an attribute appears in multiple tables and it is the primary key in one table, it is a foreign key in the other.
Normalisation:
To reduce redundancies (duplicated data), ensure data is stored logically, reduce the amount of storage space used.
First Normal Form:
Data must be atomic (a cell only has one value), each record is unique
Second Normal Form:
Must already be in 1NF, must only be a single column primary key (no composite primary key)
Third Normal Form:
Must already be in 2NF, no transitive functional dependencies
Atomic Data:
Each cell only holds one value.
Single Column Primary Key:
No composite primary key.
Functional Dependencies:
An attribute uniquely determines another attribute.
Transitive Functional Dependencies:
Functional dependencies which don’t rely on the primary key.
What does SQL stand for:
Structured Query Language
What does DDL stand for:
Data Definition Language
DDL uses:
Create and delete tables and databases
SQL uses:
Select, insert, update, and delete
Example of SELECT:
SELECT *
FROM table
WHERE Attribute = ‘example’
ORDER BY Attribute 2 Asc;
Example of INSERT:
INSERT INTO Table (Attribute1, Attribute2)
VALUES (‘Example’, 123);
Example of UPDATE:
UPDATE table
SET Attribute = ‘example’
WHERE Attribute2 = ‘example2’;
Example of DELETE:1
DELETE FROM table
WHERE Attribute = ‘example’;
Example of DDL create database:
CREATE TABLE TableName( Column1 datatype Column2 datatype Column3 datatype );
SQL datatypes:
TEXT, INTEGER, NUMERIC, REAL, BLOB
Example of DDL drop:
DROP TABLE TableName;
Constraints:
Rules for data e.g. unique, or not null.
Client Server Database:
Allows for simultaneous access to a database
Client Server Database Advantages:
Improved data sharing
Minimised data inconsistency
Improved data security
Client Server Database Disadvantages:
Concurrent Access
Concurrent Access:
Two people accessing/editing the same data at the same time
Solving concurrent access:
Record Locks
Timestamp/ordering
Commitment
Serialisation = ordering or commitment
Record Locks:
Locks a record when someone is using it
Serialisation:
Each query is scheduled to happen one after the previous query.
Timestamp ordering:
Each query is assigned a timestamp, so if two querys are on the same record, the earliest happens first.
Commitment Ordering:
If a record is dependant on another, then this query will happen first.
