4.10 Fundamentals of Databases Flashcards
Primary Keys (Definition)
A primary key is the unique identifier for each row.
Simple Primary Keys
Contain only one field to make a unique value
Composite Primary Keys
Contains more than one field to make a unique value
Foreign Keys
A field in one table that is linked to the primary key in another table
Entity
Table
Record
Row
Attribute
Field
Entity Definition
TableName (Primary Key, field1, field2)
- underline primary key
Relationships: One to One
x -|——|- y
Relationships: One to Many
x -|——-<- y
Relationships: Many to Many
x -|——<- xy ->——| y
middle box contains foreign keys of other boxes to make primary key
Relationship Diagrams are called …
Entity-Relationship Diagrams (ERDs)
Normalisation Rules help …
(3)
- organise data efficiently
- eliminate redundant data
- ensure only related data stored in table
First Normal Form (4)
- no columns with repeated or similar data
- each data item cannot be broken down any further (atomic)
- each row is unique (it has a primary key)
- each field has a unique name
Second Normal Form (2)
- must already be in 1NF
- non-key attributes must depend on every part of the primary key
Third Normal Form (2)
- must already be in 2NF
- there are no non-key attributes that depend on another non-key attribute
Benefits of Normalisation (5)
- no redundant data = smaller database size = less £ on storage
- less search data = faster query
- no duplication = better integrity, less mistake risk
- no duplication = less chance of storing 2+ different copies of data
- instant change cascade
Problems of Normalisation (5)
- be careful with splitting data (atomic)
- more tables
- more tables/more complex = slower queries
- more tables = must assign more relationships
- more tables = more complex queries
Insert: every field
INSERT INTO table VALUES (value1, value2);
Insert: selected fields
INSERT INTO table (field1, field2) VALUES (value1);
Update
UPDATE table SET fieldtochange = newfieldvalue WHERE condition;
Delete
DELETE FROM table WHERE condition;
Select
SELECT *
FROM table
WHERE condition;
Select - Order By
ORDER BY Asc/Dsc
Select - 2 Tables
SELECT *
FROM table1, table2
WHERE table1.PrimaryKey = table2.ForeignKey AND condition;
DDL - Database Definition Language
CREATE TABLE tableName (
Id integer NOT NULL PRIMARY KEY,
field1 varchar(50),
field2 date/time);
Different Data Types (6)
- VARCHAR (x)
- INT/INTEGER
- FLOAT
- REAL
- BOOL
- DATE/TIME