Databases Flashcards
What is a database?
An organised collection of data
What is a DBMS?
Database management system
software that interacts with end users, applications and the database itself to capture and analyse the data
What are the 4 core database functionalities?
- Data definition
a. Creation, modification and removal of definitions that define the organization of the data - Update
a. Insertion, modification and deletion of actual data - Retrieval
a. Providing information in a form directly useable or for further processing by other applications - Administration
a. Registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity etc..
What is a database server?
Dedicated computers that hold the actual database and run only the DBMS and related software.
Name 2 types of database
- Relational
2. NoSQL
What is a relational database?
e.g. oracle, mySQL
Composed of:
- Tables (row and column)
- records (row)
- Fields (part of a record containing a single piece of info)
Relationships between tables are represented by foreign keys
What is ACID?
Set of properties of a database transaction to guarantee validity even in the event of power failures or errors
- Atomicity- each transaction treated as one unit either completes or complete fail (no partial)
- Consistency- either creates a new state or returns dat a to original state
- Isolation- each in process or not committed must be isolated from any other transaction
- Durability- Guarantees that once a transaction has been completed it will remain committed regardless of error/power failure
What is SQL?
Structured query language- used to extracting data and manipulating a database
What is noSQL?
A way of storing and retrieving data that is not based on tabular relations used in relational databases
What are the 4 types of noSQL databases?
- key-value
- Document e.g. MongoDB uses xml or json
- column
- graph
What are the 7 steps of database design?
1, Determine the purpose for storing the data
- Find and organise the information required to be stored e.g. names and types of items
- Divide information into tables- major subject
- Define columns for each subject base on the information that needs to be stored
- Specify primary keys (usually numbers)
- set up relationships- one to one, one to many etc.
- apply normalization rules and make adjustments as necessary
What is database normalization?
The process of structuring relational databases to reduce data redundancy and undesirable characteristics like insertion, update and deletion anomalies.
Usually normalized to 3rd normal form
To be in 1st normal form which rules must apply?
- It should only have single(atomic) valued attributes/columns.
- Values stored in a column should be of the same domain
- All the columns in a table should have unique names.
- And the order in which data is stored, does not matter.
To be in 2nd normal form which rules must apply?
- It should be in the First Normal form.
- And, it should not have Partial Dependency.
a. Partial Dependency exists, when for a composite primary key, any attribute in the table depends only on a part of the primary key and not on the complete primary key.
b. When you have a composite key to uniquely identify rows in a table and one or more columns are only dependent on part of the composite key and not both
i. I.e. student score example- the teacher is only dependent on the subject not the student
c. Removing patient dependency field from the scores table and placing it in the subjects table
To be in 3rd normal form which rules must apply?
- It should be in second normal form
- And, it doesn’t have transitive dependency
a. When non-prime key attributes depend on other non-prime attributes rather than depending upon the prime attributed or primary key
b. i.e. exam ID needs an independent table
