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, records and sets)
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.
characteristics of database approach
1 self-describing (self description)
2 integrated (integrates data from multiple sources to single rep)
3 shared (multiple users and applications can share)
4 structured (structured format - tables easy to manage and query)
5 query-able (allows to query data)
How database approach differs from traditional file system
Data redundancy (duplicates, centralized rep storage)
Data inconsistency (one file updated not others, data integrity constraint)
Data Security (file-level permission, user authentication, access control)
Data sharing (file-level sharing, multiple appplication and user)
Data scalability (file system capacity, scale hor and vert to accomodate data)
what is the importance of creating constraints on a table in DBMS
Data integrity
Data accuracy
Consistency
Prevention of Data Redu
Data Security
data manager
1 converts i/p from QO to machine
understandable code
2 buffer handling
3 DBMS info access stored on disk
4 enforces constraints
5 controls backup and rec
Primary key, Candidate key, Super key
PK - unique identifier not null
CK - can be primary key (only one), various columns unique rows
SK - set of unique identifier and non primary key
Specialization | Generalization
used to represent complex relationships between entities.
Spec - creating specific entities form general
Total, partial, disjoint
Gen - creating general entities from specific
Total, partial, disjoint
PL/SQL
procedural programming lan
used to create triggers, stored procedures and functions in Oracle
Blocks (self contained unit of code)
Variables (store and mani data)
Control Structure (IF THEN, loops)
Exception (exc)
Cursor
A cursor is a control structure that enables traversal over the records in a database. It allows you to fetch and process rows from a query result set one at a time
Embedded | Dynamic
allows to embed SQL queries, generate
pre compiled, dynam. compiled at run time
faster, slower
execution at run time, dynamic execution at run time
more secure, less secure
less flexible, more flexiblle
DB languages
SQL (MySQL)
NoSQL (Mongo DB)
LnnQ (Microsoft.NET, C#)
Oracle (Oracle DB)
Graph (gremlin, Amazon Neptune)
