1.3.2

Question 1

Database

Answer 1

An organized collection of data.
Organizing data in a database allows for easy:
- Adding/ modification/ deletion/searching

Question 2

Tables

Answer 2

In a table:
- The data is stored in a table (entity/file)
- The table contains records (rows/tuples)
- The table contains fields (columns/attributes)

Question 3

Relational databases

Answer 3

A database needs an actual link to infer this logic, this link = relationship.
An entity is an item of interest about which information is stored.
A relational database is a database which recognises the differences between entities by creating different tables for each entity.

Question 4

relational database adv and dis

Answer 4

Data is only stored once- No multiple record changes needed, More efficient storage, Simple to delete or modify details
Better security. By splitting data into tables, certain tables can be made confidential.
Avoids inconsistent records
Easier to change data format

Cost- quite expensive. there must be separate software which needs to be purchased.
physical memory since it is with rows and columns.
large organizations use more number of database systems with more tables. These information can be used to be transferred from one system to another. This could pose a risk of data loss.

Question 5

Flat file

Answer 5

This database consists of a single file/table.
It can be created very easily using either database/spreadsheet software and is often saved as a comma-separated value file (CSV).

Question 6

Flat file adv and dis

Answer 6

• Very simple
• Require little to set up
• Suitable for storing small amounts of data
• Take up unnecessary space
• Be slow to query
• Become difficult to maintain
  Harder to change data format.
  Potential duplication. As more and more records are added to the database it becomes difficult to avoid duplicate records.

Question 7

Flat file uses

Answer 7

Typical uses:
- Storing contact details
- Small product database
- Maintaining a game or music collection

The flat file will most likely be based around a single entity and its attributes.
Flat files are typically written out in the following way:

Entity1(Attribute1, Attribute2, Attribute3 …)

Question 8

1:1

Answer 8

1:1
One student has one planner, and one student planner belongs to one student.

Question 9

1:M

Answer 9

1:M
One tutor group contains many students, or many students belong to one tutor group.

Question 10

M:M

Answer 10

M:M
One student can be taught many teachers or one teacher can teach many students.

Question 11

Primary key

Answer 11

This is a unique identifier for each record in the table.
The primary key is shown by underlining it.

Question 12

Why would a primary key not be a suitable

Answer 12

It is not unique

Question 13

Foreign key

Answer 13

This 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.
The foreign key is shown using an asterisk.

Question 14

Indexes

Answer 14

Database automatically maintains an index of primary keys so that a specific primary key can be located quickly and easily.
It provides a position of each record according to its primary key, makes it quicker.

Question 15

Secondary key

Answer 15

It allows a database to be searched quickly.
Therefore, a secondary index (secondary key) is set up on the surname attribute. This makes it possible to order and search by surname which makes it easier to find specific students in the database.

Question 16

why would it be suitable secondary key

Answer 16

It is likely to be used to search for / index

Question 17

Handling databases

Answer 17

Handling databases goes in four ways:
- Capturing
- Selecting
- Managing
- Exchanging

Question 18

Capturing

Answer 18

Paper based forms.
A large amount of data is still captured using paper-based data capture forms.
Data input is mainly manual: human reading the from and typing the info into a computer-based system.

To help reduce the chance of the person inputting the data incorrectly:
- Every part of the form clearly labelled
- Instruction to complete the form in a black pen
- Instruction to complete form in capital letters
- Use of tick boxes
- Squares for entering each latter separately

Question 19

Optical character recognition

Answer 19

CD Often used in post office and road cameras.
To speed up + automate data input, we use OCR.

It automatically reads text by interpreting the shape of the letters, works better with printed text than handwriting.

Question 20

Optical mark recognition

Answer 20

CD Often used for multiple choice texts and lottery tickets.

The fast and efficient way of collecting data and inputting it in a database while significantly reducing the possibility of human error.

Question 21

Selecting data SQL

Answer 21

Once captured the info in a DB, we need an efficient way to retrieve it.
Structured query language
SQL allows for fast and efficient retrieval, selection and manipulation of data using a simple set of commands: SELECT/FROM/WHERE

Question 22

Query by example

Answer 22

SD First graphical query language, making use of visual tables where the user would enter commands and conditions.

Once a query is built using QBE, its converted into statements that can be executed against the database.
By utilizing QBE, the user doesn’t have to remember the finer details of SQL syntax.

Question 23

DBMS

Answer 23

MD Provide a layer of abstraction for the user and programmer.
Hides the underlying structure of the data and ensures it remains integral by:
- Preventing the creation of duplicate primary keys
- Enforcing validation rules
- Providing secure access
- Providing encryption
- Providing program data independence
- Managing multiple users

Question 24

Exchanging data

Answer 24

Last of all, we need to make sure we can exchange data between different databases/other systems/applications such as:
- Spreadsheet software
- Accounting programs

To do this, we need to consider: Common formats used for exchanging data/ manual methods + of automatic exchange data exchange

Question 25

Common formats

Answer 25

XML and JSON, both human readable, open formats for structuring data. Both designed for strong and transporting data.
One system exports data in, another system can accept the same data and be sure of the format it will arrive in.

CSV is another format, each record is stored on a separate line in the file, and each field is separated by a comma.
The structure is fixed and know, so import routines can be written to extract the data from a CSV file.

Once the format for data exchange has bene agreed, we still need to exchange the data and consider how data will move from one system to another.

Question 26

Manual methods

Answer 26

• Memory stick
• Optical media
• Removable hard disk
• Email
• Paper-based

Question 27

Automatic methods

Answer 27

Two databases interface with one another so they can read and write directly to and from each other tables: a live connection method is known as electronic data interchange (EDI) which is a protocol between 2 systems to facilitate the exchange of data.
- Increases the speed of data transmission and the efficiency of processes for users.
- This process can be entirely automated without any human involvement.

Question 28

normalising

Answer 28

A way of structuring the data in a relational database according to formal rules, in order to avoid problems of efficiency and security in accessing and maintain the data

Question 29

0NF

Answer 29

flat file before normalisation

Question 30

1NF

Answer 30

All field names must be unique
Values in fields must be the same domain (same type of data)
Values in fields must be atomic (one item in each field)
No two records can be identical (remove duplicates)
The table has a primary key (composite is easiest to move to 2NF)

Question 31

2NF

Answer 31

Be in 1NF
Partial dependencies removed
(move fields to new tables to remove duplicate fields)”
Must remove any duplicates created to continue to be in 1NF
Primary/foreign keys must be added to be in 1NF & 2NF
Linking tables used to remove M:M relationships

Question 32

3NF

Answer 32

Be in 2NF
Remove transient dependencies
(move or remove fields dependent on non-key fields)

Question 33

problems that arise from
transaction processing, and how these can be overcome

Question 34

record locking prevents the overriding of data, and understand how record locking takes place

Question 35

why would a form be considered 1NF

Answer 35

-No Repeating fields/data
-Data is atomic
-Has a primary Key

Question 36

why would a form be considered 2NF

Answer 36

Is in First Normal Form
-Every field is dependent on the primary key.

Question 37

methods/format to provide data

Answer 37

CSV/Comma Separated Value (file)…
- A (text) file/format with values separated by
commas (or some other delimiter)

• XML/eXstensible Markup Language…
• …A markup language that uses tags to denote
  data.
• SQL/Structured Query Language…
• …A language for creating/querying databases

Question 38

SQL

Answer 38

A declarative language, meaning it expresses what needs to be achieved as opposed to a procedural language, which would express the logic of how something is achieved, used to manipulate databases.
It enables efficient retrieval, deletion and manipulation of data held in RDB.
Scope of SQL allows us to:
- Query data
- Manipulate data
- Define data
- Control data access

Question 39

SQL select

Answer 39

SELECT statement is used to collect fields from a given
table and can be paired with the FROM statement to specify
which table(s) the information will come from.

Question 40

SQL order

Answer 40

The ORDER BY part of the code specifies whether you want it in ascending or descending
order. Values are automatically placed in ascending order and adding ‘Desc’ to the end of
statement will cause values to be displayed in descending order

Question 41

SQL insert

Answer 41

Question 42

SQL delete

Answer 42

Question 43

SQL update

Answer 43

Question 44

referential integrity

Answer 44

Referential integrity is a data quality concept that ensures that when you make changes to data in one place, those changes are reflected in other related records.
This is done by enforcing a rule that says that the foreign key in one table can only refer to the primary key of another table. This means that if you change the information in one column, it will automatically be updated in all other related columns.

Question 45

transaction processing

Question 46

ACID

Answer 46

Atomicity Consistency Isolation Durability.
A relational database that fails to meet any of these four goals cannot be considered reliable.

Question 47

atomicty

Answer 47

Atomicity guarantees that there are only two possible outcomes from a task (known as a transaction) that involves changing multiple sets of interdependent data: either every step in the task completes successfully and can be committed to the database as a single unit or, if one step should fail, all other steps can be undone (“rolled back”), leaving the global state of the database unchanged.

Question 48

consistency

Answer 48

Consistency is the capability of the database engine to protect a database from any change of state that could leave any set of data in out-of-synch with any other set.
Referential integrity is the process of ensuring consistency. This ensures that information is not removed if it is required elsewhere in a linked database.
The practical implementation of such dependencies is by means of declarative referential integrity constraints (“foreign keys”) enforced by automatically-generated triggers.

Question 49

isolation

Answer 49

Isolation refers to the capability of the database to allow each user (or transaction) to operate as though it were the only user (or transaction).
The isolation mechanism must be capable of keeping each transaction’s view of database state consistent as long as that transaction is running, regardless of any changes that are performed by other transactions.

Question 50

durability

Answer 50

Durability ensures that changes made to the database (transactions) that are successfully committed will survive permanently, even in the case of system failures. They will be committed to permeant storage. This ensures that the data within the database will not be corrupted by:
Service outages
Crashes
Other cases of failure
Durability is achieved through the use of changelogs that are referenced when databases (or portions of the database) are restarted.

Question 51

record lock

Answer 51

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 others cannot access the same record.

Question 52

Redundancy

Answer 52

There is some information that people and companies cannot afford to lose. 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 can be recovered.