Week 8 Flashcards
What does Auto Increment do?
Auto Increment allows a unique number to be generated automatically when a new record is inserted into a table
Typically for primary key
In MySQL, assign AUTO_INCREMENT to a column
What is an example of Auto Increment in SQL?
ALTER TABLE Persons AUTO_INCREMENT 100;
This starts the number at 100
And will increment up or down depending on what is coded
What is an Index?
A data structure (e.g., a B-Tree) designed to improve the speed of data retrieval from a table
What are the benefits of using Indexes in Databases?
Indexes help optimize query performance by allowing faster lookups, similar to a book’s index
Using indexes is particularly helpful for columns that are frequently searched, filtered, or sorted
What is a View in SQL?
A View is a virtual table based on the reulst-set of an SQL statement
eg.
CREATE VIEW AS
SELECT C2, C3, C5, C7
FROM T1
INNER JOIN T2…
INNER JOIN T3…
What are the 2 types of Views in SQL?
Horizontal View - Focus only on rows
Vertical View - Focus only on columns
You can also join Views to get a Grouped and joined view
What is View Updatability?
All updates to base table are immediately reflected in all views that encompass that base table
Similarly, we may expect that if a view is updated then the base table(s) will reflect that change
But not all views are updatable
What is required for a View to be updatable?
The DBMS must be able to trace any row or column back to its row or column in the base table
eg. cant use functions like join or aggregation only simple ones to make sure the view is updatable
What are Migrating Rows?
CREATE VIEW Emp_D20 AS
SELECT *
FROM Emp
WHERE deptNo = 20;
What will happen if we update the Emp_D20 as follows?
UPDATE Emp_20
SET deptNo = 10
WHERE empNo = 1003;
This employee disappears in this view, which is called migrating rows
What is the WITH CHECK OPTION?
The WITH CHECK OPTION is used when creating a view to ensure that:
Insertions or Updates conform to the view’s definition: Any new rows added (via INSERT) or modifications made (via UPDATE) must satisfy the conditions defined in the view’s query
Rows cannot “migrate” out of the view: If an update causes a row to no longer meet the view’s conditions, the operation will fail
What is COMMIT and ROLLBACK?
COMMIT - Causes all outstanding changes you have made to become permanent
ROLLBACK - Causes all uncommited changes to be discarded
What is Physical Database Design (PDD)?
The process of producing a description of the implementation of the database on secondary storage
What are the steps for translating a Logical Data Model into a physical implementation?
- Translate logical data model for target DBMS
- Design file organisations and indexes
- Design user views
- Design security mechanisms
- Consider controlled redudancy
- Monitor and tune the opeational system
What are the benefits of Physical Database Design (PDD)?
Ensures:
The database is efficient, secure, and easy to maintain
It meets the requirements of the business and the users
It is optimized for the specific DBMS and workload
What are 3 ways to represent Derived Data?
- Use generated column
- Create a view
3 Use triggers
Triggers are stored programs execute automatically to respons to specific events such as inserting, updating or deleting data
How is a Database Stored?
Stored as a collection of files. A database file is partitioned into fixed-length storage units called blocks (or pages)
Block/Page - A contiguous sequence of sectors from a single track
Data blocks are the smallest units of storage allocation and data transfer
Where do data operations take place and where is the DB stored?
Operations on data happen in volatile memory eg. main memory, cache memory
Entire DB is stored on non volatile memory eg. magnetic disk, optical disk tape, flash drives etc.
Entire DB is backed up on Stable storage eg. RAID
What is File Organisation and what are the ways to ogranize blocks/pages in a file?
File Organisation - Physical arrangement of data in a file
Ways to organise blocks/pages in a file:
Heap (unsorted) files: records placed on disk in no particular order
Sequential (ordered) files: records are ordered by the value of a specific attribute
What is a Non-clustered (secondary) index?
an index which maintains a logical ordering of data rows without altering the physical ordering of the rows
A table could have several secondary indexes
Foreign keys good candidates for non clustering indexes
What is Denormalization?
Process of trying to improve the read performance of a database, at the expense of losing some write performance, by adding redundant copies of data or by grouping data
when to use?
- If performance is unsatisfactory and a relation has a low update and a very high query rate
