Databases Flashcards
1.3.1
What is an entity?
An item of interest about which information is stored
What is a relational database?
Database which recoginses the differences between entities by creating different tables for each entity
What are attributes?
The characteristics of an entity
These are categories about which data is collected
What is a flat file database?
Database that consists of a single file and based around a single entity and its attributes
How are flat files written out typically?
Entity1(Attribute1,Attribute2,Attribute3…)
What is a primary key?
A unique identifier for each record in the table
How is the primary key shown?
By underlining it
What is a foreign key?
An attribute that links two tables together
Acts as the primary key in one table and the foreign key in another
How is the foreign key shown?
With an asterisk (*)
What is a secondary key used for?
Allowing a database to be searched quickly
What is a one to one relationship?
Each entity can be linked to one other entity
What is a one to many relationship?
One table can be associated with many other tables
What is a many to many relationship?
One entity can be associated with many other entities and the other way round
How can banks capture data when they scan cheques?
Using Magnetic Ink Character Recognition (MICR)
All details excluding the amount written are printed in a special magnetic ink which can be recognised by a computer but amount must be entered manually
What method of capturing data is used in multiple choice question tests?
Optical Mark Recognition(OMR) or Optical Character Recognition(OCR)
What is data preprocessing?
Only selecting data that fits a certain criteria to reduce volume of input
What is exchanging data?
Process of transferring the collected data
What is a common way exchanging data is done?
Electronic Data Interchange(EDI) and it does’nt require human input and enables data transfer from one computer to another
How can collected data be managed?
Using sql to sort, restructure and select certain sections
What is a composite key?
A primary key that consists of more than one attribute
What is referential integrity?
When tables are linked it ensures that a particular component isn’t deleted if it is used in a product in product table
What is normalisation?
Process of coming up with the best possible layout for a relational database
What does normalisation have to try accomplish?
No redundancy(duplicates)
Consistent data throughout linked tables
Records can be added and removed without issues
Complex queries can be carried out
What is first normal form?
A table that has no repeating attributes or groups of attributes
What is second normal form?
Database which doesn’t have any partial dependencies and is in first normal form
Meaning no attributes an depend on part of a composite key
What is third normal form?
If the database is in second normal form and contains no non-key dependencies
What does non key dependency mean?
Means the attribute only depends on the value of the primary key nothing else
What is indexing?
Stores positions of each record in order by a certain attribute
To look up and access data quickly
How does indexing work?
Primary key is automatically indexed however is almost never queried because its not remembered
So we index secondary keys to make table easier and faster to search through on particular attributes
What is SQL?
Structured Query Language and is a declarative language used to manipulate databases
What is a SELECT sql statement used for?
SELECT statement is used to collect fields from a given table
What is FROM sql statement used for?
FROM statement is used to specify which table the information will come from
What is the WHERE sql statement for?
Used in conjunction with SELECT and FROM to specify the search criteria
How is an SQL statement written?
SELECT (Attribute1, Attribute2) FROM (Entity) WHERE (Attribute=search criteria)
What is the ORDER BY sql statement for?
ORDER BY specifies whether you want it in ascending or descending order
Values are automatically put in ascending order so write ‘Desc’ for descending
What is the JOIN sql statement for?
JOIN provides a method of combining rows from multiple tables based on a common field between them
What does the CREATE sql statement do?
CREATE function allows you to create a new database
How do you use the CREATE function in sql?
CREATE TABLE (Tablename)
(
Attribute1 INTEGER NOT NULL,PRIMARY KEY,
Attribute2 VARCHAR(20) NOT NULL,
…
)
How do you use the JOIN function in sql?
JOIN (Tablename2)
ON (Tablename1.Attribute1) = (Tablename2.Attribute1)
What are the 8 sql datatypes?
CHAR(n):String of fixed length n
VARCHAR(n):String of variable length with max n
BOOLEAN:True or false
INT:Integer
FLOAT:Number with decimal
DATE:Date in format date/month/year
TIME:Time in format Hour/minute/second
CURRENCY:Sets number as monetary amount
What is ALTER sql statement used for?
ALTER is used to add delete or modify columns in a table
How do you use ALTER statement in sql to add a column?
ALTER TABLE(Tablename)
ADD(AttributesX datatypes)
How do you use ALTER statement in sql to delete a column?
ALTER TABLE (Tablename)
DROP COLUMN (Column)
How do you use ALTER statement in sql to modify data of a column?
ALTER TABLE (Tablename)
MODIFY COLUMN (Column NewDataType)
What does the INSERT INTO sql statement do?
INSERT INTO is used to insert a new record into a database table
How do you use the INSERT INTO statement in sql?
INSERT INTO (Column1, Column2,…)
VALUES(value1,value2,…)
What does the UPDATE sql statement do?
UPDATE is used to update a record in a database table
What does the DELETE sql statement do?
DELETE is used to delete a record from a database table
What is a transaction?
A single operation execution operated on data or a collection of operations
What is Atomicity?
A transaction must be processed entirely 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 another
What is Durability?
Once a transaction has been executed it will remain so regardless of the circumstances surrounding it, such as a power cut
What is record locking?
The process of preventing simultaneous access to record in a database
Why do we use record locking?
To prevent inconsistencies or a loss of updates
Improves database performance by disabling multiple user to access data simultaneously
How does record locking work?
While one person is editing a record, this locks the record so prevents others from accessing the same record
What is a problem with record locking?
Deadlock- A situation in which two or more transactions are waiting for one another to give up locks
What is redundancy?
The process of having one or more copies of data in physically different locations
Why is redundancy important?
If there is any damage to one copy the others will remain unaffected and can be recovered
