Unit 1: Basics of Database - Subjective Questions

A database is an organized collection of logically related data that is stored electronically and can be accessed, managed, and updated efficiently through a Database Management System (DBMS).

Major characteristics:

• Integrated data: Related data is combined into a unified collection.
• Controlled redundancy: Duplicate data is minimized to improve consistency.
• Data sharing: Multiple users and applications can access the same data.
• Data persistence: Data remains available even after an application terminates.
• Data integrity: Constraints help maintain accurate and valid data.
• Security: Authorization and access-control mechanisms protect sensitive information.
• Concurrency: Multiple users can work with the database simultaneously.
• Backup and recovery: Data can be restored after hardware, software, or human failures.

For example, a university database may store information about students, courses, instructors, and enrollments in an integrated form.

Databases form the foundation of modern information systems because they provide a reliable mechanism for storing, organizing, retrieving, and processing large volumes of data.

Importance of databases:

• Efficient data management: Large data collections can be searched and updated quickly.
• Reduced redundancy: Centralized storage avoids unnecessary duplication.
• Improved consistency: Changes made to shared data are reflected across applications.
• Better decision-making: Accurate and timely data supports reporting, analytics, and forecasting.
• Data security: Authentication, authorization, encryption, and auditing protect information.
• Multi-user access: Many users can access data concurrently under controlled conditions.
• Integrity enforcement: Rules and constraints prevent invalid data from being stored.
• Recovery support: Backup and transaction-recovery mechanisms protect against data loss.
• Application independence: Applications can often evolve without reorganizing all stored data.

Thus, databases improve the reliability, scalability, and operational efficiency of information systems.

Database systems are used in nearly every sector that needs structured and reliable information management.

1. Banking: Databases store customer accounts, balances, loans, and transaction histories. They support operations such as fund transfers and ATM withdrawals.
2. Healthcare: Hospitals maintain patient records, diagnoses, prescriptions, laboratory results, and appointment schedules.
3. Education: Institutions store student profiles, course registrations, attendance, grades, and examination records.
4. E-commerce: Online stores manage product catalogs, customers, shopping carts, orders, payments, and inventory.
5. Telecommunications: Service providers maintain subscriber details, call records, billing information, and network usage data.

Other important applications include airline reservation systems, government records, social networks, manufacturing, human-resource management, and scientific research. In each case, a database enables secure storage, rapid retrieval, consistency, and concurrent access.

The three terms are related but represent different concepts:

• Database: The organized collection of related data itself. For example, tables containing student, course, and enrollment information form a university database.
• Database Management System (DBMS): The software used to define, create, access, update, secure, and administer a database. Examples include PostgreSQL, Oracle Database, MySQL, and Microsoft SQL Server.
• Database system: The complete operational environment, including the database, DBMS, application programs, users, procedures, and supporting hardware.

Key distinction:

• The database is the stored information.
• The DBMS is the software that manages the information.
• The database system is the entire environment in which database operations occur.

For example, customer records constitute a database, PostgreSQL may be the DBMS, and the servers, PostgreSQL software, customer records, banking application, administrators, and users together constitute the database system.

In a traditional file-processing system, each application generally maintains its own files. In the database approach, shared data is managed centrally through a DBMS.

Comparison:

• Redundancy: File systems commonly duplicate data; databases control duplication.
• Consistency: Separate files can contain conflicting values; databases provide centralized integrity controls.
• Data isolation: File data may exist in incompatible formats; databases provide uniform access mechanisms.
• Program-data dependence: File structures are closely tied to application code; a DBMS provides data independence.
• Sharing: File sharing is difficult; databases support controlled multi-user access.
• Security: File permissions are often coarse-grained; a DBMS can provide roles, privileges, views, and auditing.
• Concurrency: Simultaneous file updates can cause errors; database transactions provide concurrency control.
• Recovery: File recovery is frequently manual; a DBMS offers logging, backup, and recovery facilities.
• Query capability: File processing requires custom programs; databases support declarative query languages such as SQL.

Although file systems may be sufficient for small, simple tasks, the database approach is generally more suitable for large, shared, and evolving information systems.

Databases can be classified according to their data model, deployment, and intended workload.

Major types include:

• Relational databases: Organize data into tables consisting of rows and columns. Relationships are represented using keys, and SQL is commonly used.
• Hierarchical databases: Arrange records in a tree structure. Each child usually has one parent, making them suitable for strict one-to-many relationships.
• Network databases: Represent records as a graph and permit a record to have multiple parents. They support complex many-to-many navigation.
• Object-oriented databases: Store persistent objects containing both state and behavior. They support concepts such as classes and inheritance.
• Document databases: Store semi-structured documents, often in JSON-like formats. They provide flexible schemas.
• Key-value databases: Store values identified by unique keys. They offer simple access and high scalability.
• Column-family databases: Organize data by column families and are useful for distributed, large-scale, sparse data.
• Graph databases: Store nodes, edges, and properties, making relationship traversal efficient.
• Distributed databases: Store logically related data across multiple networked locations.
• Cloud databases: Run on cloud infrastructure and commonly provide elastic capacity and managed services.

The appropriate type depends on data structure, relationship complexity, consistency requirements, scalability, and query patterns.

Relational databases organize data into predefined tables and generally use SQL. NoSQL databases include document, key-value, column-family, and graph systems designed for flexible structures or distributed scalability.

Differences:

• Schema: Relational databases usually have a well-defined schema; many NoSQL systems allow flexible or evolving structures.
• Relationships: Relational systems use keys and joins; NoSQL systems may embed related data or use model-specific links.
• Transactions: Relational databases traditionally emphasize strong transactional guarantees; NoSQL guarantees vary by product.
• Scaling: Relational systems have traditionally scaled vertically, though modern products also support distribution. NoSQL systems are often designed for horizontal scaling.
• Querying: Relational systems commonly use standardized SQL; NoSQL query interfaces differ across products.
• Data suitability: Relational databases suit structured data and complex joins, while NoSQL databases often suit high-volume, semi-structured, or highly connected data.

Appropriate use:

• Use a relational database for banking, accounting, inventory, or systems requiring strict integrity and complex transactions.
• Use NoSQL for rapidly changing document structures, large-scale caching, event collection, content management, or social-network relationship analysis.

The choice should be based on requirements rather than the assumption that one category is universally superior.

The three-schema architecture, also called the ANSI/SPARC architecture, separates a database description into external, conceptual, and internal levels.

1. External level:

   • Contains user-specific views of the database.
   • Different users may see different subsets or representations of the same data.
   • It improves simplicity and security by hiding irrelevant or restricted information.

2. Conceptual level:

   • Describes the overall logical structure of the complete database.
   • Defines entities, attributes, relationships, and constraints.
   • It is independent of physical storage details.

3. Internal level:

   • Describes how data is physically stored.
   • Includes file organization, pages, indexes, record placement, partitioning, and access paths.

Mappings connect external schemas to the conceptual schema and the conceptual schema to the internal schema. This separation supports data abstraction and data independence, allowing changes at one level to have limited effects on higher levels.

Data independence is the ability to change the schema at one level of database architecture without requiring changes at the next higher level or in application programs.

Physical data independence:

• It is the ability to modify the internal storage schema without changing the conceptual or external schemas.
• Examples include creating an index, changing file organization, moving data to new storage, compressing records, or partitioning a table.
• Applications continue to use the same logical tables and queries.

Logical data independence:

• It is the ability to modify the conceptual schema without changing user views or application programs, provided suitable mappings are maintained.
• Examples include adding an optional attribute, splitting a logical entity, or introducing a new relationship while preserving existing views.

Difference: Physical data independence concerns changes in how data is stored, whereas logical data independence concerns changes in what logical structures are represented. Logical data independence is usually harder to achieve because application programs often depend on logical structures.

A database system consists of several interdependent components:

• Hardware: Servers, storage devices, client machines, and network equipment on which the database operates.
• Software: The DBMS, operating system, network software, utilities, and database applications.
• Data: User data, indexes, metadata, logs, and system-catalog information.
• Procedures: Rules for database design, operation, security, backup, recovery, and maintenance.
• People: Database administrators, designers, developers, analysts, operators, and end users.
• Access languages and interfaces: SQL, APIs, administrative tools, forms, reports, and graphical interfaces.

Interaction: Users or applications submit requests through an interface. The DBMS interprets the request, checks authorization and integrity constraints, selects an execution strategy, accesses stored data through the operating system, and returns results. Transaction, concurrency, and recovery components ensure that simultaneous operations remain reliable. Administrators use utilities and metadata to monitor, secure, tune, and recover the system.

These DBMS modules cooperate to process requests safely and efficiently:

• Query processor: Parses and validates database statements, translates them into an internal representation, optimizes alternative execution plans, and executes the selected plan.
• Storage manager: Controls interaction between higher-level DBMS modules and stored data. It manages files, pages, records, indexes, buffers, and disk-space allocation.
• Transaction manager: Coordinates transactions and concurrent users. It helps preserve transaction properties by controlling schedules, locks, timestamps, or other concurrency mechanisms.
• Recovery manager: Restores the database to a consistent state after failures. It uses logs, checkpoints, backups, undo operations, and redo operations.

For example, when a user submits an update, the query processor determines how to locate the target records, the storage manager accesses their pages, the transaction manager coordinates concurrent operations, and the recovery manager records enough information to reverse or repeat the update if a failure occurs.

A database schema is the formal description or blueprint of a database. It defines structures such as tables, attributes, relationships, constraints, and views.

• Schema: The overall structural definition of the database. It changes relatively infrequently.
• Subschema: A portion or user-specific view of the schema. It presents only the data required by a particular user group or application.
• Database state or instance: The actual data stored in the database at a particular moment. It changes whenever records are inserted, updated, or deleted.
• Metadata: Data that describes other data. It includes table names, column types, constraints, index definitions, privileges, and relationships. A DBMS normally stores metadata in a system catalog or data dictionary.

For example, the definition STUDENT(StudentID, Name, Program) belongs to the schema. The collection of student rows present today is the database state. Information stating that StudentID is an integer and a primary key is metadata.

A data dictionary, also called a system catalog, is a repository of metadata maintained by the DBMS.

It commonly stores:

• Names and definitions of tables, columns, views, and indexes.
• Data types, sizes, and default values.
• Primary-key, foreign-key, uniqueness, and check constraints.
• User accounts, roles, and privileges.
• Stored procedures, functions, and triggers.
• Storage information and database statistics.
• Dependencies among database objects.

Role of the catalog:

• The query processor consults statistics and index information during optimization.
• The DBMS uses constraints to validate updates.
• Security modules use privilege information to authorize access.
• Administrators use metadata to inspect, document, and maintain the database.
• Development tools use it to generate forms, reports, and object definitions.

Because the catalog describes the database itself, it is essential for automatic database management, consistency checking, security, and performance optimization.

A data model is a collection of concepts used to describe data structures, relationships, constraints, and operations on data.

Comparison of major models:

• Hierarchical model: Organizes records as a tree. Each child normally has one parent. It is efficient for predictable one-to-many navigation but is inflexible for complex relationships.
• Network model: Organizes records as a graph of owner-member sets. A record may have several parents, enabling many-to-many relationships, but access is often navigational and structurally complex.
• Relational model: Represents data in tables. Rows represent tuples, columns represent attributes, and keys establish relationships. It offers strong mathematical foundations, declarative querying, and logical simplicity.
• Object-oriented model: Represents data as persistent objects with identity, attributes, methods, classes, and inheritance. It is suitable for complex objects such as multimedia, engineering models, and scientific structures.

The hierarchical and network models emphasize navigational access, the relational model emphasizes value-based declarative access, and the object-oriented model integrates database persistence with object-oriented concepts.

Data models may be classified according to their level of abstraction:

1. Conceptual data model:

   • Provides a high-level, business-oriented representation.
   • Identifies major entities, relationships, and business rules without implementation details.
   • Artifact: An Entity-Relationship diagram containing entities such as Student, Course, and Instructor.

2. Logical data model:

   • Converts the conceptual representation into structures supported by a chosen model, commonly the relational model.
   • Defines relations, attributes, primary keys, foreign keys, and logical constraints.
   • Artifact: Relations such as STUDENT(StudentID, Name) and ENROLLMENT(StudentID, CourseID, Grade).

3. Physical data model:

   • Describes how the logical structures will be implemented in a specific DBMS.
   • Specifies column types, indexes, partitions, file placement, compression, and other storage details.
   • Artifact: SQL table definitions and index configurations.

These levels move from business meaning, through logical organization, to storage implementation.

Conceptual database design converts organizational requirements into a high-level representation independent of a particular DBMS.

Major steps:

1. Collect requirements: Interview users, examine documents, and identify required operations, reports, and business rules.
2. Identify entity types: Determine important real-world objects, such as Customer, Product, and Order.
3. Identify attributes: Specify descriptive properties and suitable identifiers for each entity.
4. Select keys: Choose candidate keys and identify the preferred key for each entity.
5. Identify relationships: Determine meaningful associations, such as a Customer placing an Order.
6. Specify cardinality: State whether relationships are one-to-one, one-to-many, or many-to-many.
7. Specify participation: Determine whether participation is mandatory or optional.
8. Identify weak entities and specialization: Model existence-dependent entities and superclass-subclass structures where required.
9. State constraints: Record uniqueness, value, temporal, and other business rules.
10. Validate the model: Review it with users and test it against required transactions and scenarios.

The main result is an ER diagram accompanied by definitions and documented business constraints.

Transforming an ER model into a relational schema involves applying systematic mapping rules:

• Strong entity: Create one relation for each strong entity. Include its simple attributes and choose its primary key.
• Composite attribute: Replace it with its component attributes. For example, Address may become Street, City, and PostalCode.
• Multivalued attribute: Create a separate relation containing the owner's key and the attribute value.
• Weak entity: Create a relation containing its attributes, partial key, and the owner entity's key. Their combination normally forms the primary key.
• One-to-many relationship: Place the primary key of the one-side as a foreign key in the relation on the many-side.
• One-to-one relationship: Place a foreign key on a suitable side, usually considering total participation, and enforce uniqueness.
• Many-to-many relationship: Create an associative relation containing foreign keys referencing both participating relations. Relationship attributes are stored there.
• Higher-degree relationship: Create a separate relation containing the keys of all participating entities.
• Specialization: Use an appropriate mapping, such as one relation for the superclass and one for each subclass.

Finally, validate keys, referential constraints, nullability, and normalization to ensure that the logical schema accurately represents the conceptual model.

Physical database design determines how a logical schema will be stored and accessed using a specific DBMS and hardware environment. Its objective is to achieve acceptable performance, capacity, availability, security, and maintainability.

Major decisions include:

• Selecting DBMS-specific data types and storage formats.
• Choosing heap, clustered, sequential, or other file organizations.
• Creating indexes for frequently searched, joined, sorted, or grouped columns.
• Selecting composite indexes and determining column order.
• Partitioning large tables by range, list, hash, or another strategy.
• Deciding whether controlled denormalization is justified for performance.
• Defining compression, caching, and memory-allocation strategies.
• Assigning data and logs to suitable storage devices or tablespaces.
• Planning backup, replication, recovery, and archival mechanisms.
• Estimating storage growth and monitoring query workloads.

Physical design requires balancing trade-offs. For example, an index may speed up retrieval but consume storage and increase the cost of insert, update, and delete operations. Design choices should therefore be based on measured or anticipated workloads.

Centralized architecture:

• The database and DBMS reside at one central site.
• It simplifies control, security, and consistency.
• The central site may become a bottleneck or single point of failure.

Client-server architecture:

• Clients handle presentation and possibly application logic, while a database server processes queries and manages data.
• Two-tier systems connect clients directly to the database; three-tier systems introduce an application server.
• It provides workload separation but requires connection and network management.

Distributed architecture:

• A logically unified database is stored across multiple sites.
• Data may be fragmented or replicated.
• It can improve locality, scalability, and availability, but distributed transactions and consistency are more complex.

Cloud architecture:

• Databases operate on cloud infrastructure and may be delivered as managed services.
• Benefits include elastic scaling, rapid provisioning, automated backups, and usage-based pricing.
• Challenges include vendor dependence, cost control, data governance, latency, and regulatory compliance.

Architecture selection depends on workload distribution, availability targets, scalability, administrative capacity, security, and cost.

The university database can be developed through three design stages.

1. Conceptual design:

• Identify entities such as Student, Department, Course, Instructor, and Enrollment.
• Identify relationships: a department offers courses, employs instructors, and admits students; instructors teach courses; students enroll in courses.
• Specify cardinalities and participation. For example, one department may offer many courses, while each course belongs to one department.
• Record business rules, such as a student being unable to enroll in the same course offering twice.

2. Logical design:

• Transform entities and relationships into relations, for example:
  • DEPARTMENT(DepartmentID, Name)
  • STUDENT(StudentID, Name, DepartmentID)
  • COURSE(CourseID, Title, Credits, DepartmentID)
  • INSTRUCTOR(InstructorID, Name, DepartmentID)
  • ENROLLMENT(StudentID, CourseID, Semester, Grade)
• Define primary keys and foreign keys.
• Use a composite key such as (StudentID, CourseID, Semester) for Enrollment when appropriate.
• Validate integrity constraints and normalize the relations to reduce redundancy.

3. Physical design:

• Select DBMS-specific column types and storage settings.
• Create indexes on identifiers and frequently searched columns.
• Consider indexes such as (StudentID, Semester) for student schedules and (CourseID, Semester) for class lists.
• Partition historical enrollment data if its volume is large.
• Establish access privileges, backups, recovery procedures, and capacity plans.

This progression ensures that business requirements are captured before logical structures and implementation-specific performance decisions are introduced.