Theory: Databases Flashcards
Describe the reasons for database normalisation (6)
-efficient without compromising data integrity
-no redundant or repeated data
-faster searching and sorting
-easier to maintain
-duplication of data minimised
-improved data consistency
What is needed to create a data model?
Data requirements
Describe a data model
An abstract model of which entities to record and which attributes of each entity
What does each entity in a table have?
A row/record
Describe the concept of an entity identifier
-sometimes called primary key, or composite key.
-a unique value used to identify a single record
-primary keys are single attributes/fields that use only one field to determine a unique value
-composite keys (or composite primary keys) use multiple attributes/fields to form a unique value for a record.
Describe an entity description:
-eg. Customer(customerID, customeremail)
-describes how information about an entity should be stored in a table.
-the name of the table as well as field names within the brackets
How is the entity identifier/primary key identified in an entity description?
A value can be underlined
Describe the concept of a relational database
-a database containing multiple tables that are linked by common attributes.
What relationship type is not supported by relational databases?
Many to many
Describe the concept of a foreign key
-an attribute/field of a table that is the primary key of another table
Describe first normal form
-no repeating attributes
-data is atomic, all fields contain one value and only one. Two values can not be stored in one field.
Describe second normal form
-partial key dependancies are removed
-For a database with a composite primary key, attributes that don’t depend on all values that make the composite key, are removed to form another table
Describe the concept of third normal form
-no non-key dependancies
-all non key attributes depend on the key, the whole key and nothing but the key.
Properties of SQL
-declarative, readable
List as many SQL commands as you can
-SELECT
-UPDATE
-INSERT INTO…VALUES()
-DELETE
-WHERE
-FROM
-UPDATE
-SET
-ORDER BY
-CREATE (TABLE)
-VARCHAR(size)CHAR(size)
-INT(size)FLOAT(size)
what is the wildcard you’ve learned?
-*
Describe the complexities of a client server database
-simultaneous access supported for multiple clients
-issues arise when clients attempt to access the same field-concurrent access
-can result in updates being lost
List some methods used to preserve data integrity in a client server database
-record locks
-serialisation
-timestamp ordering
-commitment ordering
Describe record locks
-when a record is accessed by one client/user, it is immediately locked.
-other clients/users are blocked from accessing or modifying the record until it is unlocked
Describe serialisation
-placing other user requests in a queue to be serially executed after the current user is finished
Describe timestamp ordering
-commands performed on a field are assigned a timestamp and are carried out in the order of the timestamps
Describe commitment ordering
-an algorithm determines the order by which to execute commands on the same field based on reducing the impacts of commands on other parts of the database.
How are many to many relationships handled with databases?
A link table is created that utilises the unique identifier of values to map entities to each other.
Why is database normalisation needed?
So that databases can be efficient without compromising the data integrity. It ensures that entities contain no redundant or repeated data.
How can the data of a database be defined in first normal form?
atomic- no single column contains more than one value.
What does the term atomic mean, when referring to data in a database?
No single column contains more than one value.
What is concurrent access?
When two people access a database at the same time
What is the problem with concurrent access?
Can result in updates being lost if two users edit a record at the same time
What are some processes used to prevent concurrent access?
-record locks
-serialisation
-timestamp ordering
-commitment ordering
What are some processes used to prevent concurrent access?
-record locks
-serialisation
-timestamp ordering
-commitment ordering
