database

• A relational database is a database which recognises the differences between entities by creating different tables for each entity.
• An entity is an item of interest about which information is stored.
• Redundancy ensures that if there is any damage to one copy the others will remain unaffected and can be recovered.
• Redundancy is the process of having one or more copies of the data in physically different locations.
• A flat file is a database that consists of a single file.
• A primary key is a unique identifier for each record in the table.
• A foreign key is the attribute which links two tables together.
• A secondary key allows a database to be searched quickly.
• In entity relationship modelling, tables can have different kinds of relationships, which depend on how entities are related in the real world: one-to-one, one-to-many, many-to-many.
• One-to-one relationships are demonstrated using a single line used to connect two entities.
• A one-to-many relationship will have a branch on one side, while a many-to-many relationship has branches on both sides.
• The process of coming up with the best possible layout for a relational database is called normalisation.
• Normalisation tries to accomplish the following things: no redundancy (unnecessary duplicates), consistent data throughout linked tables, records can be added and removed without issues, complex queries can be carried out.
• Exchanging data is the process of transferring the collected data, which can be done using EDI (Electronic Data Interchange) or SQL (Structured Query Language).
• Data needs to be input into the database and there are multiple methods of doing this, which is always dependent on the context.
• The primary key is automatically indexed but is almost never queried since it is not normally remembered, which is why secondary keys are used.
• Normalisation in databases involves removing attributes that contain more than a single value, and ensuring that no attributes depend on part of a composite key.
• Selecting the correct data is an important part of data preprocessing, which could involve only selecting data that fits a certain criteria to reduce the volume of input.
• A database in second normal form containing no non-key dependencies is in third normal form.
• Secondary keys are indexed to make the table easier and faster to search through on those particular attributes.
• Indexing is a method used to store the position of each record ordered by a certain attribute, used to look up and access data quickly.
• A database which doesn't have any partial dependencies and is in first normal form can be said to be in second normal form.
• Optical Mark Recognition (OMR) is used for multiple choice questions on a test, while other forms use Optical Character Recognition (OCR).
• Collected data can alternatively be managed using SQL to sort, restructure and select certain sections.
• Data is also captured when people pay cheques using Magnetic Ink Character Recognition (MICR), where all of the details excluding the amount are printed in a special magnetic ink which can be recognised by a computer but the amount must be entered manually.
• INSERT INTO is used to insert a new record into a database table.
• The example below shows the joining of two tables, Movies and Directors.
• ALTER is used to add, delete or modify the columns in a table.
• ALTER TABLE TableName DROP COLUMN AttributeX.
• ALTER TABLE TableName MODIFY COLUMN AttributeX NewDataType.
• UPDATE is used to update a record in a database table.
• Referential integrity is the process of ensuring consistency, ensuring that information is not removed if it is required elsewhere in a linked database.
• DELETE is used to delete a record from a database table.
• Record locking is the process of preventing simultaneous access to records in a database, used to prevent inconsistencies or a loss of updates.
• A transaction is defined as a single operation executed on data, however a collection of operations can also sometimes be considered a transaction.
• JOIN provides a method of combining rows from multiple tables based on a common field between them.
• The CREATE function allows you to make new databases.
• Values are automatically placed in ascending order and adding ‘Desc’ to the end of statement will cause values to be displayed in descending order: ORDER BY DatePublished Desc
• Each attribute in a table must have its data type specified, whether it is the primary key, its data type, and whether it must be filled in (‘Not Null’).
• Data Types include CHAR(n), VARCHAR(n), BOOLEAN, INTEGER/INT, FLOAT, DATE, TIME, CURRENCY.