databases Flashcards
what is an entity
an item of interest about which information is stored
what is a relational database
a database that recognises the difference between entities by creating different tables for each entity
what are attributes
characteristics of the entity
categories about which data is collected
what is a flat file
a database that consists of a single file
most likely be based around a single entity and its attributes
Entity1(Attribute1, Attribute 2…)
what is a primary key
unique identifier for each record in the table
usually underlined
what is the foreign key
the attribute that links two tables together
exist in one table as the primaryy key and act as a foreign key in another
what is a sechondary key
allows the database to be searched quickly
what are the 3 different entity relationship models
1 - 1
1 - many
many - many
1 - 1
each entity is linked to one other entity
1 - many
1 table is associated with many other tables
many - many
1 entity can be associated with many other entities
what is normalisation
the process of coming up with the best possible layout for a relational database
what does normalisation try to accomplish
no redundancy
consistent data throughout linked tables
records can be added and removed without issues
complex queries can be carried out
what are the 3 types of normalisation
1st normal form
2nd normal form
3rd normal form
first normal form
there must be no attribute that contains more than a single value
second normal form
a database which doesnt have any partial dependancies and is in first normal form can be said to be in second normal form. This means that no attributes can depend on part of a composite key
third normal form
if the database is in second normal form and contains no non key dependancies its in 3rd normal form
what is a non key dependancy
it means that the attribute only depends on the value of the primary key and nothing else
what is indexing
a method used to store the position of each record ordered by a certain attribute
used to look up and access data quickly
why are primary keys not used for indexing
although its automatically indexed its not normall remembered so secondary keys are used
3 types of capturing data
magnetic ink character recognition
optical mark recognition
optical character recognition
magnetic ink character recognition
all the details excluding the amount are printed in a special magnetic ink which can be recognised by a computer but the amount must be entered manually
optical mark recognition
used for multiple choice questions on a test
exchanging data
process of transferring the collected data
what is a way of exchanging data
electronic data interchange
doesnt require human interaction and enables data trasnfer from 1 computer to another
sql method
select, from, where, order by
what does ORDER BY do
specifies if u want it in ascending or descending order
what does JOIN do
method of combining rows from multiple tables based on a common field between them
what does CREATE do
allows u to make new databases
what does ALTER do
add, delete or modify the columns in a table
what does INSERT INTO do
used to insert a new record into a database table
what is referential integrity
process of ensuring consistency
ensures information isnt removed if required somewhere else in a linked database
if 2 tables are linked 1 cant be deleted cause the other requires its contents
what does ACID stand for
Atomicity
Consistency
Isolation
Durability
what is atomicity
a transaction must be processed in its entirety or not at all
what is consistency
a transaction must maintain the referential integrity rules between linked tables
what is isolation
simultaneous executions of transactions should lead to the same result as if they were executed one after the other
what is durability
once a transaction has been executed it will remain so regardless of the circumstances, even if a power cut
record locking
process of preventing simultaneous access to records in a database
why is record locking used
to prevent inconsistencies or a loss of updates
how does record locking work
while 1 person is editing the record it ‘locks’ so other ppl cant access the same record
what is a problem of record locking
deadlock
what is redundancy
process of having one or more copies of data in physically different locations
so if theres damage the others can be recovered
