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