SQL tables

Question 1

What are table anomalies?

Answer 1

anomalies cause problems to the database
causes by poor planning or unnormalized database
-can be avoided with normalization

Question 2

what are Update/modification anomalies?

Answer 2

inconsistencies that result from data redundancy or partial update
-incorrect data may be changes

Question 3

what are Insertion anomalies?

Answer 3

• Inability to add data to a database due to missing data
• issues can happen when adding info to a database
• avoided by entering correct details- consistent with row values

Question 4

what are deletion anomalies?

Answer 4

-unintended loss of data from deleting other data
least likely anomoly to notice or to stop you from proceeding
most costly in terms of recovery

Question 5

What is normalization?

Answer 5

normalization removed redundant data
-improved storage and efficiency, data integrity and scalability
normalization splits existing tables, into multiple tables which rejoin each time a query is issued

Question 6

What are the five steps of normalization

Answer 6

First normal form
second normal form
third normal form
boyce-codd normal form
forth normal form

Question 7

First normal form must have these 4 qualifications

Answer 7

unique attribute name
unique row
single attribute value
no repeating file groups

Question 8

Second normal form must have these 2 qualifications

Answer 8

• Table is in 1NF

- all non-key attributes all fully depend on primary ke

Question 9

Third normal form must have these 2 qualifications

Answer 9

• Table is in 2NF
• non primary key attributes do not depend on other non primary key attributes
• transive dependencies are removed and placed in another table

Question 10

What are transive dependencies?

Answer 10

Non key, dependent on non key

Question 11

What is a multi values dependency?

Answer 11

when two or more independent multi valued facts about the same attribute occur in the same table

Question 12

What is a functional dependency

Answer 12

When one keys value is fully dependent on another key