Database types (2)
Flat-file : stores all data in one table with redundant data present
relational: stores data in separate linked tables // data redundancy is reduced // has tables with primary & foreign keys
Advantages of relational databases
PROS
- Saves storage space
- Related fields automatically change if one is modified
- Editing data is easier as less data must be entered
Disadvantages of relational databases
CONS
- Takes time to design
- Harder to set up as number of tables increase
- May be difficult to set up complex queries
Advantages of flat-file databases
PROS
- doesnt need planning as data is unorganised
- Relationships aren’t required
- No expertise required
Disadvantages of flat-file databases
CONS
- uses a lot of storage
- each record has to be edited
- data is harder to edit as there are many fields
Database relationships (3)
one-to-one -> one record is linked to only one record in another table
one-to-many -> one record is linked to many records in another table
many-to-many -> many fields are linked to many fields in another table ^`
One-to-one relationships
one record is linked to only one record in another table
the fields must contain the same data
One-to-many relationships
One record is linked to many records in another table
the link uses a primary key and foreign key (which uses referencial integrity)
Many-to-many relationships
it is only theoretical / conceptual
many fields from one table are linked to many fields on another table
done using a link table (two one-to-many relationships)
Key fields (3)
Primary key -> a field in a table that enables a record to be uniquely identified. used in databases as indexes
Foreign key -> a field in a table that has the same data as a primary key. used to link tables
Compound key -> a primary key that uses two or more fields to help a field be uniquely identified
Referential integrity
Forces relationshipsd to be consistent and avoids redundancy by forcing the data in the foreign key to always have the data in the primary key
What is normalisation?
The process of structuring data in a database into a formal method that is structured correctly with no / less redundant data.
Normal forms (4)
- Un-normalised
- First normalised (1NF)
- Second normalised (2NF)
- Third normalised (3NF)
Un-normalised data
A single table that contains redundant data
First normalised data (1NF)
- Has atomic data
- Data in fields does not repeat
- has a unique key for each record (primary or compound)
Un-normalised data
A single table that contains redundant data
Second normalised data (2NF)
- non-key attributes’ fields that are a part of a compound key are removed and put to a new table and linked back.
(for example, any data about customer ID is removed and put into a separate CUSTOMER table and linked back to the main table). - only applies to tables with compound keys
Third Normalised Data (3NF)
- Non-key attributes are removed and linked back.
(eg any price data is removed and linked back to the main table)
Advantages of normalisation
:-)
- smaller file size
- data is grouped logically using ref. integrity
- searching for data is faster (less data)
- changes made to one record are automatically changed
- making changes is easier as there is less data
Disadvantages of normalisation
:-(
- takes more time
- requires more expertise / knowledge in databases
- difficult to set up complex queries
- Data processing is slower
- Location of data may be difficult to determine
Access types (3)
Sequential access
Indexed sequential access
Direct access (Random access)
Sequential Access
An access type where records are found by going through all records until the record is found
Indexed sequential access
An access type where records are indexed in a particular order, used to find the point on the disk where the record is located instead of going through all of them.
Direct access (random access)
Each record has a unique key, the computer uses this key to go find the file in the disk.
