4.10 Databases Flashcards
Define Attribute/Field
An individual fact, detail, or characteristec of an entity (also known as a field)
Define Database
A persistant store of related information
It is used to store data in a way so that relevant information can then be retrieved effectively and eficiently when required
They typically consist of one or more tables
There are 6
What are the componenets of a database? What are they defined as?
- Atrribute - Single fact, detail, characteristic of an entity/piece of information
- Entity - a thing, person, object or relationship about which data can be collected about
- Primary Key - A field in a record which is used to uniquely identify the record within a table
- Foreign key - A field in one table which is the primary key in another table, it is used to refference a record in another table/join the tables together
- Record - A single row of data/ a collection of attributes/fields about the same object
What is normalisation? What are its aims?
Normalisation a process which is used to structure and organise the database in a way that improves the overall efficiency and integrity of it.
Its aims are:
- To reduce data redundancy (repeating data), which improves storage usage which can be cheaper
- Less data to search through - faster to run queries
- Improve data consistency (change in one places** occurs everywhere)**
- Improve** efficiency of queries**
- Eliminates data anomalies (does not damage refferential integrity)
- Reduction in repeated data means less risk of mistakes (data integrity)
What is a relational database?
Databases can grow very large so it is important to keep it in an efficient format
A relational database is an organised persistant store of data which is stored in several tables. They allow users to specify information about the different tables and the relationships between them.
They are a a database with multiple linked tables
They aim to improve efficiency, accuracy (data integrity) and the size of the data needed to be looked at to retrieve the required information.
How do we describe relationships in databases?
A relationship is denoted by a line between tables, and is used to show how two tables are linked togther. They are usually identified with a verb.
What are the 3 types of relationships?
one - to - one (one entity linked to one in another table)
many - to - one (one entity linked to many in another table or vice versa)
many - to - many (many entities linked to many in another tables)
What are some general advantages of using databases (according to Craig ‘n dave)?
- Make processing data more efficient
- Reduces storage requirements
- Avoids redundancy (repeated data)
- Allows for multiple users to see relevant data
What is a flat-file database?
Allows users to specift data attributes (columns, datatypes etc) for one table at a time. There is no link or relationship between tables. This could lead to data redundancy
How do we get from 0NF to 1NF?
- Separate out attributes into their own columns so there are no atomic attributes
- Ensure there are no repeated columns (delete as necessary)
- Identify a primary key (or composite key)
- Get rid of groups of repeating data
How to get from 1NF - 2NF?
- Check if data is in 1NF
- Remove partial key dependancies (when an attribute only relates to/relies upon one part of the primary key) - only if composite key exists
- If we find a partial key dependancy, we split up the table. This can create a many-to-may relationship which needs to be separated (create linking table)
How to get from 2NF to 3NF?
- Check table is in 2NF
- Remove any non-key dependancies (any attributes that do not rely on the primary key/will be unnaffected by it. Separate it into a new table
“All attributes must be dependant on the key, the whole key, and nothing but the key”.
- Aim is to remove repeating data
When drawing ER diagrams, what’s the one really stupid thing you have to remember???!
When drawing ER diagrams, make sure you draw ALL POSSIBLE COMBINATIONS, both theoretical and real. E.g. if there is a m-m relationship, show the triple double sided crows foot AS WELL AS the o-m relationship between the LINKING TABLE
What are the rules of ERD notation?
- Capitalise table names
- All the attributes are listed in brackets after table names
- Attributes are one word (first letter capitalised)
- Primary key field(s) are underlined and listed first
What are some benefits and uses of a DBMS?
- Controls the data kept on a database
- Maintains integrity
- Ensures there is only ever one version
- Controls data:
- Access rights
- How data is stored
- Location of data
What is a database server?
… holds and manages the database itself so that all ammendments, queries etc are carried out on the server in one place
What is the first solution to solving/addressing the data loss problems on databases with concurrent access?
Record locking:
- As soon as a user with write access takes an item of data, the DBMS will lock the record. Any other user that attempts to access the data is blocked until the first user commits a query.
What is the second solution to solving/addressing the data loss problems on databases with concurrent access?
Serialisation:
- Allows transactions in a database to take place one at a time in a serial format, to ensure each process is carried out in the correct sequence to avoid comprimising data integrity.
- When two users access data at the same time, when one user commits an action with the data, the other user is temporarily blocked from comitting a transaction. They must commit a rollback to retry the query
What is the third solution to solving/addressing the data loss problems on databases with concurrent access?
Time stamp ordering:
- The database server attaches a time stamp to each transaction to ensure they are carried out in the correct order by comparing the time stamp of the current transaction to the last transaction.
What is the fourth solution to solving/addressing the data loss problems on databases with concurrent access?
Commitment ordering:
- Uses an algorithm to determine the optimum in which to carry out transactions to avoid issues such as deadlocks from occuring.
- A deadlock is where multiple, often complex, updates are attempted and there is a chain of dependance which causes each of the updates to wair for another to finish (infinite loop)
