1.10 Databases Flashcards
Relational Databases
- Entity: Item of interest about which information is stored
- Relational Database: Database which recognises the differences between entities by creating different tables for each entity
Flat File
Flat File: Database that consists of a single file. Used for a single entity and its attributes
- E.g Car(CarID, Age, Price)
Primary Key
Primary Key: Unique identifier for each record in the table
- E.g CarID
Foreign Key
Foreign Key: Attribute which links two tables together
- The foreign key will exist in one table as the primary key and act as the foreign key in another
Secondary Key
Secondary Key: Allows a database to be searched quickly
- Secondary key set up (E.g surname) making it possible to order & search by surname which makes it easier to find specific items in the database
Entity Relationship Modelling
Tables can have different kinds of relationships, which depend on how entities are related in the real world:
- One-to-one: Each entity can only be linked to one other entity, E.g husband & wife
- One-to-many: One table can be associated with many other tables, E.g mother having multiple children. Multiple child entities can be linked to the same mother entity
- Many-to-many: One entity can be associated with many other entities and the same applies the other way round, E.g students and courses
Normalisation
Normalisation: Process of coming up with the best possible layout for a relational database
- No redundancy (unnecessary duplicates)
- Consistent data throughout linked tables
- Records can be added and removed without issues
- Complex queries can be carried out
Types Of Normalisation
First Normal Form: There must be no attribute that contains more than a single value.
Second Normal Form: A database which doesn’t have any partial dependencies & is in 1NF can be said to be in 2NF. No attributes can depend on part of a composite key
Third Normal Form: If the database is in second normal form & contains no non-key dependencies, it is in 3NF. A non-key dependency means the attribute only depends on the value of the primary key & nothing else.
Indexing
Indexing: Method used to store the position of each record ordered by a certain attribute. Used to look up & access data quickly
- The primary key is automatically indexed, but primary key is almost never queried since it is not normally remembered
- This is why secondary keys are used & indexed making table easier & faster to search through on those particular attributes
Capturing Data
- Data capturing method dependent on the context
- E.g pedestrians in a survey, responses manually entered into database
- When people pay cheques. Banks scan cheques using Magnetic Ink Character Recognition (MICR)
- Optical Mark Recognition (OMR) is used for multiple choice questions on a test
- Optical Character Recognition (OCR)
Selecting & Manging Data
- Selecting the correct data is an important part of data preprocessing
- Involves only selecting data that fits a certain criteria to reduce the volume of input
- E,g background information will be removed so only the number plate is added to a record
- Collected data can also be managed using SQL to sort, restructure & select certain sections
Exchanging Data
EDI (Electronic Data Interchange): Doesn’t require human interaction & enables data transfer from one computer to another
SQL
Structured Query Language: Declarative language used to manipulate databases. Enables the creating, removing and updating of databases
SELECT: Used to collect fields from a given table
FROM: Specifies which table(s) the information will come from
WHERE: Specifies the search criteria
ORDER BY: Specifies whether you want it in ascending/ descending. Automatically Ascending, Add ‘Desc’ to the end
JOIN: Method of combining rows from multiple tables based on a common field between them
ALTER: Used to add, delete or modify the columns in a table
INSERT INTO: Used to insert a new record into a database table
UPDATE: Used to update a record in a database table
DELETE: Used to delete a record from a database table
Data Types
- CHAR(n): this is a string of fixed length n
- VARCHAR(n): this is a string of variable length with upper limit n
- BOOLEAN: TRUE or FALSE values
- INTEGER/INT: integer
- FLOAT: number with a floating decimal point
- DATE: the date in the format Day/Month/Year
- TIME: the time in the format Hour/Minute/Second
- CURRENCY: sets the number as a monetary amount
Referential Integrity
Referential Integrity: Process of ensuring consistency. This ensures that information is not removed if it is required elsewhere in a linked database
E.g If 2 database tables are linked, 1 of these tables cannot be deleted as the other table requires its contents
