DBMS

Question 1

DDL

Answer 1

is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

Question 2

DDL example

Answer 2

CREATE: to create a database and its objects like (table, index, views, store procedure, function, and triggers)

ALTER: alters the structure of the existing database

DROP: delete objects from the database

TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed

COMMENT: add comments to the data dictionary

RENAME: rename an object

Question 3

DML

Answer 3

Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.

Question 4

example of DML

Answer 4

SELECT: retrieve data from a database
INSERT: insert data into a table
UPDATE: updates existing data within a table
DELETE: Delete all records from a database table
MERGE: UPSERT operation (insert or update)
CALL: call a PL/SQL or Java subprogram
EXPLAIN PLAN: interpretation of the data access path
LOCK TABLE: concurrency Control

Question 5

Database Management System:

Answer 5

a computer program designed for creating and managing the database. It provides a systematic way to run operations like creating, storing, retrieving, deleting data from the database

The software which is used to manage database is called Database Management System (DBMS). For Example, MySQL, Oracle etc. are popular commercial DBMS used in different applications.

Question 6

DBMS allows users the following tasks:

Answer 6

Data Definition: It helps in creation, modification and removal of definitions that define the organization of data in database.

Data Updation: It helps in insertion, modification and deletion of the actual data in the database.

Data Retrieval: It helps in retrieval of data from the database which can be used by applications for various purposes.

User Administration: It helps in registering and monitoring users, enforcing data security, monitoring performance, maintaining data integrity, dealing with concurrency control and recovering information corrupted by unexpected failure.

Question 7

Paradigm Shift from File System to DBMS: File System manages data using files in hard disk, Users are allowed to create, delete, and update the files according to their requirement, Benefits are:

Answer 7

Redundancy of data: Data is said to be redundant if same data is copied at many places.

Inconsistency of Data: Data is said to be inconsistent if multiple copies of same data does not match with each other.

Difficult Data Access: A user should know the exact location of file to access data, so the process is very cumbersome and tedious.

Unauthorized Access: File System may lead to unauthorized access to data

No Concurrent Access: The access of same data by multiple users at same time is known as concurrency. File system does not allow concurrency as data can be accessed by only one user at a time.

No Backup and Recovery: File system does not incorporate any backup and recovery of data if a file is lost or corrupted.

Question 8

Disadvantages of DBMS

Answer 8

Increased Cost: Cost of Hardware and Software; Cost of Staff Training; Cost of Data Conversion

Complexity

Currency Maintenance: keep your system current because efficiency which is one of the biggest factor and need to be overlook must be maximised.

Performance

Frequency Upgrade/Replacement Cycles

Question 9

Characteristics of Database Management System

Answer 9

Data stored into Tables

Reduced Redundancy

Data Consistency

Support Multiple user and Concurrent Access

Query Language, using which data can be easily fetched, inserted, deleted and updated in a database

Security

Question 10

Metadata

Answer 10

is data about the data.

When I store my Name in a database, the DBMS will store when the name was stored in the database, what is the size of the name, is it stored as related data to some other data

Question 11

database object

Answer 11

any defined object in a database that is used to store or reference data.Anything which we make from create command is known as Database Object.It can be used to hold and manipulate the data

Question 12

Some of the examples of database objects are : view, sequence, indexes, etc.

Answer 12

Table: Basic unit of storage; composed rows and columns
View: Logically represents subsets of data from one or more tables
Sequence: Generates primary key values
Index: Improves the performance of some queries
Synonym: Alternative name for an object

Question 13

Super Key

Answer 13

It is a set of one or more attributes whose values uniquely determine each entity in the database table.

It is a subset of a candidate key.

Question 14

Candidate Key

Answer 14

It is a set of columns in the table from which primary key can be selected to identify each record

Every database table may have one or more candidate keys

Question 15

Primary Key

Answer 15

It is a special key that uniquely identifies each record in the table

It has a unique value for each row of data and it cannot contain null values.

Question 16

Composite Key

Answer 16

It is a combination of more than one column in the table that can be used to uniquely identify each record.

It is also known as a Compound key.

Question 17

Alternate Key

Answer 17

All keys except primary key are known as Alternate.

Alternate keys are also called Secondary Keys.

Question 18

Foreign Key

Answer 18

Foreign key points to the primary key of another table. It acts as a reference between tables.

It can accept the null and duplicate value.

Question 19

Data Abstraction

Answer 19

The process of masking irrelevant information from users is known as Data Abstraction. Data abstraction reduces the complexity of users with the database.

Three levels of data abstraction are Physical Level, Logical Level, and View level.