Database Fundamentals - Relational DBMS Flashcards
Relational DBMS:
- Are most widely used
- Use same principles across all offerings
- Are foundational for understanding other system
Relational Database Features
Databases are composed of tables
Databases are constructed with one OR more tables
Tables are the basic building blocks of a database, all your data is input into tables
What are tables?
Tables are formalised repeating arrays of data.
They consist of columns and rows.
Different tables would hold different types of data.
Tables are columns are defined UP FRONT
Day-to-day use is in creating and updating rows.
Tables require structure - they are not freeform
Tables must be structured by defining a column with the type of data to be input
Each subsequent row below this column header must follow the constraints of the data type associated with this column.
What are keys and primary keys?
Keys allow us to identify one particular row in a table.
Primary keys can be a column generated by a DBMS which contains only unique values which designate a single row.
Generated primary keys are also called synthetic keys or surrogate keys. This is a system generated value whose sole purpose is to act as a primary key.
Defining table relationships?
A primary key in one table can be added onto another table to define the relationship between rows between multiple tables. However these primary keys do not need to be unique values, they are known as foreign keys.
What are one-to-many relationships?
These are table relationships where a primary key can be associated with mutliple other rows on other tables as a foreign key.
E.g.
One customer has many orders
These go one way only
E.g.
One order cannot have multiple customers
What are many-to-many relationships?
This is a type of relationship between keys in different tables where many primary keys are associated with many primary keys in different tables
Problems with many-to-many relationships?
- Tricky to spot/not obvious
2. Most relationship DBMS cannot be used to express a many-to-many relationship directly; CAN BE INDIRECT INSTEAD
How do you indirectly make a many-to-many relationship?
Use a junction or linking table (acts like a middle man)
This sets a up multiple one-to-many relationships with non-unique values in the table
This table exists ONLY to link the first and second tables.
What are one-to-one relationships?
One row will be associated with only one other row in a different table.
HOWEVER this is redundant as these tables can often just be combined into a single table
What are Transactions?
A transaction defines a combined unit of work in a system where either BOTH events happen or NEITHER happen
E.g. bank transaction
Transaction between savings and checking; subtract from savings THEN add to checking. IF cannot subtract from savings OR transfer funds to checking then cancel transaction. Changes will be reveresed instantly.
Transactions must comply with the ACID test. What is the ACID Test?
Atomic - Transactions must be indivisible; either allg events occur successfully or neither occur at all and revert their state
Consistent - States throughout transactions must remain valid within the constraints of the database
Isolated - Data in transaction must be locked during transaction is occuring
Durable - Transaction must be robust; must be guaranteed when the transaction successfully occurs
What is structured query language (SQL)?
This can refer to either the query language or the DBMS
The language is a declarative query language. It is not a procedural, imperative language. This means that SQL is used to describe what you want to do, DBMS will do the work for you.
No need to describe the steps, just the outcome.
