Database Design & Development Flashcards
What is referential integrity
Every non-null foreign key value must match an existing primary key value
Why is referential integrity important?
Helps to ensure that the database contains valid and usable records. Without it, the relationships between tables could quickly become meaningless and the queries on the data would return unreliable results.
What is entity integrity?
Ensures that there are no duplicate records within the table and that the primary key within the table is unique and never null.
What is a primary key?
A constraint that uniquely identifies each record in a table
Cannot contain NULL values
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns
What is a foreign key?
A constraint that is used to prevent actions which would destroy links between tables A field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table
How do you resolve many to many relationships?
By introducing a junction table into your model. It will resolve the many-to-many relationship into multiple one-to-many relationships
What is a ternary relationship?
When three entities participate in the relationship
What is the first normal form?
A relation that contains no multivalued attributes
What is normalization?
Data analysis technique to design a database system into tables and columns by applying a series of refinements to groups of data items to produce normal forms
What is the second normal form?
A relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on the primary key.
What is the third normal form?
A relation that is in second normal form, and in which no non-primary-key attribute is transitively dependent on the primary key
What are the steps of normalization?
Select the data source and convert it into an unnormalised table (UNF)
Transform the unnormalised data into the first normal form (1NF)
Transform data in first normal form (1NF) into second normal form (2NF)
Transform data in second normal form (2NF) into third normal form (3NF)
Transform third normal form to Boyce-Codd normal form (BCNF)*
Transform Boyce-Codd normal form to fourth normal form (4NF)*
Transform fourth normal form to fifth normal form (5NF)*
What are the 3 types of anomalies?
Insertion anomalies
Deletion anomalies
Modification anomalies
Why are ACID transactions a good thing to have?
ensure the highest possible data reliability and integrity
ensure that your data never falls into an inconsistent state because of an operation that only partially completes
Benefits of normalization
allows the database designer to understand the current data structures within an organisation
aids any future changes and enhancements to the system
