DBMS unit 1

what is data? What is it in the form of? give example

raw unprocessed facts or figures that do not carry any specific meaning by themselves
in the form of numbers, characters ,words, text, images etc
data has no context on it’s own until it is processed
ex→
Marks: 85, 90 78
Names→ A, B, C

what is information?

Data that has been processed and presented with a context so as to make it meaningful
Information answers questions such as what, who where and when

ex→ ravi scored 85 marks in maths

differentiate between data and information

what is database?

Organised, structured and persistent collection of related data.

List key features of database

Data is logically related and organised
Data is stored in a structured format ( tables, documents, objects, etc)
data can be created, read, updated and deleted efficiently
data can be shared among multiple used and applications

what is DBMS

Database management system is a software that enabled us to create, read, update, delete, and control access to a database

it acts as interface between the database and the end users/application program( that uses database)

what are functions of DBMS?

1. defining structure(schema) of database

2. CRUD operations on data

3. access control

4. maintaining data integrity and consistency

5. supporting concurrent access

name a few dbms software

MySQL, PostgreSQL, MongoDB etc

What is Metadata

“data about data”, describes the content,structure, constraints and other features of the actual data stored in a database

where is metadata stored

data dictionary ( special part of database , aka, system catalog)

problems with file system approach→

1. data redundancy→ same data is stored in multiples files, wasting space

2. data inconsistency→ if data gets updated in one file and not in the other, data inconsistency may arise

3. security problems→ difficult to enforce security restrictions

4. difficulty in accessing data→ no general purpose way to retrieve data

what are advantages of DBMS→

1. no data redundancy

2. no data inconsistency

3. security mechanisms

4. concurrent access

5. crash recovery

disadvantages of DBMS→

single point of failure, requires trained staff,

characteristics of db→

data redundancy control
data consistency
security mechanisms

crash recovery mechanisms

concurrent access of data

what is database system→

database + dbms + users/applications + hardware

what is schema, what are it’s three parts

blueprint that defines how data is organized and stored in a database.
three parts→ conceptual database schema, physical database schema, logical database schema

what is conceptual database schema

describes database on a theoretical level.

modeled using ER diagram
consists of→
entities (real world objects→ customer, student etc)
attributes(features of entities ex→ name, age,address)
relationshiops→ (connection between entities

what is physical database schema

how data is stored on disk physically
determines physical layout of tables and indexes
designed by the DBA( database admin)

logical database schema

describes the logical structure of data

defines tables, columns, primary keys, foreign keys, relationships and integrity rules

this schema is built from the conceptual schema using ER modeling

what is database instance

snapshot of database at a specific moment in time

What are the users of database→

1. DBA( database admin)→
   creates and manages database
   controls user access
   ensure security

2. Naive user→
   user without technical knowledge, they view/enter data through ready-made interfaces

3. application programmer→
   dev who creates applications that use database as one of the components

4. Sophisticated user→
   users with technical knowledge who study and analyze data in the database
   ex→ data analyst, researcher etc

5. database designer→ designs structure of database before it is implemented,

6. knowledge user→ user who uses database to support decision making and analysis
   ex→ engineer, doctor, researcher etc

what are the components of DBMS architecture→

1. Disk Storage

2. Storage manager

3. Query processor
   
   

wdym by disk storage?

all info stored on physical disk, comprises of

1. data→ record/info itself

2. data directory → metadata

3. indices→Shortcuts that let the system look up specific values quickly

4. statistical data → statistical data about data

wdym by storage manager→

acts as bridge between data stored on disk and user applications/queries

responsible for creating, reading, updating and deleting data

translates DML commands into low level commands

wdym by query processor? what are it’s three parts

turns query commands into machine readable instructions. comprises of→
1. DDL interpreter

2. DML compiler

3. query evaluation engine → executes low level instr generated by DML compiler

there are three architectures of application using database, what are they, pros and cons

1-tier architecture→ client, server and database are all in one machine, common in standalone applications

pros→ simple, cost effective, easy

cons → single user only, no scalability, hard to share data, poor security

2-tier architecture → basic client server architecture, clients accesses data via server only, server processes queries and returns to the user

pros → easier and cheaper than 3 tier mode

cons → server can get overloaded with requests,

3-tier architecture → an intermediate layer is present between client and database server, the application server,which processes and relays data

pros → scalability and security increases

cons → complex and more costly, slower response time

what is the 3 schema architecture? who was it propsoed by? what was it’s aim? what are the three levels, draw diagram

the three schema architecture of DBMS , proposed by ANSI/SPARC,
aim → to achieve data abstraction and independence (change in one level does not affect the layers above it)

External level, conceptual level, internal level

tell about external schema/view level→

layer closest to end users, users only see customized views of the database

tell about conceptual schema/logical level

what data is stored and the relationship between them. simple data structures are used

tell about physical level/physical schema

the raw data sitting on the physical storage device, how data is arranged physically

advantages of ANSI/SPARC, 3 tier model→

data abstraction (unnecessary details hidden), data independence(change in one level does not affect the higher levels), multiple user views, improved security, flexibility, and performance

what is data independence? what is it’s importance

ability to modify the schema at one level without affecting the schema at the next higher level

importance→

1. reduces maintenance

2. increased flexibility

3. supports long-term growth

what is logical data independence

ability to modify logical level without changes being reflected in the view level.

ex→ add a column (email id) in the employees table

what is physical data independence

ability to modify physical level without changes being reflected in the conceptual/logical level

ex→ change file location, change disk(HDD to SSD), adding indexes

what’s a data model

A Data Model in a Database Management System (DBMS) is an abstract framework that defines the logical structure, relationships, constraints, and flow of data within a system.

It provides the blueprint for how data is stored, organized, accessed, and manipulated.

what are the types of data models?

Conceptual data model, representational data model, physical data model

what is conceptual data model?

defn → it is a high level model that defines what the database will store and how it will be structured. representation using ER( enttity relationshiop model) is highly used

focus on real-world entities, their attributes and relationship between them

useful for non-technical personnel, database designers etc

what is representational model

1. only logical part of database is defined

2. how data will be stored physically on disk is not defiend

3. Relational model(along with relational algebra and relational calculus) is used to represent this type of data mode

4. Relational model consists of tables
   
   
   
   

what is physical data model

1. Used for implementation of representational data model on physical storage devices

2. data is stored in memory

3. SQL is used to implement the physical data model
   
   

what are the four types of database languages

DDL, DCL, DML, TCL

what is DDL

Data definition language,
define and alter database schema and structure →
5 DDL commands→

1. CREATE → create database object

2. ALTER → modify structure of existing database object, such as adding a col to a table

3. DROP→ delete database objects

4. TRUNCATE → remove all rows from a table

5. RENAME → change the name of a database object

database object→ table, index, view

acronym→ DACTR

what is DML

data manipulation language

retrieval and modification of data records within tables

DML commands →

1. SELECT → retrieve data from table

2. INSERT → add new rows of data into an existing table

3. UPDATE → modify existing data in a table

4. DELETE → removes data from a table

what is DCL

Data control language

manage user permissions, access privileges and security controls

GRANT - give access to user
REVOKE - remove access from user

what is TCL

Transactional control language

manage database transactions

COMMIT → save all changes made during the current transaction to the database

ROLLBACK → restore database to it’s original state from the last commit