Chapter 2 : Database Design Concepts

Question 1

field

Answer 1

a single bit of info about a person or an item, e.g. age

Question 2

record

Answer 2

a group of related fields about a person or item that is captured in the table

Question 3

primary key

Answer 3

a compulsory field that holds a unique (one of its kind) identifier for each record in the database

Question 4

what does each table consist of?

Answer 4

fields and records

Question 5

what do you use a PK for?

Answer 5

refer to specific record in tbl, only to that one

Question 6

which field to use as PK?

Answer 6

• can use existing
• better to create new field
• ensures no duplicates

Question 7

how is data stored?

Answer 7

1s and 0s - binary code

Question 8

how does the computer read these 1s and 0s?

Answer 8

uses data structures that describe specific sequence for data to be organised to organise, understand relations between data + interpret the 1s and 0s

Question 9

how is data and instructions communicated through computers?

Answer 9

1. data (all types) converted into computer-readable form (binary)
2. processed
3. data converted into human-readable form > meaningful info > knowledge > understood and used

Question 10

how do DBs begin?

Answer 10

valuable metadata and data

Question 11

what is metadata?

Answer 11

a set of data that describes and gives info about other data

Question 12

what model is used to understand the progression of data?

Answer 12

the infoneering model

Question 13

draw the infoneering model

Answer 13

check later

Question 14

characteristics of a good DB

Answer 14

• store all relevant data + meet all requirements
• relate tbls by means of a relationship
• multiple isolated simultaneous permitted user access
• multiple UIs depending on role

Question 15

Dontneedtoknow what are the other characteristics of a good DB

Answer 15

all the ‘data’s - think characteristics of quality data

Question 16

Dontneedtoknow characteristics of quality data (in context of DB)

Answer 16

• data integrity
• data independence
• data redundancy
• data security
• data maintenance

Question 17

Dontneedtoknow data integrity

Answer 17

accuracy and consistency of data in DB

Question 18

Dontneedtoknow DB with low data integrity

Answer 18

missing info + incorrect info

Question 19

Dontneedtoknow data independence

Answer 19

separation between data and app in which it is used
allows updating of data in app without recompiling whole app

Question 20

Dontneedtoknow data redundancy

Answer 20

presence of exact same data at different places in DB
should be stored so no repeats in diff tbls

Question 21

Dontneedtoknow issues caused by data redundancy

Answer 21

increases size of DB
integrity issues
decreases efficiency
anomalies

Question 22

Dontneedtoknow data security

Answer 22

how well data in DB protected from crashes, hacks and accidental deletion

Question 23

Dontneedtoknow data maintenance

Answer 23

regular scheduled tasks run to fix errors within DB, detect potential/future errors + prevent anomalies

Question 24

how is normalisation carried out?

Answer 24

divided into normal forms

Question 25

what are the normal forms?

Answer 25

1NF - first normal form
2NF - second normal form
3NF - third normal form

Question 26

what are the forms?

Answer 26

forms that tables are in if they meet certain requirements

Question 27

rules for 1NF

Answer 27

1. each column must have a separate field/attribute/not contain multiple values
2. values stored in column of same kind/type/domain
3. all columns have unique names to avoid confusion at retrieval of data / other operation
4. order of data storage doesn’t matter

Question 28

rules for 2NF

Answer 28

1. be in 1NF
2. not have Partial Dependency

Question 29

what is Partial Dependency?

Answer 29

where attribute/field in tbl depends on part of PK and not whole key

Question 30

rules for 3NF

Answer 30

1. be in 2NF
2. not have Transitive Dependency

Question 31

what is Transitive Dependency?

Answer 31

where an attribute/field in tbl depends on other attributes/fields rather than depending on the PK - indirect relationship between values in same tbl