L5; Databases, SQL Queries & Other data sources Flashcards
what is databases?
a database stores data in an organised way so that it can be searched, retrieved and analyses later.
ex mysql, Microsoft Access
there are two main types of database; Transactional and Analytical.
Transactional database
(Online Transaction Processing)
the user is interested in creating, reading , updating and deleting records.
is highly normalised and contains one or more data tables.
data can be inserted, retrieved, updated and deleted from a table.
the relationships between the tables are important.
OLTP are important to avoid;
poor performance (speed of input, searching and deletion)
the inability to accomodate new features (data storage),
low- quality or repetitive data will be very costly to rectify once a database evolves.
Analytical database
(Online Analytical Processing)
the user is interested in analysis, reporting and clustring.
OLAP: Normalised approach created by BIll Inmon uses a top-down approach and is in 3NF.
or created by Ralph kimball is structured as the data warehouse is a de-normalised star or snowflake schema.
the data is much larger and uses a bottom-up approach.
A primary key and foreign Key
Primary Key is set as a normal attribute that is always unique (student ID) and generated by the database management system (DBMS). A generated key is often referred to as a globally unique Identifier (GUID)
Foreign Key is a column/ field in a table that matches the primary key of another table. These keys are used to create relationships between tables.
normalisation
is a process that leads to an efficient database design.
The primary goals of normalisation are to:
Avoid redundant data (not store the same data in multiple tables)
Ensuring data dependencies make sense (only storing related data in a table)
Normalisation guidelines have been developed which are referred to as normal forms.
Normal forms are numbered from one to six
in practice you should be concerned about reaching 3NF.
