DBMS Flashcards
define data
information translated to required/appropriate format for easier management
define database
collection of organized data to manage and manipulate
define dbms
managing system for databases - create,update, manipulate and delete data entities
FPS | DBMS
Data inconsistency (common in FPS)
Data redundancy (common in FPS)
Security (Robust in DBMS)
Maintainibility
Data Concurrency
Accessibility (Difficult in FPS)
DBMS structure/architecture
1 DDL Compiler (converts query to table with metadata)
2 DML compiler and Query
Optimizer (converts DML to object code which is then optimized)
3 Data manager (optimized object code to machine unders., DBMS info access, buffer in main mem, controls backup and rec)
4 Data Files (data portion of dataB stored on magnetic tapes or optical disk)
5 Data Dictionary (respository of description, data, relationship, constraints, access)
6 Compiled DML (stored)
7 End User
Schema
Design of Database
Physical
Logical
View
Data Models
Analysis of relation between one data entity and another data entity
Relation (record base, record = tuples, fields = attributes)
Hierarchical (data organized into tree structure, records connected)
Network (extended hier, multiple records to same file, flexible)
E-R
OOP (extension of ER model)
What is view in DBMS and how to create it? Can you update view?
A view in DBMS (Database Management System) is a virtual table that is based on the result of a query. It provides a simplified way to present data from one or more tables, without actually storing the data itself.
Updation : only if from single table and no aggregate functions
What is trigger? Consider following schema
Student_fee_details (rollno, name, fee_deposited, date)
Write a trigger to preserve old values of student fee details before updating in the table.
A trigger is a set of actions that are automatically executed in response to certain events, such as insertions, updates, or deletions, on a table in a database.
Constraints
Primary Key
Foreign Key
Unique
Not null
Default
Check
Referential
Entity Integrity
Index
An index is a data structure that improves the speed of data retrieval operations on a database table (copy of selected col and pointer to corres rows)
adv : fast data retrieval, less disk i/o, better query performance
dis : additional memory, index maintenance, insert, updat and delete overhead
Types of triggers
Before Trigger: A before trigger is executed before the triggering event occurs. It can be used to validate data before it is inserted or updated.
After Trigger: An after trigger is executed after the triggering event occurs. It can be used to perform actions after data has been inserted, updated, or deleted.
Instead of Trigger: An instead of trigger is executed instead of the triggering event. It can be used to replace the default behavior of an insert, update, or delete operation.
Row-Level Trigger: A row-level trigger is executed for each row affected by the triggering event.
Statement-Level Trigger: A statement-level trigger is executed once for each triggering event, regardless of the number of rows affected.
Stored procedure
A stored procedure is a set of SQL statements that are compiled and stored in a database. It is a reusable block of code that can be executed repeatedly with different input parameters.
