assorted database things and normalisation

what is a secondary key?

• databases should be indexed by things that are commonly searched for

• it is sometimes not enough to index by primary key because people may not search using their customerID etc

• therefore a secondary index should be used so that people can search by a more popular field, eg their surname (so surname is the secondary key)

what is a composite primary key?

when the primary key consists of more than one attribute

what is referential integrity?

• when tables are linked in a relational database, if a record is deleted, all records in other tables referencing that record are also deleted

• eg if a restaurant shuts down, all the reviews in the review table about that restaurant can be deleted

what is normalisation and why is it good for you?

• comign up with the best possible design for a relational database

• it ensures no unnecessary duplication/redundancy (saves storage space)

• ensures consistency (no anomalies when inserting, amending, deleting data)

• structure of table is flexible and allows for fast, complex queries involving different tables

• table is easier to maintain and modify, maintains data integrity

• faster searching and sorting as smaller tables with fewer fields

• records on the “one” side of a one to many relationship cannot be accidentally deleted

what is 1st normal form?

• the table contains no repeating attributes or groups of attributes (data is atomic)

• unrelated but when two entities have a many to many relationship, a link table is needed in the middle

what is 2nd normal form?

• table must be in 1nf

• table contains no partial dependencies

• a partial dependency is when the primary key is composite and some of the attributes depend on only part of the primary key

what is 3rd normal form?

all attributes must be dependent on the key, the whole key, and nothing but the key

no non-key dependencies basically