Unit1 - Subjective Questions

Comparison between File System and DBMS:

Disadvantages of File Systems Solved by DBMS:

1. Data Redundancy and Inconsistency: Since different programmers create different files, the same information may be duplicated. DBMS centralizes data to reduce this.
2. Difficulty in Accessing Data: File systems require writing new programs to carry out new tasks. DBMS provides a query mechanism.
3. Data Isolation: Data is scattered in various files and formats, making it hard to retrieve appropriate data.
4. Atomicity Problems: File systems often lack mechanisms to ensure a transaction happens completely or not at all (e.g., fund transfer failures).

Objective: The primary goal of the Three-Schema Architecture is to separate the user applications from the physical database. This separation ensures Data Independence.

The Three Levels:

1. Internal Level (Physical Schema):

   • Describes how data is actually stored on the storage medium (disk).
   • Deals with complex low-level data structures, file organizations, and access paths (indexes).

2. Conceptual Level (Logical Schema):

   • Describes what data is stored in the database and the relationships among the data.
   • It hides the details of physical storage structures.
   • Concentrates on entities, data types, relationships, and constraints.

3. External Level (View Schema):

   • Describes only part of the database that a particular user group is interested in.
   • It hides the rest of the database from the user.
   • There can be many external views for the same database.

Definition: Data Independence is the ability to modify the schema definition in one level without affecting the schema definition in the next higher level.

Distinction:

1. Physical Data Independence:

   • The capacity to change the internal schema (physical storage) without having to change the conceptual schema.
   • Example: Creating a new index, changing the storage device, or switching from hashing to sequential file organization to improve performance does not require changing the logical structure of the data.

2. Logical Data Independence:

   • The capacity to change the conceptual schema without having to change the external schemas or application programs.
   • Example: Adding a new entity (table) or attribute (column) to the database should not break existing application code that does not use that new data.
   • Note: Logical data independence is harder to achieve than physical data independence.

Database Schema:

• Definition: The logical structure or design of the database. It is the skeleton of the database that represents the view of the entire database.
• Change Frequency: It changes very infrequently (only during design updates).
• Analogy: Similar to a variable declaration or a class definition in programming.
• Example:
  

Database Instance:

• Definition: The collection of information (data) stored in the database at a particular moment in time. It is also called the "current state" or "extension" of the database.
• Change Frequency: It changes frequently as data is inserted, updated, or deleted.
• Analogy: Similar to the value of a variable at a specific execution step.
• Example:
  Row 1: {John Doe, 101, 20}
  Row 2: {Jane Smith, 102, 21}

The five major components of a DBMS environment are:

1. Hardware:

   • The physical electronic devices such as computers, I/O devices, and storage devices (hard drives, RAID arrays) where the database resides.

2. Software:

   • The set of programs used to control and manage the overall database. This includes the DBMS software itself (like Oracle, MySQL), the Operating System, and network software.

3. Data:

   • The most important component. It acts as a bridge between the machine components and the human components. It includes both the actual operational data and the metadata (schema/data about data).

4. Procedures:

   • The instructions and rules that govern the design and use of the database. This includes instructions for logging in, backing up data, handling failures, and regulating access.

5. People:

   • The users who interact with the system. They include:
     • Database Administrators (DBA): Manage the system.
     • Application Programmers: Write code to interact with the DB.
     • End Users: Use the data (Naive or Sophisticated users).

Data Modeling:
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 creates a blueprint for the database.

Entity-Relationship (ER) Model:

• A high-level conceptual data model.
• It represents data as Entities (real-world objects), Attributes (properties of objects), and Relationships (associations between objects).
• It is widely used for database design and is represented diagrammatically using ER Diagrams.

Relational Model:

• A representational (logical) data model proposed by E.F. Codd.
• It represents data in the form of Tables (Relations) consisting of rows (tuples) and columns (attributes).
• Relationships between tables are established using common keys (Foreign Keys).
• It is the theoretical basis for SQL-based databases.

In the ER model, attributes describe the properties of entities:

1. Simple (Atomic) Attribute:

   • Cannot be divided further.
   • Example: Age, Gender.
   • Symbol: Oval.

2. Composite Attribute:

   • Can be divided into sub-parts.
   • Example: Address (composed of Street, City, Zip) or Name (First Name, Last Name).
   • Symbol: Oval connected to sub-ovals.

3. Multi-valued Attribute:

   • Can have multiple values for a single entity instance.
   • Example: Phone_Number (a person can have two numbers) or Degrees.
   • Symbol: Double Oval.

4. Derived Attribute:

   • The value is derived from another attribute; it is not physically stored.
   • Example: Age (derived from Date_of_Birth).
   • Symbol: Dashed Oval.

5. Key Attribute:

   • Uniquely identifies an entity.
   • Example: Student_ID.
   • Symbol: Oval with underlined text.

Cardinality ratios express the number of entities to which another entity can be associated via a relationship set.

1. One-to-One ():

   • An entity in set A is associated with at most one entity in set B, and vice versa.
   • Example: One Person has one Passport.

2. One-to-Many ():

   • An entity in set A is associated with any number of entities in set B, but an entity in set B is associated with at most one entity in set A.
   • Example: One Department employs many Employees.

3. Many-to-One ():

   • Reverse of . Many entities in A are associated with one entity in B.
   • Example: Many Students belong to one Class.

4. Many-to-Many ():

   • An entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A.
   • Example: Students enroll in Courses. (A student takes many courses; a course has many students).

Strong Entity Set:

• Definition: An entity set that has sufficient attributes to uniquely identify its members (i.e., it has a primary key).
• Dependence: Independent of other entity sets.
• Representation: Represented by a single rectangle.
• Example: Employee (with Emp_ID as primary key).

Weak Entity Set:

• Definition: An entity set that does not have a primary key of its own. It relies on the primary key of a strong entity (owner) for identification.
• Discriminator: It has a partial key (discriminator) to distinguish entities within the set belonging to the same owner.
• Dependence: Existence depends on the owner entity.
• Representation: Represented by a double rectangle. The relationship connecting it to the strong entity is called an Identifying Relationship (double diamond).
• Example: Dependent of an Employee (identified by Emp_ID + Dependent_Name).

Generalization (Bottom-Up Approach):

• The process of combining a number of entity sets that share the same features into a higher-level entity set.
• It emphasizes similarities.
• Example: Entities Car and Truck can be generalized into a superclass Vehicle containing common attributes like VehicleID, LicensePlate.

Specialization (Top-Down Approach):

• The process of designating subgroupings within an entity set that differ from other subgroups.
• It emphasizes differences (specific attributes).
• Example: A generic entity Account can be specialized into Savings_Account (with InterestRate) and Current_Account (with OverdraftLimit).

Inheritance:

• In both cases, lower-level entities inherit attributes of the higher-level entities.

Integrity constraints ensure data accuracy and consistency in a relational database.

1. Domain Constraints:

   • Specifies that the value of each attribute must be an atomic value from the defined domain (data type, range).
   • Example: Age must be an integer .

2. Entity Integrity Constraint:

   • States that no primary key value can be NULL.
   • Reason: Primary keys are used to identify individual tuples; if null, identity is lost.

3. Referential Integrity Constraint:

   • Maintained between two related tables (referencing and referenced).
   • States that a Foreign Key value in the child table must either match a Primary Key value in the parent table or be NULL.
   • Example: If Student table references Dept_ID from Department table, you cannot assign a Dept_ID to a student that doesn't exist in the Department table.

4. Key Constraints (Uniqueness):

   • Specific attributes (Candidate Keys) must have unique values for every tuple in the relation.
   • Example: No two employees can have the same Social_Security_Number.

1. Super Key:

   • A set of one or more attributes that, taken collectively, allows us to identify uniquely a tuple in a relation. It may contain extraneous attributes.
   • Example: {Student_ID}, {Student_ID, Name}.

2. Candidate Key:

   • A "minimal" super key. It is a super key such that no proper subset of it is a super key.
   • Example: If Student_ID is unique, {Student_ID} is a candidate key. {Student_ID, Name} is not (because Name is redundant for uniqueness).

3. Primary Key:

   • One candidate key chosen by the database designer to be the principal means of identifying tuples within a relation. It cannot be NULL.

4. Foreign Key:

   • An attribute (or set of attributes) in a relation that refers to the primary key of another relation. It establishes the link between tables.

Relational Databases (SQL):

• Structure: Table-based (Rows and Columns).
• Schema: Pre-defined, rigid schema. Must define types before inserting data.
• Scaling: Vertically scalable (scale-up: adding more power to the server).
• Query Language: Uses Structured Query Language (SQL).
• Transactions: Follows ACID properties (Atomicity, Consistency, Isolation, Durability) strictly.
• Examples: MySQL, PostgreSQL, Oracle.

Non-Relational Databases (NoSQL):

• Structure: Document-based, Key-Value pairs, Graph-based, or Wide-column stores.
• Schema: Dynamic schema (Schema-less) for unstructured data.
• Scaling: Horizontally scalable (scale-out: adding more servers).
• Query Language: Focused on collection of documents; varies by DB (e.g., MongoDB query).
• Transactions: Often follows BASE properties (Basically Available, Soft state, Eventual consistency) (CAP theorem trade-offs).
• Examples: MongoDB, Cassandra, Redis, Neo4j.

Three-Tier Architecture:
This architecture separates the presentation, application logic, and data management into three distinct layers.

1. Client (Presentation Tier): The user interface (GUI/Web Browser). It knows nothing about the database, only how to display data sent by the application server.
2. Application Server (Business Logic Tier): Acts as an intermediary. It receives requests from the client, processes the business logic, and sends queries to the database server.
3. Database Server (Data Tier): The actual DBMS that executes queries and returns results to the application server.

Difference from Two-Tier:

• Two-Tier: The client communicates directly with the database server using an API (like JDBC/ODBC). Business logic is either in the client (Fat Client) or the DB (Stored Procedures).
• Comparison: Three-tier is more secure (client has no direct access to DB), more scalable, and easier to maintain compared to Two-tier.

Definition: A Database Administrator (DBA) is a person or group responsible for the overall control and management of the database system.

Responsibilities:

1. Schema Definition: Creating the original database schema (creating tables, defining constraints).
2. Storage Structure and Access Method Definition: Deciding how data is stored and indexed for efficiency.
3. Schema and Physical Organization Modification: Changing the schema if requirements change or tuning physical files for performance.
4. Granting Authorization: Managing user accounts and determining which users can access or modify which parts of the database.
5. Routine Maintenance: Backing up the database, monitoring disk space, and performance tuning.

1. Controlling Data Redundancy: Prevents duplicate data, saving storage and reducing inconsistency.
2. Restricting Unauthorized Access: Provides security subsystems to create user accounts with specific access rights.
3. Providing Persistent Storage for Program Objects: Complex data structures can be stored permanently.
4. Providing Storage Structures for Efficient Query Processing: Uses indexes and query optimization algorithms.
5. Backup and Recovery: Automatically handles recovery from crashes (power failure, disk failure) to a consistent state.
6. Enforcing Integrity Constraints: Ensures data entered follows business rules (e.g., account balance cannot be negative).

These are legacy data models used before the Relational Model became dominant.

Hierarchical Model:

• Structure: Organizes data in a tree-like structure (Parent-Child relationship).
• Constraint: A child record can have only one parent (1:N relationship enforced).
• Traversal: To access data, one must traverse the tree from the root.
• Example: XML files, Windows Registry, IBM IMS.

Network Model:

• Structure: Organizes data in a graph structure.
• Flexibility: A child record can have multiple parents (allows M:N relationships directly).
• Implementation: Uses pointers/links to connect records.
• Complexity: Very complex to design and modify compared to the relational model.
• Example: IDMS.

Definition: The degree of a relationship set is the number of entity sets participating in that relationship.

1. Unary (Degree 1):

   • A relationship involves a single entity set (Recursive relationship).
   • Example: Employee manages Employee (One employee is a manager of another).

2. Binary (Degree 2):

   • A relationship involves two entity sets.
   • Example: Student enrolls in Course.

3. Ternary (Degree 3):

   • A relationship involves three entity sets.
   • Example: Employee works on a Project at a Branch. (Relationship among Employee, Project, and Branch).

4. N-ary:

   • Involves N entity sets.

DBMS is used in almost every sector where data needs to be managed efficiently:

1. Banking: For customer information, accounts, loans, and banking transactions.
2. Airlines: For reservations, schedule information, and flight tracking.
3. Universities: For student information, course registrations, and grades.
4. Credit Card Transactions: For purchases on credit cards and generation of monthly statements.
5. Telecommunications: To keep records of calls made, generating bills, and maintaining prepaid balances.
6. E-commerce: For inventory, order tracking, and recommendation engines (e.g., Amazon).
7. Human Resources: For information about employees, salaries, and payroll taxes.

Participation constraints specify whether the existence of an entity depends on its being related to another entity via the relationship type.

1. Total Participation (Existence Dependency):

   • Every entity in the entity set must participate in the relationship.
   • Representation: Double line connecting the entity set to the relationship diamond.
   • Example: In a relationship between Loan and Borrower, every Loan must belong to a Borrower. Total participation of Loan.

2. Partial Participation:

   • Some entities in the entity set may not participate in the relationship.
   • Representation: Single line connecting the entity set to the relationship diamond.
   • Example: In Employee manages Department, not every employee is a manager. Partial participation of Employee.