1.3.2 Flashcards
database
an organised collection of data that allows for easy:
Adding
Modification
Deletion
Searching
relational database
2 databases that are linked together to share data
flat file
definition
a single table
can be created very easily using either database or spreadsheet software and is often saved as a CSV file
(used for small and normally personal use details)
flat file
cons
Inefficient and normally has repeated data so:
Take up unnecessary space
Be slow to query
Become difficult to maintain
entity relationship diagram
1:1 (one-to-one)
1:M (one-to-many)
M:M (many-to-many)
— 1:1
/
— 1:M
\
\ /
— M:M
/ \
primary key
a unique identifier
composite primary key
2 or more fields combined
secondary key
a second field to be indexed (can have repeats)
often used as a search
foreign key
a relational link
(the same on both databases but one has to be a primary key)
indexing
keeping an index of the primary keys so they can be searched for quickly
each index has the position of its primary key to save time
capturing data
typically done manually and then has to be read by a computer (normally using a data capture form)
using Optical Character Recognition (OCR)
or
using Optical Mark Recoignition (OMR)
or
chip and pin/barcodes/qr codes/etc…
can now be filled in on a computer
managing data
use SQL (DML) or DBMS to change the data
selecting data
use SQL (QBE) to choose data to see
exchange data
normalisation
splitting tables in a database and arranging the data to move it from 1NF 🡺 2NF 🡺 3NF
first normal form
All field names must be unique
Values in fields should be from the same domain
Values in fields should be atomic
No two records can be identical
Each table needs a primary key
second normal form
The data is already in 1NF
Any partial dependencies have been removed
Fix any M:M relationships created as a result
partial dependency means one or more of the fields depend on only part of the primary key
this issue can arise if the primary key is a composite key comprised of more than one field
third normal form
The data is already in 2NF
Any transitive dependencies have been removed (remove any non-key dependencies)
non-key dependency in one where the value of a field is determined by the value of another field that is not part of the primary key
SQL
Structured Query Language
allows for the fast and efficient retrieval, deletion, queries, definition, control and manipulation of data using a simple set of commands
SQL - QBE
Query by Example
the first graphical query language, making use of visual tables
allows the user would enter commands and conditions
SQL - DML
Database Manipulation Language
easy ways to:
Add new data
Edit/modify existing data
Delete data
SQL - DBMS
DataBase Management System
hides the underlying structure of the data and ensures it remains integral by:
Preventing the creation of duplicate primary keys
Enforcing validation rules
Providing secure access
Providing encryption
Providing program data independence
Managing multiple users
referntial integrity
refers to the accuracy and consistency of data within a relationship
transaction processing
Any information processing that is divided into individual, indivisible operations (transactions)
Each transaction must succeed or fail as a complete unit (can’t be partially complete)
transaction processing - CRUD
Create 🡺 INSERT/CREATE
Read 🡺 SELECT
Update 🡺 UPDATE
Delete 🡺 DELETE
All relational databases have certain base functionality
A.C.I.D
Atomicity
Consistency
Isolation
Durability
ensure data integrity, transaction processing in all DBMSs
A.C.I.D - A
Atomicity - A change to a database is either completely performed or not at all
A.C.I.D - C
Consistency - Any change in the database must retain the overall state of the database
A.C.I.D - I
Isolation - A transaction must not be interrupted by another transaction. The transaction must occur in isolation so other users or processes cannot access the data concerned
A.C.I.D - D
Durability - Once a change has been made to a database, it must not be lost due to a system failure.
by making sure the DBMS writes the effects of transactions immediately back to permanent secondary storage
record locking
record(s) being affected by a transaction are locked, effectively placing them in a read-only state
When the transaction is fully completed will the lock be removed
To stop multiple people trying to change the same thing (transactions being interrupted by other transactions)
redundancy
repetition of the same data
table
database
entity
file
records
database
rows
tuples
field
database
columns
attributes
1:1
database relationship example
one student has one planner; or
one student planner belongs to one student.
1:M
database relationship example
one tutor group contains many students; or
many students belong to one tutor group.
M:M
database relationship example
one student can be taught by many teachers; or
one teacher can teach many students.
not considered a good database design
SQL example - show everything from all records where the field ‘name’ is ‘algeria’ from a database called ‘world’
SELECT *
FROM world
WHERE name = “Algeria”
SQL example - show the ‘continent’ from all records where the field ‘area’ is ‘mexico’ or ‘USA’ from a database called ‘globe’
SELECT continent
FROM globe
WHERE area = “mexico” OR area = “USA”
SQL example - show everything from all records where the field ‘age’ is 2… and height over 1.5m from a database called ‘people’
SELECT *
FROM people
WHERE age LIKE “2%”
AND height > 1.5
SQL example - make a new record in a table calle DBtable with value…s and field…s
INSERT INTO <DBtable> (<field1>,<field2>,<…>)
VALUES (<value1>,<value2>,<…>)</value2></value1></field2></field1></DBtable>
SQL example - make a DELETE command that removes all records from ‘earth’ table with ‘population’ larger than 10,000,000
DELETE FROM earth
WHERE population > 10000000
SQL example - change the ‘distance’ to 2000m where ‘school’ is edgbarrow in the tables ‘schools’
UPDATE schools
SET distance= 2000
WHERE school= “Edgbarrow”
SQL example - join 2 tables: ‘group’ and ‘student’ with the common field of tutor with all students in 10V
JOIN <student>
ON <group>.<tutor> = <student>.<tutor>
WHERE student.tutor = “10V”</tutor></student></tutor></group></student>
SQL example - delete the whole of a table called information
DROP TABLE information
