Databases

• Databases consist of tables that store entities. Each entity has its own row with relevant information in each field.
• An entity is something that data is to be stored about
• An entity description is formatted as Table(Field, Field, Field). The entity identifier(s) is underlined
• In an entity relationship diagram, a crow's foot represents a many relationship
• Relationships could be one-to-one or one-to-many. Many-to-many relationships are broken down into two one-to-many relationships using a link table
• Relational databases are collections of tables with shared attributes that indicate relationships
• Attributes are characteristics of entities as dictated by the field
• A primary key is composed of one or more attributes that can uniquely identify a record within a table. In terms of entities, this is an entity identifier
• One attribute on its own may not be unique, but combining multiple could be. This can be used as a primary key - we call it a composite key
• A foreign key is an attribute that is the primary key in another table
• We normalise databases to remove redundant and repeating data, to make them more efficient
• A table is in first normal form if it has no repeating attributes or groups of attributes. (It could be described as atomic)
• A table is in second normal form if it is in 1NF and has no partial key dependencies.
• A partial key dependency means data is dependent on only part of a composite key
• A table is in third normal form if it is in 2NF and has no non-key dependencies
• A non-key dependency means data is dependent on a field that is not part of the key
• SQL, or structured query language, is a declarative language used to query databases efficiently
• In declarative languages, the user describes the result that's needed rather than the process to reach it, and the language decides the best way to reach this result
• In SQL, command words are in uppercase and strings are in quotes
• In SQL, * means all fields
• SQL can be used to create and modify databases too
• SQL can define (create) a new table using CREATE. You can provide the table name, any fields and their data types, and dictate what the key is
• Client server databases allow multiple clients to access the database simultaneously. This can create issues if two users try to access the same data at the same time - this is known as concurrent access
• Concurrent access can result in updates or data being lost but there are ways to manage this: record locks, serialisation, timestamp ordering and commitment ordering
• Record locks deny other users access to a record while it is being updated
• Record locks may create a deadlock if two updates require access to the other. The DBMS must identify this and rectify it with another solution
• With serialisation, each update is placed in a queue. One update cannot start until the previous update has finished
• With timestamp ordering, each object is assigned a read timestamp and a write timestamp. A transaction will first read the data, updating the read timestamp. It checks this timestamp before writing - if it has been updated since, it knows another transaction is taking place
• In commitment ordering, an algorithm determines the optimal order commands should be executed in, factoring in the impact on the database, as well as considering timestamps and whether they are dependent on each other