Unit1 - Subjective Questions

Purpose of Database Systems:

The primary purpose of a Database Management System (DBMS) is to provide a systematic, efficient, and secure way to store, retrieve, and manage data. It acts as an interface between the user and the database.

Overcoming File-Processing Limitations:
Traditional file systems store data in distinct, often disconnected files. A DBMS overcomes its limitations in the following ways:

• Data Redundancy and Inconsistency: File systems often duplicate data across multiple files, leading to inconsistency. A DBMS centralizes data, ensuring updates propagate universally.
• Data Isolation: In file systems, data is scattered across various formats. A DBMS abstracts this, allowing seamless data retrieval.
• Integrity Problems: A DBMS enforces integrity constraints (e.g., account balance ) universally, which is hard to hardcode into every application program in a file system.
• Atomicity of Updates: A DBMS ensures transactions are atomic (all-or-nothing), preventing partial updates during failures.
• Concurrent Access Anomalies: DBMS provides concurrency control mechanisms to allow multiple users to access data simultaneously without interference.

A complete Database Management System environment consists of five major components:

1. Hardware: The physical electronic devices like computers, storage disks, I/O devices, and network components that host and run the DBMS.
2. Software: This is the core DBMS software itself (e.g., MySQL, Oracle), along with the operating system and any associated network software. It acts as a bridge between the user and the hardware.
3. Data: The most critical component. The DBMS exists to store, manage, and retrieve this data. It includes operational data and metadata (data about data).
4. Procedures: The instructions and rules that govern the design and use of the database system. This includes procedures for logging on, making backups, and handling failures.
5. Users: The people who interact with the database. This includes Database Administrators (DBA), Application Programmers, Sophisticated Users, and Naive (End) Users.

DBMS is ubiquitous in modern computing. Major applications include:

• Banking and Finance: For storing customer information, accounts, loans, and banking transactions. Databases ensure ACID properties are maintained for financial integrity.
• Airlines and Travel: For managing flight schedules, ticket reservations, and passenger information. These systems require highly concurrent, real-time access globally.
• Universities: For maintaining student records, course registrations, grades, and payroll.
• E-commerce and Sales: For customer, product, and purchase tracking. They manage shopping carts, inventory, and personalized recommendations.
• Human Resources: To manage employee records, salaries, tax deductions, and benefits.
• Telecommunications: To keep records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and routing communication networks.

The Three-Tier DBMS Architecture separates the database system into three distinct layers, providing better security, scalability, and abstraction.

The Three Tiers:

1. Presentation Tier (Client Layer): The topmost level, which represents the user interface (e.g., a web browser or mobile app). Users interact with this layer, which translates user actions into requests for the application tier.
2. Application Tier (Business Logic Layer): The middle tier contains the application server and business logic. It processes user requests, makes logical decisions, performs calculations, and sends queries to the database tier.
3. Database Tier (Data Layer): The lowest tier consists of the database servers where the data actually resides along with its query processing languages.

Why it is preferred:

• Enhanced Security: The client never directly communicates with the database, protecting it from direct attacks.
• Scalability: Each tier can be scaled independently based on workload.
• Maintainability: Changes to the business logic do not require changes to the client interface or the database schema.

Data Independence is the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. It provides isolation between the user applications and the physical storage of data.

1. Logical Data Independence:

• Definition: The ability to modify the conceptual schema without causing existing external schemas or application programs to be rewritten.
• Example: Adding a new attribute to a table or splitting a table into two. Applications that do not use the new attribute remain unaffected.
• Difficulty: Harder to achieve because application programs are heavily dependent on the logical structure of the data.

2. Physical Data Independence:

• Definition: The ability to modify the internal/physical schema without altering the conceptual or external schemas.
• Example: Changing storage devices, modifying file organizations, or creating new indexes.
• Difficulty: Easier to achieve since the physical structure is completely hidden from the application logic.

Database Schema:

• Definition: The logical structure or design of the database. It defines the tables, the fields in each table, and the relationships between fields and tables.
• Nature: It is static and changes very rarely. It is specified during the database design phase.
• Analogy: Similar to the architectural blueprint of a house.
• Notation: Often represented mathematically as a relation schema, e.g., .

Database Instance:

• Definition: The actual content or data present in the database at a specific moment in time.
• Nature: It is dynamic and changes constantly as data is inserted, updated, or deleted.
• Analogy: Similar to the people and furniture inside the house at a given moment.

Difference Summary: The schema defines what can be stored and how it relates, while the instance is the actual data stored at a given snapshot in time.

Data Modeling is the process of creating a visual representation of either a whole information system or parts of it to communicate connections between data points and structures. It involves defining data elements, their structures, and the relationships between them.

Importance in Database Design:

• Clear Communication: It acts as a communication tool between business stakeholders (who know the requirements) and technical developers (who will build the system).
• Identifies Requirements: Helps in identifying missing or redundant data requirements early in the development cycle.
• Blueprint for Construction: Provides a clear blueprint for constructing the physical database.
• Ensures Quality: Helps establish data consistency, structural integrity, and adherence to business rules before writing any code or DDL scripts.

The Entity-Relationship (ER) model is a high-level conceptual data model. Its basic components are:

1. Entity: A real-world object or concept that is distinguishable from other objects. For example, a 'Student' or an 'Employee'. Represented by a Rectangle in an ER diagram.
2. Entity Set: A collection of similar entities. E.g., all students in a university.
3. Attribute: Properties or characteristics that describe an entity. E.g., a Student entity might have attributes like Roll_No, Name, and Age. Represented by an Oval.
4. Relationship: An association among several entities. E.g., a student "enrolls in" a course. Represented by a Diamond.
5. Relationship Set: A set of relationships of the same type.
6. Lines: Used to link attributes to entity sets and entity sets to relationship sets.

The Relational Model represents data as a collection of relations (tables). It was introduced by E.F. Codd and uses mathematical concepts, primarily set theory and predicate logic, to manage data.

Key Terminologies:

• Relation: A table with columns and rows. Mathematically, a relation is a subset of the Cartesian product of a list of domains: .
• Tuple: A single row in a relation. It represents a single record or entity instance.
• Attribute: A column in a relation. It represents a property of the relation (e.g., Employee_Name).
• Domain: The set of allowable values for one or more attributes. For example, the domain of an attribute Month might be the set .
• Degree: The number of attributes in a relation.
• Cardinality: The number of tuples in a relation.

Comparison:

Preferred Scenarios:

• Relational Databases: Preferred when data integrity is paramount, relationships between data elements are complex, and the data structure is consistent. Example: Financial systems, ERP applications.
• Non-Relational Databases: Preferred when handling massive volumes of unstructured or semi-structured data, requiring rapid agile development, or needing high horizontal scalability. Example: Real-time big data analytics, social media feeds, IoT sensor data.

Database users are categorized based on the way they interact with the system:

1. Naive Users (End Users): These are unsophisticated users who interact with the system by invoking application programs that have been written previously. They do not write queries. Example: A bank teller checking an account balance.
2. Application Programmers: Computer professionals who write application programs (in languages like Java, Python, C++) that interact with the database using DML calls.
3. Sophisticated Users: Users who interact with the system without writing application programs. They form their requests using a database query language (like SQL). Example: Data analysts or business intelligence professionals.
4. Database Administrator (DBA): A person having central control over the system. The DBA's duties include schema definition, granting access permissions, specifying integrity constraints, and monitoring performance.

Mapping Cardinalities express the number of entities to which another entity can be associated via a relationship set. They are highly useful in binary relationship sets.

There are four main types:

1. One-to-One (1:1): An entity in set A is associated with at most one entity in set B, and vice versa.
   Example: A Person has one Passport, and a Passport belongs to one Person.
2. One-to-Many (1:N): An entity in set A is associated with any number of entities in set B. An entity in set B is associated with at most one entity in set A.
   Example: A Department has many Employees, but an Employee belongs to only one Department.
3. Many-to-One (N:1): An entity in set A is associated with at most one entity in set B. An entity in set B is associated with any number of entities in set A. (Reverse of 1:N).
4. Many-to-Many (M:N): An entity in set A is associated with any number of entities in set B, and vice versa.
   Example: A Student takes many Courses, and a Course has many Students.

Attributes in an ER model can be classified into several types:

1. Simple Attributes: Attributes that cannot be divided into subparts.
   Example: Age or Gender.
2. Composite Attributes: Attributes that can be divided into smaller subparts, which represent more basic attributes with independent meanings.
   Example: Name can be divided into First_Name, Middle_Name, and Last_Name.
3. Single-valued Attributes: Attributes having a single value for a particular entity.
   Example: Roll_Number of a student.
4. Multi-valued Attributes: Attributes that have a set of values for a specific entity. They are represented by a double oval in an ER diagram.
   Example: Phone_Number (a person can have multiple phone numbers).
5. Derived Attributes: Attributes whose value is derived from the values of other related attributes or entities. They are represented by a dashed oval.
   Example: Age can be derived from Date_Of_Birth using the formula .

The Three-Schema Architecture (also known as the ANSI/SPARC architecture) is designed to separate the user applications from the physical database, facilitating Data Abstraction.

It consists of three levels:

1. Internal Level (Physical Schema): The lowest level of data abstraction. It describes how the data is actually stored on the storage media. It deals with data structures, file organizations, and access methods (indexes).
2. Conceptual Level (Logical Schema): The middle level. It describes what data is stored in the entire database and the relationships among the data. It hides the details of physical storage structures and focuses on entities, data types, and constraints. DBAs usually work at this level.
3. External Level (View Schema): The highest level. It describes only the part of the database that a particular user group is interested in and hides the rest of the database. Multiple views can exist for the same database.

Facilitation of Data Abstraction: By dividing the database into these layers, the DBMS hides complex physical storage details from logical designers, and hides the overall logical schema complexity from end-users, achieving structural independence.

The Relational Model, proposed by E.F. Codd, largely replaced the Hierarchical and Network models due to several key advantages:

• Simplicity and Ease of Use: Data is represented in simple, intuitive two-dimensional tables (relations) rather than complex trees (Hierarchical) or arbitrary graphs (Network). This makes it much easier for users to understand.
• Structural Independence: Changes in the database structure (like adding a column) do not require changes to the application programs accessing the data. Older models lacked this independence.
• Declarative Query Language: Relational databases use SQL, which is declarative (you specify what you want, not how to get it). Older models required procedural traversal of pointers to fetch data.
• Mathematical Foundation: It is based on set theory and relational algebra, providing a solid theoretical foundation for query optimization and transaction management.
• Standardization: The widespread adoption of SQL created an industry standard, making it easier to migrate between different RDBMS vendors.

Keys are crucial in the ER model for uniquely identifying entities within an entity set.

1. Super Key: A set of one or more attributes whose combined values uniquely identify an entity in an entity set. A super key may contain extraneous attributes.
   Example: , , and are all super keys for a Student entity.
2. Candidate Key: A minimal super key. It is a super key from which no attribute can be removed without losing its unique identification property.
   Example: From the super keys above, is a candidate key. If Email is also unique, is another candidate key.
3. Primary Key: A candidate key that is explicitly chosen by the database designer as the principal means of identifying entities. There can be only one primary key per entity set. It is denoted by underlining the attribute name in an ER diagram.
   Example: Choosing as the primary key over .

NoSQL (Not Only SQL) databases are non-relational data management systems designed to handle large volumes of distributed, unstructured, or semi-structured data. They offer flexible schemas and scale horizontally.

Four Main Categories:

1. Key-Value Stores: Data is stored as a collection of key-value pairs. The key serves as a unique identifier to retrieve the value. Highly performant for simple lookups. Example: Redis, Amazon DynamoDB.
2. Document Databases: Stores data in documents (usually JSON, BSON, or XML format). Documents can have varying structures. Example: MongoDB, CouchDB.
3. Column-Family Stores (Wide-Column): Stores data in rows and dynamic columns rather than rigid tables. Excellent for analytical queries over vast datasets. Example: Apache Cassandra, HBase.
4. Graph Databases: Designed to store and traverse relationships between data entities. Entities are stored as nodes, and relationships as edges. Example: Neo4j.

Data abstraction hides complex technical details from users through layers of schema. It enables physical data independence.

Mechanism:

• The Internal Schema defines how data is physically stored on disk (e.g., file allocation, B-tree indexes, hashing).
• The Conceptual Schema defines the logical structure (e.g., tables, relationships) independently of storage.
• The External Schema defines the views for end-users.

Modification without Impact:
If a DBA realizes that a database is responding slowly, they might decide to move a table from a standard HDD to an SSD, or they might build a new B-tree index on a frequently queried column.
Because of Physical Data Independence (provided by the abstraction between the internal and conceptual levels), the logical structure of the tables remains exactly the same. The application programs query the conceptual schema using SQL. The DBMS maps the conceptual SQL request to the new physical access path internally. Therefore, not a single line of application code needs to be rewritten.

Constraints in the Relational Model are rules enforced on data columns to ensure the accuracy, reliability, and integrity of the data.

Common Types of Constraints:

1. Domain Constraints: Ensure that the value placed in an attribute belongs to the predefined domain of that attribute. For example, an Age column must contain integers, not text.
2. Key Constraints (Entity Integrity): Ensure that every relation has a primary key, and the primary key cannot contain a NULL value. This guarantees that every tuple can be uniquely identified.
3. Referential Integrity Constraints (Foreign Keys): Used to maintain consistency between two relations. If an attribute in Relation A refers to the primary key of Relation B, the value in Relation A must either match an existing primary key value in Relation B or be NULL.
4. Check Constraints: A user-defined condition that must be satisfied. For example, .

Differences based on specified criteria:

1. Redundancy (Data Duplication):

   • File Processing: High redundancy. Different departments often maintain their own separate files (e.g., HR and Payroll both keeping employee addresses). Leads to wasted space and update anomalies.
   • Database Approach: Minimal redundancy. Data is integrated centrally. An employee's address is stored once, and both HR and Payroll systems access that single source of truth.

2. Data Access:

   • File Processing: Inflexible. Extracting data requires writing a new application program in a traditional language (C, Java) every time a new type of report is needed.
   • Database Approach: Flexible and fast. DBMS provides a declarative Query Language (SQL) allowing users to formulate ad-hoc queries quickly without programming.

3. Security:

   • File Processing: Security is difficult to enforce. Usually managed at the file level by the Operating System, which lacks granularity.
   • Database Approach: High security. The DBMS provides granular access control. A DBA can grant or revoke privileges at the table, row, or even specific column level for different user roles.