Class 12 Informatics Practices Revision Notes Chapter 14 Back-End Database

Class 12 Informatics Practices Quick Revision notes Chapter 14 Back-End Database

Small Database Systems
The term back-end database is most widely used among developers using small database programming systems, which can contain the end user application programming within the database as a single item. The developer must decide whether to include the application programming with the data in a single database or whether to separate them into two database files, according to the client/server model. For simple database applications, it is common for all programs to be stored with the data. This results in a single file and is easier to develop at the expense of scalability and concurrency.
Enterprise Database Systems
The term back-end database is not widely used among developers using larger or enterprise database systems. This is because enterprise database systems enforce the use of the client/server model and do not have the option to include the application programming within their databases. All such databases are used as back-end databases and so the term is redundant. Front-end and back-end are terms used to characterise program interfaces and services relative to the initial user of these interfaces and services. (The ‘user’ may be a human being or a program).
A ‘front-end’ application is that application with which the users interact directly. A ‘back-end’ application or program serves indirectly in support of the front-end services, usually by being closer to the required resource or having the capability to communicate with the required resource. The back-end application may interact directly with the front-end or typically a program called from an intermediate program that mediates front-end and back-end activities.
Purpose of a Database
The purpose of a database is to satisfy as many applications as possible, which typically makes them even more complex than special purpose database. Databases organise, store and retrieve data as fast as possible while protecting data integrity. Data retrieval would be considered the most important function. Back-end databases are used to store information that represent real life things, e.g. employees in a company or books in a library catalog are often stored in database.
Database Development Life Cycle (DDLC)
It is a structured process, which is imposed upon the development of the database portion of an application. A database developed by this structured process would be good for an effective application. On every step, database is involved and refined. The systematic approach used for developing a database is referred to as Database Development Life Cycle.

Database Initial Study
Phase I Analyse the Company Situation
In this phase we consider:

1. What is the organisation and its general environment?
2. What is its mission within that environment?
3. What is the organisation’s structure?
4. What is the requirements for changing system/database?

Phase II Define Problems and Constraints

1. How does the existing system function?
2. What input does the system require?
3. How is the system output used? And by whom?
4. What documents does the system generates?
5. What are the operational relationship among business units?
6. What are the limits and constraints imposed on the system?

Phase HI Define the Objective

1. Objective of the proposed system.
2. Does the system need to share the data with other system or users?
3. Will the system interface with the other existing or the future system in the company?

Phase IV Define Scope and Boundaries

1. Scope What is the extent (size or scale of something) of the design based on operational requirements?
2. Boundaries/Limits
   • Budget
   • Hardware and Software
   • External of organisational change is required.

Database Design
Database design is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters, needed to generate a design in a Data Definition Language (DDL), which can then be used to create a database. A fully attributed data model contains detailed attributes for each entity.
Conceptual Design
The data modeling is used to create an abstract database structure, which permits you to emphasise on the big picture without getting into details. Here you can divide this model into smaller chunks for better understanding. One of the most common technique used for conceptual design is Entity Relationship Model (ER Model). Model produced at this stage is of client world view not real world.
Logical Design
Logical design translates the conceptual design into the internal model for a selected DBMS. It specifies:

1. The high level design.
2. What tables and connections between them should exist?
3. It recognises entities in your model and relationship between them.
4. Dividing a table into smaller table and associated with relations called normalisation.

Logical design comes after conceptual design. So, it ensures:

1. What are the columns in each table?
2. It includes mapping of all objects in the model to the specific constructs used by the selected database software.
3. In RDBMS, the logical design include the design of tables, indexes, views, transactions, etc.

Physical Design
Physical design is where you translate the expected schemas into actual database structure. At this time, you have to map entities to tables, relationships to foreign key, unique identifiers to unique key. Translating your schemas into actual database structures requires creating the following:

1. Partitions
2. Indexes
3. Constraints
4. Access-control
5. Implementation of some business rule that could not be modelled earlier.

Implementation and Loading
This phase of life cycle:

1. Creates the database storage group.
2. Creates the database within the storage group.
3.  Assigns the rights to use the database to a database administrator.
4. Creates table space (storage location where the actual data underlying database objects can be kept) within the database.
5. Creates table(s) within the table space(s).
6. Assigns rights to the table spaces and the tables within specified table spaces.
7. Loads the data.
8. Backup and recovery.

Testing and Evaluation
Testing and Evaluation (a way to determine the subject merit, worth and significance, using the criteria governed by a set of standards) phase occur in parallel with the application programming.
Programmers use database tools (e.g. report generators, screen painters and menu generator) to prototype the application during the coding of the programs.
If the implementation fails then the options to enhance the system are:

1. Fine-tuning the specific system and DBMS configuration parameters.
2. Modify the physical design.
3. Upgrade or change the DBMS and hardware platform.
4. Modification of logical design.

Operation
After passing the evaluation stages, comes the operation phase.
In the beginning of the operational phase always start the process of system evolution, where it changes from a simple to a more complex form.
Maintenance
In this phase, the following points need to be performed:

1. Preventive maintenance.
2. Corrective maintenance.
3. Adaptive maintenance.
4. Assignment and maintenance of access permissions.
5. Generation of database access.
6. Periodic security audits.

Storage Manager
A storage manager is a program module that provides the interface between the low level data stored in the database and the application program and queries.
Transaction Manager
The transaction manager is responsible for ensuring that the database remains in a consistent state despite system failures. The transaction manager also ensures that concurrent transaction executions proceed without any conflicts.
Entity Relationship Model
The Entity Relationship (ER) data model is based on a perception of a real world, which consists a set of basic objects called entities and relationships among these objects. The ER model was introduced by P.P.Chen. Chen not only introduced ER model but he also introduced a corresponding diagramming technique.
In software engineering, an Entity Relationship model (ER model for short) is an abstract way to describe a database. It usually starts with a relational database, which stores data in tables. Some of the data in these tables point to data in other tables.
Some terminology related to ER model are as follows:
1. Entity An ‘Entity’ is an object that exists and is distinguishable from other objects. An entity is any object, place, person, concept or activity about which an enterprise records data. While diagramming, entities are named and represented by a rectangle as shown in figure:

• Entity type An entity type (also called entity set) is a set of entities of same type, i. e. which share common properties, e.g. the set of all persons having an account at a bank, can be defined as the entity type CUSTOMER. Similarly, TEACHER, SUBJECT and STUDENT are also entity types or entity sets.
• Entity instance An entity instance is an instance of entity type, i.e. an entity instance is a specific individual, thing or object, e.g. ‘Shyam’ is an instance of entity type STUDENT; ‘Computer’ is an instance of entity type SUBJECT and so on.

2. Relationship A relationship is defined as an association among several entities. It connects two or more entities into a relationship. It represented by a diamond o. e.g. there is a relationship between artist and song.
This relationship represents the fact that an artist performs several songs and a song is performed by many artists. You may name this relationship as PERFORMS. A relationship type defines the association of entity types (e.g. artist-song relationship) and a relationship instance is an association of entity instances (e.g. shyam – tumbin relationship).

An entity can associate with itself also. e.g. in an organisation, one employee may marry another employee. Such a relationship can be represented diagrammatically as shown in figure.

There can be following types of relationship:

• One-to-One Relationship When one entity is related with another entity through a common attribute. Then such relation is known as one-to-one relationship.
• One-to-Many Relationship When one entity is related with more than one entities through a common attribute or one attribute of an entity is related with more than one attributes of another table. Then such relation is known as one-to-many relationship.
• Many-to-Many Relationship When more than one attributes of one entity is related with more than one attributes of another table. Then such relation is known as many-to-many relationship.

3. Attributes An entity is represented by a set of attributes. An attribute is a property of a given entity. An attribute instance is a particular property of an individual entity instance. An attribute type is a property of an entity type. Attributes are represented through ellipses, labelled with the name of the property. The ‘Key attributes’ are underlined.
Figure shows an entity alongwith its attributes:

In figure, RollNo is underlined because it is the key attribute. It has unique value for each student.
There are two types of attributes:

• Composite attribute If an attribute is a group of properties, it is called composite attribute, e.g. address is a composite attribute as it is a group of sub-properties such as house no., area, city, state, etc.
• Single-valued and multi-valued attribute Attributes can either be single-valued if they are capable of storing single value or be multi-valued if they are capable of storing multiple values, e.g. if an entity STUDENT has following attributes:
  RollNo, Name, Class, Subject

Then the attribute’s RollNo, Name and Class can hold one value whereas, attribute subject can hold multiple values as the student can study multiple subjects. Thus, the attribute’s RollNo, Name and Class are single valued attributes but the attribute subject is a multi-valued attribute.
The front-end and back-end interact with one another through database connectivity. Database connectivity refers to a programming interface that lets a front-end access a database on a back-end, via some means. When database connectivity is implemented in a Java application, then it is known as Java Database Connectivity, i.e. JDBC. JDBC was based largely on Microsoft’s ODBC (Open Database Connectivity) but has largely surpassed it. Compared to ODBC, JDBC has more flexible APIs which programmers can use in their applications. JDBC has all the advantages that ODBC has and caters to the needs of programmers under a variety of platforms.
JDBC provides Java developers with an industry standard API (An API or Application Programming Interface is a set of classes, methods and resources that programmers can use to do their work.) for database independent connectivity between Java Applications (Applets, Servlets, JSPs, EJBs, etc.) and a wide range of relational database management systems such as Oracle, Informix, Microsoft SQL Server and Sybase. JDBC accomplishes most of what it does through a native API, that translates Java methods to native calls.
In its simplest form, JDBC makes it possible to do the following:

1. Connect to a database.
2. Execute SQL statements to query your database.
3. Generate query results.
4. Perform updates, inserts and deletions.
5. Execute stored procedures.

The only requirement to create a JDBC application is that the driver for JDBC connectivity should be available and installed.
Benefits of Database Connectivity

1. Any database can be accessed from within the application code if its connectivity driver is available.
2. Distribution of application logic in terms of front-end and back-end leads to better performance.
3. Since, database handling is entirely with back-end. The data is safely and securely processed. In other words, enhanced database security.
4. Some connectivity drivers lead to better performance, portability, optimised performance of the application.