UNIT-1 DBMS.pdf

# Unit-1 DBMS ## Database - A collection of interrelated information - Used for retrieving, inserting, and deleting data efficiently - Example: A college database can organize data about the staff, students, and faculty - Database management systems are used to manage databases, with examples incl...

# Unit-1 DBMS ## Database - A collection of interrelated information - Used for retrieving, inserting, and deleting data efficiently - Example: A college database can organize data about the staff, students, and faculty - Database management systems are used to manage databases, with examples including Oracle and MySQL ## DBMS (Data Base Management System) - A collection of programs that enable users to create and maintain a database - Provides an interface to perform various operations like database creation, storing data, updating, deleting, etc. ## Schema - The overall design of a database is called a schema. ### Example: A schema for a STUDENT database could include these attributes: | Attribute | |---|---| | Roll No. | | Name | | Add | | Mark | ## Metadata - Data about data is called metadata. - Information stored in the catalogy is called metadata, and it describes the structure of the primary database. ### Example: A table showing relevant information about a COLLEGE database could include: #### Relation Name - College - Student - Teacher #### Number of Columns - 3 - 3 - 4 #### Column Name - Roll No. - Name - Add - Mark #### Data Type - int - Varchar - Varchar - int #### Belongs to the Relation - Student - Student - Teacher ## Relation Key - Used to uniquely identify a row in a relation. ## Relation Instance - A finite set of tuples in the relational database instance. - Do not have duplicate tuples. ## Attributes - The properties of a field in the database. ## Record - A single row in a database. ## A Simple Database System Environment - Shows the relationship between users, application programs, DBMS software, and the stored database. ## Advantages of DBMS 1. **Data Redundancy Control**: A controlled database avoids unnecessary duplication of data, reducing storage requirements. 2. **Data Integrity**: Data stored in the database is accurate and consistent. 3. **Data Sharing**: Authorized users can share data among multiple users in an organization. 4. **Ease of Maintenance**: Easier to maintain due to the centralized nature of the database system. 5. **Security**: Confidentiality of important data is preserved, preventing unauthorized access. ## Disadvantages of DBMS 1. **Software Cost**: More expensive than traditional file-based systems 2. **Hardware Requirements**: Highly performant hardware is required. 3. **Size**: Large size of DBMS software needs more memory space. 4. **Highly Qualified Personnel**: Designing requires highly skilled individuals with expertise in logic and programming. # Database Users 1. **Database Administrator**: Primary responsibility is for the database itself and the secondary resource. 2. **Database Designers**: Responsible for identifying the data stored in the database and for choosing appropriate structures to represent the information. 3. **End Users**: People who require access to the database to perform queries, update data, or generate reports. ## Types of End Users 1. **Casual Users**: Access the database occasionally. 2. **Naive Users (or Parametric Users)**: Query and update the database. Examples include bank tellers checking balances, deposits, and withdrawals. 3. **Sophisticated Users**: Have a strong understanding of the DBMS and its capabilities. Examples include scientists, engineers, and business analysts. 4. **Stand Alone Users**: Maintain their own personal databases. 5. **System Analyst and Application Programmer**: Design the overall system and implement the design based on requirements from end users. # Characteristics of Database Approach 1. **Data Redundancy Control**: Stores inter-related data together without unnecessary redundancy. 2. **Data Independence**: Data is independent of the program and changes in data storage structure for access structures. 3. **Data Integrity**: Ensures data remains consistent. 4. **Data Growth and Control**: Enables adding new data, modifying data, and restoring data. # Three Schema Architecture - Separates user applications, the conceptual schema, and the physical database. - The schemas are defined at different levels: internal, conceptual, and external. ## Internal Level - Describes the physical storage structure of the database. - Uses a physical data model and defines data storage methods, access methods, and complete data details. ## Conceptual Level (Logical Level) - Describes the structure of the entire database. - Uses a conceptual schema and hides the details of the physical storage structure and access method. ## External Level (View Level) - Describes the part of the database viewed by a particular user group. - Hides the rest of the database from the specific user group. - Provides a specialized view of data. # Three Tier Architecture - In a database based on three schema architecture, each user group refers only to their own external schema, providing data independence. - The DBMS transforms a request specified as an external schema into a request against the conceptual schema, and then ultimately into an internal schema. This process of transforming requests is called data mapping. # Database Management System Languages - Languages used to control the database. 1. **Data Definition Language (DDL)**: Defines the schema: > - A special type of language with tools that define the database conceptual schema. > - Allows description of database objects (tables, views, and constraints) and their attributes. > - Includes statements like CREATE, ALTER, and DROP 2. **Data Manipulation Language (DML)**: Manipulates data within the database: > - For retrieving, inserting, deleting, and updating data in the database. > - Performs operations on the data held by the database, including statements like INSERT, DELETE, UPDATE, and SELECT. 3. **Storage Definition Language (SDL)**: Defines the internal schema: > - Used to specify the database's internal schema. > - Defines the mapping from the conceptual schema to the internal schema. 4. **View Definition Language (VDL)**: Defines external schemas: > - Used to specify user views or external schemas. > - Provides logical and physical views of data. 5. **High Level Language (Fourth Generation Language):** > - A collection of query languages and database languages. ## Data Independence - The capacity to change the schema at one level of the database system without having to change the schema at a higher level. ## Types of Data Independence 1. **Physical Data Independence:** The ability to change the physical schema without having to change the conceptual schema or the external schema. 2. **Logical Data Independence**: The ability to change the conceptual schema without having to change the physical schema or the external schema. ## Data Models - Define the logical structure of the database and describe the relationships between data. ## Types of Data Models 1. **Record-based Data Models:** > - Use a record-based data model to represent entities and relationships. > - The database consists of fixed format records with attributes. > - Includes the Relational data model, network data model, and hierarchical data model. 2. **Object-Based Data Models**: > - Use the concept of entities, relationships, and attributes. > - The types include entity-relationship data models, symmetric data models, functional data models, and object-oriented data models. 3. **Physical Data Models**: > - Describe the storage structure and access method of the database. > - Define how data is stored in a computer system, including record structures, ordering of records, and access paths. # Relational Data Model - Represent data in the form of tables called relations. - Used to organize data using tables with rows and columns. - The column names are called attributes, and the rows are called tuples. ## Advantages of Relational Data Model 1. **Simplicity**: Easy to understand and use. 2. **Data Independence**: Changes in one level of the schema do not affect other levels. 3. **Ease of Design, Implementation, and Maintenance**: Easier than other data models. ## Disadvantages of Relational Data Model 1. **Hardware Requirements**: Requires more hardware. 2. **Design**: Can lead to bad designs. 3. **Network Data Model** : Relational data models are not as efficient as network data models, which store data in a hierarchical structure. # Network Data Model - Represents data as a collection of records connected by links that represent relationships. - Similar to the hierarchical data model. - A record can have multiple parents. ## Advantages of Network Data Model 1. **Simplicity**: Easy to design. 2. **Data Access**: Easier to access data than the hierarchical data model. ## Disadvantages of Network Data Model 1. **Complexity**: More complex than the hierarchical data model. 2. **Data Integrity**: May allow inconsistencies in data. # Hierarchical Data Model - Organizes data in a tree-like structure with a parent-child relationship. ## Advantages of Hierarchical Data Model 1. **Simplicity**: Easy to design. 2. **Data Sharing**: Data can be shared in a common database. ## Disadvantages of Hierarchical Data Model 1. **Complexity**: Can be difficult to implement and maintain. 2. **Data Integrity**: Can be challenging to enforce data integrity constraints. # Entity-Relationship (ER) Model - A high-level conceptual model used in database design. - Provides a visual representation of the database, using diagrams called ER diagrams. - Helps in understanding how different entities are related. ## ER Diagram Elements - **Entities**: Real-world objects or concepts that are represented by rectangles in an ER diagram. - **Attributes**: Properties of an entity, represented by ovals that are connected to the entity rectangle. - **Relationships**: Connections between entities, represented by diamonds in an ER diagram. ## Types of Attributes - **Single-Valued**: Have a single value for a specific entity. - **Multi-valued**: Can have multiple values for a given entity. - **Derived**: Calculated from other attribute values. - **Key Attribute**: Identifies an entity uniquely. ## Relationship Types - **One-to-One (1:1)** : A single entity in one set corresponds to a single entity in another set. - **One-to-Many (1:M)**: A single entity in one set corresponds to multiple entities in another set. - **Many-to-One (M:1)**: Multiple entities in one set correspond to a single entity in another set. - **Many-to-Many (M:N)**: Multiple entities in one set correspond to multiple entities in another set. ## Degree of a Relationship - The number of entities involved in a relationship. - **Binary**: Two entities. - **Ternary**: Three entities ## Advantages of ER Model 1. **Straightforward Relationships**: Clearly shows relationships between entities. 2. **Ease of Conversion**: Easy to convert to a database representation. 3. **Graphical Representation**: Visually represents data. ## Disadvantages of ER Model 1. **High-Level Design**: Not suitable for low-level database design. 2. **Physical Data Model**: Does not describe the physical storage of data. # Enhanced Entity-Relationship (EER) Model - Extends the ER model to include concepts like subclasses, superclasses, and inheritance. ## EER Model Elements - **Superclass**: A general entity type. - **Subclass**: More specific entity types that inherit properties from their superclass. - **Inheritance**: The process of sub-classes inheriting the properties of their superclass. ## Generalization and Specialization - **Generalization**: The process of combining specific entity types into a more general entity type. - **Specialization**: The process of defining a set of subclasses from an entity type. # UML Class Notation - A visual representation of database design using class diagrams. ## UML Class Diagram Elements - **Class**: Represents a specific entity type. - **Attributes**: Properties of a class. - **Methods**: Functions or operations that can be performed on a class. - **Association**: Represents a relationship between classes. ## Multiplicity - The number of instances of one class that can be associated with an instance of another class. # Types of Database Systems 1. **Single-User DBS**: Supports only one user at a time. 2. **Multiuser DBS**: Supports multiple users concurrently. 3. **Centralized DBS**: The database and DBMS are stored at a single site. 4. **Distributed DBS**: The database and DBMS are spread across multiple sites. ## Types of Distributed DBS 1. **Homogeneous Distributed Database**: The same DBMS software is used across all sites. 2. **Heterogeneous Distributed Database**: Different sites might use different DBMS software. # Object-Oriented Data Models - Represent real-world entities as objects. - Use concepts like encapsulation, inheritance, and polymorphism. **Types of object-oriented data models:** - **Entity-relationship (ER) model**: A high-level conceptual model used for representing entities, attributes, and relationships. - **Object-oriented (OO) model**: Defines data and operations as objects. - **Functional data model**: Based on functions and their relationship to data. To get the most complete understanding of this material, it is recommended to refer to a book about DBMS to understand its different applications in detail.