Week 3 Notes

Question 1

Redundancy causes three kinds of anomolies

Answer 1

update, insertion, and deletion

Question 2

Redundancy (redundant storage) stores the same data repeatedly

Answer 2

problem: requires more storage and more labor enter the data

Question 3

update anomaly

Answer 3

if you change one relational variable in one area, you must change the redundant data everywhere it is stored or data will be inconsistent

Question 4

deletion anomaly

Answer 4

you lose the last copy of some data when you delete other data

Question 5

insertion anomaly

Answer 5

you cannot insert some data without inserting other data that may not be applicable

Question 6

normalization of a relational database

Answer 6

process of developing tables, columns, and the dependencies of columns upon one another so they satisfy the normalization rules

Question 7

each rule builds upon previous ones

Answer 7

a database is in the third normal form if it satisfies the first three rules

Question 8

goals of normalization (similar to advantages of a relational database)

Answer 8

minimize redundant data, reducing inconsistent data, avoiding insert/ delete/ and update anomalies, and reducing maintenance

Question 9

requirements for a table to be relational

Answer 9

• each table has a unique name for an owner in the database
• each column has a unique name, data type, and a domain
• each row in the table has a unique combo of values (data integrity)/ or primary key with a unique combo of values
• order of rows and columns is arbitrary
• the value at each row/column is atomic
• NO repeating columns of similar data

Question 10

dependencies that cause redundancies

Answer 10

• NO repeating columns of similar data = first normal form (1NF)
• NO partial dependencies = (2NF)
• NO transitive dependencies = 3NF
• NO non-key attributes determine the part of the key = boyce/ codd normal form (BCNF)
• NO non-trivial multi-valued dependencies = 4NF
• NO join dependencies = 5NF
• NO remaining dependencies = domain/ key normal form (DKNF)

Question 11

candidate key

Answer 11

minimal set of attributes which can uniquely identify a tuple
- value unique and not null for every tuple
- there can be more than one candidate key in a relation

Question 12

super key

Answer 12

set of attributes which can uniquely identify a tuple
- adding zero or more attributes to candidate key generates super key
- candidate is super key but vice versa not true

Question 13

alternate key

Answer 13

candidate key other than the primary key

Question 14

surrogate keys

Answer 14

artificial key which uniquely identifies each record
- generated right before a record is inserted into a table

Question 15

Normalization rule 1

Answer 15

no repeating groups with similar info appear in a table
- procedure: identify the repeating group, remove the repeating group, and move the repeating group into a new table (w copy of column that identifies that group)

Question 16

normalization rule 2

Answer 16

all non-primary-key columns depend on all parts of the primary key

Question 17

normalization rule 3

Answer 17

all non-primary columns depend only on the primary key

Question 18

boyce-codd normal form (BCNF)

Answer 18

table can be in 3NF, but not BCNF
- requirements: 3NF form & for any dependencies, A -> B, A should be a super key

Question 19

4NF

Answer 19

BCNF form and does not have multi-valued dependency

Question 20

higher levels of normalization vs query performance

Answer 20

common to violate normalization rules to improve performance or convenience
- typically design is in 3NF

Question 21

denormalization from 3NF to 2NF only if

Answer 21

• performance with realistic test data is unsatisfactory
• performance cannot be made satisfactory with hardware improvements
• performances cannot be made satisfactory by software improvements
• database designer and builder understand tradeoff (improved performance v losses)

Question 22

data dictionary

Answer 22

table within a database that lists metadata for all the tables within the database (also may include constraints)
- can also be used as planning tool by database engineer

Question 23

system catalog

Answer 23

data dictionary may be derived from SC.
- detailed system-created database whose tables store database characteristics
can describe all objects in the database
sometimes, SC is the DD

Question 24

retrieving or selecting data affects neither raw or metadata stored but

Answer 24

may change format or values displayed (if data is manipulated)