DBMS — Placement Preparation¶

Level 1 — Fundamentals¶

Topics Covered¶

DBMS vs RDBMS
Data Models
Keys
Schema vs Instance
Data Independence
ER Model Basics
Mapping ER Model to Relational Tables

1. DBMS vs RDBMS¶

1.1 What is a Database?¶

A database is an organized collection of data that can be stored, accessed, and managed efficiently.

Example: A college database may contain:

Students
Courses
Teachers
Marks
Attendance
Departments

1.2 Why do we need a DBMS?¶

If data is stored only in files such as .txt or .csv, several problems arise:

1. Data Redundancy¶

The same information may be stored multiple times.

Example:

101, Roshan, IT, DBMS
101, Roshan, IT, OS
101, Roshan, IT, CN

Roshan, IT is repeated unnecessarily.

2. Data Inconsistency¶

If the same data is stored in multiple places, updating one copy but not another can create contradictory information.

Example:

Student file → 101, Roshan, CSE
Marks file   → 101, Roshan, IT

3. Difficult Data Retrieval¶

Finding complex information from files requires custom programs.

A DBMS allows declarative querying using SQL:

SELECT *
FROM Student
WHERE cgpa > 8;

4. Concurrent Access¶

Multiple users may access or modify the same data simultaneously.

A DBMS provides mechanisms to manage concurrent access safely.

5. Security¶

Different users can have different permissions.

Example:

Student → view own marks
Teacher → update marks
Admin   → manage student records

6. Integrity¶

A DBMS can enforce rules that prevent invalid data.

Examples:

Student ID must be unique.
Age cannot be negative.
A foreign key must refer to a valid record.

7. Backup and Recovery¶

A DBMS provides mechanisms to recover data after crashes or failures.

1.3 What is a DBMS?¶

DBMS = Database Management System

A DBMS is software used to:

Create databases
Store data
Retrieve data
Update data
Delete data
Manage relationships
Enforce constraints
Control access
Handle concurrent operations
Manage transactions
Provide backup and recovery

Conceptually:

User / Application
        ↓
       DBMS
        ↓
    Database
        ↓
 Physical Storage

Examples:

MySQL
PostgreSQL
Oracle Database
Microsoft SQL Server
SQLite

Important distinction¶

A database is the data.

A DBMS is the software that manages that data.

1.4 What is an RDBMS?¶

RDBMS = Relational Database Management System

An RDBMS is a type of DBMS based on the relational data model.

Data is represented using relations, which in practical SQL usage are represented as tables.

Example:

STUDENT¶

Student_ID	Name	Branch
101	Roshan	IT
102	Rahul	CSE
103	Aman	ECE

DEPARTMENT¶

Dept_ID	Dept_Name
10	IT
20	CSE
30	ECE

Relationships between data are represented using relational concepts such as keys and foreign keys.

1.5 DBMS vs RDBMS¶

The most important relationship is:

DBMS
 ↓
General category of database management systems

RDBMS
 ↓
A DBMS based specifically on the relational model

Therefore:

Every RDBMS is a DBMS, but not every DBMS is necessarily an RDBMS.

DBMS	RDBMS
General database management system	Relational database management system
Can use different data models	Uses relational model
Data need not be represented relationally	Data is organized into relations/tables
SQL is not mandatory as a defining property	SQL is commonly used
Broader category	Specialized type of DBMS

Important caveat¶

Do not oversimplify this as:

"DBMS doesn't use tables, RDBMS uses tables."

A DBMS is a broader category. The fundamental distinction is the data model.

1.6 Examples¶

MySQL
  ↓
RDBMS
  ↓
DBMS

PostgreSQL
  ↓
RDBMS
  ↓
DBMS

Oracle Database
  ↓
RDBMS
  ↓
DBMS

MongoDB is a document-oriented database system, not an RDBMS.

MongoDB
  ↓
Document-oriented model
  ↓
Not relational

SQL is not an RDBMS¶

SQL       → Query language
MySQL     → RDBMS
PostgreSQL → RDBMS

2. Data Models¶

2.1 What is a Data Model?¶

A data model defines:

How data is structured
How data is related
What constraints apply
How data is represented conceptually

It acts as a blueprint for representing data.

2.2 Hierarchical Data Model¶

Data is organized like a tree.

University
    |
    +── Department
    |      |
    |      +── Student
    |      +── Student
    |
    +── Department
           |
           +── Student

Characteristics:

Parent-child structure
Tree-like organization
Traditionally, a child has one parent
Naturally suited to one-to-many relationships

2.3 Network Data Model¶

The network model allows more flexible relationships than a tree.

Data can form a graph-like structure.

Example:

       Course A
       /      \
Student 1     Student 2
       \      /
        Course B

A student can be associated with multiple courses, and a course can have multiple students.

It naturally supports many-to-many relationships.

2.4 Relational Data Model¶

The most important model for traditional DBMS placement preparation.

Data is represented using relations/tables.

Example:

STUDENT¶

student_id	name	dept_id
101	Roshan	10
102	Rahul	20
103	Aman	10

DEPARTMENT¶

dept_id	dept_name
10	IT
20	CSE

The relationship is represented through:

STUDENT.dept_id
        ↓
DEPARTMENT.dept_id

Important terminology¶

Relation → Table
Tuple    → Row
Attribute → Column

2.5 Object-Oriented Data Model¶

Data is represented as objects similar to object-oriented programming.

An object can contain:

Student
 ├── Attributes
 │    ├── ID
 │    ├── Name
 │    └── CGPA
 │
 └── Methods
      ├── calculateGrade()
      └── getDetails()

Useful when applications naturally work with complex objects.

2.6 Modern Data Models¶

Document Model¶

Data is stored as documents, commonly JSON-like.

{
  "id": 101,
  "name": "Roshan",
  "skills": ["C++", "Go", "SQL"]
}

MongoDB is a common example.

Key-Value Model¶

Data is represented as:

Key → Value

Example:

"user:101" → "Roshan"

Useful for fast lookups and caching.

Graph Model¶

Data is represented using nodes and edges.

Roshan ──FRIEND_OF──> Rahul
Roshan ──TAKES──────> DBMS

Useful for highly connected data.

Column-Family Model¶

Data is organized around column families and is commonly used in distributed NoSQL systems.

3. Keys in DBMS¶

3.1 Why do we need Keys?¶

A key helps identify rows uniquely and/or establish relationships between tables.

Example:

STUDENT¶

Student_ID	Name	Branch	CGPA
101	Roshan	IT	8.4
102	Rahul	CSE	8.1
103	Aman	IT	8.7

Name cannot necessarily identify a student because two students can have the same name.

Student_ID, however, can uniquely identify a student.

3.2 Super Key¶

A super key is any set of one or more attributes that can uniquely identify a tuple.

Suppose:

Student_ID → unique
Email      → unique

Then examples of super keys include:

{Student_ID}

{Email}

{Student_ID, Name}

{Student_ID, Branch}

{Email, Name}

{Student_ID, Email}

{Student_ID, Email, Name, Branch}

All of these can uniquely identify a student.

However, some contain unnecessary attributes.

3.3 Candidate Key¶

A candidate key is a minimal super key.

Minimal means:

No attribute can be removed while retaining the ability to uniquely identify the row.

Suppose:

Student_ID → unique
Email      → unique

Then:

{Student_ID}
{Email}

are candidate keys.

But:

{Student_ID, Name}

is not a candidate key because:

{Student_ID}

already uniquely identifies the row.

Therefore:

Candidate Key ⊆ Super Key

Every candidate key is a super key, but every super key is not necessarily a candidate key.

3.4 Primary Key¶

A primary key is a candidate key selected to uniquely identify tuples in a relation.

Suppose candidate keys are:

{Student_ID}
{Email}

If we choose:

Student_ID

as the primary key:

Student_ID → Primary Key
Email      → Alternate Key

Properties¶

A primary key:

Must uniquely identify rows
Cannot be NULL
There is one primary-key constraint per table
It may contain multiple columns

Example of a composite primary key:

PRIMARY KEY (Student_ID, Course_ID)

3.5 Alternate Key¶

An alternate key is a candidate key that was not selected as the primary key.

Example:

Candidate Keys:
    Student_ID
    Email

Selected:
    Student_ID → Primary Key

Remaining:
    Email → Alternate Key

There can be multiple alternate keys if there are multiple remaining candidate keys.

3.6 Composite Key¶

A composite key contains multiple attributes.

Example:

ENROLLMENT¶

Student_ID	Course_ID	Grade
101	DBMS	A
101	OS	B
102	DBMS	A

Neither:

Student_ID

nor:

Course_ID

is sufficient to uniquely identify a row.

Together:

(Student_ID, Course_ID)

can uniquely identify an enrollment.

Therefore it is a composite key.

Important distinction¶

"Composite" refers to the number of attributes.

"Candidate" refers to uniqueness + minimality.

So a key can be:

Single-attribute candidate key
Composite candidate key
Composite primary key

3.7 Foreign Key¶

A foreign key is an attribute or set of attributes in one table that references a candidate key of another table.

Usually it references the primary key.

Example:

DEPARTMENT¶

Dept_ID	Dept_Name
10	IT
20	CSE

STUDENT¶

Student_ID	Name	Dept_ID
101	Roshan	10
102	Rahul	20
103	Aman	10

Here:

STUDENT.Dept_ID
       ↓
DEPARTMENT.Dept_ID

STUDENT.Dept_ID is a foreign key.

3.8 Referential Integrity¶

A foreign key helps maintain referential integrity.

If valid department IDs are:

10
20
30

then:

Dept_ID = 20 → valid
Dept_ID = 30 → valid
Dept_ID = 99 → invalid

assuming the foreign-key constraint does not allow such a reference.

3.9 Foreign Key Does Not Have to Be Unique¶

Example:

Student_ID	Dept_ID
101	10
102	10
103	20
104	10

Multiple students can belong to the same department.

Therefore:

A foreign key does not have to be unique.

This is normal for one-to-many relationships.

3.10 Can a Foreign Key Be NULL?¶

Yes, unless the schema also specifies NOT NULL.

For example:

Student_ID | Dept_ID
101        | 10
102        | NULL

can be valid if the relationship is optional.

A foreign-key constraint by itself does not automatically imply NOT NULL.

3.11 Can a Foreign Key Reference Something Other Than a Primary Key?¶

Yes.

Conceptually, a foreign key references a candidate key of another relation, although in practical database designs it most commonly references the primary key.

Avoid the oversimplified statement:

"A foreign key always references the primary key."

3.12 Candidate Key and NULL¶

Conceptually, a candidate key must uniquely identify every tuple and therefore cannot depend on NULL values.

Placement rule:

Candidate Key = unique + minimal + NOT NULL

A primary key is a selected candidate key, so:

Primary Key = unique + NOT NULL

An alternate key is also a candidate key that was not selected as primary.

Important SQL nuance¶

A SQL UNIQUE constraint is not exactly the same as the theoretical concept of a candidate key because SQL implementations can have special handling of NULL values under UNIQUE.

Therefore:

Candidate Key (theory)
    → Unique
    → Minimal
    → NOT NULL

PRIMARY KEY
    → Unique
    → NOT NULL

UNIQUE constraint
    → SQL uniqueness constraint
    → NULL behavior depends on DBMS

3.13 Complete Key Hierarchy¶

SUPER KEY
    ↓
Can uniquely identify a row
    ↓
Remove unnecessary attributes
    ↓
CANDIDATE KEY
    ↓
Choose one
    ↓
PRIMARY KEY

Remaining candidate keys
    ↓
ALTERNATE KEYS

Foreign key is conceptually separate:

TABLE A
   |
   | Foreign Key
   ↓
TABLE B
   |
   └── References a candidate key

4. Schema vs Instance¶

4.1 Schema¶

A schema is the structure/design of a database.

It describes things such as:

Tables
Columns
Data types
Relationships
Keys
Constraints

Example:

STUDENT
----------------
student_id
name
branch
cgpa

or:

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    branch VARCHAR(20),
    cgpa DECIMAL(3,2)
);

4.2 Instance¶

An instance is the actual data stored in the database at a particular point in time.

Example:

student_id	name	branch	cgpa
101	Roshan	IT	8.4
102	Rahul	CSE	8.1

This is the current instance.

If another student is inserted, the schema does not change, but the instance does.

4.3 Easy Mental Model¶

Schema   = Structure / Blueprint
Instance = Actual data at a particular time

4.4 Which Operations Change Schema vs Instance?¶

INSERT¶

INSERT INTO Student ...

Changes:

Instance

UPDATE¶

UPDATE Student
SET cgpa = 8.8
WHERE student_id = 101;

Changes:

Instance

DELETE¶

DELETE FROM Student
WHERE student_id = 101;

Changes:

Instance

ALTER TABLE¶

ALTER TABLE Student
ADD email VARCHAR(100);

Changes:

Schema

4.5 Three Levels of Database Architecture¶

DBMS architecture commonly uses three levels:

External Level
      ↓
Conceptual Level
      ↓
Internal Level

External Level¶

What a particular user/application sees.

Different users may have different views.

Student → Student View
Teacher → Teacher View
Admin   → Admin View

Conceptual Level¶

The overall logical structure of the database.

It describes:

Entities/tables
Attributes
Relationships
Constraints

without worrying about physical storage.

Internal Level¶

How data is physically stored.

It deals with concepts such as:

Files
Pages
Blocks
Indexes
Physical storage structures

5. Data Independence¶

5.1 Definition¶

Data independence is the ability to change the schema at one level without requiring changes at the next higher level.

There are two types:

Physical Data Independence
Logical Data Independence

5.2 Physical Data Independence¶

It means changing the internal/physical level without changing the conceptual level.

Example:

Suppose the logical schema remains:

STUDENT
---------
ID
Name
CGPA

The DBA may:

Add an index
Change file organization
Change physical storage structures
Change how records are stored

without changing the logical schema.

Therefore:

Physical Data Independence = Internal → Conceptual independence

5.3 Logical Data Independence¶

It means changing the conceptual/logical level without requiring changes to the external views.

Example:

The database's logical structure is reorganized while existing user views continue to work.

Therefore:

Logical Data Independence = Conceptual → External independence

5.4 Physical vs Logical¶

	Physical Data Independence	Logical Data Independence
Change occurs at	Internal level	Conceptual level
Higher level protected	Conceptual	External
Concern	Physical storage	Logical structure
Example	Add/change indexes	Change logical schema

Important interview point¶

Logical data independence is generally harder to achieve than physical data independence.

Reason:

Physical implementation details are usually hidden by the DBMS.

Logical schema changes can directly affect:

Queries
Applications
Views
Relationships
Business logic

6. ER Model Basics¶

6.1 Why ER Modeling?¶

Before creating tables, we need to understand:

What objects exist
What properties they have
How they are related

The ER model provides a conceptual blueprint.

Real-world requirements
        ↓
     ER Model
        ↓
Relational Schema
        ↓
      Tables

6.2 Entity¶

An entity is a distinguishable real-world object about which we store information.

Examples:

Student
Teacher
Course
Department
Employee
Customer
Order

Entity Type vs Entity Instance¶

Entity Type:
Student

Entity Instance:
Student 101 — Roshan

Entity type = category.

Entity instance = specific object.

6.3 Entity Set¶

An entity set is a collection of similar entity instances.

Example:

Student Entity Set:

101 → Roshan
102 → Rahul
103 → Aman

6.4 Attributes¶

Attributes describe an entity.

Example:

Student
 ├── Student_ID
 ├── Name
 ├── Email
 ├── Branch
 └── CGPA

6.5 Types of Attributes¶

Simple Attribute¶

Cannot be meaningfully divided into smaller attributes.

Examples:

Age
CGPA
Gender
Student_ID

Composite Attribute¶

Can be divided into meaningful sub-attributes.

Example:

Address
 ├── House_No
 ├── Street
 ├── City
 ├── State
 └── PIN

Another example:

Name
 ├── First_Name
 ├── Middle_Name
 └── Last_Name

Important¶

A composite attribute is not the same as a composite key.

Composite Attribute
→ attribute divided into sub-attributes

Composite Key
→ key containing multiple attributes

Single-Valued Attribute¶

Has one value for a particular entity instance.

Examples:

Student_ID
Date_of_Birth

Multi-Valued Attribute¶

Can have multiple values for one entity.

Example:

Student
 └── Phone_Number
       ├── 9876...
       └── 9123...

Another example:

Skills = {C++, Go, Python}

Derived Attribute¶

Can be calculated from another attribute.

Example:

Date_of_Birth
      ↓
     Age

If DOB is stored, Age can be derived.

Another example:

Quantity × Price
       ↓
     Total

Key Attribute¶

An attribute that uniquely identifies an entity.

Example:

Student
 ├── Student_ID ← Key
 ├── Name
 └── CGPA

In traditional ER diagrams, a key attribute is underlined.

6.6 Relationship¶

A relationship represents an association between entities.

Example:

Student ─── enrolls in ─── Course

Here:

Student → Entity
Course → Entity
enrolls in → Relationship

Other examples:

Teacher ─── teaches ─── Course
Student ─── belongs to ─── Department

6.7 Cardinality¶

Cardinality describes how many entity instances can participate in a relationship.

Common types:

1:1
1:N
N:1
M:N

One-to-One — 1:1¶

Example:

Person 1 ───── 1 Passport

One person has one passport, and one passport belongs to one person, under the given business rule.

One-to-Many — 1:N¶

Example:

Department 1 ───── N Student

One department can have many students.

Each student belongs to one department.

Many-to-One — N:1¶

Same relationship viewed from the other side:

Student N ───── 1 Department

Many students belong to one department.

Many-to-Many — M:N¶

Example:

Student M ───── N Course

One student can enroll in many courses.

One course can have many students.

6.8 Cardinality vs Degree¶

Cardinality¶

Answers:

How many?

1:1
1:N
M:N

Degree¶

Answers:

How many entity types participate in the relationship?

Unary / Recursive¶

One entity type participates.

Employee ─── manages ─── Employee

Only Employee is the entity type.

Binary¶

Two entity types participate.

Student ─── enrolls ─── Course

Ternary¶

Three entity types participate.

Supplier
    \
     Supplies
      /    \
 Product   Project

6.9 Participation Constraint¶

Participation answers:

Does an entity have to participate in the relationship?

Two types:

Total Participation¶

Every entity must participate.

Example:

Every employee must belong to a department.

Employee → Total Participation

Partial Participation¶

Participation is optional.

Example:

An employee may manage nobody.

Employee → Partial Participation

6.10 Cardinality vs Participation¶

These are independent concepts.

Cardinality → How many?

Participation → Mandatory or optional?

For example:

Department 1 ───── N Employee

can simultaneously have:

Cardinality:
1:N

Employee:
Total participation

Department:
Partial participation

depending on the business rules.

6.11 Strong Entity¶

A strong entity has its own key and can be identified independently.

Example:

Student
 ├── Student_ID ← Key
 ├── Name
 └── CGPA

It does not depend on another entity for identification.

6.12 Weak Entity¶

A weak entity cannot be uniquely identified using its own attributes alone.

It depends on an owner/strong entity.

Example:

Employee
   |
   | has
   ↓
Dependent

Suppose:

Dependent_Name
Age
Relationship

Dependent_Name may not be globally unique.

Example:

Employee 101 → Rahul
Employee 102 → Rahul

Therefore:

Employee_ID + Dependent_Name

can identify the dependent.

Here:

Employee_ID
→ Owner's key

Dependent_Name
→ Partial key / discriminator

7. Mapping ER Model to Relational Tables¶

7.1 Basic Mapping Rules¶

Entity       → Table
Attribute    → Column
Key          → Primary Key
Relationship → Foreign Key / Separate Table

7.2 Strong Entity Mapping¶

ER:

Student
 ├── Student_ID
 ├── Name
 └── CGPA

Relational table:

STUDENT(
    Student_ID PRIMARY KEY,
    Name,
    CGPA
)

Rule:

Each strong entity becomes a table containing its simple attributes, with its key as the primary key.

7.3 Composite Attribute Mapping¶

Suppose:

Student
 └── Address
      ├── House_No
      ├── Street
      ├── City
      └── PIN

Usually, don't create an Address table merely because Address is composite.

Instead, store the component attributes:

STUDENT(
    Student_ID PRIMARY KEY,
    Name,
    House_No,
    Street,
    City,
    PIN
)

7.4 Derived Attribute Mapping¶

Suppose:

Student
 ├── DOB
 └── Age

where Age is derived from DOB.

Usually store:

DOB

and calculate:

Age = Current Date - DOB

rather than storing both independently.

Reason:

Storing derived values can introduce inconsistency.

Example:

DOB = 2005
Age = 20

After the birthday, Age changes but may not be updated.

Therefore:

Derived attributes are generally not stored as independent columns.

7.5 Multivalued Attribute Mapping¶

Suppose:

Student
 └── Phone_Number

and a student can have multiple phone numbers.

Do not design:

Student_ID | Phone1 | Phone2 | Phone3

Instead create a separate relation:

STUDENT(
    Student_ID PRIMARY KEY,
    Name
)

STUDENT_PHONE(
    Student_ID,
    Phone_Number,
    PRIMARY KEY(Student_ID, Phone_Number)
)

Example:

Student_ID	Phone_Number
101	9876
101	9123
102	8888

Rule:

Multivalued attribute → separate table containing the owner's primary key + the multivalued attribute.

7.6 Mapping a 1:N Relationship¶

Suppose:

DEPARTMENT 1 ───── N STUDENT

Tables:

DEPARTMENT(
    Dept_ID PRIMARY KEY,
    Dept_Name
)

STUDENT(
    Student_ID PRIMARY KEY,
    Name,
    Dept_ID FOREIGN KEY
)

The foreign key is:

STUDENT.Dept_ID
        ↓
DEPARTMENT.Dept_ID

Critical rule¶

For a 1:N relationship, place the primary key of the 1-side as a foreign key in the N-side table.

Why?

Many students can reference the same department:

Student 101 → Dept 10
Student 102 → Dept 10
Student 103 → Dept 10

A foreign key does not have to be unique.

7.7 Mapping an M:N Relationship¶

Suppose:

STUDENT M ───── N COURSE

A direct foreign key in either table is insufficient because both sides can have multiple related entities.

Create a separate relationship/junction table:

STUDENT(
    Student_ID PRIMARY KEY,
    Name
)

COURSE(
    Course_ID PRIMARY KEY,
    Course_Name
)

ENROLLMENT(
    Student_ID FOREIGN KEY,
    Course_ID FOREIGN KEY,
    PRIMARY KEY(Student_ID, Course_ID)
)

Example:

Student_ID	Course_ID
101	DBMS
101	OS
102	DBMS
103	CN

Other names¶

The relationship table may be called:

Junction table
Associative table
Bridge table
Relationship table

7.8 Attributes of an M:N Relationship¶

Suppose:

Student M ── ENROLLS ── N Course

and ENROLLS has:

Grade
Enrollment_Date

These attributes belong to the relationship, not to Student or Course.

Therefore:

ENROLLMENT(
    Student_ID,
    Course_ID,
    Grade,
    Enrollment_Date,
    PRIMARY KEY(Student_ID, Course_ID)
)

Reason:

Grade is specific to a particular student's enrollment in a particular course.

A student can have different grades in different courses.

7.9 Mapping a 1:1 Relationship¶

Suppose:

PERSON 1 ───── 1 PASSPORT

We can put a foreign key in one of the tables.

Example:

PERSON(
    Person_ID PRIMARY KEY,
    Name
)

PASSPORT(
    Passport_ID PRIMARY KEY,
    Passport_Number,
    Person_ID FOREIGN KEY UNIQUE
)

The UNIQUE constraint is important.

Without it:

Passport P1 → Person 101
Passport P2 → Person 101
Passport P3 → Person 101

would be allowed.

That would represent multiple passports for the same person, contradicting the intended 1:1 relationship.

Therefore:

For a 1:1 relationship, a foreign key may need a UNIQUE constraint to enforce one-to-one cardinality.

Which side should contain the FK?¶

It depends on:

Participation
Optionality
Existence dependency
Practical design considerations

If every passport must belong to a person, but a person may not have a passport, putting Person_ID in PASSPORT is natural.

7.10 Mapping a Weak Entity¶

Suppose:

EMPLOYEE
   |
   | has
   ↓
DEPENDENT

Employee:

EMPLOYEE(
    Employee_ID PRIMARY KEY,
    Name
)

Dependent:

DEPENDENT(
    Employee_ID FOREIGN KEY,
    Dependent_Name,
    Age,
    Relationship,
    PRIMARY KEY(Employee_ID, Dependent_Name)
)

The weak entity table contains:

Owner's primary key
Weak entity's partial key
Weak entity's attributes

Together:

Employee_ID + Dependent_Name
identify the dependent.

(Self-correction based on format consistency with the rest of the text)

Together:

Employee_ID + Dependent_Name

identify the dependent.

7.11 Mapping a Recursive Relationship¶

Suppose:

EMPLOYEE
   |
   | manages
   ↓
EMPLOYEE

This is a unary/recursive relationship because the same entity type participates twice.

Use a self-referencing foreign key:

EMPLOYEE(
    Employee_ID PRIMARY KEY,
    Name,
    Manager_ID FOREIGN KEY REFERENCES EMPLOYEE(Employee_ID)
)

Example:

Employee_ID	Name	Manager_ID
101	Raj	NULL
102	Roshan	101
103	Aman	101

Meaning:

Raj
├── Roshan
└── Aman

Manager_ID references another row of the same EMPLOYEE table.

7.12 Mapping a Ternary Relationship¶

Suppose:

SUPPLIER
    \
     SUPPLIES
      /    \
 PRODUCT   PROJECT

Three entity types participate.

Create a separate relation:

SUPPLIES(
    Supplier_ID FOREIGN KEY,
    Product_ID FOREIGN KEY,
    Project_ID FOREIGN KEY,
    PRIMARY KEY(
        Supplier_ID,
        Product_ID,
        Project_ID
    )
)

The exact key can depend on the relationship constraints, but the participating entity keys are represented in the relationship table.

8. Complete Mapping Cheat Sheet¶

ER Concept	Relational Mapping
Strong entity	Separate table
Simple attribute	Column
Composite attribute	Store component attributes as columns
Key attribute	Primary key
Derived attribute	Usually not stored
Multivalued attribute	Separate table
1:N relationship	FK on N-side
M:N relationship	Separate junction table
M:N relationship attributes	Put them in junction table
1:1 relationship	FK in one table, often with UNIQUE
Weak entity	Separate table + owner PK + partial key
Recursive relationship	Self-referencing FK
Ternary relationship	Separate relationship table

9. Placement Traps — Level 1¶

Trap 1: DBMS vs RDBMS¶

Incorrect:

DBMS doesn't use tables, RDBMS does.

Correct:

RDBMS is a type of DBMS based on the relational data model.

Trap 2: SQL vs RDBMS¶

Incorrect:

SQL is an RDBMS.

Correct:

SQL → Query language
MySQL → RDBMS

Trap 3: Super Key vs Candidate Key¶

Incorrect:

Every unique combination is a candidate key.

Correct:

Candidate key = minimal super key.

If:

Student_ID

is already unique, then:

(Student_ID, Name)

is a super key but not a candidate key.

Trap 4: Candidate Key vs Primary Key¶

Incorrect:

Every candidate key is a primary key.

Correct:

Multiple candidate keys can exist, but one is selected as the primary key.

Trap 5: Foreign Key Must Be Unique¶

Incorrect.

A foreign key can contain duplicates.

Dept_ID
10
10
10
20

is completely valid if multiple students belong to the same department.

Trap 6: Foreign Key Cannot Be NULL¶

Not necessarily.

A foreign key can be NULL unless NOT NULL or another constraint prevents it.

Trap 7: Composite Attribute vs Composite Key¶

They are completely different.

Address
 ├── City
 └── State

→ Composite attribute

(Student_ID, Course_ID)

→ Composite key

Trap 8: Cardinality vs Participation¶

Cardinality → How many?
Participation → Mandatory or optional?

They are independent.

Trap 9: Weak Entity¶

"Weak" does not mean:

Fewer attributes.

It means:

The entity cannot be uniquely identified independently and depends on another entity for identification.

Trap 10: 1:N Mapping¶

Remember:

1-side PK
    ↓
N-side FK

Example:

DEPARTMENT 1 ───── N STUDENT

DEPARTMENT.Dept_ID → PK
STUDENT.Dept_ID    → FK

Trap 11: M:N Mapping¶

Do not simply put multiple foreign-key values inside one column.

Instead:

STUDENT
COURSE
   ↓
ENROLLMENT

The junction table contains the keys of both entities.

Trap 12: 1:1 Mapping¶

A foreign key alone may not enforce 1:1.

Use:

FOREIGN KEY + UNIQUE

when appropriate.

10. Level 1 — Final Mental Model¶

The entire level can be remembered as one progression:

DATABASE
   ↓
DBMS
   ↓
Manages data
   ↓
RDBMS
   ↓
Uses relational model
   ↓
Tables / Relations
   ↓
Rows / Tuples
   ↓
Columns / Attributes
   ↓
Keys identify and connect data
   ↓
ER Model describes entities and relationships
   ↓
ER Model is mapped to relational tables

And the core distinctions:

Schema
→ Structure of the database

Instance
→ Actual data at a particular time

Physical Data Independence
→ Change physical storage without changing logical structure

Logical Data Independence
→ Change logical structure while protecting external views

Super Key
→ Any unique identifying attribute set

Candidate Key
→ Minimal super key

Primary Key
→ Selected candidate key

Alternate Key
→ Candidate key not selected as primary

Foreign Key
→ References a candidate key in another table

Cardinality
→ How many?

Participation
→ Mandatory or optional?

1:N
→ FK on N-side

M:N
→ Separate junction table

1:1
→ FK on one side, often UNIQUE

Multivalued attribute
→ Separate table

Weak entity
→ Owner PK + partial key

Level 1 — Placement Takeaways¶

If you can confidently explain the following without memorizing definitions, your Level 1 foundation is strong:

Why a DBMS is preferable to simple file storage.
Why every RDBMS is a DBMS but the reverse is not necessarily true.
What a data model represents.
Difference between super key, candidate key, primary key, alternate key, composite key, and foreign key.
Why a foreign key can contain duplicates.
Why a candidate key must be minimal.
Difference between schema and instance.
Difference between physical and logical data independence.
Difference between entity, attribute, and relationship.
Difference between cardinality and participation.
Difference between simple, composite, single-valued, multivalued, derived, and key attributes.
Difference between strong and weak entities.
How 1:1, 1:N, and M:N relationships are mapped to tables.
Why M:N relationships require a junction table.
Where relationship attributes such as Grade and Enrollment_Date belong.
Why a 1:1 foreign key may need UNIQUE.
How a weak entity is represented.
How recursive relationships are represented using self-referencing foreign keys.

Level 1 Complete¶

LEVEL 1 — FUNDAMENTALS
─────────────────────────────
1. DBMS vs RDBMS             ✓
2. Data Models               ✓
3. Keys                      ✓
4. Schema vs Instance        ✓
5. Data Independence         ✓
6. ER Model Basics           ✓
7. ER → Relational Mapping   ✓
─────────────────────────────

Next level: Normalization

Functional Dependencies
        ↓
Candidate Keys / Attribute Closure
        ↓
1NF
        ↓
2NF
        ↓
3NF
        ↓
BCNF
        ↓
MVD
        ↓
4NF
        ↓
Denormalization
        ↓
Anomalies