1.3.2 databases Flashcards
What is a relational database?
-Recognises the differences between entities by creating different tables for each entity
What is a flat file database?
Consists of a single file likely based around a single entity and its attributes
“Entity1(Attribute2, Attribute2…)”
What is a primary key?
-Unique identifier for each record in the table. (Always different)
What is a foreign key?
-Attribute which links two tables together
What is a secondary key?
-Allows a database to be searched quickly
Entity relationship modelling
*One-to-one: Each entity can only be linked to one other entity
*One-to-many: One table can be associated with many other tables
*Many-to-many: One entity can be associated with many other entities and vice versa
What is normalisation?
The process of coming up with the best possible layout for a relational database, tries to accomplish:
*No redundancy
*Consistent data throughout linked tables
*Records can be added and removed without issues
*Complex queries can be carried out
First normal form normalisation
There must be no attribute that contains more than a single value
Second normal form
A database which doesn’t have any partial dependencies and is in the first normal form.
No attributes can depend on part of a composite key
Third normal form
Database is in second normal form and contains no non-key dependencies
-A non-key dependency means the attribute only depends on the value of the primary key and nothing else
What is indexing
-Method used to store the position of each record ordered by a certain attribute. Used to look up and access data quickly
-Primary key is automatically indexed
SQL
*Structured Query Language; declarative language used to manipulate databases
*Enables the creating, removing and updating of databases
SQL statements
*SELECT- collect fields from a given table
*FROM- specifies which table the information will come from
*WHERE- specifies search criteria
*ORDER BY- ascending or descending order (ordered in ascending, changed to descending by adding ‘desc’ to the end of the statement)
*JOIN- provides a method of combining rows from multiple tables based on a common field between them
*CREATE- make new databases
Referential integrity
-Process of ensuring consistency; ensures that information is not removed if it is required elsewhere in a linked database
-If two database tables are linked, one of these tables cannot be deleted as the other table requires its contents
Transaction processing
A single operation executed on data
ACID
*Atomicity- a transaction must be processed in its entirety or not at all
*Consistency- A transaction must maintain the referential integrity rules between linked tables
*Isolation- simultaneous executions of transactions should lead to the same result as if they were executed one after the other
*Durability- Once a transaction has been executed it will remain so regardless of the circumstances surrounding it, such as in the event of a power cut
Record locking
Process of preventing simultaneous access to records in a database
