Unit- I: Introduction to Databases

UNIT-I Introduction to Databases Syllabus: Introduction: Database system, Characteristics (Database Vs File System), Database Users (Actors on Scene, Workers behind the scenes), Advantages of Database systems, Database applications, Data Models, Concepts of Schema, and Instance, three tier schema architecture for data independence, Database system structure. Database System: • Data: Raw facts or unprocessed figures that do not yet reveal any specific meaning. Data serves as the foundation for creating information. Examples: Entries in survey forms, such as numbers, text, symbols, dates, or measurements. • Information: The outcome of processing raw data, resulting in meaningful, contextual, and relevant content. Information supports decision-making. • Knowledge: Insights or understanding gained from information, often based on experience, familiarity, or expertise in a specific subject. • Data Management: The practice of generating, storing, and retrieving data efficiently and securely. • Databases: A shared and integrated computer-based structure that stores collections of end-user data and metadata. Examples: Customer records, inventory systems, or library catalogs. • Metadata: Data about data. Metadata describes the characteristics and relationships of the data to facilitate integration and management. • Database Management System (DBMS): A general-purpose software system that can facilitate define, construct, manipulate, and share databases among multiple users and applications. Examples: MySQL, Oracle Database, PostgreSQL, MongoDB. o Functions of a DBMS: ▪ Defining: Establishing data types, structures, and constraints (metadata). ▪ Constructing: Storing data on a medium controlled by the DBMS. ▪ Manipulating: Retrieving or updating data in the database. ▪ Sharing: Allowing simultaneous access to data by multiple users. o Characteristics of a DBMS: ▪ Acts as a "mini world" of data representation. ▪ A logical collection of data designed, built, and populated for a specific purpose. Provides user interfaces for database interaction. ▪ Ensures data integrity and security. 1 ▪ Supports queries, transactions, and data analytics. ▪ Can accommodate databases of varying sizes and complexities. • Application Programs: Software that interacts with the database by sending requests to the DBMS for data retrieval or updates. • Query: A request to retrieve specific data from a database. • Transaction: A sequence of operations that may involve reading from or writing to the database. • Life Cycle of the Database Design 1) Requirement Analysis: Textual Data Collection 2) Conceptual Design: ER – Model 3) Logical Design: Database Schema implementation 4) Physical Design: Storing and Accessing Database Paths Characteristics: • In traditional file processing, users can define and implements files for specific software as a part of the program. Ex: Grade office, Account office are having redundant data • In database, the data is maintained as a single repository. The data can be defined once and access by various users by queries or application programs. • The main characteristics of the data base approach are as follows: 1. Self – describing nature of the database systems. 2. Insulation between programs and data 3. Support of multiple views of the data 4. Sharing of the data and multi-user transaction processing 1. Self – describing nature of the database systems: ▪ The database system contains database, description of the database structure and constraints. ▪ The data can be stored as DBMS catalog (Metadata + Data + Transactions). ▪ NOSQL database is not require metadata. Stores as self – describing data includes data items and data values as single structure. ▪ But in traditional databases data definitions are part of application programs themselves. 2 Relations data Columns data Relation_Name No_of_Columns Student 4 Course 4 Section 5 Grade 3 Column_Name Sname S_Id Class Phone_no Course_name Points Data_Type varchar2(10) number(5) varchar2(10) number(10) varchar2(10) . . . number(5) Belongs_to_Relation Student Student Student Student Course Grade Internal Storage Format Column_Name Start_Position Sname 1 S_Id 11 Class 16 Phone_no 26 . . . Length (Bytes) 10 5 10 10 2. Insulation between programs and data ▪ The structure of data files is embedded in the program, any changes in the structure can change all the programs in traditional file system. But DBMS stores data files separately from accessing programs. ▪ The operations are categories into interfaces (name, args) and implementation (changes without effect interfaces). User can call operations it won't affect the interface calls from implementation calls, so the DBMS is program operation independent. ▪ Data abstraction consist of program data and program operations. The conceptual representation of data abstraction is represented as object (Name, Properties, and Operations). ▪ DBMS Allows changing structures and storage organization of the data without having to change of access programs. 3. Support of multiple views of the data: ▪ Database can access by many types of users based on their perspective; the data can be represented in the form of views. The views are also called as virtual data or subset of the data. ▪ Example: User1 can access the data for printing the transcripts and user2 can check all the documents for joining the student in the course. 3 4. Sharing of data and multi-user transaction processing: ▪ DBMS must Allow multiple users to access the data simultaneously, so DBMS need to maintain the concurrency control. ▪ In general, the transactions can perform reading or updating of the records, and these are logically connected with database without affecting the other transactions. ▪ OLTP (Online Transaction Processing) is a major part of database applications. This allows hundreds of concurrent transactions to execute per second. ▪ ACID properties are used for maintaining the concurrency control in the transactions. Differences between File System and Database Management Systems Basics Structure Data Redundancy Query processing Consistency Data Independence User Access Sharing Data Abstraction Integrity Constraints Backup and Recovery Example File System The file system is a way of arranging the files in a storage medium within a computer. Redundant data can be present in a file system. There is no efficient query processing in the file system. DBMS DBMS is software for managing the database. In DBMS there is no redundant data. Efficient query processing is there in DBMS. There is less data consistency in the There is more data consistency because of file system. the process of normalization. In DBMS data independence exists, mainly of two types: There is no data independence. 1) Logical Data Independence 2) Physical Data Independence. Only one user can access data at a Multiple users can access data at a time. time. Data is distributed in many files. So, Due to centralized nature data sharing is easy it is not easy to share data. It gives details of storage and It hides the internal details of Database representation of data Integrity Constraints are difficult to Integrity constraints are easy to implement implement It doesn’t provide Inbuilt It provides in house tools for backup and mechanism for backup and recovery recovery of data even if it is lost. of data if it is lost. Cobol, C++ Oracle, SQL Server Database Users: • A primary goal of a database system is to retrieve information from database and store new information in the database. People who work with a database can be categorized as database Actors or database Workers. Actors on the scene: ▪ Database actors are the people involved in day to day use of large databases. ▪ Workers maintain a database environment, but not involved in daily jobs. ▪ Actors can be classified into Admin, Designers, End users and Software engineers. 4 Database Administrator: ▪ The DBMS have central control of both the data and the programs that access those data. A person who has such central control over the system is called a database administrator (DBA). ▪ The function of a DBA includes like Schema definition, Storage structure and access-method definition, Granting of authorization for data access, Periodically backing up the database to prevent losses of data, Monitoring jobs running on the database and ensuring that performance is not degraded by very expensive tasks submitted by some user. Database Designer: ▪ These peoples are responsible to store the data into proper structure of the database. ▪ The designers are DBA staff or other stuff. The designers made the database by group of users requirements and fitting the data according the design of the database. End users: ▪ End users can access the database for querying updating and generating the reports. 1. Casual users: Casual users (Higher level management) are occasionally access the database and each time they will access different information. 2. Naïve users or Parametric Users: These users are constantly querying, updating the database using standard queries. The queries are designed and tested by the application programmers. For example, bus conductor can generate the tickets repeatedly. 3. Sophisticated users: These users are familiar about the facilities of the database, and they can implement their own applications to meet the complex requirements. The sophisticated users are like General engineers, Scientists, Business analyst. 4. Standalone users: These users are personal users they can access the database based on GUI based interfaces. Software engineers: ▪ System analyst: They can determine the requirements of clients and develop the database to meet their requirements. ▪ Application programmers: These peoples are developed the programs according to the specifications of the clients later perform the debugging, testing and deploy the application. Workers behind the scenes: ▪ Workers are associated with design, and development operations on DBMS software and its environment. These peoples are not interested on database content. ▪ The workers are categorised into DBMS designer and implementors, Tool developers and operations, maintenance personnel (Admins). DBMS designer and implementors: ▪ These workers can design the DBMS modules and interfaces as software packages. The modules can perform various operations like query processing, interface processing, accessing data, 5 buffering data, concurrency control, data recovery, and security. It also provides the interface with operating system and other programming language (JDBC). Tool Developers: ▪ The DBMS can design and develop as a tool. The tool may consists modelling, design, and improve the performance. It may have optional packages then we to purchase separately to perform all the operations like database design, performance monitoring, graphical interfaces, and test data generation. Operators and maintenance personnels: ▪ These workers are responsible for running and maintenance of hardware and software in environment of database system. Advantages of DBMS: 1. Redundancy control: ▪ In traditional databases the group of each user having their won data. For example, University having admissions and account sections. The admission people can perform course and grade operations and the account people can perform registration and bills. ▪ If we store the same data multiple Times then the data consists of duplications (redundancy), memory shortage and inconsistency (every file need to update separately) problems. By using normalisation, we avoid all these problems and improve the performance of the database. 2. Unauthorised access control ▪ When large database can be accessed or share by multiple users need to be authorized to access the information. ▪ Provide the username and password for each user, user groups for gaining the access of the database. DBA person can create the username and passwords according to the privileges of user. 3. Persistent Storage for program objects ▪ Database used to provide persistent storage for database structure program objects. The values of the program variables are discarded once the program terminates, unless stores explicitly into the permanent files of database. 4. Storage structure & Search techniques ▪ For making the efficient query processing of database, use either tree or hash-based indexes are performed by DBA. ▪ Each data is stored on to the disc when process any data, initially the data is loaded to the main memory and performing the operations and stores the results into the buffer, once the operation is completed then the data is permanently stored into the secondary memory. 5. Backup and recovery ▪ When the hardware or software failures occur, the data should recover from the disc. 6 ▪ DBMS can perform the backup automatically and it is responsible for restore to previous state of the database. 6. Multiuser interface ▪ Many types of users having various level of knowledge on the database, So the database should provide multiple interfaces. ▪ It includes. Casual users – Query Language Naïve or Parametric Users – Forms and Common code Application programmers – Programming language interface Standalone Users – GUI 7. Complex relationships among the data ▪ The database consists of many varieties of the data which is interrelated, if we have the relationships, retrieve, and update the data easily. 8. Integrity Constraints ▪ The integrity constraints are written along with the table structure, and it is applied on the data. ▪ The simple type of constraint involves specifying data type for each item in the table. ▪ The NOTNULL constraint is used for avoiding the null values into the database. ▪ The duplicate values can be avoided by using unique constraint. ▪ Maintaining the relations between more than one table, use referential integrity constraint, but the error ness of the data is check manually. 9. Inference, actions using rules and triggers ▪ The database system can define detective rules for inferencing new information from stored database facts. Ex: When the student is on probation period, the detective rules applied to manage DBMS. ▪ The RDBMS tables are associated with triggers. ▪ The triggers are rules associated on the tables when updation or deletion or adding the records are performed on the table then automatically the trigger will be fire and getting the alerts for the users. ▪ The triggers are written in stored procedures when certain conditions are meet it will invoke automatically. Database Applications: ▪ Overview of the applications can impetus (momentum) for new type of the database. 1. Hierarchical and network systems ▪ Database systems started in 1960’s and continue in 1970’s, 1980’s. 7 ▪ Used to store the records in the large organisations, The data is intermixing of relations and the data like universities, hospitals, and banks. ▪ The data is structure and program dependent. ▪ The interface for accessing the data is programming language, it is time consuming and very expensive to write the queries and perform the transactions. 2. Data abstraction and Relational databases ▪ Started in 1970’s and introduced RDBMS in 1980’s. ▪ RDBMS can separate the storage of the data from the structure of the data. ▪ The program interface for accessing data is high level query language. ▪ The database abstraction are much improved structure and program independence. ▪ The performance can be improved by using new storage, indexing techniques, query processing and optimizations. 3. Object oriented applications ▪ It started in 1980’s, can store and share complex structured objects leads to OODB (Object Oriented Databases). ▪ Initially the oops is competitor for RDBMS later oops concepts (abstract datatype, inheritance, encapsulation, class, and object) are adopted into the RDBMS then ORDBMS (object relational database management system) is evolved. 4. Interchanging data on the web using XML ▪ It is started in 1990’s. ▪ HTML create static web pages; it can store and access the data. ▪ The hyperlinks can be used to navigate from one webpage to another webpage. ▪ There is a pointer for every document for extracting the data from the web page. ▪ XML (eXtensible Markup Language) can dynamic activity to the web page and with interchangeable data among the databases. 5. Extending database capabilities for new applications ▪ The successive development of traditional databases encourages other types of applications, and they can develop specialized software’s and file systems. ▪ For maintaining the scientific applications like Human Genome, physics, protein structures; Need to store and retire the images, audios, video and space applications like weather data, maps, and navigation data; The Timeseries data like stock data or economic data are new type of database application. ▪ The data mining can analyse large amount of the data and search for patterns and relationships between the data. Ex: Credit card fraud detection 8 6. Big data storage and NOSQL databases ▪ In 21st century more platforms like social media, e-commerce, websites are using cloud storage for maintaining the large databases and server. ▪ A new type of database is necessary to manage faster search retrieval and safe storage of nontraditional data types. ▪ The data is unstructured then the RDBMS is not suitable to store, so NOSQL can be used for storing such kind of large amount of the data. Data Models, Schema & Instances: ▪ Data Abstraction: Data abstraction is the process of hiding the complexities of data storage and organization while emphasizing its essential features. Example: Users can query a database using high-level SQL statements without worrying about how the data is stored on disk. ▪ Data Model: A data model is a collection of concepts and rules used to describe the structure of a database, including its data types, relationships, and constraints. Purpose: To provide a framework for organizing and managing data effectively. ▪ Basic Building Blocks of a Data Model: ▪ Entity: Represents a real-world object or concept about which data is collected and stored. Example: A "Student" in a university database or an "Employee" in a company database. ▪ Attribute: A characteristic or property of an entity that describes it. Example: For the entity "Student," attributes could be "Name," "Roll Number," and "GPA." ▪ Tuple: A single record in a table, representing an instance of an entity. Example: A row in the "Student" table containing data for one specific student. ▪ Relationship: Describes the association between two or more entities. Types of Relationships: There are three primary types of relationships between entities. ▪ One-to-One (1:1): One entity is associated with exactly one other entity. Example: Each "Manager" oversees one "Store." ▪ One-to-Many (1:M): One entity is associated with multiple entities. Example: A "Painter" creates multiple "Paintings." ▪ Many-to-Many (M:M): Many entities are associated with many others. Example: An "Employee" may have multiple "Skills," and a "Skill" may belong to multiple employees. ▪ Constraints: Constraints are rules or restrictions placed on the data to ensure accuracy, consistency, and integrity. They help maintain the quality of the data in the database. Example: The GPA of a student must be between 6 and 9. The salary of an employee must be between ₹10,000 and ₹2,00,000 per month. 9 Evolution of data models Model Year File System Pre-1960s Hierarchical Data Model 1960s Network Data Model 1970s Relational Data Model 1970s Object-Oriented Data Model 1980s XML-Based Data Model 1990s NoSQL and Big Data Models 2000s Purpose Simple data storage without relationships Organize data in parent-child hierarchy Support many-to-many relationships Simplify data management using tables and SQL Combine object-oriented programming with databases Store and exchange hierarchical and semi-structured data Handle unstructured data with scalability Examples Payroll, Inventory IBM IMS IDMS Oracle, MySQL ObjectDB BaseX MongoDB, Cassandra 1. Hierarchical Model: ▪ The hierarchical model was designed to manage large amounts of data for manufacturing industry projects. ▪ The logical structure is represented as an upsidedown tree. ▪ It consists of various levels called segments, where each segment is represented as a record type. ▪ However, it is very complex to implement and manage the database due to its rigid structure and lack of flexibility. Example: Let us consider the tables or entities like Customer, Invoice, Payment, and Products. 2. Network Model: ▪ The network model allows complex data relationships to be represented more effectively than the hierarchical model. ▪ The Database Task Group (DBTG) suggested specifications. o Network schema, DBA as a conceptual organization of the entire database. o Network subschema - It allows programs to invoke applications to access and manage the database. o Data Manipulation Language (DML), providing flexibility in managing data. 10 3. Relational Model: ▪ The relational model was developed by E.F. Codd in the 1970s. ▪ It simplifies the process of maintaining relationships between tables by hiding the complexities of the underlying structure. ▪ The relational model's building blocks include entities, attributes, tuples, and relationships, making it one of the most widely used models in database management systems. 4. ER Model: ▪ The Entity-Relationship (ER) model was developed by Peter Chen in 1976. ▪ It provides an effective design for representing relationships between entities in a database. ▪ Conceptually simple, the ER model uses entities to represent real-world objects and relationships to represent the associations between these entities, making it an essential tool for database design. 5. Object-Oriented Model: ▪ In the object-oriented model, both data and relationships are represented in a single structure. ▪ This structure is typically visualized using UML (Unified Modeling Language) class diagrams, which define the objects, their attributes (properties), methods (Operations), and the relationships between them. ▪ This model is useful for complex data representation and behaviour. Database Schema & Instances: ▪ The description of a database is called the database schema. It is essential during the database design phase and typically does not change frequently. ▪ The schema is displayed using a schema diagram, which includes the names of record types, items, and constraints. 11 ▪ The data in the database at a particular moment in time is referred to as the database state, also known as an occurrence or instance of the database. ▪ Initially, the database state is empty, with no data. When the database is first loaded with initial data, it transitions to an initial state. As changes or updates are made to the database, the current state reflects the latest data. A database is said to be in a valid state when it satisfies the structure and constraints defined in the schema. ▪ Three tier Schema Architecture (or) Levels of Data Abstraction: ▪ Databases exhibit several key characteristics, including: • Self-Describing Nature of the Database • Insulation Between Programs and Data (Data Abstraction) • Data Sharing and Multi-User Transaction Processing To achieve and visualize these characteristics, databases are designed using a three-level architecture, which separates the application layer from the physical database. This architecture consists of: 1. Internal Level 2. Conceptual Level 3. External Level Three-Level Schema Architecture 1. Internal Level Schema • Focuses on the physical storage of data. • Describes how data is stored, the type of storage model, and the access paths used for data retrieval. 12 2. Conceptual Level Schema • Provides a unified structure of the entire database for a group of users. • Includes metadata, relationships, and operations while abstracting the physical details of data storage. 3. External Level Schema • Describes the specific part of the database relevant to a particular user group. • Hides irrelevant data, providing a customized view for different users. Mapping is the process of translating requests between different schema levels: • External Level → Conceptual Level. • Conceptual Level → Internal Level. • Internal Level → Physical Database. The results of operations are then written back in reverse order. • For smaller databases, the external level may not exist. • Mapping is a time-consuming process, especially for large and complex databases. Data Independence: • Data independence refers to the ability to change the schema at one level without requiring changes at the next higher level. There are two types: 1. Logical Data Independence: Changes in the conceptual schema (e.g., adding or removing columns, altering constraints) do not affect the external schema or application programs. Example: Adding a new attribute to a table without affecting the existing queries or user views. • Logical data independence is harder to achieve because changes in relationships or constraints require significant updates to mappings and catalog information. 2. Physical Data Independence: Changes in the internal schema (e.g., file organization, access paths, or storage structures) do not affect the conceptual schema or external schema. Example: Reorganizing the file structure or changing the data access path without affecting the database operations or user views. • Advantage: Most databases achieve physical data independence efficiently. Catalog and Metadata Management: All information about mappings, schemas, and operations is stored in the catalog. This includes: • Metadata about the database structure and details of constraints, relationships, and operations. • Mappings between the three schema levels. 13 Database System Structures: ▪ A database consists of logically related data stored in a single repository. While it is physically distributed across multiple locations and storage systems. ▪ The catalogue stores metadata about relationships, transactions, and other essential information for managing the database. 1. Database System Environment: ▪ The database system environment refers to the organization of components required to define, collect, store, manage, and access data. It comprises five key components: hardware, software, people, procedures, and data. 1. Hardware: Represents the physical devices used in the database system, such as computers, storage devices, printers, networks, and other components. 2. Software: Includes various categories: ▪ Operating System: Manages hardware components and provides a platform for running other software. Examples: Windows, Linux, Unix, Mac. ▪ DBMS: Manages the database and ensures its functionality. Examples: MS Access, Oracle, DB2. ▪ Application Programs: Used to access and manipulate the database, generate reports, and support decision-making processes. ▪ Utilities: Assist in managing database system components, such as backup, recovery, and performance monitoring tools. 3. People: ▪ System Administrators: Manage general operations of the database system. ▪ Database Administrators (DBAs): Ensure database functionality and manage DBMS operations. ▪ Database Designers: Design the database structure. Poor design can lead to inefficiency and failure to meet application requirements. ▪ End Users: Use application programs to perform daily operations. ▪ System Analysts and Programmers: Design and implement application programs, such as data entry screens, reports, and procedures. 4. Procedures: These are instructions and rules for designing and operating the database system. Procedures enforce database standards, monitor data integrity, and audit operations. 5. Data: The collection of facts stored in the database, representing the core content managed by the system. 14 ▪ The pre-compiler extracts DML (Data Manipulation Language) commands from application programs and sends them to the DML compiler for processing. The remaining parts of the program are passed to the host language compiler. ▪ The object code generated for the DML commands, and the rest of the program are linked together to form canned transactions. These are precompiled transactions used for repetitive tasks to enhance performance and reliability. ▪ The DDL (Data Definition Language) compiler processes the schema definitions and stores them in the DBMS catalogue. ▪ Query Optimization: The query optimizer performs several tasks to enhance query performance: o Rearranges and reorders operations for efficiency. o Eliminates redundancies in query execution. o Utilizes indexing techniques during execution to maximize speed. ▪ During query execution, the runtime database processor: o Executes user-submitted commands. o Handles queries using query optimization techniques. o Utilizes the system catalogue and dictionary to update data and maintain consistency. 2. DBMS Functions: 1. Data Dictionary Management: Stores metadata and automatically records changes in the database, ensuring abstraction and minimizing structural dependencies. 2. Data Storage Management: Creates and manages complex structures for data storage, including forms, reports, videos, and images. Performance tuning depends on the type of storage and access speed. 15 3. Data Transformation and Presentation: Transforms user-entered data into the required formats (date), such as physical or logical structures. 4. Data Integrity: Minimizes redundancy and maximizes consistency by storing relationships in the data dictionary. It is essential for online transactional processing. 5. Database Access Language: Allows database access using SQL, a non-procedural query language for data manipulation and retrieval. 6. Database Communication Interface: Facilitates user requests for data through a network environment, such as websites. Automatically publishes reports based on user queries. 7. Multi-User Access Control: Ensures data integrity and consistency when multiple users access the database concurrently. 8. Backup and Recovery: Maintains data safety and integrity. Utilities help recover data in cases of disk failures, bad sectors, or power outages. 9. Security Management: Monitors and enforces user security and privacy. The database administrator ensures that only authorized users can access or modify data. 3. Managing Database Systems: 1. Increased Cost: Managing a database system requires investment in hardware, software, and personnel. 2. Management Complexity: Concurrent access by multiple users requires careful management to maintain data consistency and ensure security. 3. Maintaining Currency: Keeping the database system efficient requires regular updates to software, patches, and security protocols. Employees need ongoing training in the latest technologies. 4. Vendor Dependency: Organizations may face challenges with vendor lock-in, as they depend on specific technologies and training. This dependency may lead to high costs and limited flexibility in meeting client or user needs. Sample Questions: 1. Discuss the main characteristics of the DBMS and how it differs from traditional file systems? 2. What is data and data model? Explain briefly. (or) What is a Database model? List out various database models and explain any two of them. 3. What are the benefits with Database approach? Explain briefly? (or) Explain any four significant advantages of using Data Base Management Systems over storing conventional file system for maintaining data in an organization. 4. Explain different types of database users in DBMS? (or) Discuss various categories of users of DBMS system. 16 5. With the help of a diagram and example, explain in detail about three levels of architecture. (or) Discuss various levels of data abstraction in a DBMS? 6. Draw and explain major DBMS functions and components? (or) Explain in detail about the key components of the DBMS system structure. 17