Databases Flashcards
Data model
An abstract model of which things to store and what information about them should be recorded
Entity
A thing about which data is to be stored
Attributes
Characteristics or other information about entities
Databases
Tables used to store multiple entities
Entity identifier
An attribute given to each entity that is unique within the table
Entity description
The name of the table is shown outside of brackets (which contain each of the attributes)
Relational databses
The tables in a database are connected by common attributes
Entity relationship diagrams
Used to graphically represent the relationships between tables in a database
Primary key
An attribute that provides a unique identifier for every entity in the database table
Foreign key
The attribute that is the primary key in the other, connected table
Composite primary key
Combining multiple attributes that act as a primary key
Many-to-many relationships
A new linking table must be created
Database normalisation
The process of making a database efficiency without compromising the integrity of the data
Advantages of normalisation
- Reduced redundancies
- Faster searching, sorting and querying of data
- Easier maintenance
- Improves data integrity
First normal form
No repeating attributes (data must be atomic)
Second normal form
- Be in 1NF
- No partial key dependencies
Partial key dependencies
When a non-key attribute doesn’t depend on the entire composite primary key
Third normal form
- Be in 2NF
- Have no non-key dependencies
Non-key dependencies
When a non-key attribute is dependent on other no-key attributes instead of the primary key
SQL
An easy to learn, declarative language used with databases
SELECT
- Used for retrieving data from a database table
- SELECT <attribute> FROM <table> WHERE <condition></condition></attribute>
UPDATE
- Used for modifying the attributes of an existing entity
- UPDATE <table> SET <attribute> = <value> WHERE <attribute> = <value></value></attribute></value></attribute>
DELETE
- Used for removing entities from a database
- DELETE FROM <table> WHERE <condition></condition>
INSERT
- Used to add new records to an existing table
- INSERT INTO <table> (<column1>, <column2>, …) VALUES (<value1>, <value2>, …)</value2></value1></column2></column1>
- INSERT INTO <table> VALUES (<value1>, <value2>, …)</value2></value1>
Wildcard
- Used to specify any possible value
- *
CREATE
- Used to make new database tables
- Specifies name, attributes and data types
Datatypes
- CHAR
- VARCHAR
- INT
- FLOAT
- DATE
- DATETIME
- TIME
- YEAR
Client server databases
A database that allows for simultaneous access for multiple clients
Concurrent access
An issue where multiple different users try to access the same field at the same time
Record locks
- When a record is accessed by one user, it is immediately locked to other users until
the first user has finished using it - Other users are blocked from accessing or modifying the content of a field until it
has been unlocked
Serialisation
Requests from other users are placed in a queue, executed sequentially
Timestamp ordering
When multiple commands are sent to the same field in a database, each is
assigned a timestamp which marks the point in time at which the command was
initiated
Commitment ordering
An algorithm is used to work out an
optimum order in which to execute commands for the same field
