3.7 Databases And SQL Flashcards
Tables
Where data is stored.
Records
A single entity within a table.
Field
A single item of information within a table.
Primary key
A field (or combination of fields) that uniquely identify a record.
Foreign key
A field that links a record in one table to a record in another table.
Database
A method of storing information.
• Flat file
• Relational
Flat file
Data is stored simply in one table.
• Easier to setup
• Harder to manage
• Takes up more space
• Data repetition - redundancy : can lead to inconsistency, mistakes.
Relational
Data is stored in multiple tables that can be linked together.
• Requires expertise to setup
• When setup well can be easier to manage
• Optimised storage use
• Data should be stored just once, minimises redundancy : prevents inconsistency, reduces errors
Relational databases: Keys
Data is stored in tables which have Relations.
We link information across tables using keys.
• Primary keys
• Foreign keys
Primary Key
A field which is unique for each record within a table.
Foreign Key
A field which is the Primary Key with another table, enabling us to link it.
How do we show relations?
Using Entity Relationship Diagrams.
SQL
Specialist computer language used for querying Relational Databases
Standard basic Syntax across all RDBMS systems
Allows us to manipulate with data in a database:
• Creating
• Change
• Adding
• Deleting
• Querying (Retrieving)
Retrieving data:
Select statement
Four parts:
1. SELECT – What information do we want?
2. FROM – Which table(s) hold the information?
3. WHERE – Are there any criteria we need to match?
4. ORDER BY – How the data should be sorted/organised
Insert statement
We use SQL to insert information into our Database.
It has the following structure:
- INSERT INTO – The table (and fields) we are adding 2. VALUES – The data we are adding
