Unit 4: Introduction to Database Management System - Subjective Questions

A database is an organized collection of logically related data that is stored electronically and can be accessed, managed, and updated efficiently.

Key characteristics:

• Related data: The stored data describes related entities, such as students, courses, and enrollments.
• Persistent storage: Data remains available even after an application is closed.
• Controlled redundancy: Unnecessary duplication of data is minimized.
• Data integrity: Rules and constraints help maintain accurate and consistent data.
• Data sharing: Multiple authorized users and applications can access the data.
• Security: Access controls protect data against unauthorized use.
• Efficient retrieval: Queries can locate and process required information quickly.

A Database Management System (DBMS) is software that enables users to define, create, store, retrieve, update, and administer a database.

Major functions of a DBMS include:

• Data definition: Creates tables, relationships, constraints, and schemas.
• Data manipulation: Supports insertion, modification, deletion, and retrieval of data.
• Query processing: Interprets and efficiently executes user queries.
• Security management: Authenticates users and controls permissions.
• Integrity enforcement: Ensures that stored data follows defined rules.
• Transaction management: Maintains reliable execution of database operations.
• Concurrency control: Coordinates simultaneous access by multiple users.
• Backup and recovery: Restores data after hardware, software, or user failures.
• Metadata management: Maintains information about the structure of the database.

Traditional file-processing system:

• Each application generally maintains its own files.
• Data redundancy and inconsistency are common.
• Data sharing between applications is difficult.
• Program logic is strongly dependent on file formats.
• Security, recovery, and concurrent access must be implemented separately.

DBMS:

• Data is centrally organized and managed.
• Redundancy can be controlled through database design.
• Multiple applications can share the same database.
• Data independence reduces the effect of structural changes on programs.
• Security, integrity, concurrency, backup, and recovery are built-in services.

Thus, a DBMS provides a more integrated, reliable, and scalable approach, although it may require greater cost, expertise, and computing resources.

Advantages:

• Reduces uncontrolled data duplication.
• Improves data consistency and integrity.
• Enables controlled data sharing.
• Provides centralized security and authorization.
• Supports concurrent access and transaction management.
• Offers backup and recovery facilities.
• Promotes standardization and data independence.
• Provides flexible query and reporting capabilities.

Limitations:

• DBMS software, hardware, licensing, and maintenance can be expensive.
• Installation and administration require trained personnel.
• A DBMS may introduce processing overhead for small applications.
• Centralized failure can affect many users if recovery measures are inadequate.
• Migration from older systems may be complex.
• Security breaches can expose a large amount of centralized data.

A DBMS is most beneficial when an organization needs reliable, shared, secure, and structured data management.

Important DBMS applications include:

1. Banking: Stores customer accounts, transactions, loans, and payment records while supporting secure concurrent transactions.
2. Education: Manages student admissions, attendance, courses, examinations, grades, and library records.
3. Healthcare: Maintains patient details, appointments, medical histories, prescriptions, and billing information.
4. E-commerce: Stores product catalogs, customer profiles, orders, payments, inventory, and delivery details.
5. Transportation: Supports ticket reservations, schedules, passenger records, routes, and seat availability.

Other applications include telecommunications, government records, social media, payroll, manufacturing, and scientific research. In each case, the DBMS supports organized storage, fast retrieval, security, consistency, and multi-user access.

A DBMS environment contains the following major components:

• Hardware: Servers, client computers, storage devices, and network equipment on which the database operates.
• Software: The DBMS, operating system, application programs, utilities, and network software.
• Data: Operational data as well as metadata that describes database structures and constraints.
• Procedures: Instructions and policies for database design, operation, security, backup, and recovery.
• Database access language: A language such as SQL used to define, retrieve, and modify data.
• People: Database administrators, designers, developers, end users, and support personnel.

These components cooperate to provide a complete environment for securely storing and processing organizational data.

The main internal functional components are:

• Query processor: Parses, validates, optimizes, and executes database queries.
• Storage manager: Controls the storage, retrieval, and updating of data on secondary storage.
• File manager: Organizes database files and manages disk-space allocation.
• Buffer manager: Transfers required data pages between disk and main memory.
• Transaction manager: Ensures that transactions execute reliably and preserve database consistency.
• Concurrency-control manager: Coordinates simultaneous operations to prevent incorrect results.
• Recovery manager: Uses logs and backups to restore the database after a failure.
• Authorization and integrity manager: Enforces user privileges and data constraints.
• Data dictionary or catalog: Stores metadata about schemas, tables, attributes, constraints, and users.

Together, these modules translate user requests into safe and efficient operations on stored data.

The principal users and actors include:

• Database administrators (DBAs): Control security, storage, performance, backup, recovery, and routine maintenance.
• Database designers: Identify data requirements and design schemas, relationships, and constraints.
• System analysts: Study organizational requirements and specify how information systems should behave.
• Application programmers: Develop programs, forms, reports, and interfaces that communicate with the DBMS.
• End users: Use database applications or query tools to perform their work.
• DBMS system designers and implementers: Develop the DBMS software and its internal modules.
• Tool developers: Create design, monitoring, reporting, administration, and performance tools.
• Operators and maintenance staff: Run hardware and software systems and keep the operational environment available.

These actors may interact directly with the database or work behind the scenes to develop and maintain its environment.

A Database Administrator (DBA) has overall responsibility for the effective operation of a database system.

Key responsibilities include:

• Installing, configuring, and upgrading DBMS software.
• Defining storage structures and access methods.
• Creating user accounts, roles, and access privileges.
• Enforcing security, privacy, and integrity policies.
• Monitoring database availability and performance.
• Tuning queries, indexes, memory, and storage usage.
• Planning and performing backups.
• Recovering the database after failures.
• Managing concurrent access and resolving operational problems.
• Maintaining documentation and coordinating with designers, developers, and users.

The DBA ensures that the database remains secure, consistent, available, and efficient.

• Naive or parametric users: Perform repetitive, predefined operations through forms or menus. Examples include bank tellers and reservation clerks.
• Casual users: Access the database occasionally and may issue different queries each time. Managers are common examples.
• Sophisticated users: Understand database facilities and use complex query, analysis, or development tools. Examples include engineers, analysts, and researchers.
• Standalone users: Maintain personal databases using ready-made software packages, often on an individual computer.

The categories differ mainly in their technical knowledge, frequency of access, and manner of interaction. Naive users rely on prepared transactions, whereas sophisticated users can formulate advanced queries and analyses.

DBMS architecture describes how database components, schemas, users, applications, and storage mechanisms are organized and how they interact.

A layered architecture is useful because it:

• Separates user views from the logical database design and physical storage.
• Hides unnecessary implementation details from users.
• Allows different users to have different views of the same data.
• Improves security by exposing only authorized information.
• Simplifies database design and maintenance.
• Supports data independence by isolating changes at one level from higher levels.
• Enables the DBMS to optimize physical storage without changing user applications.

The widely used three-schema architecture divides the system into external, conceptual, and internal levels.

The three-schema architecture separates a database into three levels:

1. External level: Contains user-specific views. For example, a teacher may see student names and grades, while an accounts clerk sees names and fee details.
2. Conceptual level: Describes the complete logical structure of the database, including entities such as Student, Course, and Enrollment, their attributes, relationships, and constraints.
3. Internal level: Describes physical storage details, such as files, record layouts, indexes, partitions, and access paths.

Mappings connect these levels:

• External-conceptual mappings associate user views with the conceptual schema.
• Conceptual-internal mapping associates the logical schema with physical storage.

This separation supports multiple views, security, abstraction, and both logical and physical data independence.

The external level, also called the view level, is the highest level of database abstraction. It describes the portion of the database visible to a particular user or application.

Importance:

• Presents only data relevant to a user's work.
• Hides the complexity of the complete database.
• Supports multiple views of the same underlying data.
• Improves security by concealing unauthorized attributes or records.
• Allows information to be renamed, formatted, or combined for a particular application.
• Reduces the effect of some conceptual-schema changes on user programs.

For example, an employee may view personal and salary information, while a customer sees only service-related information. Both views can originate from the same conceptual database.

The conceptual level describes the complete logical structure of the database for the entire organization. It specifies entities, attributes, relationships, constraints, and data types without describing physical storage details.

Conceptual level versus external level:

• The conceptual level provides a global logical view; the external level provides user-specific views.
• A database generally has one conceptual schema but may have many external schemas.
• The conceptual schema contains all logically relevant data; each external schema contains only a selected portion.
• Database designers and DBAs mainly manage the conceptual schema, whereas users and applications interact with external views.

For a college database, the conceptual schema may include students, teachers, courses, departments, and enrollments, while a student's external view may show only personal details, registered courses, and grades.

The internal level, also called the physical level, describes how data is actually stored and accessed on storage devices.

It may define:

• File organization and record layouts.
• Data pages, blocks, and physical record placement.
• Indexes, hashing structures, and access paths.
• Data compression and encoding methods.
• Partitioning and clustering arrangements.
• Storage allocation and free-space management.
• Physical ordering of records.

The internal level is normally hidden from end users. The DBMS and DBA can modify these structures to improve performance or storage efficiency without changing the logical meaning of the data. This separation is the foundation of physical data independence.

Schema mappings are rules that connect descriptions at adjacent levels of the three-schema architecture.

• External-conceptual mapping: Connects each external view to the conceptual schema. It tells the DBMS how data requested through a user view corresponds to the global logical database.
• Conceptual-internal mapping: Connects the conceptual schema to the internal schema. It tells the DBMS how logical records and relationships are represented in physical storage.

Role of mappings:

• Translate queries and results between abstraction levels.
• Allow several user views to share one conceptual database.
• Hide physical implementation details.
• Help preserve applications when schemas change.
• Support logical and physical data independence.

When a lower-level schema changes, the relevant mapping can often be updated instead of rewriting all higher-level schemas and application programs.

Data independence is the ability to modify a schema at one level of DBMS architecture without requiring changes to the schema at the next higher level or to the application programs that use it.

Importance:

• Reduces the cost of database maintenance.
• Prevents minor storage or design changes from breaking applications.
• Allows the database to evolve with organizational requirements.
• Enables performance tuning without altering user views.
• Extends the useful life of application software.
• Separates concerns among users, designers, developers, and administrators.
• Improves system flexibility and maintainability.

The two principal forms are physical data independence, involving changes to internal storage, and logical data independence, involving changes to the conceptual schema.

Physical data independence is the ability to change the internal schema without changing the conceptual schema or application programs.

Examples of internal changes include:

• Adding or removing an index.
• Changing from sequential file organization to hashing.
• Moving data to a new storage device.
• Changing record placement or page size.
• Partitioning a large table.
• Applying compression.
• Modifying an access path to improve query performance.

For example, a DBA may create an index on StudentID to speed up searches. The Student entity, user views, and application queries remain logically unchanged. Physical data independence is generally easier to achieve than logical data independence because physical changes do not alter the logical meaning or relationships of the data.

Logical data independence is the ability to change the conceptual schema without requiring changes to external schemas or application programs that are not affected by the change.

Examples include:

• Adding a new attribute such as Email to a Student entity.
• Introducing a new entity such as Department.
• Splitting one relation into two related relations.
• adding a new relationship or integrity constraint.
• Extending the database to store additional categories of information.

For example, a college may add a Guardian entity to its conceptual schema. Applications that only display course results should continue to operate through their existing external views.

Logical data independence is difficult to achieve because changes to the logical structure may affect relationships, constraints, queries, and user views. Carefully defined views and external-conceptual mappings help reduce this effect.

Logical data independence:

• Concerns changes to the conceptual schema.
• Protects external views and application programs.
• Examples include adding an attribute, adding an entity, or changing a relationship.
• Depends mainly on external-conceptual mappings.
• Is comparatively difficult to achieve because applications often depend on logical structures.

Physical data independence:

• Concerns changes to the internal schema.
• Protects the conceptual schema, external views, and applications.
• Examples include adding indexes, changing file organization, or moving data to different storage.
• Depends mainly on conceptual-internal mapping.
• Is generally easier to achieve because the logical content remains unchanged.

Both types reduce program-data dependence and allow the database to evolve with fewer changes to application software.