More commonly used when an abstract model of a database is being considered
Entity identifier
More commonly used when an abstract model of a database is being considered
The terms given here will be used for both implementations of and abstract models of databases
Table
Stores data about things that exist
Record
Stores data about an individual thing that exists
Field
A category of information
Primary Key
A field that is a unique identification for each record
Relational databases
Databases where relationships exist between records within tables
Foreign Key
A field in one table that links to a primary key in another table using a relationship
One to one relationships
One record from one table relates to only one record from another table
One to many relationships
One record from one table relates to many records from another table
Many to many relationships cannot be implemented within a database
Redundant data can be a problem in flat file databases
Inconsistent data can be a problem in flat file databases
Eliminating redundant data and inconsistency are advantages of relational databases
Selecting data
Querying a database to retrieve data
Selecting fields
Choosing which fields to retrieve data from
Ordering data
Sorting the retrieved data in a particular order
Selecting records
Choosing which records to retrieve data from
Wild cards
Special characters used to match patterns in data
IN
SQL operator used to check if a value is in a list
BETWEEN
SQL operator used to check if a value is between two other values
Inserting data
Adding new records to a database table
Updating data
Modifying existing records in a database table
Deleting data
Removing records from a database table
Foreign keys
The entity that will always have a link to the other entity
One to many relationships
ONE record from one table relates to MANY records from another table
Foreign key
Links to the primary key of another table
The rule for determining which side of the one to many relationship the foreign key will go on is: the foreign key always goes on the many side of the relationship so that it points to one single record in the other table
If the foreign key is put on the wrong side of the relationship (the one side), then it is impossible to store the related data properly because it will result in non-atomic data (multiple data items in one field for a record)
The foreign key always goes on the many side of the relationship so that it points to one single record in the other table
Redundant data
Data that exists more than once, so it is classed as being redundant (not needed)
Inconsistent data
Redundant data can lead to inconsistency, where the same data is stored differently in different places
Eliminating redundant data also results in inconsistent data being removed
Eliminating redundant data saves storage space, eliminates the problem of inconsistent data, and provides more reliable search results
Eliminating inconsistent data does not eliminate errors, it just ensures the data is consistently incorrect if there are errors
Primary key
A field or combination of fields that uniquely identifies each record in a table
Foreign key
A field in one table that links to the primary key of another table, allowing the tables to be related
Redundant data
Data that is duplicated and not needed
Redundant data can lead to inconsistent data, where the same data is stored differently in different places