normalisation Flashcards
what are the alternative names for a table ?
entity and relation
what are the alternative names for a record ?
row entity instance
what is the alternative name for a field
attribute, column, and property
what is a relational datebase
the databases consist of many tables that contain s data that is related in some way.
these databases are called relational databases and are managed by the Database Management Systems (DBMS).
what is a primary key
a field in a table that uniquely identifies each record in the table
what is an easy way to make a primiary key
add an extra auto-numbered field to make each record unique
what is a super key
a comnination of fields in a table which uniquely identify each row
what is a candidate key
they are minimal super-keys and they are possible options for primary key s
what is normalisation
the process of representing a problem in the real word as a set of tables
what does normalisation avoid
repeating groups data redundancy anomalies : update insertion deletion complex queries
what is data redundancy ?
expanding the primary key produces data redundancy is when one field has two values like a name and a surname in the name field
what is an update anomaly
when the same data needs to be updated in more than one place
what is deletion anomaly
when deletion cause unecessary loss of data
what is an insertion anomaly
when records are added that do not satisfy the primary key requirements
what are the characteristics of first normal form
no repeating groups
choose a primay key
what are the characteristics of second normal form
relation is in 1NF
no partial dependencies
what are the characteristics of third normal form
relation is in 2NF and no transitive dependencies
what is a partial Dependency
when a field’s walue is dependent on only part of composite key
what is transitive dependency
when a field is dependent on a non- key field
what is derived data
when you use field to calculate antoher like deriving a person’s age from their date of birth
what is duplicate data
when a record has the same value for a field as another record
what is atomic data
each value of a field should store a single datqa item and should no be combined
what is referential Integrity
the idea that you can have data that refers to other data that does not exist
what is an example one to one relationship
an example would be one teacher to a register class
what is an example of a one to many relationship
would be a house to students there is one house and many students
what is an example of many to many
that would be doctor to patient
how to identify different tables
each table shold respresent an entity or a subject then each table should have a primary key. Then link the table using the primary and foreig keysthe rule is that the table on the many side gets the primary key of the one side of the relationship
give a summary of the relationship rules
one to one: put both fields in the same table
one to many: add a foriegh key to the table on the many side which the primary key of the table on the one side
many to many: Create a new link table with a combination primary key using the primary key of the other two tables
how do you ensure regerential Integrity?
we need to ensure queries cannot be performed that will violate the integrity of the database
