8. Database Fundamentals

Foundation Course on Information Technology Outsourcing UNIT - 8: DATABASE FUNDAMENTALS Structure 8.0 Learning Objectives 8.1 Introduction 8.2 Data Base for beginners 8.3 DBMS 8.4 RDBMS 8.5 SQL 8.6 NoSQL 8.7 Summary 8.8 Learning Activity 8.9 Glossary 8.10 References 8.0 Learning Objectives After studying this unit, you will be able to • Examine the meaning and applications of Database • Identify DBMS and its functionality • Interpret the functioning of RDBMS • Associate with the concept of SQL • Describe the NoSQL concept 8.1 Introduction 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. 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.

Foundation Course on Information Technology Outsourcing • 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. Database 8.2 Database for Begineers Database a collection of well-organized related records incoherent manner. These records are stored in the form of a table. As we know their rows and columns. Rows are known as tuples and columns are known as attributes of the database. Example: - Employee records, Students records, telephone Directory, Inventory control, Patients records, Sales reports, etc.

Foundation Course on Information Technology Outsourcing The Database stores metadata in an area which is called the data dictionary, which represents the tables, columns, indexes, constraints, and other items that creates the database. Metadata is the data that describes the structure of data within a database. If you know how your data is arranged, then you easily can retrieve it. Because the database consists of a description of its structure, it’s “self-describing”. The database is integrated because it includes not only data items but also the relationships between data items. Types of databases The four main types of databases are • DBMS • RDMS • RDMS • NoSQL Types of Databases

Foundation Course on Information Technology Outsourcing 8.3 DBMS • 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. 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. • 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.

Foundation Course on Information Technology Outsourcing • 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 subsystemsthath 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. Types of DBMS: There are four types DBMS • Relational database • Object-oriented database • Hierarchical database • Network database 8.4 RDBMS RDBMS stands for Relational Database Management System.All modern database management systems like SQL, MS SQL Server, ORACLE, My-SQL and Microsoft Access are based on RDBMS.It is called Relational Data Base Management System (RDBMS) because it is based on relational model introduced by E.F. Codd.

Foundation Course on Information Technology Outsourcing RDBMS The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data. A table is the simplest example of data storage in RDBMS.Let's see the example of student table. ID Name AGE COURSE 1 Ajeet 24 B.Tech 2 aryan 20 C.A 3 Mahesh 21 BCA 4 Ratan 22 MCA 5 Vimal 26 BSC A row of a table is also called record. It contains the specific information of each individual entry in the table. It is a horizontal entity in the table. For example: The above table contains 5 records. Let's see one record/row in the table.

Foundation Course on Information Technology Outsourcing 1 Ajeet 24 B.Tech A column is a vertical entity in the table which contains all information associated with a specific field in a table. For example: \"name\" is a column in the above table which contains all information about student's name. Ajeet Aryan Mahesh Ratan Vimal 8.5 SQL SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language.SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language. Also, they are using different dialects, such as − • MS SQL Server using T-SQL, • Oracle using PL/SQL, • MS Access version of SQL is called JET SQL (native format) etc.

Foundation Course on Information Technology Outsourcing Applications of SQL As mentioned before, SQL is one of the most widely used query language over the databases. • Allows users to access data in the relational database management systems. • Allows users to describe the data. • Allows users to define the data in a database and manipulate that data. • Allows to embed within other languages using SQL modules, libraries & pre- compilers. • Allows users to create and drop databases and tables. • Allows users to create view, stored procedure, functions in a database. • Allows users to set permissions on tables, procedures and views. 8.6 Non-Relational Structured Query Language (Nosql) Most databases can be categorized as either relational or non-relational. Non- relational databases are sometimes referred to as “NoSQL,” which stands for Not Only SQL. The main difference between these is how they store their information. A non- relational database stores data in a non-tabular form, and tends to be more flexible than the traditional, SQL-based, relational database structures. It does not follow the relational model provided by traditional relational database management systems. Relational databases use Structured Query Language (SQL). In relational database design, the database usually contains tables consisting of columns and rows. When new data is added, new records are inserted into existing tables or new tables are added. Relationships can then be made between two or more tables. Relational databases work best when the data they contain doesn’t change very often, and when accuracy is crucial. Relational databases are, for instance, often found in financial applications. Non-relational databases (often called NoSQL databases) are different from traditional relational databases in that they store their data in a non-tabular form. Instead, non-relational databases might be based on data structures like documents. A document can be highly detailed while containing a range of different types of information in different formats. This ability to digest and organize various types of information side-by-side makes non-relational databases much more flexible than relational databases.

Foundation Course on Information Technology Outsourcing The benefits of a non-relational database Today’s applications collect and store increasingly vast quantities of ever-more complex customer and user data. The benefits of this data to businesses, of course, lie in their potential for analysis. Using a non-relational database can unlock patterns and value even within masses of variegated data. There are several advantages to using non-relational databases, including: • Massive dataset organization-In the age of Big Data, non-relational databases can not only store massive quantities of information, but they can also query these datasets with ease. Scale and speed are crucial advantages of non- relational databases. • Flexible database Expansion-Data is not static. As more information is collected, a non-relational database can absorb these new data points, enriching the existing database with new levels of granular value even if they don’t fit the data types of previously existing information. • Multiple data structures-The data now collected from users takes on a myriad of forms, from numbers and strings, to photo and video content, to message histories. A database needs the ability to store these various information formats, understand relationships between them, and perform detailed queries. No matter what format your information is in, non-relational databases can collate different information types together in the same document. • Built for the cloud-A non-relational database can be massive. And as they can, in some cases, grow exponentially, they need a hosting environment that can grow and expand with them. The cloud’s inherent scalability makes it an ideal home for non-relational databases. Non Relational Databases

Foundation Course on Information Technology Outsourcing Relational database A relational database typically stores information in tables containing specific pieces and types of data. For example, a shop could store details of their customers’ names and addresses in one table and details of their orders in another. This form of data storage is called structured data. Relational Databases Relational databases use Structured Query Language (SQL). In relational database design, the database usually contains tables consisting of columns and rows. When new data is added, new records are inserted into existing tables or new tables are added. Relationships can then be made between two or more tables. Relational databases work best when the data they contain doesn’t change very often, and when accuracy is crucial. Relational databases are, for instance, often found in financial applications. Non-relational databases (often called NoSQL databases) are different from traditional relational databases in that they store their data in a non-tabular form. Instead, non-relational databases might be based on data structures like documents. A document can be highly detailed while containing a range of different types of information in different formats. This ability to digest and organize various types of information side-by-side makes non-relational databases much more flexible than relational databases. The benefits of a non-relational database Today’s applications collect and store increasingly vast quantities of ever-more complex customer and user data. The benefits of this data to businesses, of course, lie in their potential for analysis. Using a non-relational database can unlock patterns and value even within masses of variegated data. There are several advantages to using non-relational databases, including: • Massive dataset organization-In the age of Big Data, non-relational databases can not only store massive quantities of information, but they can also query these datasets with ease. Scale and speed are crucial advantages of non- relational databases. • Flexible database Expansion-Data is not static. As more information is collected, a non-relational database can absorb these new data points, enriching the

Foundation Course on Information Technology Outsourcing existing database with new levels of granular value even if they don’t fit the data types of previously existing information. • Multiple data structures-The data now collected from users takes on a myriad of forms, from numbers and strings, to photo and video content, to message histories. A database needs the ability to store these various information formats, understand relationships between them, and perform detailed queries. No matter what format your information is in, non-relational databases can collate different information types together in the same document. • Built for the cloud-A non-relational database can be massive. And as they can, in some cases, grow exponentially, they need a hosting environment that can grow and expand with them. The cloud’s inherent scalability makes it an ideal home for non-relational databases. 8.6 Summary • Database Fundamentals introduces database concepts, including relational databases, tables and data types, data selection • Database components are hardware, software, data, procedure, and database access language. • Database, uses row to represent a single, implicitly structured data item in a table. • Database types are DBMS, RDBMS, SQL, NoSQL. 8.7 Glossary • Cluster: In computing, a cluster is a group of computers all dedicated to helping with a shared task. • Column family: A column family is a database object that stores groups of key- value pairs where each key is a row identifier and each value is a group of column names and values. • Data: In the broadest sense, data are facts or pieces of information. • Database: A database is a structure used to organize, structure, and store data. • Dataset: A dataset, sometimes spelled data set, is a single collection of data. 8.8 References • https://www.javatpoint.com/dbms-tutorial • https://www.gartner.com/en/information-technology/glossary/rdbms-relational- database-management-system • https://www.edx.org/learn/sql • https://www.mongodb.com/nosql-explained

Foundation Course in Information Technology Outsourcing: Database Fundamentals existing tables or new tables are added. Relationships can then be made between two or more tables. Relational databases work best when the data they contain doesn’t change very often, and when accuracy is crucial. Relational databases are, for instance, often found in financial applications. Non-relational databases (often called NoSQL databases) are different from traditional relational databases in that they store their data in a non- tabular form. Instead, non-relational databases might be based on data structures like documents. A document can be highly detailed while containing a range of different types of information in different formats. This ability to digest and organize various types of information side-by- side makes non-relational databases much more flexible than relational databases. The benefits of a non-relational database Today’s applications collect and store increasingly vast quantities of ever- more complex customer and user data. The benefits of this data to businesses, of course, lie in their potential for analysis. Using a non- relational database can unlock patterns and values even within masses of variegated data. There are several advantages to using non-relational databases, including: ● Massive dataset organization In the age of Big Data, non-relational databases can not only store massive quantities of information, but they can also query these datasets with ease. Scale and speed are crucial advantages of non-relational databases. ● Flexible database Expansion-Data is not static. As more information is collected, a non-relational database can absorb these new data points, enriching the existing database with new levels of granular value even if they don’t fit the data types of previously existing information. ● Multiple data structures-The data now collected from users take on a myriad of forms, from numbers and strings, to photo and video content, to message histories. A database needs the ability to store these various information formats, understand relationships between Page 12 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Information Technology Outsourcing: Database Fundamentals them, and perform detailed queries. No matter what format your information is in, non-relational databases can collate different information types together in the same document. ● Built for the cloud-A non-relational database can be massive. And as they can, in some cases, grow exponentially, they need a hosting environment that can grow and expand with them. The cloud’s inherent scalability makes it an ideal home for non-relational databases. 8.6 SUMMARY ● Database Fundamentals introduces database concepts, including relational databases, tables and data types, data selection ● Database components are hardware, software, data, procedure, and database access language. ● Database, uses a row to represent a single, implicitly structured data item in a table. ● Database types are DBMS, RDBMS, SQL, NoSQL. 8.7 GLOSSARY ● Cluster: In computing, a cluster is a group of computers all dedicated to helping with a shared task. ● Column family: A column family is a database object that stores groups of key-value pairs where each key is a row identifier and each value is a group of column names and values. ● Data: In the broadest sense, data are facts or pieces of information. ● Database: A database is a structure used to organize, structure, and store data. ● Dataset: A dataset, sometimes spelled data set, is a single collection of data. 8.8 REFERENCES ● https://www.javatpoint.com/dbms-tutorial ● https://www.gartner.com/en/information- technology/glossary/rdbms-relational-database-management-system ● https://www.edx.org/learn/sql ● https://www.mongodb.com/nosql-explained Page 13 of 13 All Rights Reserved. Vol. TLE001/03-2022