Database Management System Semester 1 Quiz

Relational Database Design

• Anomaly refers to inconsistencies in relational tables during operations, arising from redundant data and poor table construction.
• Database integrity is compromised due to anomalies, necessitating normalization to eliminate these issues.
• Normalization involves splitting and joining tables to design a more efficient database structure.

Anomalies in Databases

• Types of anomalies include:
  • Updation/Update Anomaly: Inconsistencies during data updates; requires updating all affected rows (e.g., multiple entries for the same worker).

Data Independence

• Physical Data Independence: Allows modifications to physical storage without changing the conceptual or external views; enhances performance and is easier to achieve.
• Logical Data Independence: Involves changes to the conceptual schema without altering application programs; more complex to achieve.

Database System Architecture

• The 3-tier architecture is widely used, consisting of:
  • Database (Data) Tier: Contains the database and query processing languages.
  • Application (Middle) Tier: Houses application servers that access the database.
  • User (Client) Tier: Where user applications operate.

User Types

• Sophisticated Users: Interact using database query languages without programming.
• Specialized Users: Create specialized applications not fitting traditional data processing.
• Naive Users: Have minimal database knowledge and interact using predefined application programs (e.g., bank clerks).

Database Administrator (DBA)

• Controls design and utilization of the database.
• Provides technical support throughout the database lifecycle (design, development, testing, operation).
• Knowledge of database technology is vital; business understanding enhances effectiveness.

Data Models

• Defines the logical structure of databases; the most common model is the relational model.
• Types of database models include:
  • Hierarchical Model: Tree-like structure with single parent-child relationships.
  • Network Model: More complex relationships than hierarchical.
  • Entity-Relationship Model: Represents data entities and their relationships.
  • Relational Model: Uses tables for data representation.
  • Object-oriented Database Model: Incorporates object-oriented programming concepts into database design.

Cardinalities

• Types of cardinality relationships:
  • One-to-One: Each entity in set one associates with one entity in set two.
  • One-to-Many: A single entity in set one relates to multiple entities in set two (e.g., a hospital to multiple compounders).
  • Many-to-One: Multiple entities in set one relate to a single entity in set two (e.g., patients to a doctor).
  • Many-to-Many: Entities in both sets can relate to multiple entities in the opposite set.

Keys in Databases

• Keys uniquely identify records or rows in a table and establish relationships between tables.

Transaction Control Language (TCL)

• Commands include:
  • GRANT: Assigns user access privileges to the database.
  • REVOKE: Withdraws previously granted privileges.
  • COMMIT: Saves changes made by Data Manipulation Language (DML) statements.
  • ROLLBACK: Restores the database to its state since the last COMMIT.
  • SAVEPOINT: Marks a point in a transaction for potential rollback.

SQL Basics

• The SELECT statement retrieves records from tables. Syntax includes optional clauses for conditions, grouping, and ordering.
• Example SQL queries:
  • Retrieve all columns: SELECT * FROM dept;
  • Select specific columns: SELECT LOC, DEPTNO FROM DEPT;
  • Conditional selections: SELECT name, city FROM suppliers WHERE supplier_id > 1000;

Test your knowledge on the Database Management System for Semester 1. This quiz covers key concepts and topics from UNIT 3 of the course. Prepare to demonstrate your understanding of database theory and its practical applications.