Module_4 Database Fundamentals

Foundation Course in Applications: Database Fundamentals UNIT - 4: DATABASE FUNDAMENTALS Structure 4.0 Learning Objectives 4.1 Introduction 4.2 Data Base for beginners 4.3 DBMS 4.4 RDBMS 4.5 SQL 4.6 NoSQL 4.7 Summary 4.8 Glossary 4.9 References 4.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 4.1 INTRODUCTION A database is a compilation of data that is arranged hence that it could be simply retrieved, handled, and updated. In old age, data are stored manually in a file-based system from which it takes time to extract the data required. Computer databases usually include combinations of data records or files, consisting of information regarding sales transactions or dealings with certain customers. Page 1 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals ● 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 programs provides access to 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. Database 4.2 DATABASE FOR BEGINNERS DBMS is a compilation of data that is interrelated by nature and collection of program data that helps to access it when required. The information gathering with an inherent meaning is called the database that contains data relevant to a company. The primary aim of database systems is to Page 2 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals store as well as retrieve the stored information in a convenient and efficient way. For example, let’s consider the names, telephone numbers, and addresses of the people of a company. One might record this data either in an indexed address book or have to store it on a diskette by using a PC and software like DBASE (IV or V), Microsoft access, or EXCEL. Example: - Employee records, Students records, telephone Directory, Inventory control, Patients records, Sales reports, etc. 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 Page 3 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals Types of Databases 4.3 DBMS It is a set of programs that enables the user in creating and maintaining an organized view of data i.e. database. It is known to be general-purpose software that provides the processes of defining the data, constructing and manipulating the information of the database for different applications. Database systems manage large bodies of information. Data management involves the functions regarding defining the storage structure of information and providing manipulation mechanisms of information. Additionally, it manages information protection despite any kind of system crashes or any unauthorized attempts to access data. Multiuser data sharing is always at high-security risks and possibly gives anomalous results. DBMS allows users the following tasks: ● Data Definition: It is used for the creation, modification, and removal of the 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. Page 4 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals ● 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, maintaining data integrity, enforcing data security, dealing with concurrency control, monitoring performance, and recovering information corrupted by unexpected failure. Advantages of DBMS Information in any organization plays a vital part of the system; Computer scientists have judged this issue very well and developed large support of concepts and new techniques for storing and managing the data effectively. A few advantages of DBMS are listed below: • Data Independence. • Data Access efficiency. • Data Integrity. • Data security. • Data administration. • Concurrent access. • Crash recovery. • Reduced Application Development Time. Disadvantages of DBMS • Data redundancy • Data inconsistency • Data accessing issues • Data isolation • Data integrity. Page 5 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals • Concurrent data accessing issues • Security issues Types of DBMS: There are four types of DBMS ● Relational database ● Object-oriented database ● Hierarchical database ● Network database 4.4 RDBMS The simplest method to understand a database is as a compilation of related files. Imagine a file of sales orders in a shop. Then there's another file of products, consisting of the stock records. To complete an order, you'd require to search the product in the order file and then look up and adjust the stock levels for that specific product in the product file. A database and the software that regulates the database called a database management system (DBMS), facilitate this kind of task. Most databases today are relational databases, named like this as they deal with tables of data associated with a common field. RDBMS stands for Relational Database Management System. RDBMS is the foundation for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. A Relational database management system (RDBMS) is founded on the relational model as presented by E. F. Codd. Page 6 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals 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 stored in RDBMS. Let's see the example of the 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 a record. It contains the specific information of each 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. 1 Ajeet 24 B.Tech Page 7 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals 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 a student's name. Ajeet Aryan Mahesh Ratan Vimal 4.5 SQL SQL (often pronounced “sequel”) is an abbreviation for Structured Query Language. It is a simple and powerful language used to create, access, and manipulate data and its structure in the database. Other data storage tools like spreadsheets or CSVs are not always practical or advisable for handling large volumes of data. Imagine, for example, an e-commerce company with terabytes of data that is being updated regularly by people in different parts of the globe. Managing this data with spreadsheets would be a logistical and security nightmare! Databases allow for the secure storage of a large volume of data, and SQL is the tool we use to access and make changes to that data without needing to store it all in a file on our local machine. In SQL, we express our requests to the database as “queries”. A query is a request to a database to return a specific subset of data based on some condition or to update a particular value in the database. Also, they are using different dialects, such as − Page 8 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals ● MS SQL Server using T-SQL, ● Oracle using PL/SQL, ● MS Access version of SQL is called JET SQL (native format) etc. Applications of SQL As mentioned before, SQL is one of the most widely used query languages 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 embedding within other languages using SQL modules, libraries & pre-compilers. ● Allows users to create and drop databases and tables. ● Allows users to create a view, stored procedures, functions in a database. ● Allows users to set permissions on tables, procedures, and views. 4.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 Page 9 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals 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 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. Page 10 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: Database Fundamentals ● 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 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 Page 11 of 13 All Rights Reserved. Vol. TLE001/03-2022

Foundation Course in Applications: 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 Applications: 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. 4.7 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. 4.8 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. 4.9 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