Databases

Question 1

What is a Relational Database

Answer 1

A database where data is held in tables (relations) and the tables are linked by common attributes

Question 2

What is a Flat File

Answer 2

A database that consists of a single file. The flat file will most likely be based around a single entity and its attributes

Question 3

What are the potential problems of using a flat file database

Answer 3

• Data might be inconsistent since data changed in one record may not be changed in another
• Space is wasted through repeated data

Question 4

What is an Entity

Answer 4

An item of interest

Question 5

What are the three types of keys

Answer 5

• Primary Key
• Secondry Key
• Foreign Key

Question 6

What is a Primary Key

Answer 6

A primary key is a unique identifier for each record in the table

Question 7

What a Secondary Key

Answer 7

• A secondary key allows a database to be searched quickly
• For example, secondary key can be set up on the surname attribute as it is unlikely someone will remember their ID but will remeber their surname it makes it easier to find specific people in the database

Question 8

What is a Foreign Key

Answer 8

• A foreign key is the attribute which links two tables together
• The foreign key will exist in one table as the primary key and act as the foreign key in another

Question 9

What is the name given to a key that is made up of multiple attributes

Answer 9

Composite key

Question 10

What are the three Entity Relationship Models

Answer 10

• One-to-one- each entity can only be linked to one other entity, such as the relationship between a husband and wife
• One-to-many - one table can be associated with many other tables, such as a doctor having multiple patients
• Many-to-many - one entity can be associated with many other entities and the same applies the other way round. An example is customers and products - each customer can have more than one product and each product can have more than one customer

Question 11

What is Normalisation

Answer 11

The process of coming up with the best possible layout for a relational database

Question 12

What does Normalisation try to achieve

Answer 12

• No redundancy (unnecessary duplicates)
• Consistent data throughout linked tables
• Records can be added and removed without issues
• Complex queries can be carried out

Question 13

What is First Normal Form

Answer 13

• No repeating fields / data
• Each record has a primary key
• Field names should only contain a single value

Question 14

What is Second Normal Form

Answer 14

• No partial dependencies (one or more fields is depedent on only part of the primay key)
• The data must be in first normal form

Question 15

What is Third Nomalisation form

Answer 15

• Contains no non-key dependencies (A non-key dependency means the attribute only depends on the value of the primary key and nothing else)
• The data must be in second normal form

Question 16

What is Indexing

Answer 16

• A method of numbering the position of each record in a database
• This is used to look up and access data quickly

Question 17

What does capturing data mean and give examples

Answer 17

• The process of getting the data that will be stored in a database
• Manual methods include transcribing data from a form that has been filled in
• Banks scan cheques using Magnetic Ink Character Recognition (MICR). All of 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 (OMR) is used for multiple choice questions on a test.
• Other forms use Optical Character Recognition (OCR).

Question 18

What does selecting and managing data mean and give an example

Answer 18

• Selecting data involves only selecting data that fits a certain criteria to reduce the volume of input
• For example, a camera catching speeding cars will only select cars going above a certain speed. Then, background information will be removed so only the number plate is added to a record.

Question 19

What does exchanging data mean and give an example

Answer 19

• The process of transferring collected data
• One common way of exchanging data is EDI (Electronic Data Interchange). This doesn’t require human interaction and enables data transfer from one computer to another

Question 20

What are the eight types of SQL operations

Answer 20

• SELECT – finds data
• INSERT – adds data
• UPDATE – changes data
• DELETE – deletes records from a Table
• DROPS - drops an entire table
• Inner Join - selects records with matching values in both tables
• Order by - sorts the data in ascending or descending order
• * or ‘% - wildcards

Question 21

What is the general form for a SQL SELECT operation

Answer 21

SELECT

FROM

WHERE

Question 22

What is the general form for a SQL UPDATE operation

Answer 22

UPDATE

SET

WHERE

Question 23

What is the general form of an SQL INSERT operation

Answer 23

• INSERT INTO
• VALUES

Question 24

What is the general form for a SQL DELETE operation

Answer 24

DELETE

FROM

WHERE

Question 25

What is the general form for a SQL Inner Join operation

Answer 25

SELECT FROM INNER JOIN ON

Question 26

What is the general form for a SQL Order by operation

Answer 26

SELECT FROM ORDER BY (ASC|DESC)

Question 27

Describe an example of a nested SQL SELECT statement

Answer 27

SELECT FROM WHERE (condition) IN ( SELECT, FROM, WHERE)

Question 28

Why is it a good idea to do a SELECT SQL statement before a DELETE SQL statement

Answer 28

A SELECT SQL returns a value, so the user can see what they are deleting before they delete it. If the user just deleted what they are removing would not be returned

Question 29

What is Referential Integrity

Answer 29

* Referential integrity is the process of ensuring consistency * It 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

Question 30

What is a Transaction

Answer 30

A single operation executed on data

Question 31

What is ACID

Answer 31

* Atomicity – the transaction must be completed fully. If it is not completed fully it will not be recorded * Consistency - a transaction must maintain the referential integrity rules between linked tables * Isolation - simultaneous 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 (must write to non - volatile storage )

Question 32

Give an example of Atomicity in the real world

Answer 32

* In a supermarket they may have a customer who is buying a new product * Before the stock can decrease the customer may need to be added * If the system crashes during the sale then a rollback would need to occur which meant that all of the transactions occur again so that they all occur together * This could be implemented using a try and catch statement

Question 33

What is Record Locking

Answer 33

* The process of preventing simultaneous access to records in a database is called record locking and it is used in order to prevent inconsistencies or a loss of updates * While one person is editing a record, this ‘locks’ the record so prevents others from accessing the same record

Question 34

What is the potential problem of record locking

Answer 34

If two users are attempting to update two records, a situation can happen where neither can proceed known as deadlock

Question 35

Name a method that can prevent a deadlock from happening

Answer 35

* Time stamp ordering * Each database object has a timestamp to say when it was last read or written. * This ensures that records are correctly updated in the right order

Question 36

How can redundancy be helpful

Answer 36

* Redundancy is the process of having one or more copies of the data in physically different locations. * This means that if there is any damage to one copy the others will remain unaffected and can be recovered