Databases

Question 1

define database

Answer 1

persistant organised (stored in records and fields) store of related data

Question 2

define an entity

Answer 2

A real thing like people or customers (the table name in the database)

Question 3

define a table

Answer 3

collection of data that relates to an entity (customers)

Question 4

define a record

Answer 4

collection of data about one singular entity (rows)

Question 5

define field

Answer 5

a unique piece of data about an entity i.e (student firstname) (collumns)

Question 6

define a flat file database

Answer 6

a table with multiple entities
i.e table with data on both customer and item that they buy

Question 7

why is a flat file database bad

Answer 7

because everytime a customer buys an item all their data needs to be re added to the table so if address changed it has to be changed on every order

Question 8

when would an intersection table be used

Answer 8

in a many to many database

Question 9

define primary key

Answer 9

A field which has a unique value for every record
A unque identifier

Question 10

how does an intersection table work

Answer 10

uses the primary keys from the needed records in order to reference them i.e customer 3 bought procuct 17

Question 11

define secondary key

Answer 11

a second unique identifier Not always present. film number (primary key) film name(secondary key)

Question 12

define foreign key

Answer 12

primary key used in another table (intersection table?)

Question 13

what is referential integrity

Answer 13

a given element that is referenced in another table is not deleted (not just records could be fields)

Question 14

What does ACID stand for

Answer 14

atomicity consistency isolation durability

Question 15

what does Atomicity mean (in ACID)

Answer 15

that a transaction is either entirely processed or not at all proccessed

Question 16

What does consistency mean

Answer 16

That the transaction cannot break the pre-exsisting rules of the database i.e a string is not added in place of an integer

Question 17

defin normalisation

Answer 17

a set or rules for a database to follow in order to be the most efficient

Question 18

what level normalisation is flat file

Answer 18

Un normalised

Question 19

what are the rules of first form normalisation

Answer 19

no collumns have repeating data (i.e maths,science) in one box

data must be in its atomic form i.e first name surname

must have a primary key

Question 20

rules of second normal form

Answer 20

must be in first normal form first

partial dependencies get split so any fields that only depend on one of the keys get split into a new table

one primary key

Question 21

rules of third normal form

Answer 21

all fields are fully dependent on the primary key
non key dependencies are split
i.e email is based of student name not subject

Question 22

Why use an intersection table [3]

Answer 22

to change many to many relationships
to make the database third normal form
reduces data redundancies

Question 23

1 pro and 1 con to having multiple keys [2]

Answer 23

pro , faster searching
con, larger storage or insert, update and delete actions will take longer

Question 24

Using an example, describe a scenario when a flat file database may be an appropriate solution. State an advantage of using a flat file instead of a relational database. [3]

Answer 24

when the data bse only contains one type of entity
personal contacts list (only contains phone numbers)
easier to use or import into another system

Question 25

state two advantages of a fully normalised databases over flat file databases [2]

Answer 25

less storage is required
data only has to be stored once
queeries can be handled easier
improved usability
better data consistency and forces referential integrity

Question 26

methods of capturing data

Answer 26

Optical character recognition
Optical Mark Recognition
form
QR code

Question 27

Methods of Selecting data

Answer 27

SQL
QBE (query by example)

Question 28

Methods of managing data

Answer 28

SQL
built in DBMS

Question 29

Methods formatting data being exchanged

Answer 29

XML, JSON and CSV

Question 30

Methods of exchanging data

Answer 30

manual methods like memory stick
EDI (protocol to allow two systems to facilitate data exchange)

Question 31

SQL for inserting a piece of data into a database

Answer 31

INSERT
INTO table name (field 1,field2…..)
VALUES (value1,value2,…..)

Question 32

SQL for deleting a record

Answer 32

DELETE
FROM table name
WHERE conditions

Question 33

SQL for selecting two fields from different tables

Answer 33

SELECT
FROM Table1
JOIN Table2
ON Table1.field in common = Table2.field in common
WHERE

Question 34

SQL for a updating a record

Answer 34

UPDATE table name
SET field1 = value1,field2 = value2
WHERE