DBMS Fundamentals and Relational Database Design
DDL vs DML: Definitions and Examples
Q1. Differentiate between DDL and DML Languages with example. [5 Marks]
Answer:
DDL (Data Definition Language) is used to define, create, and modify the structure of database objects such as tables, databases, and schemas. It mainly deals with the structure of the database.
- Commands:
CREATE,ALTER,DROP,TRUNCATE. - Example:
CREATE TABLE Student(Roll_No INT, Name VARCHAR(20), Age INT);
DML (Data Manipulation Language) is used to insert, modify, delete, and retrieve data
Read MoreDatabase Normalization and Management Principles
Database Normalization and 3rd Normal Form
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It divides large tables into smaller related tables.
Objectives of Normalization
- Remove duplicate data.
- Eliminate insertion anomalies.
- Eliminate deletion anomalies.
- Eliminate update anomalies.
- Improve data consistency.
3rd Normal Form (3NF)
A table is in 3NF if:
- It is already in 2NF.
- No transitive dependency exists.
- Non-key attributes depend only on the primary
Database Management Systems: Essential Concepts and Queries
Mapping Conceptual Models to Relational Models
Mapping a conceptual (ER) model to a relational model involves:
- Converting entities into tables
- Converting attributes into columns
- Defining primary keys for each table
- Representing relationships using foreign keys
DML in DBMS
DML (Data Manipulation Language) is used to retrieve and manipulate data in database tables. Examples include: SELECT, INSERT, UPDATE, and DELETE.
Reasons for Defining a View
- To provide security by restricting access to sensitive data.
- To
Purpose and content of
ER Diagrams (Chapter 2 – ER Diagram) Key Concepts
Entity:
A person, place, thing, or event you store data about (e.G., Student, Course).
Attribute:
A detail about an entity (e.G., StudentName, StudentID).
Identifier (Key):
Uniquely identifies each entity (StudentID).
Relationship:
How entities connect (e.G., Student registers for Course).
| Type | Meaning | Example |
|---|---|---|
| Simple | Single value | Name |
| Composite | Can be split | FullName → FirstName, LastName |
| Multivalued | Has several values | Skills = {Java, SQL} |
| Derived | Computed from |
Essential Concepts: DBMS Keys, Constraints, and Relational Model
Understanding Keys in DBMS
A Key in a Database Management System (DBMS) is an attribute or a set of attributes used to uniquely identify each record (tuple) in a table and to maintain relationships between tables.
Types of Database Keys
Super Key
A set of one or more attributes that can uniquely identify a record in a table.
Example: Given
Student(Roll_No, Name, Email), both{Roll_No}and{Roll_No, Name}are Super Keys.Candidate Key
A minimal Super Key, meaning no unnecessary attribute is included.
Example:
Essential Concepts in Database Management Systems (DBMS)
Database Administrator (DBA) Functions
A Database Administrator (DBA) is responsible for managing and maintaining the database system. Key functions include:
- Ensuring data security.
- Creating and managing user accounts.
- Managing storage allocation.
- Performing backups and recovery procedures.
- Tuning database performance.
- Maintaining database integrity.
Specialization and Aggregation in ER Modeling
Specialization is the process of dividing an entity into sub-entities based on specific characteristics (e.g.,
Read More