1.3.2 Databases definitions/info

Question 1

What are the conditions for first normal form (NF 1)?

Answer 1

• data should be atomic
• there should be a primary key

Question 2

what are the conditions for second normal form (NF 2)?

Answer 2

• must be in NF 1
• No partial key dependencies

Question 3

what is dependency?

Answer 3

a value that varies in line with another value

Question 4

How are partial dependencies resolved?

Answer 4

creating another table with the foreign keys which link the two other tables together

Question 5

what is a foreign key?

Answer 5

a link to the primary key in another table

Question 6

what are the conditions for third normal form (3 NF)?

Answer 6

• must be in 2NF
• no non-key dependencies

Question 7

what are non-key dependencies?

Answer 7

any fields which are not designated a primary key or part of a primary key

Question 8

what is the ‘acid test’ for checking a table is in 3NF?

Answer 8

each attribute is dependent on the key, the whole key, and nothing but the key

Question 9

what is SQL used for?

Answer 9

• 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

Question 10

what is SQL syntax?

Answer 10

• 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

Question 11

what does the DROP keyword do?

Answer 11

• delete a table
• delete an entire database

Question 12

what does a SELECT statement do?

Answer 12

allow you to retrieve data from databases

Question 13

what is the SELECT keyword syntax?

Answer 13

SELECT __Field/s__ FROM __Table__;

Question 14

what SQL command can be used to return all fields from a database?

Answer 14

SELECT * FROM __Table__;

Question 15

what is the SQL command to return fields in ascending order?

Answer 15

SELECT __Fields__ FROM __Table__ ORDER BY __selected field__;

Question 16

what is the SQL command to return fields in descending order?

Answer 16

SELECT __Fields__ FROM __Table__ ORDER BY __selected field__ DESC;

Question 17

what is the SQL command to return records given a condition?

Answer 17

SELECT __Fields__ FROM __Table__ WHERE __condition__;

Question 18

what does the LIKE keyword do?

Answer 18

used with the WHERE keyword to return records in the table are in the same format as the given condition

Question 19

What is a JOIN/INNER JOIN?

Answer 19

a specification as to how multiple tables are joined together

Question 20

what is the syntax for a JOIN?

Answer 20

SELECT __Fields__ FROM __Table__ JOIN __second table__ ON __field that they are connected with__;

Question 21

what is the syntax for the INSERT keyword?

Answer 21

INSERT INTO __table__ VALUES __values__;

Question 22

what is the syntax for the DELETE keyword?

Answer 22

DELETE FROM __table__ WHERE __criteria__;

Question 23

what is a transaction in a database system?

Answer 23

a single logical unit of work

Question 24

what does ACID stand for?

Answer 24

A - atomicity
C - consistency
I - isolation
D - durability

Question 25

what does atomicity mean?

Answer 25

ether the whole transaction takes place or none of the transaction

Question 26

what does consistency mean?

Answer 26

the transaction does not create inconsistencies in the database

Question 27

what does isolation mean?

Answer 27

transactions do not affect other transactions

Question 28

what is durability?

Answer 28

once a transaction is complete, it is permanent and cannot be lost

Question 29

what is record locking?

Answer 29

an affected record is locked until the update is completed

Question 30

what is a field in a database?

Answer 30

used to provide category headings for each item of data in the database

Question 31

what is a record?

Answer 31

a collection of data for a set of fields

Question 32

what is a primary key?

Answer 32

a field that uniquely identifies each record