Databases Flashcards
define database
persistant organised (stored in records and fields) store of related data
define an entity
A real thing like people or customers (the table name in the database)
define a table
collection of data that relates to an entity (customers)
define a record
collection of data about one singular entity (rows)
define field
a unique piece of data about an entity i.e (student firstname) (collumns)
define a flat file database
a table with multiple entities
i.e table with data on both customer and item that they buy
why is a flat file database bad
because everytime a customer buys an item all their data needs to be re added to the table so if address changed it has to be changed on every order
when would an intersection table be used
in a many to many database
define primary key
A field which has a unique value for every record
A unque identifier
how does an intersection table work
uses the primary keys from the needed records in order to reference them i.e customer 3 bought procuct 17
define secondary key
a second unique identifier Not always present. film number (primary key) film name(secondary key)
define foreign key
primary key used in another table (intersection table?)
what is referential integrity
a given element that is referenced in another table is not deleted (not just records could be fields)
What does ACID stand for
atomicity consistency isolation durability
what does Atomicity mean (in ACID)
that a transaction is either entirely processed or not at all proccessed
What does consistency mean
That the transaction cannot break the pre-exsisting rules of the database i.e a string is not added in place of an integer
defin normalisation
a set or rules for a database to follow in order to be the most efficient
what level normalisation is flat file
Un normalised
what are the rules of first form normalisation
no collumns have repeating data (i.e maths,science) in one box
data must be in its atomic form i.e first name surname
must have a primary key
rules of second normal form
must be in first normal form first
partial dependencies get split so any fields that only depend on one of the keys get split into a new table
one primary key
rules of third normal form
all fields are fully dependent on the primary key
non key dependencies are split
i.e email is based of student name not subject
Why use an intersection table [3]
to change many to many relationships
to make the database third normal form
reduces data redundancies
1 pro and 1 con to having multiple keys [2]
pro , faster searching
con, larger storage or insert, update and delete actions will take longer
Using an example, describe a scenario when a flat file database may be an appropriate solution. State an advantage of using a flat file instead of a relational database. [3]
when the data bse only contains one type of entity
personal contacts list (only contains phone numbers)
easier to use or import into another system
state two advantages of a fully normalised databases over flat file databases [2]
less storage is required
data only has to be stored once
queeries can be handled easier
improved usability
better data consistency and forces referential integrity
methods of capturing data
Optical character recognition
Optical Mark Recognition
form
QR code
Methods of Selecting data
SQL
QBE (query by example)
Methods of managing data
SQL
built in DBMS
Methods formatting data being exchanged
XML, JSON and CSV
Methods of exchanging data
manual methods like memory stick
EDI (protocol to allow two systems to facilitate data exchange)
SQL for inserting a piece of data into a database
INSERT
INTO table name (field 1,field2…..)
VALUES (value1,value2,…..)
SQL for deleting a record
DELETE
FROM table name
WHERE conditions
SQL for selecting two fields from different tables
SELECT
FROM Table1
JOIN Table2
ON Table1.field in common = Table2.field in common
WHERE
SQL for a updating a record
UPDATE table name
SET field1 = value1,field2 = value2
WHERE
