Databases Flashcards
What is a data model?
An abstract description of the essential data within a given context and how the data is related.
What is an entity?
A district thing about which data must be stored.
What is an attribute?
A single property of an entity.
What is an entity identifier?
An attribute, or a collection of attributes, that uniquely identifies each instance of an entity.
What is a composite entity identifier?
Multiple attributes are required to uniquely identify an instance of an entity
What do you need to know when designing a data model?
- Data that will be stored
- Real-Word entities that this data refers to
- Relationships that exist between entities
- Constraints that belong to the data
How can a data model be visually presented?
- Entity Description
- Entity-relationship diagram
What is an entity description?
Entity(Entity Identifier, Attribute, Attribute, Attribute)
What is a relationship in an E-R diagram?
Two-way association or link between two entities
What are the different types of relationships in an E-R diagram?
- One-to-Many
- Many-to-Many
- One-to-One
How are entities represented in E-R diagrams?
A rectangle with the name of the entity inside it. The name is always singular.
What is a Relational Database?
A collection of tables between which relationships are created through common attributes (primary and foreign keys)
What is a primary key?
An attribute that will uniquely identify a particular instance of an entity.
What is a composite primary key?
Two or more attributes which together uniquely identify a particular instance of an entity
What is a foreign key?
An attribute in one table which is the primary key attribute in another table
How can we store required data from a data model?
- In an OOP language
- Relational Databases
How do we turn a data model into a relational database?
Entity —> Table
Attribute —> Field
Instance of an entity —> Record
What is a Flat File Database?
- Simplest way of storing data in a text file (e.g. CSV)
- Each line represents a record, with fields separated by commas
What are the advantages and disadvantages of flat file databases?
+ Easy to create and use for small databases
- Inefficient as the data grows
How does a relational database work?
- Organises data across multiple tables
- Tables related through common fields
What are the advantages of relational databases?
- Scales better as a data set grows
- Avoids data duplication/redundancy
- Avoids data inconsistencies
What is a data dictionary?
- Description of the tables of a relational database
- Presented as a table describing one entity/table in the database
- Shows constraints on data
What are the main data types used in databases?
- Integer
- Float
- Text - any length
- Varchar(X) - maximum length X
- Date
- DateTime
- Boolean
What is the structure of a SELECT query?
SELECT field1, field2, field3..
FROM table
WHERE condition
ORDER BY fieldname ASC|DESC
How is the LIKE key-word used in a query?
SELECT ….
WHERE fieldname LIKE “string%”
What are the different ways you can use LIKE and placeholder values?
“%string” = ends with the string
“string%” = starts with the string
“%string%” = contains the string
How can you use the BETWEEN key-word in queries?
SELECT … FROM …
WHERE date BETWEEN “01/01/2025” AND “31/01/2025”
(This is inclusive)
How can you query multiple tables?
SELECT table1.field1, table1.field2, table2.field1
FROM table1 JOIN table2 ON table1.foreignKey = table2.primaryKey
How do you update records in SQL?
UPDATE table
SET fieldname = value
WHERE condition
How do you delete records in SQL?
DELETE FROM table WHERE condition
How do you create a new table using SQL?
CREATE TABLE table_name
(
PrimaryFieldName dataType PRIMARY KEY,
Field1 dataType constraints,
Field2 dataType constraints,
)
What are some typical constraints on fields?
- NOT NULL
- UNIQUE
- PRIMARY KEY
- DEFAULT
How can you use INSERT INTO in an SQL query?
INSERT INTO tableName (field1, field2, field3)
VALUES (value1, value2, value3)
What is data normalisation?
All non-key attributes depend upon the key, the whole key and nothing but the key (and there are no repeating groups)
What is data redundancy?
Data that is unnecessarily duplicated within a database
What is an update anomaly?
Inconsistent data as a result of updating multiple instances of the same data
What can data redundancy result in?
- Increased storage costs: require larger storage capacities, including backups
- Reduced performance: makes queries much slower as more data to process
- Data inconsistency
What are the benefits of normalisation in databases?
- Minimising data duplication
- Eliminating data redundancy
- Eliminating data inconsistencies
- Eliminates update and insertion anomalies
What are the different degrees to which data can be normalised called?
Normal forms
What is a client-server database?
A specific instance of a client-server application that provides simultaneous access to a database for multiple clients
What is the lost update problem?
When two users attempt to update the same record simultaneously resulting in one of the updates being ‘lost’
What is a record lock?
A ‘lock’ applied to the record when a transaction on the record starts
What are the advantages of a client-server application model?
- Data is stored in one place
- Centrally managed and backed up
- Data can be easily shared across client services
Why is it better for data and resources to only be stored in one place in a client-server database?
- There is no need for duplication and distribution of data
- No redundant data
- Risk of data integrity issues if data is out-of-date
What are the disadvantages of the client-server application model?
- Everything could be lost or inaccessible if the server is unavailable
- Simultaneous access of resources can cause congestion and poor performance
- Multiple user access for a resource can result in conflict
What are some ways to deal with concurrent access?
- record locks
- serialisation / transaction queueing
- timestamp ordering
- commitment ordering
What are the disadvantages of record locks?
- They can result in deadlock
- They do not scale well for large databases and lead to performance issues
What is concurrent access?
When different users try to access the same record at the same time
What does 1st normal form require?
- Atomic data
- Each record has a primary key
- No repeating groups
- No duplicated records
What does 2nd normal form require?
- 1st normal form
- No partial key dependencies
What does 3rd normal require?
- 1st and 2nd normal form
- No transitive partial key dendancies
What is atomic data?
Data that has been fully decomposed into multiple attributes
What are repeating groups?
Two or more fields store data for the same attribute with a single record
What are partial key dependencies?
When there is a composite key, not all non-key attributes relate to the whole key
What are transitive partial key dependencies?
Non-key attributes that depend/relate to other non-key attributes
What is an insertion anomaly?
Two clients insert records into a database simultaneously that create a logical conflict
What is a time stamp?
A value that marks the point in time at which a transaction was started
What is a transaction?
A single unit of work in a database that can consist of one or more queries
When is a transaction committed?
If all operations are successful.
When is a transaction abandoned?
If any operations fail.
What is deadlock?
When two or more transactions have placed locks on records that they are dependant upon
What is serialisation?
Handles concurrent access issues by ensuring that transactions are done in a logical order so they do not interfere with one another.
What is transaction queuing?
Transactions are put into a processing queue such that when one is finished the next is started.
What is timestamp ordering?
- Each transaction is assigned a timestamp
- Older transactions are processed before newer ones
- Databases records timestamps of last successful read/write transactions for each record
- Server applies rules to transactions before committing to determine whether it will result in the loss of data integrity
In timestamp ordering, what does the database store for each record?
The timestamp of the last successful read or write transaction for each record.
What are the timestamp ordering rules?
- Read: TS(T) >= WT(X)
- Write: TS(T) >= RT(X)
What is commitment ordering?
An algorithm is used to determine an optimum order to perform transactions by tracking conflicts so that they can be resolved in the correct order or abandoned.
What is a commitment in a database?
The permanent application of a change to the data in a database as a result of a transaction
How do record locks work?
- Maintain information about which records are currently being accessed
- When a user tries to access a record, consult this information and only permit access if it is not currently being used
