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 );
