1.3.2 Databases definitions/info Flashcards
Normalisation, SQL, ACID, transactional processing, concepts (a) (c) (d) (e) (f)
What are the conditions for first normal form (NF 1)?
- data should be atomic
- there should be a primary key
what are the conditions for second normal form (NF 2)?
- must be in NF 1
- No partial key dependencies
what is dependency?
a value that varies in line with another value
How are partial dependencies resolved?
creating another table with the foreign keys which link the two other tables together
what is a foreign key?
a link to the primary key in another table
what are the conditions for third normal form (3 NF)?
- must be in 2NF
- no non-key dependencies
what are non-key dependencies?
any fields which are not designated a primary key or part of a primary key
what is the ‘acid test’ for checking a table is in 3NF?
each attribute is dependent on the key, the whole key, and nothing but the key
what is SQL used for?
- searching and retrieving records
- inserting new records into databases
- deleting records and individual items of data from tables
- updating existing data and replacing it with new data
what is SQL syntax?
- SQL statements are made up of key words
- convention to write key words in caps
- each SQL statement is terminated with a semicolon
- convention to write longer statements over multiple lines
what does the DROP keyword do?
- delete a table
- delete an entire database
what does a SELECT statement do?
allow you to retrieve data from databases
what is the SELECT keyword syntax?
SELECT __Field/s__ FROM __Table__;
what SQL command can be used to return all fields from a database?
SELECT * FROM __Table__;
what is the SQL command to return fields in ascending order?
SELECT __Fields__ FROM __Table__ ORDER BY __selected field__;
what is the SQL command to return fields in descending order?
SELECT __Fields__ FROM __Table__ ORDER BY __selected field__ DESC;
what is the SQL command to return records given a condition?
SELECT __Fields__ FROM __Table__ WHERE __condition__;
what does the LIKE keyword do?
used with the WHERE keyword to return records in the table are in the same format as the given condition
What is a JOIN/INNER JOIN?
a specification as to how multiple tables are joined together
what is the syntax for a JOIN?
SELECT __Fields__ FROM __Table__ JOIN __second table__ ON __field that they are connected with__;
what is the syntax for the INSERT keyword?
INSERT INTO __table__ VALUES __values__;
what is the syntax for the DELETE keyword?
DELETE FROM __table__ WHERE __criteria__;
what is a transaction in a database system?
a single logical unit of work
what does ACID stand for?
A - atomicity
C - consistency
I - isolation
D - durability
what does atomicity mean?
ether the whole transaction takes place or none of the transaction
what does consistency mean?
the transaction does not create inconsistencies in the database
what does isolation mean?
transactions do not affect other transactions
what is durability?
once a transaction is complete, it is permanent and cannot be lost
what is record locking?
an affected record is locked until the update is completed
what is a field in a database?
used to provide category headings for each item of data in the database
what is a record?
a collection of data for a set of fields
what is a primary key?
a field that uniquely identifies each record
