Chapter 2 : Database Design Concepts Flashcards
field
a single bit of info about a person or an item, e.g. age
record
a group of related fields about a person or item that is captured in the table
primary key
a compulsory field that holds a unique (one of its kind) identifier for each record in the database
what does each table consist of?
fields and records
what do you use a PK for?
refer to specific record in tbl, only to that one
which field to use as PK?
- can use existing
- better to create new field
- ensures no duplicates
how is data stored?
1s and 0s - binary code
how does the computer read these 1s and 0s?
uses data structures that describe specific sequence for data to be organised to organise, understand relations between data + interpret the 1s and 0s
how is data and instructions communicated through computers?
- data (all types) converted into computer-readable form (binary)
- processed
- data converted into human-readable form > meaningful info > knowledge > understood and used
how do DBs begin?
valuable metadata and data
what is metadata?
a set of data that describes and gives info about other data
what model is used to understand the progression of data?
the infoneering model
draw the infoneering model
check later
characteristics of a good DB
- 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
Dontneedtoknow what are the other characteristics of a good DB
all the ‘data’s - think characteristics of quality data
Dontneedtoknow characteristics of quality data (in context of DB)
- data integrity
- data independence
- data redundancy
- data security
- data maintenance
Dontneedtoknow data integrity
accuracy and consistency of data in DB
Dontneedtoknow DB with low data integrity
missing info + incorrect info
Dontneedtoknow data independence
separation between data and app in which it is used
allows updating of data in app without recompiling whole app
Dontneedtoknow data redundancy
presence of exact same data at different places in DB
should be stored so no repeats in diff tbls
Dontneedtoknow issues caused by data redundancy
increases size of DB
integrity issues
decreases efficiency
anomalies
Dontneedtoknow data security
how well data in DB protected from crashes, hacks and accidental deletion
Dontneedtoknow data maintenance
regular scheduled tasks run to fix errors within DB, detect potential/future errors + prevent anomalies
how is normalisation carried out?
divided into normal forms
what are the normal forms?
1NF - first normal form
2NF - second normal form
3NF - third normal form
what are the forms?
forms that tables are in if they meet certain requirements
rules for 1NF
- each column must have a separate field/attribute/not contain multiple values
- values stored in column of same kind/type/domain
- all columns have unique names to avoid confusion at retrieval of data / other operation
- order of data storage doesn’t matter
rules for 2NF
- be in 1NF
- not have Partial Dependency
what is Partial Dependency?
where attribute/field in tbl depends on part of PK and not whole key
rules for 3NF
- be in 2NF
- not have Transitive Dependency
what is Transitive Dependency?
where an attribute/field in tbl depends on other attributes/fields rather than depending on the PK - indirect relationship between values in same tbl
