DBMS

What Is Data In DBMS –:

Data :-

  -> Data means know facts that can be recorded and that

      have implicit meaning.

  -> In simple words data is information.

Daily Activities Uses Data :-

  ->Banking system.

  ->Library system.

  ->Contact list.

  ->Enquiry System.

  ->Billing system.

   ->Hotel management.

 Why Software ?

    ->Any software is made to manage data.

    ->Several benefits :-

    1) Less time.

    2) Less human resources

    3) Accuracy.

    4) Free from manual errors.

Simple Programming Examples :-

    ->Add two numbers.

    ->Factorial of a number.

    ->Calculate area.

    ->Calculate simple interest.

Conclusion :-

    ->Data is any information.

    ->In software system we manage data.

    ->Manage data means storing, processing and         extracting data

What Is Data Persistence In DBMS -:

Data Persistence :-

    ->Data persistence means existing of data.

    ->In any program we want to manage data.

    ->Data that is required to manage needs to be stored

       somewhere until job is not over.

Life of Data :-

    ->Constants in the expressions.

    ->Data stored in variables.

    ->Data stored in secondary storage.

Life of variable <= Life of program

Conclusion :-

    ->We need to analyse that for how long we want data

       to persist in the memory.

    ->Data persistence is life of data.

    ->Sometimes we need data even beyond the life of           the program.

What Is Database In DBMS -:

Data In Secondary Storage :-

    ->Data is sometimes needed to remain available

       even beyond the life of the program.

    ->In such case data is stored in secondary storage

       like hard disk.

Files :-

    ->When data is stored in the secondary memory it is

        bound to reside in a file.

    ->File is an operating system concept, which makes

       separation among bundles of heterogeneous data

       stored in the storage.

What Is Database :-

    ->A software application most of the times required

       to store relevant information in the secondary storage

       ( in the form of files ).

    ->A database is a place where all your application related

       data is stored.

    ->One application data can be stored in a bunch of files.

    ->We can say , database is a collection of files.

What Is DBMS :-

What Is File Handling :-

    ->Files need to be handled by set of programs.

    ->This bunch of program is a part of actual application.

What Is DBMS :-

    ->DBMS is a database management system.

    ->It is a collection of program that enables users to

       create and maintain a database.

    ->The DBMS is a general purpose software system that

       facilitates the process of defining , constructing ,

       manipulating and sharing databases among various

       users and application.

DBMS Vs Traditional File Handling In DBMS -:

DBMS Vs File Handling :-

 

    ->Exhaustive analysis , huge coding and rigorous testing

       of file handling code is expansive , time consuming and      risky.

    ->DBMS is general purpose software which can be used in      place of file handling code to perform the same task in

       the most efficient way possible.

DBMS Vs File Handling :-

  ->Controlling Redundancy.

    ->Restricting Unauthorized access.

    ->Efficient Query Processing.

    ->Provide Backup and Recovery.

    ->Multiple user interfaces.

    ->Integrity Constraints.

    ->Relationship among data.

    ->Development time.

    ->Flexibility.

Database Architecture In DBMS -:

Two Types of Architecture :-

    ->Database architecture in DBMS is logically divided into

       two types :

    1) Two tier client server architecture.

    2) Three tier client server architecture.

Two Tier :-

    ->Two tier architecture is used for application programs

       that runs on client side.

    ->An interface called ODBC (Open Database Connectivity )

       provides an API that allow client program to call the DBMS.

    ->Most DBMS vendors provide ODBC drivers.

Three Tier :-

    ->Three tier client / server database architecture is      commonly used architecture for web applications.

    ->Intermediate layer called application server.

Database Model In DBMS -:

Database Model :-

    ->Database model is a logical frame in which data is stored.

    ->The model also describes the relationships between          different parts of the data.

Traditional Model :-

    ->Hierarchical model

    ->Network model

    ->Relational model

1) Hierarchical Model :-

    ->In this model each entity has only parent but can have      several children.

    ->At the top of hierarchy there is only one entity which is      called ROOT.

2) Network Model :-

    ->In this model entities are organised in a graph in which      entities can be accessed through several path.

3) Relational Model :-

    ->In this model data is organised in two dimensional tables      called relations.

RDBMS Concepts In DBMS -:

What Is RDBMS :-

    ->A relational database management system (RDBMS) is a         database management system based on relational        introduced by E. F. Codd.

    ->In relational model data is represented in terms of      tuples(rows).

    ->RDBMS is used to manage relational database.

Tables :-

    ->Relational database is a collection of organised set tables      from which data can be accessed easily.

Table :-

    -> A table is a collection of data elements organised in      terms of rows and columns.

Record :-

    ->A single entry in a table is called a record on row.

    ->A record in a table represents set of related data.

Field :-

    ->A table consists of several records (row) , each record     can be broken into several smaller entities known as     fields.

Column :-

    ->A column is a set of value of a particular type.

    ->Sometimes also known as attributes.

Codd`s 12 Rules In DBMS -:

Who Is Codd ?

  ->Edgar Frank “Ted” Codd (19 Aug 1923 – 18 April 2003) was      an English computer scientist who, while working for      IBM, invented the relational model for database      management, the theoretical basis for relational      databases.

13 Not 12 :-

    ->Codd proposed thirteen rules , numbered 0 to 12.

    ->According to him if a database meets these rules, it can

       be called relational database management system. 

Rule 0 :-

  ->The system must qualify as relational as a database and as a management system.

  ->For a system to qualify as a relational database management system , that system must use its relational facilities to manage database.

  ->The other 12 rules drive from this rule.

Rule 1 :- Information Rule :-

  ->All information including metadata is to be represented as stored data in cells of tables.

  ->The rows and columns have to be strictly unordered.

Rule 2 :- Guarantied Access :-

  ->Each unique piece of data (atomic value) should be accessible by TableName + Primary Key (row) + Attribute (Column)

Rule 3 :- Systematic Treatment of null :-

  ->Null has several meanings , it can mean missing data, not applicable or no value . It should be handled consistency.

  ->Primary key must not be null.

  ->Expression on null must give null.

Rule 4 :- Active Online Catalogue :-

  ->Database dictionary must have description of database.

  ->Catalogue to be generated by same rule as rest of the database . The same query language to be used on catalogue as on application database.

Rule 5 :- Powerful Language :-

  ->One well defined language must be there to provide all manners of access to data.

  ->SQL.

Rule 6 :- View Updation :-

  ->All view that are theoretically updatable should be updatable by system.

Rule 7 :- Relational Level Operation :-

  ->There must be insert, delete, update operations at each level of relations.

  ->Set operation like Union , Intersection and Minus should also be supported.

Rule 8 :- Physical Data Independence :-

  ->The physical storage of data should not matter to the system.

  ->If say , some file supporting table were renamed or moved from one disk to another , it should not effect the application.

Rule 9 :- Logical Data Independence :-

  ->If there is change in the logical structure (table structure ) of the database the user view of data should not change.

  ->Say , if a table is split into two tables , a new view should give result as the join of the two tables . This rule is most difficult to satisfy.

Rule 10 :- Integrity Independence :-

  ->The database should be able to con-force its own integrity rather than using other programs. Key and check constraints , trigger etc should be stored in data dictionary.

  ->This also make RDBMS independent of front-end.

Rule 11 :- Distribution Independence :-

  ->A database should work properly regardless of its distribution across a network . This lays foundation of distributed database.

Rule 12 :- Non-Subversion rule :-

  ->If low level access is allowed to a system ,it should not be able to subvert or bypass integrity rule to change data.

  ->This can be achieved by some sort of looking or encryption.

Database Keys In DBMS -:

Keys :-

->Keys are a key part of a relational database.

  ->They ensure each record within a table can be uniquely identified by one or a combination of fields within the table.

  ->They help in identifying the relationship between data.

Types of Keys :-

  1)Candidate Keys

  2)Primary Keys

  3)Foreign Keys

Super Key :-

  ->A super key is any combination of fields within a table that uniquely identifies each record within that table.

1)Candidate Key :-

    ->A candidate key is a subset of a super key.

  ->A candidate key is a single field or the least combination of fields that uniquely identifies each record in the table.

    ->In order to be eligible for a candidate key it must pass certain criteria :-

    - It must contain unique values.

    - It must not contain null values.

    - It contains the minimum number of fields to ensure     uniqueness.

    - It must uniquely each record in the table.

2)Primary Key :-

  -> Once your candidate keys have been identified you can now select one to be your primary key.

  ->A primary key is a candidate key that is most appropriate to be the main reference key for the table.

  ->The primary key must contain unique values.

  ->It must never be null.

  ->It uniquely identify each record in the table.

3)Foreign Key :-

  ->A foreign key is a primary key from one table that appears as a field in another where the first table has a relationship to the second.

ER Diagram In DBMS -:

Database :-

  ->Application data need to be stored in set of files (physically) and conceptualize as set of tables (logically).

  ->Set of such tables is known as database , where you keep all application data.

ER – Diagram :-

  ->ER Diagram is a diagrammatic representation of logical structure of database.

  ->ER diagram describes relationship between tables.

  ->Peter Chen developed ERDs in 1976.

  ->Since then Charles Bachman and James Martin have added some slight refinements to the basic ERD principles.

Entities & Attributes :-

  ->The basic object that the ER model represents is an entity , which is a thing in real world with an independent existence.

  ->Each entity has attributes . They are the properties whose values are the data that stored in the database.

First Normal Form In DBMS -:

Normalization :-

  ->The normalization process was first proposed by codd in 1972.

  ->This process takes a relation schema through a series of tests to certify whether it satisfies a certain normal form.

How Many Normal Forms ?

  ->Initially , codd proposed three normal forms , which he called first, second and third normal form.

  ->A stronger definition of third normal form is called Boyce –Codd normal form , was proposed later by Boyce and Codd.

  ->Later fourth and fifth normal form were proposed. 

Why Normalization ?

  ->It is the process of analysing the given relation schema based on their functional dependencies and primary keys to achieve the desirable properties of :-

    - Minimizing redundancy.

    - Minimizing the insertion, deletion and update

First Normal Form :-

  ->First normal form states that , the domain of an attributes must include only atomic values.

  -> Atomic ??

Second Normal Form In DBMS -:

Second Normal Form :-

  ->Second normal form 2NF is a normal form used in database normalization .

  ->2NF was originally defined by E.F. codd.

  ->A table that is in first normal form must meet additional criteria if it is to qualify for second normal form.

  ->Second normal form states that it should meet all the rules for 1NF and there must be no partial dependencies of any of the columns on the primary key.

Third Normal Form In DBMS -:

Third Normal Form :-

  ->Third normal form 3NF is a normal form used in database normalization .

  ->3NF was originally defined by E.F. codd.

  ->Codd`s definition that a table is in 3NF if and only if both of the following conditions held.

    - The relation R is in second normal form.

    - Every non-prime attribute of R is non-transitively dependent on every key of R.

Introduction to Sql In DBMS -:

What is Sql ?

    ->Sql stands for structure query language.

    ->Standard language (ANSI standard) for dealing with all relational database Ex.  Oracle, mysql, mssql,Sybase.

About Sql :-

    ->Initially developed by IBM.

    ->Initial name was SEQUEL (structured English query language).

    ->Although most database systems use sql , most of them also have their own additional proprietary extensions that are usually only used on their system.