1.3.2 Databases Flashcards
Database
An organised collection of data
Database advantages
Make data available to users, the data is correct and the data can be held securely
Table
A collection of associated data stored within a database
Fields
The characteristics of each table
Record
An individual entry that belongs in a table
Entity
Something in the real world that we store data about
Becomes a table in a database
Attribute
A characteristic of an entity
Becomes a field in a database
Entity description format
TableName(Entity1, Entity2…)
Char(n) vs VarChar(n)
A char string has fixed length n, a varchar string has variable length up to n
How to access a database
Using a DBMS (Database Management System)
Flat file database
A database comprised of a single table, based around a single entity and its attributes
Flat file advantages and disadvantages
+ simple and quick to set up
- very inefficient, used for storing small amounts of data
Relational databases
Databases split into multiple tables linked by relationships
Primary key
A field in a record which is unique, allowing each record to be uniquely identified. Must not be duplicated or reused.
Secondary key
A field can be defined as a secondary key to be indexed for faster lookups.
Foreign key
The key in a table that correlates to a primary key in another table
Entity Relationship Diagrams (ERD)
One to one is linked by a straight line
One to many diverges into 3 near the many side]
Many to many diverges into 3 at both ends
SQL
Structured Query Language
A declarative language used for querying and updating tables in a relational database
SQL Queries
Retrieves data based on a given criteria
SQL Clauses
Filters data and returns data that fulfils a specific condition
SQL Predicates
Specify conditions which return the filter
SQL Expression
A combination of one or more values, functions and operators
SQL Select format
SELECT lists the fields to be displayed
FROM states which table they come from
WHERE specifies a condition
ORDER BY lists how they are sorted (ASC,DESC)
e.g. SELECT name FROM sales WHERE cores=4 ORDER BY name DESC
SQL UPDATE
Modifies existing values in a table
SQL DELETE
Deletes existing records from a table
SQL DROP
Deletes a database or table
DROP table tableName
SQL INSERT INTO
Inserts new records into a table
INSERT INTO Table(Fields), VALUES (Data)
Join
Combines rows from 2 or more tables based on a related column between them
INNERJOIN
The SQL for joining tables
INNERJOIN newTableName on table.field = table.field
SQL UPDATE Syntax
UPDATE tableName SET field = newValue WHERE …
SQL Additional Data Types
TIME and CURRENCY
SQL Select All
SELECT *
SQL Like
Like “%xx%”
Where the string xx is included anywhere in the data it is returned
