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
