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 MoreDatabase Architecture, Data Models, and Relational Algebra
**i) View of Data:**A database has multiple views to cater to different users.It consists of 3 levels:*Internal Level (Physical View):*Defines how data is physically stored on hardware.*Conceptual Level (Logical View):*Represents the entire database structure without storage details.*External Level (User View):*Provides customized views of data for different users,hiding unnecessary details.**ii) Data Abstraction:**Data abstraction simplifies database complexity by hiding lower-
level details.It
Essential Database and Data Management Concepts
Database Fundamentals
Core Database Terminology
- Catalog: A set of schemas that constitute the description of a database.
- Schema: The structure that contains descriptions of objects created by a user (base tables, views, constraints).
- Data Definition Language (DDL): Commands that define a database, including creating, altering, and dropping tables and establishing constraints.
- Data Manipulation Language (DML): Commands that maintain and query a database.
- Data Control Language (DCL): Commands that control