Unit – 1

Introduction to database systems

Q1) Give a brief introduction of the database?

A1) Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc.

Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum.

In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable.

What is Database?

A database is an organized collection of data, so that it can be easily accessed and managed.

You can organize data into tables, rows, columns, and index it to make it easier to find relevant information.

Database handlers create a database in such a way that only one set of software program provides access of data to all the users.

The main purpose of the database is to operate a large amount of information by storing, retrieving, and managing data.

There are many dynamic websites on the World Wide Web nowadays which are handled through databases. For example, a model that checks the availability of rooms in a hotel. It is an example of a dynamic website that uses a database.

There are many databases available like MySQL, Sybase, Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.

Modern databases are managed by the database management system (DBMS).

SQL or Structured Query Language is used to operate on the data stored in a database. SQL depends on relational algebra and tuple relational calculus.

A cylindrical structure is used to display the image of a database.

Fig 1- Image of a database

Q2) What is three schema architecture?

A2) The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks.

The client/server architecture consists of many PCs and a workstation which are connected via the network.

DBMS architecture depends upon how users are connected to the database to get their request done.

Types of DBMS Architecture

Fig 2 - DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two types like: 2-tier architecture and 3-tier architecture.

1-Tier Architecture

●        In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.

●        Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.

●        The 1-Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response.

2-Tier Architecture

●        The 2-Tier architecture is same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API's like: ODBC, JDBC are used.

●        The user interfaces and application programs are run on the client-side.

●        The server side is responsible to provide the functionalities like: query processing and transaction management.

●        To communicate with the DBMS, client-side application establishes a connection with the server side.

Fig 3: 2-tier Architecture

3-Tier Architecture

●        The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.

●        The application on the client-end interacts with an application server which further communicates with the database system.

●        End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.

●        The 3-Tier architecture is used in case of large web application.

Fig 4: 3-tier Architecture

Q3) What is DBMS and what are its characteristics?

A3) Database management system is software that is used to manage the database.

What is Database?

The database is a collection of interrelated data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

●        Database management system is a software which is used to manage the database. For example: MySQL, Oracle, etc are a very popular commercial database which is used in different applications.

●        DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database and a lot more.

●        It provides protection and security to the database. In the case of multiple users, it also maintains data consistency.

DBMS allows users the following tasks:

●        Data Definition: It is used for creation, modification, and removal of definition that defines the organization of data in the database.

●        Data Updation: It is used for the insertion, modification, and deletion of the actual data in the database.

●        Data Retrieval: It is used to retrieve the data from the database which can be used by applications for various purposes.

●        User Administration: It is used for registering and monitoring users, maintain data integrity, enforcing data security, dealing with concurrency control, monitoring performance and recovering information corrupted by unexpected failure.

Characteristics of DBMS

●        It uses a digital repository established on a server to store and manage the information.

●        It can provide a clear and logical view of the process that manipulates data.

●        DBMS contains automatic backup and recovery procedures.

●        It contains ACID properties which maintain data in a healthy state in case of failure.

●        It can reduce the complex relationship between data.

●        It is used to support manipulation and processing of data.

●        It is used to provide security of data.

●        It can view the database from different viewpoints according to the requirements of the user.

Q4) What are the advantages and disadvantages of DBMS?

A4) Advantages of DBMS

●        Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.

●        Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.

●        Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.

●        Reduce time: It reduces development time and maintenance need.

●        Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required.

●        multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces

Disadvantages of DBMS

●        Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software.

●        Size: It occupies a large space of disks and large memory to run them efficiently.

●        Complexity: Database system creates additional complexity and requirements.

●        Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever.

Q5) What is the purpose of the database system?

A5) It is a set of tools that allows a user to construct and manage databases. In other words, it is general-purpose software that enables users to define, construct, and manipulate databases for a variety of applications.

Database systems are made to handle massive amounts of data. Data management entails both the creation of structures for storing data and the provision of methods for manipulating data. Furthermore, despite system crashes or efforts at illegal access, the database system must preserve the security of the information stored. If data is to be shared across multiple users, the system must avoid any unexpected outcomes.

Fig 5: Process of transforming data

The figure above depicts the process of transforming data into information, knowledge, and action in a database management system.

The file system was used as the foundation for the database applications.

The goal of a database management system (DBMS) is to transform the following:

1. Data into information.

2. Information into knowledge.

3. Knowledge of the action.

Q6) What is DBMS architecture and what are its different types?

A6) You may split the database management system into five main components, which are

1. Hardware
2. Software
3. Data
4. Procedure
5. Database Access Language

In order to see how they all work together to form a database management system, let's have a basic diagram.

Fig 6: Component of DBMS

1. Hardware

When we say hardware, before any data is successfully stored in the memory, we mean computers, hard discs, I/O channels for data, and any other physical part involved.

When we run Oracle or MySQL on our personal computer, the hard disc of our computer, the keyboard we use to type all the commands, the RAM of our computer, the ROM, all become part of the hardware of the DBMS.

2. Software

As this is the software that governs everything, this is the key part. More like a wrapper around the physical database, the DBMS programme gives us an easy-to-use interface for storing, accessing and upgrading data.

The DBMS programme is able to understand and translate the Database Access Language into actual database commands in order to execute them on the DB.

3.  Data

Data is the resource for which the DBMS is intended. The motive behind the creation of DBMS was to store and use information.

Data saved by the user is present in a standard database and meta data is stored.

Metadata is data about knowledge. To better understand the data contained in it, this is information stored by the DBMS.

4.  Procedure

Procedures refer to general instructions for using a method to handle a database. This includes procedures for setting up and installing a DBMS, logging in and logging out of the DBMS programme, database maintenance, backups, report creation, etc.

5. Database Access language

Database Access Language is a basic language designed to access, attach, update and delete data stored in any database by writing commands.

In the Database Access Language, a user can write commands and send them to the DBMS for execution, which is then interpreted and executed by the DBMS.

The user can use the access language to build new databases, tables, insert data, fetch stored data, update data and remove data.

Q7) Define entity relationship model?

A7) Entity-relationship model

• The logical structure of a database can be expressed by an E-R diagram.
• Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.
• Using relationships, various entities are related.
• To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are: -

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

Fig 7: E-R relationship

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department entity has two attributes: -

Dept_id

Dept_name

Relation Teaches in gives relationship between Teacher, Department.

Relationship can be of the type:

1:1 → One to one

1:M   → One to many

M:1   → Many to one

M:M   → Many to many

Q8) Explain network model?

A8) Network model

• Network model uses two different data structures: -

a) A record type is used to represent as entity set.

b) A set type is used to represent a directed relationship between two record types.

• This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.
• Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.
• This was the most commonly used database architecture prior to the advent of the Relational Model.

Fig 8: Network model

Q9) Write about relational model?

A9) Relational model

• Relational model uses a collection of tables to represent both data and the relationships among those data.
• This model is useful for constructing a database that can then be converted into relational model tables.
• Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.
• In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

A. Teacher Table

B. Department Table

C.  Teaches in table

Q10) Describe an object oriented data model?

A10) Object oriented data model

The object-oriented database derivation is the integrity of object-oriented

Programming language systems and consistent systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.

Fig 9: Object-oriented model

Object-oriented databases use small, recyclable separated of software called objects. The objects themselves are stored in the object-oriented database. Each object contains of two elements:

1. Piece of data (e.g., sound, video, text, or graphics).

2. Instructions, or software programs called methods, for what to do with

The data.

There are two types of ORM: -

a. Object-Oriented Data Model (OODM)

b. Object-Relational Data Model (ORDM)

These models are used to handle objects:

1) To provide support for complex objects.

2) To provide user extensibility for data types operators and access methods.

3) There should be mechanism to support:

a) Abstract data type.

b) Data of type ‘procedure’.

c) Rules.

Q11) What are the challenges in building a dbms?

A11) Conflicting goals, such as adherence to design standards (design elegance), processing speed, and information requirements, force database designers to make design tradeoffs.

Design standards

Design guidelines must be followed when creating a database. As a result of these standards, you've been able to create logical structures that reduce data redundancy and, as a result, the chance of destructive data anomalies. You've also learned how to avoid nulls as much as feasible according to standards. In a nutshell, design standards allow you to work with well-defined components and evaluate their interactions with precision. It is practically impossible to establish a suitable design process, analyse an existing design, or trace the expected logical consequence of design modifications without design standards.

Processing speed

High processing speeds are often a major priority in database architecture in many organisations, particularly those that generate huge quantities of transactions. Minimal access time is achieved by reducing the quantity and complexity of logically desirable relationships, which can be achieved by increasing processing speed. For example, a "ideal" design might eliminate nulls by utilising a 1:1 relationship, whereas a better transaction-speed design might combine the two tables to minimise the need for an additional relationship and avoid nulls by using dummy entries. If data retrieval speed is a priority, you may be required to include derived attributes in the design.

Information requirements

The desire of real-time data could be the goal of database design. Complex data requirements may necessitate data transformations, as well as an increase in the number of entities and characteristics in the design. To ensure maximal information creation, the database may have to trade some of its "clean" design structures and/or part of its high transaction performance.

An important goal is to create a design that fits all logical requirements and design norms. However, if this flawless design fails to match the customer's transaction speed and/or information needs, the designer will not have done a good job in the eyes of the end user. In the real world of database design, compromises are unavoidable.

Even as the designer concentrates on the entities, characteristics, relationships, and constraints, end-user requirements like as performance, security, shared access, and data integrity should be considered. The designer must take into account processing needs and ensure that all update, retrieval, and deletion options are available. Finally, a design is worthless unless the finished product can meet all of the required query and reporting criteria.

Last but not least, document, document, document! Make a list of all design activities. Then go over what you've written again. Documentation not only keeps you on track during the design process, but it also allows you (or anyone who follows you) to pick up where you left off when it's time to make changes. Despite the obvious need for documentation, one of the most frustrating aspects of database and systems analysis work is that the "put it in writing" criterion is not always followed throughout the design and implementation stages. The creation of organisational documentation standards is a critical component of assuring data compatibility and consistency.