Database 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 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