Databases Flashcards

Question

What is data warehousing

Answer 1

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

Answer 2

Pros Avoids data duplication Avoids inconsistent records Saves storage space Cons Data complexity Maintaining / modifying relational databases can be difficult

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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

Answer 12

SELECT FROM WHERE ORDER BY [fieldname] ASC/DESC

Answer 13

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)

Answer 14

Used to add new rows of data to a table E.g. INSERT INTO VALUES( , , )

Answer 15

Used to modify existing data in a table UPDATE SET WHERE

Answer 16

DELETE FROM WHERE

Answer 17

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

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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

Answer 25

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

Answer 26

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

Answer 27

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.