Databases

Question 1

What is a database?

Answer 1

An organised collection of data

-there are several models of data organisation, the most common being the relational mode

Question 2

What is data consistency

Answer 2

where the database never records the same piece of information more than once

If it did, then it would lead to data inconsistency

Question 3

What is bad about data being held in more than one place in a database

Answer 3

• leads to inconsistency

- waste of disk space

Question 4

What is data redundancy

Answer 4

where the data doesn’t need to be held more than once and so any copies of the data are redundant

Question 5

How should a database act

Answer 5

A database should act as a repository or pool of data, separate from the applications that deal with it.

Question 6

What is data independence

Answer 6

the concept of keeping the data separate from the applications that use it

Means that it should be possible to write a new application with forms, reports, etc. That can connect to the data without having to change the way data is stored.

Question 7

What are the properties of a flat file database

Answer 7

-very basic system that only allows a single table of data

Question 8

What are the advantages of a flat-file database

Answer 8

can be useful if very simple information is being recorded

Question 9

What are the disadvantages of a flat-file database over a relational database

Answer 9

• a relational database allows data to be held in multiple tables which have relationships between them
• a well-designed (normalised) database will not have any of the problems associated with flat-file databases

-potential inconsistency

-potential redundancy (wasting space as information is stored more than once)

-takes longer to enter information as data has to be entered more than once

Potential loss of data

Question 10

What is an index

Answer 10

if a field is going to be regularly used as part of a search, then it can be set to indexed to allow searches to be performed much more quickly

A table can have several indexes if required

Question 11

downside of an index

Answer 11

the more memory is required to hold each index and every time a record is added or deleted the index needs to be updated which can add a very slight delay

this is why only field which are regularly used should be indexed

Question 12

What is a primary key

Answer 12

a field or fields that uniquely identify a record, e.g. the pin uniquely identifies a student

Question 13

What would you use if no single field uniquely identifies a record

Answer 13

a composite primary key

e.g. in the student-subjects table on the previous slide, neither the pin nor subject id alone could be the primary key. Both are needed to identify a record and therefore the primary key would be PIN and Subject ID.

Question 14

What is a foreign key

Answer 14

a field in a table that is the primary key in another table and is used to form a link between the two

Question 15

LOOK AT ERD and entity description

Answer 15

do lesson 4-6 exam question video on erd for practice

Question 16

What is the identifier known as in a relational database

Answer 16

primary key

Question 17

What are the three types of relationship between entities

Answer 17

One-to-many

One-to-One

many-to-one

Question 18

What is meant by the term database normalisation

Answer 18

A way of structuring data according to theoretical rules

Usually reduces data duplication / redundancy

Avoids danger of inconsistency / maintains integrity

Avoids wasting processing time

Question 19

Describe 1st normal form

Answer 19

Makes field independent, avoids duplication of items

No repeating attributes

Question 20

What is 2nd normal form

Answer 20

Each field depends on the whole primary key

No partial key dependencies

Question 21

What does data normalisation do to a database

Answer 21

Reduces data duplication

If not done :

Danger of inconsistency
Danger of data being lost during update
Storage space wasted

Question 22

What is the third normal form

Answer 22

No non key dependencies

Dependent on the key, the whole key and nothing but the key

No transitive dependencies

Question 23

What is big data

Answer 23

Relatively modern term

Isn’t structured into a relational database

Used to describe large collections of data, but too large to be processed using standard database techniques

Data sets are growing rapidly because of the data being gathered by the vast number of cheap devices (cameras, sensors, mobile devices etc.)

Question 24

What are distributed databases

Answer 24

In which the data is stored across two or more computer systems located at different sites on a computer network

Question 25

What is data warehousing

Answer 25

Where large quantities of data are collected together from a variety of locations for efficient analysis

Question 26

What is the pros and cons of a relational database

Answer 26

Pros
Avoids data duplication
Avoids inconsistent records
Saves storage space

Cons

Data complexity

Maintaining / modifying relational databases can be difficult

Question 27

Advantages of relational database over flat file

Answer 27

Relational databases allows data to be held in multiple tables that can have relationships between them

A well designed (normalised) database will not have any of the problems associated with flat-file databases (redundancy, inconsistency etc.)

By splitting the tables it is possible to hold exactly the same information without the problems we have previously identified

Question 28

Composite primary key

Answer 28

Used when no single field uniquely identifies a record

E.g. Two fields need to be used together to make a composite primary key to identify a record

Question 29

Define entity

Answer 29

A category of object, person, event or thing of interest about which data needs to be recorded

Each entity has attributes (fields)

Needs an identifier(primary key) which uniquely identifies a record

Question 30

What are the relationships between entitites

Answer 30

One to one

One to many

Many to many

If there is a many to many relationships a new entity must be made and two one to many links are made instead

Question 31

Define validation and the 6 types

Answer 31

Checking how appropriate the data being entered is

Range check - used to ensure input data lies between a specific range

Presence check - used to prevent further progress if a required field is left blank

Format check - used to ensure data matches a specific pattern, e.g. dd/mm/yyyy

Data type check - Used to ensure input data is a particular data type

Length check - used to ensure an input data string is a sensible length

Question 32

What is restricted validation

Answer 32

Where fields in a database have a definite amount of data that can be entered into them e.g. days of the week limited to Sunday, Monday etc.

Programming a database to accept only one of a series of valid choices can prevent errors and lessen the time it takes to input data

This has different forms like listbox, combobox, radio button

Question 33

What is verification

Answer 33

Checking that what has been entered is what was intended

To make sure there isn’t mistakes

May make you input the data twice

E.g. when creating a password it makes you enter it twice

Question 34

Data dictionary

Answer 34

Data dictionary is a database which holds information about the database

E.g. there will be a table that holds information about every table in the database. A table that holds information about every field in the database

Other information:
- characteristics of each data item such as length and data type

• any restrictions on the value of certain columns (validation rules)
• the relationships between data items
• which programs access which item of data, and whether they read the data or change it.

Question 35

What is SQL used for

Answer 35

To search for data and optionally sort it

It can also search and provide a result in the form of a table of results

These results can potentially be used is different graphs e.g. graphs, charts, reports, etc.

Is a text based query language

The query is sent to the databse management system and it produces a result in the form of a temporary table

Question 36

What are the commands used in a query when showing something with specific conditions and can be ordered

Answer 36

SELECT
FROM
WHERE
ORDER BY [fieldname] ASC/DESC

Question 37

What are SQL sub queries

Answer 37

It is possible to have the result of one query be used in the where clause of another

(When doing questions recommended to do the sub query first)

Question 38

what is SQL order by

Question 39

What is SQL insert into

Answer 39

Used to add new rows of data to a table

E.g.

INSERT INTO
VALUES( , , )

Question 40

What is SQL update

Answer 40

Used to modify existing data in a table

UPDATE
SET
WHERE

Question 41

What is SQL query delete

Answer 41

DELETE
FROM
WHERE

Question 42

What is SQL query create table

Answer 42

CREATE TABLE [tablename] ( [fieldname] INT PRIMARY KEY NOT NULL,
[Fieldname] CHAR(40) NOT NULL,
[Fieldname] DATETIME,
[Fieldname] NUMERIC(7,2)

for numeric(7,2)

7 digits total, 2 digits to the right of the decimal place

Question 43

What is big data

Answer 43

Used to describe large collections of data (similar to data warehouse) But too large or complex to be processed by standard database techniques

Isn’t structured into a relational database like a data warehouse would be and can’t be queried (e.g. using SQL)
In traditional ways in acceptable time frames

Data sets growing rapidly due to all the data being gathered by the vast number of cheap devices e.g. cameras, sensors, mobile devices etc.

Size of these data sets grow rapidly as the Internet continues to grow

Question 44

Difference between big data and data warehousing

Answer 44

Big data is largely unstructured and not stored in a formal well designed data warehouse

Question 45

What is data warehousing

Answer 45

Data warehousing represents an ideal vision of maintaining a central repository of all the useful data in an organisation

Very structured, large scale relational databases that can be queried in traditional ways e.g. SQL

Transactions, daily operational data etc. Are fed into the data warehouse

These transactions will usually come from multiple very different sources and systems, often on a national or world-wide level

The data isn’t generally overwritten so that patterns and trends over long periods can be considered.

Data mining can be used to search through the data warehouse to look for patterns or trends

Question 46

Define data mining and what It does

Answer 46

The analysis of large amounts of data (in a data warehouse)

Analysing from different perspectives and summarising it into useful information for firms used to cut costs + increase revenue

E.g. supermarkets look for patterns in buying and track what individuals buy (through loyalty cards) and target them with special offers.

Insurance companies can use data mining to spot customers who have regular patterns of claims which may be fraudulent, or identify customers who may be interested in particular insurance policies

Question 47

Define predictive analysis

Answer 47

A combination of statistics and data mining and using statistical techniques and AI to analyse current and historical facts to predict future events.

Can forecast (with probabilities) what may happen in the future and therefore make decisions based on those predictions e.g. economic forecasting

A probabily score is assigned for the likelihood of a thing e.g. a customer will behave a certain way

Question 48

Database management systems

Answer 48

Programs that provides an interface between the OS and the user in order to make access to the data as simple as possible

Other functions:
Data storage, retrieval and update

Allows access to queries and reports

Creation and maintainance of the data dictionary

Managing the facilities for sharing the database

Backup and recovery - must provide the ability to recover the database in the event of failure

Security - The DBMS must handle password allocation and checking, and view the database that a given user is allowed

Question 49

Define distributed databases

Answer 49

Databases where the data is stored across two or more computer systems located at different sites on a computer network

It is also likely that the processing (DBMS) is distributed too

This is a single database where parts of the database can be held on different servers on the network, perhaps over a wide geographical area

Either involves having parts of the databse at different locations or the whole database in different locations

Question 50

Benefits of distributed databases

Answer 50

Having databases spread out can maximise performance and there will be less data required to pass across a network/Internet so data can be recorded/read more quickly

Question 51

Concerns of distributed databases

Answer 51

Need to be powerful systems that can cope with the synchronisation of the data between the branches to ensure data inconsistency doesn’t occur and to make sure data is kept upto date where it is a requirement

Question 52

Benefits of cloud services

Answer 52

Organisations will run their database systems on the servers in data centres owned by very large cloud providers e.g. amazon meaning organisations don’t need to invest as much money into money/time/staffing in their own data centres

Organisations use cloud services to deal with issues e.g. security, upgrades, backups, etc.