Database Management System





The collection, configuration, relation and day by day action on data are called database. The logical relationship among different data is database. The concept of database is essential for every field of computer science. In general sense, text is also a type of database of characters, symbols and numbers.

The animation is a database of frames, frames are database of colored pixels etc. The government and business organization, all are database based organization because every transactions are revolves around data.

 

Database: The database is an organization of data based on data and for the data. The data is very valuable for any organization either business organization or software organization. The computer is invented for data manipulation, not for other jobs. Now, it becomes very vast, deepest than ocean and wider than universe. The data mart, data mining, data warehousing are modern concept born in the lap of relational database. The database managing procedures are termed as data management. If you have collection of data, its maintaining is required and it is done through database management system.

 

 

1. Data Modeling

 

The data model is a collection of concepts used to describe structure of database and determine where 1:1 or 1: M or M: M or any two or all relationship is established among entities. There are three types of data model or structure:

[A] Hierarchical Data Model,

[B] Networking Data Model and

[C] Relational Data Model

 

Components: The components of data model are following:

(a) Entity and Attributes: The entity is the real word objects like as person, vehicle, account Number, License Number, salary, chair etc. The characteristic of entity is termed as attributes. Each entity has at least on attributes.

Example: The building is an entity and breadth, height, flooring, roof, rooms, ceiling type, doors, windows are its attributes.

(b) Identification: The identification is an attribute or a set of attributes. The primary key, secondary key, candidate keys are known as Identification.

(c) Relationship: The database is a table of logical relationship among entities.

These are classified as:

(i) one to one (1:1): If an entity has only one entity, relationship is called one to one relationship. Example: Student and roll

(ii) One to many (1: M): If an entity has only more than one entity, relationship is called one to many. Example: Father and children

(iii) Many to Many (M: M): It is a bi-directional relationship in which more than one entity are related.

 

 

2. Methodology

 

Normalization: The process of converting table from one form to other form is called Normalization.

Use of normalization:

  • Insertion: Easy insertion of new data
  • Deletion: Easy deletion of tuples
  • Updating: Modification of Values of tuples is done easily.

First Normal Form (1NF): It is called Flat file due to simple structure. It has no any composite attributes and every attribute is single.

Level Code

Levels

Teacher

1001

Secondary

B.k. Singh

P.N. Ojha

Raghubir Chatta

1002

Higher Secondary

G.S. Thapa

Sher Bdr. Chand

B.K. Singh

Raghudir Bhatta

 

The following table is an example of 1NF.

Level Code

Levels

Teacher

1001

Secondary

B.K. Singh

1001

Secondary

P.N. Ojha

1001

Secondary

Raghubir Bhatta

1002

Higher Secondary

G.S. Thapa

1002

Higher Secodary

Sher Bdr. Chand

1002

Higher Secondary

B.K. Singh

1002

Higher Secondary

Raghubir Bhatta

 

Second Normal Form (ZNF): If a relation is in 1NF form and non-key attributes are functionally dependent on the key attribute, such type of relation is called 2NF (Second Form Normalization).

The enrolment of students is used in every query in university. The Name, Address, Courses, Fee, Year etc are functionally depending on enrolment numbers.

Enrolment

Enrolment


Enrolment –> [Name, Address, Course, fee/Semister]

Teacher –> Subjects: the attributes subject depends on teacher.

Teacher

Subject

B.K. Singh

Computer

P.N. Ojha

English

D.B. bist

Account

 

Item Code –> Item: the attribute item depends on Item Code.

Item Code

Item

BS01

Lux

WS01

Wheel

BR01

Topaz

BR02

Gillette

 

Third Normal Form (3NF): When relation is in 2NF and non-Key attributes are functionally dependent on any other non-key attributes, such type of normalization is called 3NF.

2NF Relations

Emp#

Name

Department

Location

Room No

E001

Mohan S

Marketing

New Delhi

15

E002

Shah P

Finance

Patna

21

E003

Ojha t

Production

Lucknow

12

E004

Kumar A

Sale

Patna

21

E005

Diwan S

Management

New Delhi

15

 

3NF Relations

Emp#

Name

Department

E001

Mohan S

Marketing

E002

Shah P

Finance

E003

Ojha T

Production

E004

Kumar A

Sale

E005

Diwan S

Management

 

3NF Relations

Locations

Room No

New Delhi

15

Patna

21

Lucknow

12

 

 

3. Database Design:

 

There are three main data model:

[A] Hierarchical,

[B] Networking and

[C] Relational design

 

Hierarchical Design: Its structure is tree like, so, it is also called tree data model. In tree, there is only one root and many branches. In this model, only one parent (root) node owing many children nodes.

Hierarchial Model

Hierarchial Model


Advantages:

  • Node has one or many attributes.
  • It supports 1:1 and 1: M relationship.
  • Every node has one or more attributes.
  • All the children nodes are accessed through parent nodes.
  • Helpful in building complex system from simple components.

 

Disadvantage:

  • It does not support M: M relationship.
  • When parent node is deleted, all the nodes are deleted automatically.
  • The dependency on parent node is not beneficial always.

Networking Design: The Networking data model supports all types of relationship among entities and child nodes may controlled and connected by more than one parent’s .In this way, networking is established among entities.

Example:

Networking Model


A, B, and C are parent nodes and X, Y, Z and W are child nodes. Generally, child nodes are linked with more than one-parent nodes.

Advantage:

Many to many relationships of real world entities are established successfully.

Disadvantage:

Entities and attributes grow; along with it complexity of structure also grows.

Relational Data Model: They are like ordinary database, except for the fact that they are linked together. A relational database takes into account logical relations between two or more ‘simple’ database. As a concrete example, consider a huge bookshop employing a relational database for all their data. They could have a database having information about the books they own (name, book number, author and date of publishing). Another database for stock could be made having book number, amount, and its cost. Now, these two database would then be linked to each other to form a single relational database. The link here is made between the book numbers field present in both them. The relation is defined by a set of tuples and attributes. It is abase of relational database management system (RDBMS). It became popular due to its simplicity and mathematical background.

 

Some technical words used in relational data model:

  • Domain: A world of real world data is called domain. It is a data pool from which data is entered into table for database management.
Domain

Domain


  • Tuple: The row or record of table is called tuple. Example: The following database has only two tuples.

Item No.

Item date

Item Name

Unit Price

Qty

AA0021

18-08-03

Mouse

60.00

25

CC0033

21-08-03

Keyboard

430.50

5

 

  • Attribute: The column of table is called attribute. For instance, the sale is an entity and its attributes are ItemN0, ItemDate, ItemName, Unitprice, and Qty.
  • View: View is a virtual table which contents can be queried, updated, and deleted also. New data can be inserted and view can be joined with other table or view. View is a security step in which certain required parts are shown and entire logical models are hidden. It has not physicals existence, so considered as virtual table. SQL provides powerful tools for view creation.

Syntax:

CREATE VIEW viewName as <query expression>;

Example:

 

CREATE VIEW result AS SELECT *FROM student

WHERE school=”AVN” AND division “first”;

Suppose database table contains result of whole Nepal from which only first divisional students of AVN can be viewed creating view table result.

RegNo

Symbol

FirstName

LastName

School

Division

0033965

176

Subash

Poudel

AVN

First

15470

180

Subash

Ghimire

AVN

First

15475

185

Sandeep

Gautam

AVN

First

15476

186

Ran

Badhur

AVN

First

 

To drop view table following syntax is used:

DROP VIEW result;

Features of Relational data:

  • Updating: The contents of table can be upgraded easily. The process of inserting modification and deletion can be done any part of table.
  • Merging: Two or more table can be merged to form one relation.
  • Unique entry: Each tuple of table is unique, that is no two tuples have identical values.
  • Each attribute is given an arbitrary name. Two attributes name cannot be identical.

The power of relational database comes to light when you query the database for information. Data can be collected and presented in a more flexible manner. Another useful aspect of relational databases is flexibility. Changes extensions can be made afterwards without changing previous programs and applications which use database.

 

 

4. DBMS and RDBMS

 

The database management system is a set of procedures that manage the database and provides access to the database in the form of required by any application program. The relational database is a modern concept of database logic with relation details among entities and attributes.

DBMS: It stands for Database Management System. The collection of logically interrelated data necessary to control and manage organization is called database Management. The database management is a part and parcels of any organization around which all the business activities revolve. We can compare it with axle of wheel.

DBMS

Function of DBMS:

  • Data Definition: It defines structures, constraints of data to be stored in the database.
  • Data Store: It manages and controls data stored in storage medium.
  • Data Manipulation: It handles all the activities of database. The process of querying, retrieving, deleting and inserting is controlled by it.
  • Data Control: The DBMS is responsible for data security also. So, it uses many tools like as password, encryption etc to protect data from external modifications.

Example:

dBase-IV, FoxPro, FQXBASE, Clippers, Microsoft Access, Oracles, SQL etc.

 

Advantage of DBMS:

A database is a logically coherent collection of data with some inherent meanings. The database management system is a computer based database controlling system.

Some important advantages of DBMS are:

  • It satisfies current and future application needs of an organization
  • It allows only authorized persons to have access to the data stored in database.
  • It makes data modification easy with change in hardware also.
  • It provides data security and integrity

Some features of database management or database management system:

(a) Control redundancy: The system should identify existence of common data and avoid duplicate recording.

(b) Relating data item: Relationship between data items should be specified.

(c) Data Integrity: Consistency of data values and relationship must be preserved.

(d) Data security: This is concerned with protecting data from unauthorized access.

(e) Database performance: The system should be able to provide timely information required. The cost of storing and retrieving data should be commensurate with the value of information provided.

(f) Management Control: The required data modification facilities, insertion and deletion facilities are subject of management. The data management process is accomplished by data management system.

 

Data Administrator: The data administrator is a post of high responsibility. The person with managerial background is appropriate for this post.

  • Managing data activities: The data administrator ensures the integrity, security and privacy of data. It also coordinates in data collecting and organizing process.
  • Managing database structure: He/ she also responsible for designing of logical database, its implementation and maintenance.
  • Managing DBMS: The Database Administrator maintains a data dictionary, which is a central repository of information about entities, relationship, data elements corresponding to entities and relationship.

Centralized database system: The data is stored in central office and peripherals are connected with it to store and retrieve data as business requirement.

Benefits:

  • Control: The database is easily controlled by higher authority.
  • High security: The database is centralized, so there is no chance of data modifications.
  • Maintenance: The database is maintained easily because it is centrally stored.
  • Minimum risk factor: The database is centralized and out from access of unauthorized persons.

Distributed database System: This is a complex type of database system in which a collection of multiple logically interrelated databases is distributed countries wide or out of geographical boundaries. The computers in a distributed system communicate with one another through various communication media, such as high-speed networks or telephone lines. These computers do not share main memory or discs.

Distributed Database

Distributed Database


Advantage:

(a) Transparency

(b) Reliability and availability

(c) Improved performance

(d) Easier expansion

 

RDBMS (Relational Database Management System): The relational database is table with logically related tuples (rows) and attributes (columns). A Database Management System is a computer based system to manage a database or a collection of databases or files. It has many uses. The DBMS used to control relational database is termed as RDBMS. The relational database is a type of database with some special features. It allows the user to update (add, modify, or delete) the table’s contents, and two or more table can be merged to form one relation.

  • Data is presented as a collection of relation
  • Each relation is depicted as table.
  • Columns are attributes.
  • Rows(tuples) represent entities
  • Every table has set of attributes that taken together as a ”key”(technically ”Super key”) mainly identifies each entity.

 

 

5. Data Security

 

The term data security and integrity are separate terms, but both are mixed such a way that becomes unrepeatable from each other. The data security is used instead of data integrity or vice versa. The data security is related with protection of data from malicious access and modifications, but integrity is related with accidental loss on data due to any haphazarding reasons.

Some data losses are:

-          Accidental loss

-          Modification

-          Destruction

-          Discloser

-          Logical errors

-          Virus attack

-          Crushing

-          Hacking

-          Fire, flood, storm

 

  • Physical: The data is physically protected or not, is also major issue of data security. The amount of light, height of data store from surface, electrical circuit, fire etc are major considerations.
  • Human: The unauthorized access of data should be prevented. There are many measures to protect data from unauthorized access like as password etc.
  • Operating System: The weakness in operating system can cause accidental data loss. So, fully reliable operating system should use used.
  • Network: The software level security is needed, otherwise the attack of virus or leakage of data are common on internet.
  • Database System: The database is a ware house of data, any user can utilize definite portion of it. The Database system should be allowed only to access authorized personnel without any modifications.

Security measures:

Antivirus software

Authorization

Biometrics System

Encryption

Firewall

IPS (lnstrvision Prevention System)

Password System

Physical layout

 

 

6. E-R Diagram

 

Entity-Relationship diagram is a tool for problem analysis. It is a detailed logical representation of the data for an organization and uses three main constructs ie. data entities, relationship, and their associated attributes. E-R diagram is graphical representation of different entities and their relationship. It is a tool for relational database management.

  • Entity: An entity is a thing in the real world that is distinguishable from other objects. For example, each person is an entity, and bank accounts can be considered also be entities. Entities are described in a database by a set of attributes. For example, the attributes account-number and balance describe one particular account in a bank. The set of all entities of the same type is called entity set.
  • Relationship: A relationship is an association among several entities. For example, a depositor relationship associates a customer with each account that he has. The set of all relationship of the same type is called relationship set.

The overall logical structure of a database can be expressed graphically by an E-R diagram, which is built up from the following components:

E-R Diagram Symbols

E-R Diagram Symbols


(a) Rectangle: It is used for entity representation. It contains objects used in relational database.

(b) Diamond: Diamond represents relationship.

(c) Oval or Ellipse: The Oval or ellipse is used to represent attributes of entities.

(d) Line: It is used to link attributes to entity sets and entity set to relationship.

(e) ISA: The keyword ISA is used to indicate that a class is a specialization of other class.

We consider an example of training department of an organization is interested in tracking which training courses each of its employees has completed. This leads to a relationship called completes between the Employee and Course entity types.

E-R drawing

Example: Customer and Item are related with Bill, because customer has to pay amount according to bill. CustNo, Name, Address etc are attributes of customer and Item has also attributes (ItemNo, ItemName, Price, Quality).

E-R Diagram

E-R Diagram


Example: Customer deposits amount in his/ her account. Customer and Account, both have their attributes.

E-R Diagram

7. ODBC and JDBC

 

ODBC (Open Data Base Connectivity) is developed by Microsoft as standard API (Application Programming Interface) for database connectivity. ODBC consists of known functions that can be called by any program to operate on a database. Thus any program made in any programming language can use ODBC to interface with database systems that support the standard. ODBC acts as a middleman between the database system and the program, translating commands and data to and fro.

JDBC stands for Java Data Base Connectivity. It was primarily created for the Internet age. Allowing database systems to be available to Java applications and applets (the mini program on web pages), JDBC, in its most basic form, is simply ODBC for Java. JDBC basically functions as an API that translates the program’s demands into SQL commands for the database.

The potential of connecting a database over the net is huge, as it facilitates ease of transactions and worldwide connectivity. As more and more people are viewing online shopping as a viable and safe alternative, Internet connectivity for database systems is also increasingly being employed. Most online shopping today is based on web pages being dynamically generated by programs that use a product database.

 

8. Data Dictionary

 

A structured repository of data about data is called- data dictionary. It contains the names of all data item arranged in alphabetic order like as dictionary. It holds the corporate data resources for analysis and planning. There are two types of it: active and passive. The passive data dictionary is used by designers, users, administrators, but it is not used by DBMS software.

Functions of Data Dictionary:

  • It is a valuable reference in any organization. It helps in data processing.
  • It plays an important role in the designing of a physical implementation of the logical model.
  • It makes essential parts of the documentation of RDBMS system.

 

9. Data Flow Diagram

 

The pictorial representation of information flow inside and outside from system is called Data Flow Diagram (DFD) or Data Flow Graph or Bubble Chart. The presentation of information flow in the form first time introduced by De Marco (1978), and Gane and Sarson in 1979. It is a system-designing tool used by system analyst or software professional for designing software.

Symbols used to draw DFD:

Data Flow Algorithm

Data Flow Algorithm


(a) External entities (Source and destination): External Entities are represented through rectangle. External entities are persons, program, products, proposals, requests, hardware etc. It is a presentation of source and destination. We can use ellipses with name or a unique identifier is also used for external entities.

(b) Data flow: It is a pipeline through which information flows. Data object flow/ data item direction or direction of information flow represents the movement of data between other components, for example a report produced by a process and sent to an external entity.

(c) System or Process: It is used to represent System, which can process or transform and transfer data.

Processes represent activities in which data is manipulated by being stored or retrieved or transformed in some way. They are shown as large rectangle with a numeric identifier in a box at the top left corner. The location where the process take place or the job role performing it is recorded in a box in the top right corner and is only used in diagrams of the current physical systems.

(d) File or data store: Slant line or Parallel line or open-ended rectangle is used for data store or file. Examples are computer files or databases or, in a manual system, a paper files held in filing cabinet. Manual data stores are identified by letter M and computerized store by letter D.

The circle or real bubble is used by both Victor Weinberg and Tom De-Marco. The rectangular bubble is used by Chris Gane and Trish Sarson for the same purpose. The reason for the rectangular bubble is the perceived need to enter more information than can be contained in the bubble.

Bubble Rectangle

Bubble Rectangle


 

Data flow takes place between

(a) External Entities to System or process or vice versa

(b) File(Store) to System or vice versa

(c) One System to Other System

The following seven rules govern construction of data flow diagram (DFD):

  • Arrows should not cross each other.
  • Squares, circles, and files must bear names.
  • Decomposed data flows must be balanced (all data flows on the decomposed diagram reflect flows in the original diagram.)
  • No two data flows, squares, or circle can have the same name.
  • Choose meaningful names for data flows, processes, and data stores. Use strong verbs followed by nouns.
  • Control information such as recorded counts, passwords, and validation requirement are non-Pertinent to a data-flow diagram.

Example:

Data Flow

Data Flow


The DFD writing skill depends on knowledge of concerning systems. The every transaction inside and outside of the system is needed at the time of DFD writing. It is also a work of patience and knowledge. Without enough knowledge of system, DFD cannot be written correctly. So, the system analyst spent more and more time to understand choice, interest and objective of system as well as all the transactions to and fro the system at the time of system analysis.

Example-1: The keyboard is an external entity, which is used to input data into processor and processed data, is stored in file (in hard disk)

Data Flow

Data Flow

 



Related posts:

  1. Memory Management Memory: It is part of computer which stores data the...
  2. Data, Information and Knowledge Data, Information and Knowledge are described below individually below: Data:...
  3. File System Files The files of information in appropriate format are known...
  4. Introduction to operating system Operating System: An operating system is a collection of system...
  5. Operating System Structure operating system structure: An operating system might have many structure....