Fundamental of DBMS

 

DATABASES  AND SQL

 

Database:- it is a combination of two words first data which means raw facts and figures and second base where data is to be stored.

►   Organize collection of data is known as data base.

►   It serves as a base from which the desired or multiple information can be retrieved by further reorganizing or processing the same data.

►   It is created or managed by a system is known as database management system.

►   A DBMS (database management system) or collection of database or database files in an automated way.

►   Now –a-days , organizations are realizing the importance and need of proper storage and manipulation of data as they feel that relevant information on time is the key of good decision-making. And as we know that good decision –making is the key to success for any organization.

DBMS:- it stands for database management system.

►   It is a computerized record keeping system whose purpose is to manage the data and make it available as per the requirement.

►   It can be referred to as a digital filing system.

►   It is a package that enables the users to store, modify and properly maintain the database and that helps to extract useful information from the database as and when required.

►   It is a software designed to gather , handle and process the information.

►   It manipulates different information in such a way that helps to analyze the data and thus help in decision making.

►   It performs the tasks of maintaining databases so that the information is readily available.

►   It helps to manage the data of different sectors like banks, corporate, railways ,airlines ,universities, hospital , space explorations, etc.

►   DBMS is used to build the database and also to manage the databases in different ways like:-

i.                    Adding new records

ii.                  Editing existing records

iii.                Removing unwanted data

iv.                 Sorting data either in ascending or descending

v.                   Extracting the data based on quires

vi.                 Updating and manipulating data item

vii.               Viewing data , generating reports

viii.             Performing calculations on it.

►   The DBMS serves as the intermediary between the user and the database.

►   There are some examples of DBMS packages are MySQL , Oracle, MS-Access, Sybase , dBase , FoxPro, Open Office Base.

►   The person having the centralized control over the database system is called the Database Administration.

►   The database administrator is an I.T.  expert who is responsible for the technical operations and management of database.

Benefits of database management system

There are following benefits of DBMS such as:-

1.      Reduction of Data Redundancy

2.      Reduction in data inconsistency

3.      Sharing of Data

4.      Enforcement of Data Standards

5.       Ensure Data Security

6.      Data Integrity

7.      Interactive interface

 

1.       Reduction of Data Redundancy:- it refers to the duplication or the repetition of data.

►   In non-database systems , each application has its own separate collection of files. It leads to the repetition of the stored data, thus resulting in wastage of space and time.  So in such situation our DMBS helps to us to mange centralized data. And there is  less chances of duplication.

 

2.       Reduction in Data Inconsistency:- In DBMS , the stored database is consistent and remains updated, in case data of any item is changed then the changes are done automatically to all other portions wherever that data has been used. This process is known as propagating update.

 

3.       Sharing of data :- it means that the same data made stored at one place and can be shared by multiple users or for different applications. So it saves our time and cost in creating new record again .

 

4.       Enforcement of Data standards:- in DBMS, access to the database is done in a standardized and systematic manner. Standard may relate to the naming of the data , format of data, report generation, structure of data ,and so on.

 

5.       Ensure Data Security:- A database management system ensures security as only the authorized users are allowed to access the data of a database. To operate different users can be given separate & restricted access levels to the data of a database.

 

6.       Data Integrity :- it means that validity of data. The database management system ensures that only valid data can be entered into the database. There may be certain standard laid down by an organization , which needs to be implemented while storing the data in the database.

 

7.       Interactive interface:- DBMS provides more convenient interface to enter or view data. In conventional systems, usually the data is not arranged properly and as a result, the availability of organized information becomes poor and it becomes too difficult to search the required information from a huge database.

 

ELEMNTS OF DATABASE

Table :- A table is a collection of logically related records.

►   The multiple records of a database are arranged together in a tabular structure to make a table.

►   It is a made up of rows and columns.

►   Column represents a field which contains information of a particular type , whereas a row represents record which contains information of the related fields.

 

NAME	FATHERS NAME	MOTHERS NAME	ADDRESS

 

 

Field :- A field represents one related part of a table.

►                    It contains the logically related data contained in a database.

►                    It is a vertical line in a table.

 

Record:- A record is a collection of multiple data which are stored in related fields that can be treated as a single unit.

►   It represents the data which are entered in a set of different fields which are related to a particular item.

 

COMPONENTS OF DATABASE PACKAGE

There are following components of database package.

1.       TABLES

2.       QUERIES

3.       FORM

4.       REPORT

 

1.       Table :- A table is the   basic element of a database in which data are kept in columns (field) or rows(record). It acts as a container which stores data.

 

2.       Queries :- it is a call or request command which is used to generate some information from tables. And it also helps to whatever you want to do with table. It is the basic tool for retrieving information from the tables of database.

 

►   It also help in the filtration of a large volume of data and displays required format of information.

►   It fetches records from one or more tables of a database so that they can be viewed, analyzed and stored on a common database.

►   It produces information according to specified need.

 

3.       Forms :- it is a layout that provides an interface to view the data or enter new data or to modify the existing data records.

►   A form can be saved separately and when needed it can be modified or deleted within the respective table.

 

4.       Reports :- it displays the information in a format as per user’s need.

►   It is used to view and print information.

►   It can be represent information whose data can be from a single table or from more than one table.

►   Reports can be previewed on screen or on page.

 

 

Field name :- A field name is the identifier of a field as it contains the data that is to be stored in that field. Generally , it represents a particular property of the table entity. For example in a table of student record ‘name’ field describes the name and ‘age’ field describes the age of the student.

►   In other words field name is the name of field.

►   It denotes the contents of that field.

►   Field name can consists of letters , numbers and some special characters like period(.), exclamation marks(!), single apostrophe(‘), square brackets([ ]) etc.

►   The name of field consists of more than two words, then join them by (_) underscore sign.

Field type :- it shows the type of the field and  what kinds of data is to be stored in that particular field and what kinds of operation can be performed on that data.

►   For example , Marks can be only numeric data and not alphabetical, so  data type of the field’ Marks’ should be Numeric.

►   The data type of a field depends upon the values to be entered in that field.

 

Field properties:- it controls the behaviour of the field.

►   They are related directly with the consistency in the data that is entered by user.

►   There are following field properties such as-

§  Entry Required :- This property is applied if the field cannot be kept blank i.e. it ensures that the user always enters some data in the field.

►   Auto value:- it refers to the data or value that is automatically applied in the field. the user has to choose “yes” if this field should contain Auto Increment values. One cannot enter data in the fields of this type.

 

§  Length :- This property is used to specify the length of the data that can be stored in a field.

 

►   For Numeric Fields:- The default length of numeric data type is a field varies with respect to its type.

 

►   For text[Varchar] fields:- The default length of Text[Varchar] is fifty(50) characters.

 

►   For Yes/No[BOOLEAN] field:- The default length of Yes/No[BOOLEAN] is one character.

 

►   For Other[OTHER] Fields:- The default length of Other[OTHER] is 2147483647.

 

§  Default Value:- This property is used to enter a default value (string) in a field.  When the user enters the data in the table then the default value will be used in each new record for the respective field selected.

 

§  Format:- This property is used to determine the format of output of the data.

 

Key field :- In DBMS  data between tables can be related on the basis of a common field called key field.

►   The key field links the data between two or more tables.

►   DBMS  sets relationship by comparing the key fields of the tables and if they match there is an automatic linking such automatic linking is called AUTO-JOIN.

►   Users can specifically link any two tables on the basis of key fields by creating a relationship.

 

Primary key:- it is a features of DBMS by which we identify the unique data in table of database.

►   It is a tool that is used in field of a table that contains unique data.

►   It is unique field and it cannot left blank.

►   There can be only one primary key in a table.

►   For example , in a bank master file , the account is identified by one Account number. Students Admission number, Account number, Customer number, Receipt number are the examples for primary key fields.

 

Foreign key:- The  common key field between  the master table (main table) and transaction table is called foreign key.

►   Its value depends on the primary key values of the master table.

 

Relationship

Relationship:- It refers to link between different tables of a database by matching the data on the basis of the common primary key field.

►   Relational databases support different types of relationships between tables which are designed to enforce the concept of referential integrity.

►   Relational database management system(RDBMS) supports different types of relationship among tables.

►   There are three types of relationship in RDBMS such as-

1.      One –to-one relationship:- It occurs when there is exactly one record of first table that corresponds to exactly one record with second table.

 

Example :- consider a database of shop in which there are ‘customer’ table, ‘Item’ table, ‘Order’ table , ‘Address’ table and ‘Ordered_Items’ table with field as ‘CustomerID’ , ‘OrderID’ , ‘ItemID’ ..etc.

 Now if we talk about one –to-one relationship then if we creates relationship between the CustomerID in the Customer table and CustomerID in the Address table. Then this is known as one-to-one relationship.  This shows the each customer may be associated with an address.

 

 

 

2.      One – to-many relationships:- It occurs when each record in first table  can have many linked records in second table but each record in second table may have only one corresponding record in first table.

 

Example :- consider a database of shop in which there are ‘customer’ table, ‘Item’ table, ‘Order’ table , ‘Address’ table and ‘Ordered_Items’ table with field as ‘CustomerID’ , ‘OrderID’ , ‘ItemID’ ..etc.

If we talk about one –to-many relationship then if we create a relationship between the CustomersID  in the Customer  table and the CustomersID in the Order table. Then this relationship is known as one-to-many relationship.

 

3.      Many-to-many relationships:- it occurs when each record in first table may have many linked records in second table and vice –versa.

 

       Example :- consider a database of shop in which there are ‘customer’ table, ‘Item’ table, ‘Order’ table , ‘Address’ table and ‘Ordered_Items’ table with field as ‘CustomerID’ , ‘OrderID’ , ‘ItemID’ ..etc.

 

If we talk about many –to-many relationship then if we create a relationship between the CustomersID  in the Customer  table and the CustomersID in the Order table as well as ProductID in Order table and ProductID in Ordered_Items table  . Then this relationship is known as one-to-many relationship.

 

 

History of DBMS

F  In 1960 Charles Bachan designed first DBMS system.

F  In 1970 Codd introduced IBM’S information Management System(IMS).

F  IN 1976 Peter Chen coined and defined the Entity-relationship model also know as the ER model.

F  In 1980 Relational Model becomes a widely accepted as database component.

F  In 1985 Object- oriented DBMS develops.

F  In 1990 incorporation of object-orientation in relational DBMS.

F  In 1991 Microsoft ships MS access , a personal DBMS and that displaces all other personal DBMS products.

F  In 1995 the first internet database applications was launched.

F  In 1997 XML applied to database processing. Many vendors begin to integrate XML into DBMS products.

 

Difference between DBMS and  Flat File Management System

 

§  In DBMS multi –user can be access at a time.

         Whereas in flat file management System , it does not support multi-user access features.

 

§  DBMS design to full fill the need for small and large businesses.

         Whereas flat file management system is only limited to smaller DBMS system.

 

§  DBMS removes the redundancy and integrity.

         Whereas flat file management system does not remove the redundancy and integrity issues.

§  DBMS is an expensive but in the long term total cost of ownership is cheap.

                Whereas flat file management system is cheaper.

§  DBMS is easy to implement complicated transactions.

         Whereas flat file management system does not support for complicated transactions.

 

Key in database :-

There are following keys in database such as-

Primary key:-  it is a features of DBMS by which we identify the unique data in table of database.

►   It is a tool that is used in field of a table that contains unique data.

►   It is unique field and it cannot left blank.

►   There can be only one primary key in a table.

►   For example , in a bank master file , the account is identified by one Account number. Students Admission number, Account number, Customer number, Receipt number are the examples for primary key fields.

 

Candidate Key:- A super key with no redundant attribute is known as candidate key.

►   It is selected from the set of super keys, but  we take care while selecting candidate key is that the candidate key should not have any redundant attributes. So due to this reason they are called as minimal super key.

►   Example :- lets take a ‘employee’ table . this table has three attributes. EmpId , EmpNum and EmpName.  EmpNum will be having unique values and   EmpName can have duplicate values as more than one employees can have same name.

Employee

EmpId	EmpNum	EmpName
10001	E222	Shivam mishra
10002	E223	Gaurav pandey
10003	E224	Inderjeet gupta
10004	E225	Ankur sinha

 

Note above the Employee table it has candidate key i.e. EmpId and EmpNum .

 

 

 Alternate key :- it is the key that has not been selected to be the primary key, but are candidate keys.

►   However , it is considered a candidate key for the primary key.

►   It is also known as secondary key.

►   A candidate key is not selected as a primary key is called alternate or secondary key. Candidate is an attribute or set of attributes that you can consider as a primary key.

►   Example :- lets Student table

 

StudentID	StudentRoll	StudentName	StudentEmail
10001	1	Shivam mishra	shivam@gmail.com
10002	2	Gaurav pandey	gaurav@gmail.com
10003	3	Inderjeet gupta	inderjeet@gmail.com
10004	4	Ankur sinha	ankur@gmail.com

 

                            Note – Above table StudentID, StudentRoll, StudentEmail are the candidate keys. They are considered candidate keys since they can uniquely identify the student record. select any one of the candidate key as the primary key . rest of the two keys would be Alternate or Secondary key.

Foreign key :- it is the primary key of master table within child table or transaction table.

►   The  common key field between  the master table (main table) and transaction table is called foreign key.

►   Its value depends on the primary key values of the master table.

►   It is a key which is used to link two tables together. This is sometimes called as referencing key.

►   It is a column or a combination of column whose values match a primary key in a different table.

 

Example :- let us take two table first ‘Student1’  and  second  ‘studentadd’

 

Student1

Studentroll	studentname	studentclass
1	Inderjeet gupta	12th
2	Ayush kumar	12th
3	Gaurav pandey	12th
4	Ankur sinha	12th
5	Shivam mishra	12th

 

Studentadd

Studentroll	studentaddress	studentfee
1	Kumhar toli	20000
2	Kumhar  toli	20000
3	Hamidganj	20000
4	hamidganj	20000
5	hamidganj	20000

 

Note – above in table Studentroll is the common key field in both table. so this is known as foreign key.