Final FYIT PDF

**2.Degree or level of abstrations explain internal view , external view and conceptual view** In database management, the **degree or levels of abstraction** help to separate the data structure from its implementation. This abstraction simplifies interaction with the database by hiding unnecessary...

**2.Degree or level of abstrations explain internal view , external view and conceptual view** In database management, the **degree or levels of abstraction** help to separate the data structure from its implementation. This abstraction simplifies interaction with the database by hiding unnecessary details from users and ensures data integrity and security. The three levels of abstraction are **internal view**, **conceptual view**, and **external view**. **1. Internal View (Physical Level)** - **Definition**: The internal view is the lowest level of abstraction and describes the physical storage of data in the database. - **Purpose**: It defines how data is stored on physical media, such as on disks, as well as the data structures and algorithms used to retrieve and manage this data efficiently. - **Components**: Includes details about file organization, indexes, data blocks, compression, and storage format. - **Users**: Database administrators work with this view to optimize performance, ensure efficient storage, and handle data backups and recovery. **2. Conceptual View (Logical Level)** - **Definition**: The conceptual view is the middle level of abstraction and represents the entire database structure as seen by the database designer. It is also called the logical view. - **Purpose**: It defines what data is stored in the database and the relationships among those data entities, focusing on logical data organization rather than physical storage. - **Components**: Includes tables, relationships, constraints, and data integrity rules but abstracts away storage details. - **Users**: Database designers and developers use this view to design a schema that satisfies organizational requirements without worrying about the physical storage of data. **3. External View (User Level)** - **Definition**: The external view is the highest level of abstraction, presenting the data to end users in a way that is most relevant to their needs. - **Purpose**: It defines how individual users or user groups view and interact with the database. This view allows customization based on user roles, only displaying data that is necessary or authorized for them. - **Components**: Consists of user-defined views, which may include only specific columns, rows, or aggregates based on user permissions and requirements. - **Users**: End users, such as employees, managers, and analysts, interact with the database through this view to access the information they need. **Summary of Levels of Abstraction** - **Internal View**: Focuses on how data is physically stored. - **Conceptual View**: Focuses on the logical structure of the entire database. - **External View**: Provides tailored views of the database to individual users or groups. **3.What is CODDS rule list out any 7 rules for DBMS system** **Codd's Rules** were proposed by Dr. E.F. Codd to define what a fully functional **Relational Database Management System (RDBMS)** should meet. He created **12 rules** (numbered from 0 to 12) that set criteria for a database to be considered relational. Here's a summary of 7 key rules: **1. Rule 0: Foundation Rule** - **Description**: For a system to be considered relational, it must use only relational capabilities to manage data. It must follow all of Codd\'s other rules to qualify as an RDBMS. **2. Rule 1: Information Rule** - **Description**: All information in a relational database should be stored in tables (relations), with data organized into rows and columns. This includes metadata, which describes the structure of the data. **3. Rule 2: Guaranteed Access Rule** - **Description**: Each data item (value) in the database must be accessible by specifying the table name, column name, and primary key of the row. This ensures direct and unique access to each data item. **4. Rule 3: Systematic Treatment of Null Values** - **Description**: Null values must be uniformly supported to represent missing or unknown data. A null should be distinct from a zero, a blank string, or any other value, and it must be handled consistently. **5. Rule 4: Dynamic Online Catalog Based on the Relational Model** - **Description**: The database's metadata (schema information) should be stored in tables and accessible using the same relational query language as ordinary data. This allows users to query metadata just like regular data. **6. Rule 5: Comprehensive Data Sublanguage Rule** - **Description**: The system must support at least one language that allows data manipulation, definition, constraints, and transaction management. SQL is an example of such a language, as it covers these functionalities. **7. Rule 6: View Updating Rule** - **Description**: Any view that is theoretically updatable must also be updatable in the system. Views are virtual tables created by querying other tables, and this rule mandates that changes to views should reflect in the base tables when possible. 4\. **what is relational database model and write not on Logical view of data give some example with table characteristics** The **Relational Database Model** is a type of database model that organizes data into one or more tables (or \"relations\") where each table consists of rows and columns. This model was developed by Dr. E.F. Codd in 1970 and is based on the concept of data organized in tables with relationships between them, using keys to link data between tables. **Key Characteristics of a Relational Database Model** 1. **Tables (Relations)**: Data is stored in tables with rows and columns. Each table represents an entity (like students, products, etc.). 2. **Rows (Tuples)**: Each row in a table represents a single record of data. 3. **Columns (Attributes)**: Columns define the properties of the data within the table, such as names, ages, or prices. 4. **Primary Key**: A unique identifier for each record within a table, ensuring no duplicate rows. 5. **Foreign Key**: A key in one table that links to the primary key of another table, establishing relationships between tables. 6. **Data Integrity and Constraints**: Rules like foreign keys, primary keys, and data types maintain data accuracy and consistency. **Logical View of Data** The **Logical View of Data** represents the data structure from the user\'s perspective rather than the physical storage of data. It is an abstraction of the database\'s actual data and hides the complexities of the physical storage details. Users only see a high-level structure that includes tables, columns, and relationships, without worrying about how data is stored and managed on disk. **Key Points about Logical View** 1. **Data Independence**: Users interact with the logical structure without knowing about physical storage, which allows for easier modifications without affecting the application. 2. **Security and Customization**: The logical view can be customized for different users by creating views that only show relevant data. 3. **User-Oriented**: The logical view focuses on what data is available and how it is organized, not on how it is stored. 5\. **What is normalization and its types** **Normalization** is a database design technique used to organize data in a way that reduces redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them to improve data integrity, minimize anomalies, and optimize data storage. Normalization is typically achieved through various \"normal forms,\" each with its own set of rules or criteria. Here are the main types of normalization, also known as normal forms: **1. First Normal Form (1NF)** - **Rule**: Each column should contain only atomic (indivisible) values, meaning no repeating groups or arrays within a column. - **Goal**: Ensures that data is stored in a tabular format with each value in a cell, making it easier to access and manage. - **Example**: A table containing multiple phone numbers in a single cell violates 1NF; separating each phone number into its own row satisfies 1NF. **2. Second Normal Form (2NF)** - **Rule**: The table must be in 1NF, and all non-key attributes must depend fully on the primary key, not on part of it. - **Goal**: Removes partial dependencies, which occur when only part of a composite primary key determines a non-key attribute. - **Example**: In a table with OrderID and ProductID as a composite key, if ProductName depends only on ProductID and not OrderID, the table does not meet 2NF. **3. Third Normal Form (3NF)** - **Rule**: The table must be in 2NF, and all non-key attributes must depend only on the primary key, not on any other non-key attribute. - **Goal**: Removes transitive dependencies, where non-key attributes depend on other non-key attributes, leading to potential anomalies. - **Example**: If EmployeeID is the primary key, but DepartmentName depends on DepartmentID (a non-key attribute), it violates 3NF. To achieve 3NF, separate DepartmentName into its own table. **Boyce-Codd Normal Form (BCNF)** - **Rule**: A table is in BCNF if it is in 3NF and for every dependency X→YX \\rightarrow YX→Y, XXX must be a super key. A super key is a unique identifier for each row in a table. - **Goal**: Eliminates any anomalies due to functional dependencies where a non-prime attribute (not part of any candidate key) determines a key attribute. - **Example**: - Consider a table with columns StudentID, CourseID, and Instructor, where the combination of StudentID and CourseID is the primary key. - If CourseID determines Instructor, then CourseID should be a key in its own table to satisfy BCNF. - We split the table into two: CourseID and Instructor in one table, and StudentID with CourseID in another. BCNF removes all anomalies from the table, making it highly normalized. 6\. **What is transcaction explain transaction state diagram and give an example for banking system for below cases** **increment of saving account balance** **Decrement of saving account balance** **Records gets inserted** In a **Database Management System (DBMS)**, a **transaction** is a sequence of operations performed as a single logical unit of work. A transaction ensures data consistency, integrity, and reliability by following the **ACID properties**: **Transaction State Diagram** **The transaction state diagram represents the lifecycle of a transaction, showing its different states as it progresses. Here's a breakdown of the transaction states:** 1. **Active:** The transaction is currently being executed. 2. **Partially Committed:** All operations are complete, but changes are not yet committed to the database. 3. **Committed:** Changes are saved to the database, making them permanent. 4. **Failed**: An error occurs, causing the transaction to fail and roll back. 5. **Aborted:** The transaction is canceled, and any changes made are undone. The transaction can either restart or end here. 1\. Increment of Savings Account Balance START TRANSACTION; UPDATE SavingsAccount SET balance = balance + 500 WHERE AccountNumber = \'12345\'; COMMIT; 2\. Decrement of Savings Account Balance START TRANSACTION; UPDATE SavingsAccount SET balance = balance - 300 WHERE AccountNumber = \'12345\'; COMMIT; 3.Record Insertion for New Account **7.Explain DDL, DML & DCL languages with suitable exanples** **1. DDL (Data Definition Language)** **DDL** commands are used to define, modify, and manage the structure of database objects like tables, indexes, and schemas. These commands define the database schema and affect the structure rather than the data itself. DDL commands are auto-committed, meaning changes made are saved automatically. - **Common DDL Commands**: - **CREATE**: Used to create database objects like tables, views, and indexes. - **ALTER**: Used to modify the structure of existing database objects. - **DROP**: Used to delete database objects.  **DML (Data Manipulation Language)** **DML** commands are used to manipulate and interact with the data within database tables. Unlike DDL, DML commands are not auto-committed, meaning they can be rolled back if needed. DML focuses on operations such as retrieving, inserting, updating, and deleting data. - **Common DML Commands**: - **SELECT**: Retrieves data from one or more tables. - **INSERT**: Adds new records to a table. - **UPDATE**: Modifies existing data in a table. - **DELETE**: Removes data from a table. **DCL (Data Control Language)** **DCL** commands are used to control access to data within the database by granting and revoking permissions to users. DCL ensures that only authorized users can perform certain operations, helping to maintain database security. - **Common DCL Commands**: - **GRANT**: Provides specific privileges to users. - **REVOKE**: Removes specific privileges from users.  **List out any 5 Differentiate between DBMS and File System give 2 example for File system and DBMS system** **Examples of File Systems and DBMS Systems**