CompSci - Databases Flashcards
Database
Contains individual attributes about entities structued in various data structures.
Data Consistency
Ensures data is correct by controlling all occurences of the same data item to be updated dynamically.
Referential Integrity
Ensuring that foreign keys in tables refer to valid records/tables.
Data Redundancy
Ensures that data isn’t duplicated, wasting storage
Data Independence
Ensures the database is a separate system from its application so that changes are simpler to make.
Atomic Data
Attributes that are broken down into their fundamental parts to maximise flexibility.
DBMS
Database Management System
DBMS Tools
IQRAMS DUM
Integrity
Queries
Report generation
Access levels
Maintenance
Security
Dictionaires
User interface
Manipulation
Primary Key
Unique identifier for an entity
Compound Key
Unique identifier with >1 fields for an entity
Candidate Key
Any field other than a dedicated ID that can be used as a primary key.
Foreign Key
A primary key from another table to represent a relationship between the objects
Secondary Key
A second primary key, allowing users to access the data in some other order
Validation Techniques
Presence
Format
Length
Range
Lookup
Type
Verification Techniques
Double-Entry
Proof-reading
Big Data
A large & complex set of data collected from various sources that can be analysed using dedicated systems to find trends and patterns.
Predictive Analysis
An active process that relates past patterns and trends with potential future behaviour using modelling and machine learning to inform business risks or opportunities.
Data Warehousing
Storing big data sets in a centralised location from several sources that support analysis.
Data Mining
A passive process that uses automated software to find patterns and relationships in large data sets.
Distributed Systems
Using a local connected network of devices to harness their collective processing power and storage capacity to more efficiently analyse / store data.
0NF
Flat file (contains a number of single tables w no links between them)
1NF
- Primary Key
- Fields are atomic
- No repeating rows / columns
2NF
Attributes are dependent on only one primary key (split into tables).
3NF
No transitive partial dependency (no composite keys).
SQL - TABLE
CREATE TABLE [name] (
[var1] Int PRIMARY KEY
[var2] Varchar(n)
[var3] Varchar(n) NOT NULL
)
SQL - SELECT
SELECT */### FROM [table_name] WHERE [condition]
SQL - INSERT
INSERT INTO [table_name] VALUES [vars]
SQL - DELETE
DELETE FROM [table_name] WHERE [condition]
Purpose of SQL (Data Operations)
CSS TRIM but on databases
Copying
Searching
Sorting
Traversing
Removing
Inserting
Merging
Normalisation Format
TABLE_NAME ([underscore]PK[underscore], extras, [overscore]FK[overscore])
IQRAMS DUM - Integrity
If any data is updated, linked data in other tables is updated
IQRAMS DUM - Queries
Can use code to perform commands to manipulate the database
IQRAMS DUM - Report gen.
Generate a visual represntation of database contents
IQRAMS DUM - Access lvls.
Users can only view the data they are granted to see
IQRAMS DUM - Maintenance
Ensures that data is stored properly in memory
IQRAMS DUM - Security
Data is protected with passwords or other methods
IQRAMS DUM - Dictionaries
Allows users to specify the structure of data and the metadata of each attribute (length, type…)
IQRAMS DUM - User Interface
Provides a visual representation of the database that users can interact with
IQRAMS DUM - Manipulation
CSS TRIM
Copying
Searching
Sorting
Traversing
Removing
Inserting
Merging
Distributed Database Benefits
Problem in one site doesn’t stop the rest
Staff access can be limited to sections
Scaling is simplified
No network bottleneck as it’s local
Cost
High performance
SQL - UPDATE
UPDATE [table_name] SET [table_var = var]
SQL - JOIN
SELECT */### FROM [table_name] WHERE [condition]
Data Normalisation
Reorganising data in a database to make it more convenient, simple and flexible.
