1.3.2 d + e + f – Databases - DONE
No description yet. This curated deck is ready for daily study.
Card preview (19)
How do you select data in SQL?
Using SELECT field FROM table WHERE condition
How do you use SQL to find an item that starts with, ends with, or contains a specific character
[WHERE condition] becomes: WHERE condition LIKE character% when searching for items that start with the character WHERE condition LIKE %character when searching for items that end with with the character WHERE condition LIKE %character% when searching for items that contain the character
How do you select the items in all fields of a record?
Using SELECT *
How do you implement multiple conditions?
Using AND and/or OR
How do you add a record into a database using SQL?
By inserting the record’s data using: INSERT INTO table (field1, field2, field3) VALUES(value1, value2, value3) E.g. INSERT INTO world(name, capital, population) VALUES(“Oz”, “Emerald City”, 1000000)
How do you delete a record/records from a table?
Using: DELETE FROM table WHERE condition
How do you join tables?
Using: SELECT table.field FROM table JOIN table2, ON table1.field = table2.field WHERE condition The specified field is the foreign key. E.g. SELECT Student.Surname, Tutor_Group.Tutor room FROM Student JOIN Tutor_Group ON Student.Tutor Group = Tutor_Group.Tutor Group WHERE Student.Tutor Group = “10B”
How do you delete a table?
Using: DROP TABLE table
Describe referential integrity
When relationships between databases are consistent, such that each foreign key links to an existing primary key.
Why is referential integrity desirable?
It ensures that information is not removed if it is required elsewhere in a linked database, in order to prevent the occurrence of missing data.
Describe a technique for ensuring referential integrity
Enforcing a cascade delete restraint on the primary relationship between tables mean that if a record is deleted from a table, any associated rows within a linked table will also be deleted. It can also be used to prevent a user from adding a record to a table if there isn’t a matching record for it in another table.
How can referential integrity be broken?
If a primary key is deleted or updated or if a foreign key is no longer valid.
What is transaction processing?
It refers to the handling of a sequence of database operations (a transaction). Each transaction must succeed or fail as complete unit, it can’t be partially complete. It ensures databases remain accurate and consistent.
What does ACID stand for?
Atomicity Consistency Isolation Durability
Describe how atomicity is used in a database management system
A transaction must be processed in its entirety or not at all.
Describe how consistency is used in a database management system
Any change in the database must retain the overall state of the databases. This ensures data integrity and is done through maintaining referential integrity.
Describe how isolation is used in a database management system
A transaction must not be interrupted by another transaction. This is done so other users or processes cannot access the data concerned. This is done through implementing record-locking so that they are placed in a read-only state.
Describe how durability is used in a database management system
Data is not lost in case of power failure. This can be ensured by storing completed transactions in secondary storage.
Describe record locking
The outcome of concurrent transactions is the same as if transactions were completed sequentially. Record locking allows only one user to access record level data at any one time. So data that is being used elsewhere cannot be modified. It’s the process of preventing simultaneous access to records in a database and it’s used in order to prevent inconsistencies or a loss of updates. However, this can cause delays as users wait for access. RECORD LOCKING DOESN’T LOCK THE WHOLE DATABASE, ONLY RELEVANT RECORDS