Chapter 7: Relational databases and SQL

• Attribute


  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