2.5 Databases and Distributed Systems (Key Terms (Flat file - Where all…
2.5 Databases and Distributed Systems
Flat file - Where all data is contained in a single table, and data is often repeated.
Relational - Where tables are linked together by relationships, to reduce data redundancy
Primary Key - A field in a table, where each value is identical
Foreign Key - A field which links to a primary key in another table
Field - Something that defines the data. Eg. Name
Record - A group of data, subject to fields, relating to one item or person
Table - A group of records, which all follow the same fields
Indexing - A process of finding a record quicker (eg. filtering results)
Data Dictionary - Stores all the data about a database. Eg. Table names, fields, field datatypes, etc.
Data Warehousing - Large quantities of data used by companies for processing. Also acts as archiving.
Data Integrity - Data needs to be accurate and up-to-date.
Entity - Every table needs a primary key
Referential - Foreign keys must point to another primary key, but can be empty
Domain - All attributes are related to the domain (user) the database was created on
Data Consistency - Constraints on data need to be upheld when adding/editing data.
Data redundancy - When data is repeated unnecessarily.
Normalisation - Process of turning a flat file database into a relational one.
1NF - Each field contains a single piece of data and two values cannot be under the same primary key.
2NF - Remove any more repetition from the main table
3NF - Seperate out the tables so that each ID has its own table (pretty much)
Admin can create rights that stop certain users from doing things within a database.
Eg. Viewing tables, editing data, performing search queries
Select - Returns data from a table. Eg. SELECT * FROM Table
Where - Constraints. Eg. SELECT Students FROM Table // WHERE Name = "Adam"
Insert - Adding records. Eg. INSERT INTO Students (Name, DoB) // VALUES ("Adam", 17/03/1999)
Update - Edits records. Eg. UPDATE Students // SET Year = "13" // WHERE Name = "Adam"
Delete - Deletes records. Eg. DELETE FROM Students // WHERE Name = "Adam"
Drop - Deletes an entire table. Eg. DROP Students
Create - Creates a table. Eg. CREATE TABLE Students // ( // ID // VARCHAR(4), NOT NULL, // Name // String, NOT NULL // PRIMARY KEY (ID) // )
Data definition language (DDL) - Altering tables (Drop, create, etc.)
Data manipulation language (DML) - Altering data (Insert, update, delete, etc.)
Databases are servers which run in the background and process requests in the form of SQL statements.
To access a database, IP, schema name, username and passwords are needed.
Data Mining - Searching through a large amount of data to find links between them and creating contexts for them. Eg. Searching through a supermarkets milk sales history and seeing when and how it sells best. Or banks revoking huge payments in another country by looking at past transactions.
Predictive Analysis - Similar to data mining but it predicts what you'll buy next, for example, and will trigger an investigation if it finds it too irregular.
Databases need to be run off of servers.
Database server links to application servers, which process the requests from user desktops on the network.
Distributed databases - Databases can have their tables spread across multiple different servers, which makes it easier to delegate privileges for people using them.