Introduction of Database

image_pdfimage_print
Data: Facts, figures, statistics etc.
Record: Collection of related data items.
Table or Relation: Collection of related records.
Database: Collection of related relation/data. In database, data is organized strictly in row and column format.The columns are called FieldsAttributes or Domains. The rows are called Tuples or Records.

Features of Data In a Database:
1)Security
2)Consistency
3)Non-Redundancy
4)Shared
5)Independence
6)Persistence
DBMS(Database Management System)It is software that allows creation, definition and manipulation of database.l It is middle layer between data and program.
File System:
  • Stores permanent records in various files
  • Need application program to access and manipulate data.

Disadvantage of File System:

  • Data Redundancy
  • Data Inconsistency
  • Difficult in accessing data
  • Data Integrity
  • Low Security

Data redundancy: Data redundancy is the repetition or superfluity of data. Data redundancy data is an common issue in computer data storage and database systems.This data repetition may occur either if a field is repeated in two or more tables or if the field is repeated within the table.Data can appear multiple times in a database for a variety of reasons. A positive type of data redundancy works to safeguard data and promote consistency. Many developers consider it acceptable for data to be stored in multiple places. The key is to have a central, master field or space for this data, so that there is a way to update all of the places where data is redundant through one central access point. Otherwise, data redundancy can lead to big problems with data inconsistency, where one update does not automatically update another field.For example, a shop may have the same customer’s name appearing several times if that customer has bought several different products at different dates.

Disadvantages Of Data Redundancy:
1)Increases the size of the database unnecessarily.
2)Causes data inconsistency.
3)Decreases efficiency of database.
4)May cause data corruption.

Data Isolation: The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.As an example, if two people are updating the same catalog item, it’s not acceptable for one person’s changes to be “clobbered” when the second person saves a different set of changes. Both users should be able to work in isolation, working as though he or she is the only user. Each set of changes must be isolated from those of the other users.

Data Integrity is the assurance that information is unchanged from its source, and has not been accidentally (e.g. through programming errors), or maliciously (e.g. through breaches or hacks) modified, altered or destroyed. In another words, it concerns with the completeness, soundness, and wholeness of the data that complies with the intention of data creators.It’s a logical property of the DB, independent of the actual data.

Data Consistency refers to the usability of the Data, and is mostly used in single site environment. But still in single site environment, some problems may arise in Data Consistency during recovery activities when original data is replaced by the backup copies of Data. You have to make sure that you data is usable while backing up data.

Data Abstraction:To simplify the interaction of users and database, DBMS hides some information which is not user interest is called Data Abstraction. So, developer hides complexity from users and show Abstract view of data.

DBMS Architecture/3-Tier Architecture:
1)External/View Level:It is user’s view of the database.This level describes the part of the database that is relevant to each user.
2)Conceptual/Logical Level:
  • Describes what data is stored in the database and the relationship among the data.
  • Represent all entities, their attributes and their relationship
  • Constraints on the data
  • Security and Integrity information
3)Physical/Internal Level:
  • Describes how the data is stored in the database
  • Storage Space allocation for data and indexes
  • File System
  • Data compression and Data encryption techniques
  • Record Placement

Image result for physical logical and view level

Schemas: 

  • It is overall description of the database.In three-level architecture, one schema at each level.
  • Does not specify relationship among files.
Instances:
Collection of information stored in the database at a particular moment.
Sub-schema:It is a subset of schema and inherits the same property that the schema has. It is an application programmer’s or user view of the data items types and record types which he or she uses.
Data Independence in DBMS:
Upper level are unaffected by changes in lower level.Two Types of Data Independence:
a)Physical Data Independence:

  • Physical storage structure or devices can be changed without affecting conceptual schema.
  • Modification done to improve performance.
  • It provide independence to conceptual schema and external schema

b)Logical Data Independence:

  • Conceptual schema can be changed without affecting external schema.
  • Structure of database is altered when modification done in conceptual schema.
  • It provide independence to external schema.

DBMS Components:
1)Hardware

  • Processor/main memory(used for execution)
  • Secondary Storage devices(for physical storage)

2)Data
3)Software
4)Users
5)Procedures(Set of rules for database management)

Types of Users:
a)Naive Users:
End Users of the database who work through menu driven application programs, where the type and range of response is always indicated to the users.

b)Online Users:
Those users who may communicate with database directly through an online terminal.

c)Application Programmer:
Those users who are responsible for developing the application program.

d)DBA(Database Administrator)
DBA(Database Administrator):
DBA directs or performs all activities related to maintaining a successful database environment.
Function of DBA:

  • Defining Conceptual Schema
  • Physical Database Design
  • Tuning database performance
  • Security and Integrity Check
  • Back up and Recovery Strategies
  • Improving query processing performance
  • Granting User Access

 

Database Languages:
1)DDL(Data Definition Language):

  • Deals with database schemas and description, how the data should reside in the database.
  • Used to alter/modify a database or table structure and schema.

Command used in DDL:

  • Create
  • Alter
  • Drop
  • Rename
  • Truncate
  • Comment

2)DML(Data Manipulation Language)

  • Deals with data manipulation
  • These statements affects records in a table.

Command used in DML:

  • Update
  • Select
  • Insert
  • Delete
  • Merge
  • Call
  • Lock Table

Two Types of DML:
a)Procedural DML(Non Declarative)(How data is fetch)
b)Non-Procedural DML(Declarative )(What data is to be fetch)

3)DCL(Data Control Language)
Control the level of access that users have on database objects.
Command used in DCL:

  • Grant
  • Revoke

4)Transaction Language:
Control and manage transactions to maintain integrity of data within SQL statement.
Command used in Transaction Language:

  • Set Transaction
  • Commit
  • Savepoint
  • Rollback

Image result for dml and ddl commands