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
