Loading...
Loading...
Phase 1: Technical Theory | Date: 27 July 2026 | Subject: DBMS Theory | Expected Questions: 15 — Highest-Weight Subject
DBMS stands for Database Management System.
A DBMS is software used to create, store, organize, retrieve, update and protect data in a database.
Today you will study:
| Hour | Topic |
|---|---|
| 1 | DBMS overview, file system vs DBMS, advantages, database users and data models |
| 2 | Three-schema architecture and data independence |
| 3 | ER model: entities, attributes and relationships |
| 4 | Relational model and all important key types |
| 5 | Functional dependencies and normalization: 1NF, 2NF, 3NF and BCNF |
After completing this material, you will be able to:
Data means raw facts that have not yet been organized or interpreted.
Examples:
101
Asha
85
Jaipur
These values alone may not give complete meaning.
Information is processed or organized data that has meaning.
Example:
Student Asha, Roll Number 101, scored 85 marks and lives in Jaipur.
| Data | Information |
|---|---|
| Raw facts | Processed and meaningful facts |
| May not provide context | Provides context |
| Input to processing | Output of processing |
Example: 85 | Example: Asha scored 85 marks |
Raw Data
│
v
Processing
│
v
Information
A database is an organized collection of related data.
Examples:
| RollNo | Name | Course | Marks |
|---|---|---|---|
| 101 | Asha | BCA | 85 |
| 102 | Ravi | BSc | 76 |
| 103 | Mohan | BCA | 81 |
The data is related because every row describes a student.
A Database Management System is software that allows users and applications to:
Users and Applications
│
v
┌───────────┐
│ DBMS │
└─────┬─────┘
│
v
┌───────────┐
│ Database │
└───────────┘
Users do not normally manage physical data files directly. They communicate with the DBMS.
A complete database system contains more than data.
| Component | Meaning |
|---|---|
| Hardware | Computer, storage, network and server |
| Software | DBMS, operating system and applications |
| Data | Stored records and metadata |
| Procedures | Rules for operation, backup and security |
| Access language | Usually SQL |
| Users | DBA, designers, programmers and end users |
Metadata means data about data.
Examples:
If a table has a column named RollNo of type integer, that definition is metadata.
Before DBMS software became common, many applications stored data in separate files.
Example:
Admissions Program ───> admissions.txt
Fees Program ───> fees.txt
Examination Program───> marks.txt
Each department may keep its own data file.
This approach is called a file processing system.
Redundancy means the same data is stored more than once.
Example:
A student’s name and address may appear in:
This wastes storage and increases maintenance work.
Inconsistency means different copies of the same data contain different values.
Example:
Admission file address: Jaipur
Library file address: Ajmer
Which address is correct?
Redundancy can lead to inconsistency.
Data is separated across different files and formats.
It becomes difficult to combine information.
Example:
Finding students who:
may require reading three different files.
A new information request may require a new program.
Example:
If the principal asks for a report that was not planned earlier, a programmer may need to write new file-processing code.
Integrity means correctness and validity of data.
Example rule:
Marks must be between 0 and 100.
In a file system, the same rule may need to be written separately in many programs.
It is difficult to give different users different levels of access.
Example:
A DBMS provides controlled permissions.
Concurrent access means several users access or update data at the same time.
Without proper control, one user’s update may overwrite another user’s update.
A file system may not provide automatic support for:
File structure may be hard-coded into programs.
If the file format changes, many programs may need to change.
| Basis | File System | DBMS |
|---|---|---|
| Data organization | Separate files | Integrated database |
| Redundancy | Usually high | Controlled or reduced |
| Consistency | Difficult to maintain | Better consistency control |
| Data sharing | Limited | Easier |
| Security | Basic file permissions | Detailed authorization |
| Integrity constraints | Written separately in programs | Defined centrally |
| Concurrent access | Difficult to control | Concurrency control available |
| Backup and recovery | Usually manual or limited | DBMS recovery facilities |
| Data independence | Low | Higher |
| Querying | Custom programs may be required | Query language such as SQL |
| Relationships | Managed manually | Explicitly supported |
| Cost | Lower for very small needs | Higher setup and administration cost |
| Complexity | Simple | More complex |
A well-designed database avoids unnecessary duplication.
Updating one controlled record reduces conflicting copies.
Many users and applications can use the same database.
Permissions can be assigned to users and roles.
Rules can be defined centrally.
Examples:
The DBMS coordinates simultaneous users.
A DBMS can help restore data after:
Storage details can change without forcing all applications to change.
Common data definitions, naming rules and procedures can be applied.
Different users can see different parts of the same database.
A DBMS also has costs and limitations.
| Limitation | Meaning |
|---|---|
| Cost | Software, hardware, training and maintenance may cost money |
| Complexity | Designing and managing a DBMS requires knowledge |
| Large resource use | A DBMS may require more memory and storage |
| Conversion cost | Moving old files into a database can be difficult |
| Failure impact | Central database failure can affect many users |
| Security target | A valuable centralized system may attract attacks |
| Administration | Skilled database staff may be required |
A DBMS may be unnecessary for a very small, simple, single-user task.
Different people interact with a database in different ways.
DBA stands for Database Administrator.
The DBA manages the database environment.
SABER
The database designer decides:
The designer creates conceptual models such as ER diagrams.
A system analyst studies user requirements and converts business needs into system requirements.
Example:
A school needs:
The analyst determines how the full system should work.
An application programmer writes programs that communicate with the DBMS.
Examples:
The programmer may use:
End users use the database through applications or query tools.
| User Type | Meaning | Example |
|---|---|---|
| Naive or parametric user | Uses predefined forms repeatedly | Bank clerk |
| Casual user | Accesses database occasionally | Manager |
| Sophisticated user | Writes complex queries or analysis | Data analyst |
| Standalone user | Uses a personal database package | Small-office user |
A data model is a collection of concepts used to describe:
It gives a way to represent the database.
The hierarchical model organizes data in a tree structure.
Each child normally has one parent.
School
/ \
Department Library
/ \
Student Teacher
Simple and fast for strict tree-structured data.
Many-to-many relationships are difficult.
The network model organizes data as a graph.
A record may have multiple parent records.
Student ───── Course
│ │
└── Club ───┘
The relational model stores data in tables called relations.
STUDENT
| RollNo | Name | DeptID |
|---|---|---|
| 101 | Asha | 10 |
| 102 | Ravi | 20 |
DEPARTMENT
| DeptID | DeptName |
|---|---|
| 10 | Computer |
| 20 | Science |
DeptID connects the two tables.
The ER model represents data using:
It is mainly used for conceptual database design.
Example:
STUDENT ─── ENROLLS ─── COURSE
The ER model is explained fully in Section 3.
The object-oriented model stores information as objects.
Objects can include:
It is useful for complex data such as:
The object-relational model combines:
It may support:
| Model | Structure | Main Relationship Strength |
|---|---|---|
| Hierarchical | Tree | One-to-many |
| Network | Graph | Many-to-many |
| Relational | Tables | Keys connect tables |
| ER | Entities and relationships | Conceptual design |
| Object-oriented | Objects | Complex object relationships |
| Object-relational | Tables plus object features | Complex relational data |
A schema is the structure or design of a database.
It describes:
A schema is similar to a blueprint.
| Schema | Instance |
|---|---|
| Database design | Actual data at a particular time |
| Changes rarely | Changes frequently |
| Also called intension | Also called extension or state |
| Example: table definition | Example: rows currently stored |
STUDENT(RollNo, Name, Marks)
| RollNo | Name | Marks |
|---|---|---|
| 101 | Asha | 85 |
| 102 | Ravi | 76 |
The three-schema architecture separates a database into three levels:
Users and Applications
│ │ │
v v v
View 1 View 2 View 3
\ | /
\ | /
v v v
┌───────────────────────┐
│ External Level │
│ User-specific views │
└───────────┬───────────┘
│
External–Conceptual
Mapping
│
v
┌───────────────────────┐
│ Conceptual Level │
│ Complete logical │
│ database structure │
└───────────┬───────────┘
│
Conceptual–Internal
Mapping
│
v
┌───────────────────────┐
│ Internal Level │
│ Physical storage │
└───────────┬───────────┘
│
v
Storage Devices
The external level is the highest level.
It contains user-specific views.
A view shows only the data required by a particular user or application.
A college database contains:
Different users see different data.
| RollNo | Name | Marks |
|---|
| RollNo | Name | FeeStatus |
|---|
| Name | Marks | Attendance |
|---|
A database can have many external schemas.
The conceptual level describes the complete logical structure of the entire database.
It includes:
It does not focus on exact physical storage details.
STUDENT(RollNo, Name, DeptID)
DEPARTMENT(DeptID, DeptName)
STUDENT.DeptID references DEPARTMENT.DeptID
A database normally has one main conceptual schema.
The internal level is the lowest level.
It describes how data is physically stored.
It includes:
A database normally has one internal schema.
| Feature | External | Conceptual | Internal |
|---|---|---|---|
| Position | Highest | Middle | Lowest |
| Focus | User views | Complete logical design | Physical storage |
| Number | Many | Usually one | Usually one |
| Users | End users and applications | Designers and DBA | DBA and system developers |
| Example | Teacher view | All tables and relationships | Files and indexes |
ECI
A mapping connects schemas at different levels.
Connects each user view to the conceptual schema.
Connects the conceptual structure to physical storage.
Mappings help the DBMS translate requests between levels.
Data independence is the ability to change the schema at one level without requiring changes at the next higher level.
Main types:
Physical data independence means the internal schema can change without changing the conceptual schema or application views.
Before:
STUDENT stored as a heap file
After:
STUDENT stored with a B-tree index
The logical table remains:
STUDENT(RollNo, Name, Marks)
Applications do not need to change.
Internal changes
│
X
Conceptual schema remains unchanged
Logical data independence means the conceptual schema can change without requiring changes to external views or application programs, when mappings can preserve those views.
Original conceptual table:
STUDENT(RollNo, Name, DeptName)
New conceptual structure:
STUDENT(RollNo, Name, DeptID)
DEPARTMENT(DeptID, DeptName)
A user view can still show:
RollNo, Name, DeptName
The application view does not need to change if the DBMS mapping preserves it.
| Basis | Logical Data Independence | Physical Data Independence |
|---|---|---|
| Change Level | Conceptual level | Internal level |
| Higher Level Protected | External level | Conceptual and external levels |
| Example | Add or split a table | Add an index |
| Difficulty | Harder to achieve | Easier to achieve |
| Focus | Logical structure | Storage method |
Physical data independence is generally easier to achieve than logical data independence.
ER stands for Entity–Relationship.
The ER model is a high-level conceptual model used to design a database.
It represents:
An ER diagram provides a visual database blueprint.
An entity is a distinguishable real-world object or concept about which data is stored.
Examples:
One particular entity is an entity instance.
Examples:
Student Asha
Book with BookID B101
Employee with EmpID E10
An entity type defines a collection of entities with similar attributes.
Example:
STUDENT(RollNo, Name, Marks)
An entity set is the collection of entity instances of an entity type.
Example:
{Asha, Ravi, Mohan}
A strong entity has its own primary key.
Example:
STUDENT
Primary key: RollNo
ER notation commonly uses a single rectangle:
┌─────────────┐
│ STUDENT │
└─────────────┘
A weak entity cannot be uniquely identified using only its own attributes.
It depends on a strong entity.
Example:
A dependent may be identified using:
EmployeeID + DependentName
A weak entity is commonly shown using a double rectangle.
╔═════════════╗
║ DEPENDENT ║
╚═════════════╝
The relationship connecting it to its owner is an identifying relationship.
An attribute is a property that describes an entity.
For a student:
An attribute is traditionally shown as an oval.
┌───────────┐
│ STUDENT │
└─────┬─────┘
│
(Name)
A simple attribute cannot be meaningfully divided into smaller parts.
Examples:
Diagram:
(RollNo)
A composite attribute can be divided into smaller meaningful parts.
Example:
Name
├── FirstName
├── MiddleName
└── LastName
Another example:
Address
├── HouseNo
├── Street
├── City
├── State
└── PIN
(Name)
/ \
(FirstName) (LastName)
A single-valued attribute has one value for each entity.
Examples:
One student normally has one date of birth.
A multivalued attribute can have more than one value for one entity.
Examples:
Traditional ER notation uses a double oval.
((PhoneNumber))
A multivalued attribute is usually placed in a separate table.
Example:
STUDENT_PHONE(RollNo, PhoneNumber)
A stored attribute is physically stored in the database.
Example:
DateOfBirth
A derived attribute is calculated from another attribute.
Example:
Age derived from DateOfBirth
Traditional ER notation uses a dashed oval.
(- - Age - -)
Other examples:
A key attribute uniquely identifies an entity.
Example:
RollNo uniquely identifies STUDENT
Traditional ER notation underlines the key attribute.
(_RollNo_)
| Attribute Type | Meaning | Example |
|---|---|---|
| Simple | Cannot be divided | Salary |
| Composite | Has smaller parts | Address |
| Single-valued | One value per entity | DateOfBirth |
| Multivalued | Multiple values per entity | PhoneNumber |
| Stored | Physically stored | DateOfBirth |
| Derived | Calculated | Age |
| Key | Uniquely identifies entity | RollNo |
A relationship represents an association between entities.
Example:
Student enrolls in Course
Entities:
Relationship:
┌───────────┐ ◇──────────◇ ┌──────────┐
│ STUDENT │───────│ ENROLLS │───────│ COURSE │
└───────────┘ ◇──────────◇ └──────────┘
A relationship is traditionally drawn using a diamond.
The degree is the number of entity types participating in a relationship.
One entity type participates.
Example:
EMPLOYEE supervises EMPLOYEE
Two entity types participate.
Example:
STUDENT enrolls in COURSE
Three entity types participate.
Example:
SUPPLIER supplies PART to PROJECT
| Degree | Entities Involved |
|---|---|
| Unary | 1 |
| Binary | 2 |
| Ternary | 3 |
Cardinality describes the maximum number of entities that can participate in a relationship.
Main types:
One entity in A is related to at most one entity in B, and vice versa.
Notation:
A 1 ───────── 1 B
Example:
PERSON 1 ─── 1 PASSPORT
One person has one passport, and one passport belongs to one person under the assumed rules.
Other example:
COUNTRY 1 ─── 1 CAPITAL
One entity in A can be related to many entities in B, but each B entity is related to one A entity.
Notation:
A 1 ───────── N B
Example:
DEPARTMENT 1 ─── N EMPLOYEE
One department has many employees.
Each employee belongs to one department under the assumed rules.
Place the primary key of the “one” side as a foreign key in the “many” side.
DEPARTMENT(DeptID, DeptName)
EMPLOYEE(EmpID, EmpName, DeptID)
DeptID in EMPLOYEE is the foreign key.
Many entities in A can relate to many entities in B.
Notation:
A M ───────── N B
Example:
STUDENT M ─── N COURSE
One student can take many courses.
One course can have many students.
Create an intersection or associative table.
STUDENT(RollNo, Name)
COURSE(CourseID, CourseName)
ENROLLMENT(RollNo, CourseID)
The key of ENROLLMENT may be:
(RollNo, CourseID)
Participation describes whether every entity must take part in a relationship.
Every entity must participate.
Example:
Every dependent must belong to an employee.
Traditional ER notation uses a double line.
Some entities may not participate.
Example:
Some employees may not manage a department.
Traditional ER notation uses a single line.
A relationship may have its own attributes.
Example:
STUDENT ─── ENROLLS ─── COURSE
The ENROLLS relationship may have:
In the relational model:
ENROLLMENT(RollNo, CourseID, EnrollmentDate, Grade)
Requirement:
(DeptName)
│
┌────────────┐
│ DEPARTMENT │
└─────┬──────┘
│ 1
│
HAS
│ N
┌─────┴──────┐
│ STUDENT │
└──┬──┬──┬───┘
│ │ │
│ │ └──── ((PhoneNumber))
│ └─────── (DateOfBirth)
└────────── (- - Age - -)
STUDENT M ─── ENROLLS ─── N COURSE
│
(Grade)
DEPARTMENT(
DeptID,
DeptName
)
STUDENT(
RollNo,
Name,
DateOfBirth,
DeptID
)
STUDENT_PHONE(
RollNo,
PhoneNumber
)
COURSE(
CourseID,
CourseName
)
ENROLLMENT(
RollNo,
CourseID,
Grade
)
Age need not be stored if it is calculated from DateOfBirth.
| Symbol | Meaning |
|---|---|
| Rectangle | Strong entity |
| Double rectangle | Weak entity |
| Oval | Attribute |
| Double oval | Multivalued attribute |
| Dashed oval | Derived attribute |
| Underlined attribute | Key attribute |
| Diamond | Relationship |
| Double diamond | Identifying relationship |
| Double line | Total participation |
| Single line | Partial participation |
In the relational model, a table is called a relation.
Example relation:
STUDENT
| RollNo | Name | DeptID |
|---|---|---|
| 101 | Asha | 10 |
| 102 | Ravi | 20 |
A row is called a tuple.
Example tuple:
(101, Asha, 10)
Each tuple represents one record.
A column is called an attribute.
Attributes in STUDENT:
A domain is the set of permitted values for an attribute.
Examples:
Marks domain: integers from 0 to 100
Gender domain: permitted category values
DeptID domain: valid department identifiers
A domain describes both type and permitted values.
Number of attributes or columns.
For:
STUDENT(RollNo, Name, DeptID)
Degree:
3
Number of tuples or rows currently in the relation.
If STUDENT contains 100 rows:
Cardinality = 100
ER cardinality describes relationship mapping such as 1:N.
Relational cardinality often means the number of rows.
The meaning depends on context.
STUDENT(RollNo, Name, DeptID)
This describes the relation’s structure.
The actual set of rows stored at a particular time.
In the pure relational model:
SQL tables may require explicit constraints to enforce some of these properties.
NULL represents:
It is not the same as:
Example:
A student’s middle name may be unknown, so it may be NULL.
Integrity constraints maintain valid data.
Main types:
An attribute value must belong to its domain.
Example:
Marks must be an integer from 0 to 100.
Primary-key values cannot be NULL.
Reason:
Every row must be identifiable.
A foreign-key value must:
Example:
If EMPLOYEE.DeptID is 10, a department with DeptID 10 must exist.
A key is an attribute or set of attributes used to identify tuples or establish relationships.
Important key types:
A super key is any attribute set that uniquely identifies a tuple.
Consider:
STUDENT
| RollNo | Name | |
|---|---|---|
| 101 | a@x.com | Asha |
| 102 | r@x.com | Ravi |
Assume RollNo and Email are individually unique.
Super keys include:
{RollNo}
{Email}
{RollNo, Name}
{Email, Name}
{RollNo, Email}
{RollNo, Email, Name}
A super key may contain unnecessary attributes.
A candidate key is a minimal super key.
Minimal means no attribute can be removed while uniqueness remains.
In the previous example:
{RollNo}
{Email}
are candidate keys.
But:
{RollNo, Name}
is not a candidate key because Name is unnecessary. RollNo alone is unique.
Candidate key = Minimal super key
A primary key is the candidate key chosen to identify tuples.
Example:
Candidate keys:
RollNo
Email
Chosen primary key:
RollNo
Candidate keys not selected as the primary key are alternate keys.
Example:
Candidate keys:
RollNo
Email
Primary key:
RollNo
Alternate key:
Email
Alternate keys = Candidate keys − Chosen primary key
A composite key contains more than one attribute.
Example:
ENROLLMENT
| RollNo | CourseID | Grade |
|---|---|---|
| 101 | C1 | A |
| 101 | C2 | B |
| 102 | C1 | A |
Neither RollNo nor CourseID alone uniquely identifies a row.
Together:
(RollNo, CourseID)
uniquely identify an enrollment.
Therefore, the composite key is:
{RollNo, CourseID}
A foreign key is an attribute or set of attributes in one relation that references a candidate key, usually the primary key, in another relation or sometimes the same relation.
Example:
DEPARTMENT
| DeptID | DeptName |
|---|---|
| 10 | Computer |
| 20 | Science |
Primary key:
DeptID
STUDENT
| RollNo | Name | DeptID |
|---|---|---|
| 101 | Asha | 10 |
| 102 | Ravi | 20 |
Foreign key:
STUDENT.DeptID references DEPARTMENT.DeptID
A foreign key:
| Key | Meaning |
|---|---|
| Super key | Any unique attribute set |
| Candidate key | Minimal super key |
| Primary key | Chosen candidate key |
| Alternate key | Unchosen candidate key |
| Composite key | Key with multiple attributes |
| Foreign key | References a key in another or the same relation |
SCPACF
Memory sentence:
Smart Candidates Pick An Excellent Foreign-link
Relation:
EMPLOYEE(
EmpID,
AadhaarNo,
Email,
EmpName,
DeptID
)
Assumptions:
{EmpID}
{AadhaarNo}
{Email}
{EmpID}
{AadhaarNo}
{Email}
{DeptID}
{EmpID}
{EmpID, EmpName}
{AadhaarNo, DeptID}
{Email, EmpName}
Poor database design can create:
Normalization is the process of organizing relations to reduce redundancy and undesirable dependencies.
It usually decomposes a large relation into smaller, well-structured relations.
Consider:
EMP_DEPT
| EmpID | EmpName | DeptID | DeptName | DeptPhone |
|---|---|---|---|---|
| 1 | Asha | 10 | Computer | 1111 |
| 2 | Ravi | 10 | Computer | 1111 |
| 3 | Mohan | 20 | Science | 2222 |
Department details repeat for every employee.
If the Computer department phone changes, multiple rows must be updated.
If one row is missed, inconsistent values appear.
Suppose a new department is created but has no employees.
The table may not allow the department to be stored without an EmpID.
If Mohan is the only employee in the Science department and his row is deleted, the Science department information is also lost.
Split the relation:
EMPLOYEE
| EmpID | EmpName | DeptID |
|---|
DEPARTMENT
| DeptID | DeptName | DeptPhone |
|---|
Now department details are stored once.
A functional dependency, abbreviated as FD, describes a relationship between attributes.
Notation:
X → Y
Read as:
X functionally determines Y
This means:
If two tuples have the same X value, they must have the same Y value.
In STUDENT:
RollNo → Name
RollNo → Marks
RollNo → DeptID
A roll number identifies one student, so it determines that student’s other details.
For:
X → Y
Example:
DeptID → DeptName
DeptID is the determinant.
DeptName depends on DeptID.
An FD is trivial if the right side is a subset of the left side.
Example:
{RollNo, Name} → RollNo
This is always true because RollNo is already part of the determinant.
An FD is non-trivial if the right side is not a subset of the left side.
Example:
RollNo → Name
Name is not part of RollNo.
Y is fully dependent on a composite determinant X if removing any attribute from X breaks the dependency.
Example:
{StudentID, CourseID} → Grade
Assume:
Therefore, Grade fully depends on the complete composite key.
A partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key.
Prime attribute means an attribute that belongs to at least one candidate key.
Example relation:
ENROLLMENT(
StudentID,
CourseID,
StudentName,
CourseName,
Grade
)
Candidate key:
{StudentID, CourseID}
Dependencies:
StudentID → StudentName
CourseID → CourseName
{StudentID, CourseID} → Grade
Problems:
These are partial dependencies.
A transitive dependency occurs when a non-key attribute depends on another non-key attribute.
Example:
EmpID → DeptID
DeptID → DeptName
Therefore:
EmpID → DeptName
through DeptID.
This is a transitive dependency:
EmpID → DeptID → DeptName
The closure of an attribute set X, written X⁺, is the set of all attributes functionally determined by X.
Closure helps identify super keys and candidate keys.
Relation:
R(A, B, C, D)
FDs:
A → B
B → C
AC → D
Find A⁺:
Start:
A⁺ = {A}
Using A → B:
A⁺ = {A, B}
Using B → C:
A⁺ = {A, B, C}
Now A and C are available, so using AC → D:
A⁺ = {A, B, C, D}
A⁺ contains all attributes of R.
Therefore:
A is a super key.
If no proper subset of A exists, A is also a candidate key.
An attribute that belongs to at least one candidate key.
An attribute that does not belong to any candidate key.
Example:
Candidate key:
{StudentID, CourseID}
Prime attributes:
StudentID
CourseID
Non-prime attributes:
StudentName
CourseName
Grade
A relation is in First Normal Form, or 1NF, if:
Atomic means treated as one indivisible value in the relational design.
| StudentID | Name | PhoneNumbers |
|---|---|---|
| 1 | Asha | 9999, 8888 |
| 2 | Ravi | 7777 |
PhoneNumbers contains multiple values in one cell.
STUDENT
| StudentID | Name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
STUDENT_PHONE
| StudentID | PhoneNumber |
|---|---|
| 1 | 9999 |
| 1 | 8888 |
| 2 | 7777 |
Each cell now contains one phone number.
One cell → One value
A relation is in Second Normal Form, or 2NF, if:
Partial dependency is relevant when a candidate key is composite.
If every candidate key contains only one attribute, a relation in 1NF is automatically in 2NF.
Relation:
ENROLLMENT(
StudentID,
CourseID,
StudentName,
CourseName,
Grade
)
Candidate key:
{StudentID, CourseID}
Functional dependencies:
StudentID → StudentName
CourseID → CourseName
{StudentID, CourseID} → Grade
StudentName depends only on StudentID.
CourseName depends only on CourseID.
These are partial dependencies.
Therefore, the relation is not in 2NF.
STUDENT
STUDENT(
StudentID,
StudentName
)
Primary key:
StudentID
COURSE
COURSE(
CourseID,
CourseName
)
Primary key:
CourseID
ENROLLMENT
ENROLLMENT(
StudentID,
CourseID,
Grade
)
Primary key:
{StudentID, CourseID}
Foreign keys:
StudentID references STUDENT
CourseID references COURSE
Now each non-prime attribute depends on the whole key of its relation.
No dependence on part of a composite key
A relation is in Third Normal Form, or 3NF, if:
For every non-trivial functional dependency:
X → A
at least one must be true:
For beginner-level questions, remember:
A non-key attribute should not depend on another non-key attribute.
Relation:
EMPLOYEE(
EmpID,
EmpName,
DeptID,
DeptName,
DeptPhone
)
Primary key:
EmpID
Functional dependencies:
EmpID → EmpName, DeptID
DeptID → DeptName, DeptPhone
Therefore:
EmpID → DeptID → DeptName, DeptPhone
DeptName and DeptPhone depend transitively on EmpID through DeptID.
The relation is not in 3NF.
EMPLOYEE
EMPLOYEE(
EmpID,
EmpName,
DeptID
)
DEPARTMENT
DEPARTMENT(
DeptID,
DeptName,
DeptPhone
)
Keys:
EMPLOYEE primary key: EmpID
DEPARTMENT primary key: DeptID
EMPLOYEE.DeptID is a foreign key
Now department facts are stored in DEPARTMENT.
No non-key → non-key dependency
A common normalization mnemonic is:
Every non-key attribute must depend on:
More accurately:
1NF: Atomic values
2NF: No partial dependency
3NF: No improper transitive dependency
BCNF stands for Boyce–Codd Normal Form.
A relation is in BCNF if, for every non-trivial functional dependency:
X → Y
X is a super key.
Every determinant must be a super key.
BCNF is stricter than 3NF.
Every BCNF relation is in 3NF.
A 3NF relation is not always in BCNF.
| 3NF | BCNF |
|---|---|
| For X → A, X is super key or A is prime | For X → Y, X must be a super key |
| Allows a special prime-attribute exception | No prime-attribute exception |
| Less strict | More strict |
| Preserving all FDs is often easier | Decomposition may not preserve every FD |
| Every BCNF relation is 3NF | Not every 3NF relation is BCNF |
Consider:
CLASS(Student, Subject, Teacher)
Assumptions:
A student taking a subject has one teacher.
{Student, Subject} → Teacher
Each teacher teaches only one subject.
Teacher → Subject
Candidate keys:
{Student, Subject}
{Student, Teacher}
Prime attributes:
Student
Subject
Teacher
All attributes are prime because each belongs to at least one candidate key.
For:
Teacher → Subject
Therefore, the relation satisfies the 3NF exception.
BCNF requires every determinant to be a super key.
Teacher is not a super key.
Therefore, the relation is not in BCNF.
TEACHER_SUBJECT
TEACHER_SUBJECT(
Teacher,
Subject
)
STUDENT_TEACHER
STUDENT_TEACHER(
Student,
Teacher
)
Now determinants are keys in their relations.
| Normal Form | Main Requirement | Removes |
|---|---|---|
| 1NF | Atomic values | Repeating groups and multivalued cells |
| 2NF | 1NF + no partial dependency | Dependency on part of a composite key |
| 3NF | 2NF + no improper transitive dependency | Non-key depending on non-key |
| BCNF | Every determinant is a super key | Remaining determinant anomalies |
A-P-T-D
Consider a college table:
STUDENT_COURSE(
StudentID,
StudentName,
DeptID,
DeptName,
{CourseID, CourseName, InstructorID, InstructorName, Grade}
)
The braces represent a repeating group of courses.
Create one row for each student-course combination:
ENROLLMENT_DATA(
StudentID,
StudentName,
DeptID,
DeptName,
CourseID,
CourseName,
InstructorID,
InstructorName,
Grade
)
Candidate key:
{StudentID, CourseID}
Assumed functional dependencies:
StudentID → StudentName, DeptID
DeptID → DeptName
CourseID → CourseName, InstructorID
InstructorID → InstructorName
{StudentID, CourseID} → Grade
Every cell is now atomic.
Therefore, the relation is in 1NF.
Candidate key:
{StudentID, CourseID}
Partial dependencies:
StudentID → StudentName, DeptID
CourseID → CourseName, InstructorID
These non-prime attributes depend on only part of the composite key.
Therefore, the relation is not in 2NF.
Create:
STUDENT(
StudentID,
StudentName,
DeptID,
DeptName
)
COURSE(
CourseID,
CourseName,
InstructorID,
InstructorName
)
ENROLLMENT(
StudentID,
CourseID,
Grade
)
Now:
Grade depends on {StudentID, CourseID}
The relations satisfy 2NF regarding the identified partial dependencies.
In STUDENT:
StudentID → DeptID
DeptID → DeptName
Therefore:
StudentID → DeptID → DeptName
This is a transitive dependency.
In COURSE:
CourseID → InstructorID
InstructorID → InstructorName
Therefore:
CourseID → InstructorID → InstructorName
This is another transitive dependency.
Final relations:
STUDENT(
StudentID,
StudentName,
DeptID
)
DEPARTMENT(
DeptID,
DeptName
)
COURSE(
CourseID,
CourseName,
InstructorID
)
INSTRUCTOR(
InstructorID,
InstructorName
)
ENROLLMENT(
StudentID,
CourseID,
Grade
)
| Relation | Primary Key | Foreign Key |
|---|---|---|
| STUDENT | StudentID | DeptID |
| DEPARTMENT | DeptID | None |
| COURSE | CourseID | InstructorID |
| INSTRUCTOR | InstructorID | None |
| ENROLLMENT | StudentID + CourseID | StudentID, CourseID |
The design is now in 3NF under the stated dependencies.
A decomposition is lossless if joining the decomposed relations reconstructs the original information without creating false combinations or losing valid information.
A good normalization decomposition should be lossless.
A decomposition is dependency-preserving if the original functional dependencies can be enforced by checking the individual decomposed relations without always joining them.
Good database design generally aims for:
Data = Raw facts
Information = Processed meaningful data
Database = Organized related data
DBMS = Software that manages a database
Metadata = Data about data
| File System | DBMS |
|---|---|
| High redundancy | Controlled redundancy |
| Possible inconsistency | Better consistency |
| Difficult sharing | Shared data |
| Weak data independence | Stronger data independence |
| Manual recovery | Backup and recovery support |
| Limited security | Detailed authorization |
| User | Main Work |
|---|---|
| DBA | Security, backup, recovery and performance |
| Designer | Defines entities, attributes and relationships |
| Analyst | Converts requirements into system design |
| Programmer | Creates database applications |
| End user | Uses forms, reports and queries |
DBA mnemonic:
SABER
Security
Authorization
Backup
Efficiency
Recovery
| Model | Structure |
|---|---|
| Hierarchical | Tree |
| Network | Graph |
| Relational | Tables |
| ER | Entities and relationships |
| Object-oriented | Objects |
| Object-relational | Tables plus object features |
External level
User views
│
v
Conceptual level
Complete logical database
│
v
Internal level
Physical storage
Mnemonic:
ECI
External = Eyes of users
Conceptual = Complete structure
Internal = Inside storage
| Type | Change | Protected Level |
|---|---|---|
| Physical | Storage, index or file organization | Conceptual and external |
| Logical | Tables, attributes or relationships | External views |
Physical = Change storage, keep tables
Logical = Change tables, keep views
Physical data independence is generally easier.
| Symbol | Meaning |
|---|---|
| Rectangle | Entity |
| Double rectangle | Weak entity |
| Oval | Attribute |
| Double oval | Multivalued attribute |
| Dashed oval | Derived attribute |
| Underlined attribute | Key |
| Diamond | Relationship |
| Double line | Total participation |
1:1 = One to one
1:N = One to many
M:N = Many to many
Implementation:
1:N → Put foreign key on N side
M:N → Create intersection table
| Term | Meaning |
|---|---|
| Relation | Table |
| Tuple | Row |
| Attribute | Column |
| Domain | Permitted values |
| Degree | Number of columns |
| Cardinality | Number of rows |
| Schema | Structure |
| Instance | Current data |
Super key = Any unique attribute set
Candidate key = Minimal super key
Primary key = Selected candidate key
Alternate key = Unselected candidate key
Composite key = Multiple-attribute key
Foreign key = References another key
Important:
Primary key: Unique and not NULL
Foreign key: May repeat and may be NULL if allowed
X → Y
Meaning:
Same X value must give the same Y value.
| Dependency | Meaning |
|---|---|
| Full | Depends on complete composite key |
| Partial | Depends on part of composite key |
| Transitive | Key → non-key → another non-key |
| Normal Form | Rule |
|---|---|
| 1NF | Atomic values |
| 2NF | No partial dependency |
| 3NF | No improper transitive dependency |
| BCNF | Every determinant is a super key |
Mnemonic:
APTD
Atomic
Partial removed
Transitive removed
Determinant is super key
Which term means processed data that has useful meaning?
A. Metadata
B. Information
C. Redundancy
D. Schema
Which is a major problem caused by storing the same data in several files?
A. Reduced storage use
B. Data redundancy and inconsistency
C. Automatic normalization
D. Physical independence
Who is mainly responsible for database backup, recovery, authorization and performance?
A. End user
B. Database Administrator
C. Data-entry operator
D. Hardware manufacturer
Which data model organizes data mainly in tables?
A. Hierarchical model
B. Network model
C. Relational model
D. Tree-only model
Which data model naturally represents a strict parent–child tree?
A. Hierarchical
B. Relational
C. Object-relational
D. ER only
Which schema level contains user-specific views?
A. Internal level
B. Conceptual level
C. External level
D. Physical device level
Which level describes files, indexes and physical storage?
A. External level
B. Internal level
C. User-view level
D. Application level
Adding an index without changing tables or applications demonstrates:
A. Logical data independence
B. Physical data independence
C. Data redundancy
D. Entity integrity
Which type of data independence is generally more difficult to achieve?
A. Physical data independence
B. Logical data independence
C. File independence
D. Hardware independence
Which attribute type can be divided into smaller meaningful parts?
A. Simple attribute
B. Composite attribute
C. Derived attribute
D. Key-only attribute
A student can have several phone numbers. PhoneNumber is:
A. Single-valued
B. Derived
C. Multivalued
D. Composite key
Age calculated from DateOfBirth is:
A. Stored-only attribute
B. Derived attribute
C. Key attribute
D. Multivalued attribute
A department has many employees, while each employee belongs to one department. The relationship is:
A. 1:1
B. 1:N
C. M:N
D. Unary
How is an M:N relationship normally converted into relations?
A. Delete one entity
B. Place all values in one cell
C. Create an intersection relation
D. Remove both primary keys
What is a row in a relation called?
A. Attribute
B. Domain
C. Tuple
D. Schema
A minimal super key is called:
A. Foreign key
B. Candidate key
C. Secondary file
D. Derived key
Candidate keys are RollNo and Email. RollNo is selected as the primary key. Email becomes:
A. Foreign key
B. Composite key
C. Alternate key
D. Partial key only
Which statement about a foreign key is correct?
A. It must always be unique.
B. It can never repeat.
C. It references a key in another or the same relation.
D. It must always be the table’s primary key.
In ENROLLMENT(StudentID, CourseID, Grade), neither StudentID nor CourseID alone is unique, but their combination is unique. The key is:
A. Derived key
B. Composite key
C. Single key
D. Foreign key only
Which normal form requires atomic values and no repeating groups?
A. 1NF
B. 2NF
C. 3NF
D. BCNF
A non-prime attribute depends on only one part of a composite candidate key. This is:
A. Trivial dependency
B. Partial dependency
C. Transitive dependency
D. Multivalued attribute
Consider:
ENROLLMENT(
StudentID,
CourseID,
StudentName,
Grade
)
Candidate key:
{StudentID, CourseID}
Dependencies:
StudentID → StudentName
{StudentID, CourseID} → Grade
Which dependency prevents 2NF?
A. StudentID → StudentName
B. StudentID, CourseID → Grade
C. Grade → StudentID
D. No dependency prevents 2NF
Consider:
EMPLOYEE(EmpID, EmpName, DeptID, DeptName)
Dependencies:
EmpID → EmpName, DeptID
DeptID → DeptName
Which dependency causes the 3NF problem?
A. Full dependency
B. Transitive dependency through DeptID
C. Multivalued dependency
D. Trivial dependency only
Which is the defining BCNF rule?
A. Every cell must contain several values.
B. Every determinant of a non-trivial FD must be a super key.
C. Every relation must have two primary keys.
D. Foreign keys are prohibited.
Relation:
R(A, B, C, D)
Functional dependencies:
A → B
B → C
AC → D
What is A⁺?
A. {A, B}
B. {A, B, C}
C. {A, B, C, D}
D. {A, D}
| Q. | Answer | Q. | Answer | Q. | Answer | Q. | Answer | Q. | Answer |
|---|---|---|---|---|---|---|---|---|---|
| 1 | B | 6 | C | 11 | C | 16 | B | 21 | B |
| 2 | B | 7 | B | 12 | B | 17 | C | 22 | A |
| 3 | B | 8 | B | 13 | B | 18 | C | 23 | B |
| 4 | C | 9 | B | 14 | C | 19 | B | 24 | B |
| 5 | A | 10 | B | 15 | C | 20 | A | 25 | C |
Data is raw fact.
Information is processed data with meaning.
Data: 85
Information: Asha scored 85 marks.
Storing the same fact in several files creates redundancy.
If one copy is updated while another is not, inconsistency occurs.
The DBA manages:
Mnemonic:
SABER
The relational model organizes data in relations, commonly shown as tables.
Rows are tuples, and columns are attributes.
The hierarchical model uses a tree.
Parent
│
v
Child
Each child normally has one parent.
The external level contains user views.
Different users may see different parts of the same database.
The internal level describes:
An index is a physical access structure.
Adding an index changes the internal schema but does not have to change the conceptual schema or applications.
Logical data independence protects user views from changes to the conceptual design.
It is generally harder than physical data independence.
A composite attribute can be divided.
Example:
Address
├── Street
├── City
├── State
└── PIN
One student may have multiple phone numbers.
A multivalued attribute is traditionally drawn with a double oval.
Age can be calculated from DateOfBirth.
Therefore, Age is derived.
One department can have many employees.
Each employee belongs to one department under the stated rule.
DEPARTMENT 1 ─── N EMPLOYEE
For:
STUDENT M ─── N COURSE
Create:
ENROLLMENT(StudentID, CourseID)
The two foreign keys often form a composite primary key.
Relational terms:
Table = Relation
Row = Tuple
Column = Attribute
A candidate key is a minimal super key.
It uniquely identifies a tuple without unnecessary attributes.
Candidate keys:
RollNo
Email
Chosen primary key:
RollNo
Unchosen candidate key:
Email = Alternate key
A foreign key establishes a relationship.
It may repeat because many rows may reference the same parent row.
It may be NULL if allowed.
The unique identifier contains two attributes:
{StudentID, CourseID}
Therefore, it is a composite key.
First Normal Form requires:
Memory rule:
One cell → One value
A partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key.
It violates 2NF.
Candidate key:
{StudentID, CourseID}
StudentName depends only on StudentID, which is part of the composite key.
This is a partial dependency.
Decompose:
STUDENT(StudentID, StudentName)
ENROLLMENT(StudentID, CourseID, Grade)
Dependencies:
EmpID → DeptID
DeptID → DeptName
Therefore:
EmpID → DeptID → DeptName
DeptName depends on a non-key attribute, DeptID.
This violates 3NF under the given design.
BCNF rule:
For every non-trivial FD:
X → Y
X must be a super key.
BCNF is stricter than 3NF.
{A, B, C, D}Start:
A⁺ = {A}
Using:
A → B
we get:
A⁺ = {A, B}
Using:
B → C
we get:
A⁺ = {A, B, C}
Now A and C are available. Using:
AC → D
we get:
A⁺ = {A, B, C, D}
Therefore, A determines every attribute and is a super key.
A student’s address is stored in admission, library and fee files.
The student changes their address, but only the admission file is updated.
Identify:
Creates users, grants permissions and performs backup.
Answer:
DBA
Identifies entities, attributes and relationships.
Answer:
Database designer
Writes an admission application using SQL and Java.
Answer:
Application programmer
Uses a predefined banking form.
Answer:
Naive or parametric end user
| Description | Model |
|---|---|
| Data organized as a tree | Hierarchical |
| Data organized as a graph | Network |
| Data organized as tables | Relational |
| Conceptual entities and relationships | ER |
| Data represented as objects | Object-oriented |
Match:
| Example | Level |
|---|---|
| Teacher sees RollNo, Name and Marks | External |
| Complete STUDENT and COURSE design | Conceptual |
| B-tree index and disk blocks | Internal |
Move database files to a new storage device.
Answer:
Physical data independence
Add an index.
Answer:
Physical data independence
Split STUDENT into STUDENT and DEPARTMENT while preserving user views.
Answer:
Logical data independence
For a STUDENT entity:
| Attribute | Type |
|---|---|
| RollNo | Key and simple |
| Name | Composite |
| PhoneNumbers | Multivalued |
| DateOfBirth | Stored |
| Age | Derived |
One person has one passport.
Answer:
1:1
One department has many employees.
Answer:
1:N
A student takes many courses, and a course has many students.
Answer:
M:N
Implementation:
ENROLLMENT(StudentID, CourseID)
Relation:
USER(UserID, Email, Name, DeptID)
Assume:
Candidate keys:
{UserID}
{Email}
Possible primary key:
{UserID}
Alternate key:
{Email}
Foreign key:
{DeptID}
Examples of super keys:
{UserID}
{Email}
{UserID, Name}
{Email, DeptID}
{UserID, Email, Name}
UserID uniquely identifies each row and is minimal.Email also uniquely identifies each row and is minimal.UserID is selected as the primary key, Email becomes an alternate key.DeptID connects USER to DEPARTMENT, so it is a foreign key.Consider:
STUDENT(RollNo, Email, Name)
Assume:
RollNo is unique.
Email is unique.
Name is not unique.
Classify the following:
{RollNo}{Email}{RollNo, Name}{Email, Name}{Name}| Attribute Set | Super Key? | Candidate Key? | Reason |
|---|---|---|---|
{RollNo} | Yes | Yes | Unique and minimal |
{Email} | Yes | Yes | Unique and minimal |
{RollNo, Name} | Yes | No | Name is unnecessary |
{Email, Name} | Yes | No | Name is unnecessary |
{Name} | No | No | Name is not unique |
Remember:
Every candidate key is a super key.
Not every super key is a candidate key.
Relation:
EMPLOYEE(EmpID, AadhaarNo, Email, EmpName)
Assume:
Candidate keys:
EmpID
AadhaarNo
Email
Primary key:
EmpID
Alternate keys:
AadhaarNo
Email
Non-key attribute:
EmpName
Consider:
ENROLLMENT(StudentID, CourseID, Semester, Grade)
Assume one student can take the same course in different semesters.
Which attributes may be required to uniquely identify one enrollment?
A possible composite key is:
{StudentID, CourseID, Semester}
Reason:
Tables:
DEPARTMENT(
DeptID,
DeptName
)
EMPLOYEE(
EmpID,
EmpName,
DeptID
)
Assume:
DEPARTMENT.DeptID is the primary key.
EMPLOYEE.DeptID is a foreign key.
Department rows:
| DeptID | DeptName |
|---|---|
| 10 | Computer |
| 20 | Science |
Can this employee row be inserted?
(101, "Asha", 30)
Normally, no.
Reason:
DeptID 30 does not exist in DEPARTMENT.
This would violate referential integrity.
Valid choices include:
Relation:
STUDENT
| RollNo | Name | Marks | DeptID |
|---|---|---|---|
| 101 | Asha | 85 | 10 |
| 102 | Ravi | 76 | 20 |
| 103 | Mohan | 81 | 10 |
Find:
Number of columns:
RollNo, Name, Marks, DeptID
Therefore:
Degree = 4
Number of rows:
3
Therefore:
Cardinality = 3
Exam warning:
Relation:
STUDENT(RollNo, Name, Marks, DeptID)
Assume RollNo uniquely identifies a student.
Write the main functional dependency.
RollNo → Name, Marks, DeptID
Meaning:
If two rows have the same RollNo, they must have the same Name, Marks and DeptID.
Determinant:
RollNo
Dependent attributes:
Name, Marks, DeptID
Classify:
{A, B} → AA → B{StudentID, CourseID} → CourseIDRollNo → Name| Dependency | Type |
|---|---|
{A, B} → A | Trivial |
A → B | Non-trivial |
{StudentID, CourseID} → CourseID | Trivial |
RollNo → Name | Non-trivial |
Rule:
A functional dependency X → Y is trivial when:
Y is a subset of X.
Relation:
ENROLLMENT(
StudentID,
CourseID,
StudentName,
CourseName,
Grade
)
Candidate key:
{StudentID, CourseID}
Functional dependencies:
StudentID → StudentName
CourseID → CourseName
{StudentID, CourseID} → Grade
Partial dependencies:
StudentID → StudentName
CourseID → CourseName
Full dependency:
{StudentID, CourseID} → Grade
StudentName depends on only part of the composite key.
CourseName depends on only part of the composite key.
Grade requires both StudentID and CourseID.
Relation:
EMPLOYEE(
EmpID,
EmpName,
DeptID,
DeptName
)
Functional dependencies:
EmpID → EmpName, DeptID
DeptID → DeptName
Find the transitive dependency.
EmpID → DeptID
DeptID → DeptName
Therefore:
EmpID → DeptName
through DeptID.
Dependency chain:
EmpID → DeptID → DeptName
DeptName transitively depends on EmpID.
Relation:
R(A, B, C, D, E)
Functional dependencies:
A → B
B → C
C → D
AD → E
Find A⁺.
Start:
A⁺ = {A}
Apply:
A → B
Now:
A⁺ = {A, B}
Apply:
B → C
Now:
A⁺ = {A, B, C}
Apply:
C → D
Now:
A⁺ = {A, B, C, D}
A and D are now available, so apply:
AD → E
Final:
A⁺ = {A, B, C, D, E}
A⁺ contains all attributes.
Therefore:
A is a super key.
Because A has no smaller proper subset, it is also a candidate key.
Table:
| StudentID | Name | PhoneNumbers |
|---|---|---|
| 1 | Asha | 9999, 8888 |
| 2 | Ravi | 7777 |
Is this table in 1NF?
No.
Reason:
The PhoneNumbers cell for Asha contains more than one value.
This violates the atomic-value requirement.
STUDENT
| StudentID | Name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
STUDENT_PHONE
| StudentID | PhoneNumber |
|---|---|
| 1 | 9999 |
| 1 | 8888 |
| 2 | 7777 |
Now each cell contains one value.
Memory rule:
1NF = One cell, one value
Relation:
RESULT(
StudentID,
SubjectID,
StudentName,
SubjectName,
Marks
)
Candidate key:
{StudentID, SubjectID}
Functional dependencies:
StudentID → StudentName
SubjectID → SubjectName
{StudentID, SubjectID} → Marks
Is the relation in 2NF?
No.
Partial dependencies exist:
StudentID → StudentName
SubjectID → SubjectName
The non-prime attributes depend on parts of the composite key.
STUDENT(
StudentID,
StudentName
)
SUBJECT(
SubjectID,
SubjectName
)
RESULT(
StudentID,
SubjectID,
Marks
)
Keys:
STUDENT primary key = StudentID
SUBJECT primary key = SubjectID
RESULT primary key = {StudentID, SubjectID}
Marks depends on the complete RESULT key.
Relation:
STUDENT(
StudentID,
StudentName,
City
)
Candidate key:
StudentID
Assume the relation is in 1NF.
Can it have a partial dependency on this candidate key?
No.
The candidate key contains only one attribute.
There is no smaller proper part of StudentID on which a non-prime attribute could partially depend.
Therefore, with respect to this single-attribute candidate key, a 1NF relation is automatically free from partial dependency.
Important accuracy point:
The complete 2NF test considers every candidate key. If all candidate keys are single attributes, a relation in 1NF is automatically in 2NF.
Relation:
STUDENT(
StudentID,
StudentName,
DeptID,
DeptName,
DeptOffice
)
Functional dependencies:
StudentID → StudentName, DeptID
DeptID → DeptName, DeptOffice
Primary key:
StudentID
Is the relation in 3NF?
No.
Dependency chain:
StudentID → DeptID
DeptID → DeptName, DeptOffice
DeptName and DeptOffice depend on a non-key attribute, DeptID.
This is a transitive dependency.
STUDENT(
StudentID,
StudentName,
DeptID
)
DEPARTMENT(
DeptID,
DeptName,
DeptOffice
)
Keys:
STUDENT primary key = StudentID
DEPARTMENT primary key = DeptID
STUDENT.DeptID = Foreign key
Consider:
ORDER_DATA(
OrderID,
ProductID,
OrderDate,
CustomerID,
CustomerName,
ProductName,
UnitPrice,
Quantity
)
Assume:
Candidate key:
{OrderID, ProductID}
Functional dependencies:
OrderID → OrderDate, CustomerID
CustomerID → CustomerName
ProductID → ProductName, UnitPrice
{OrderID, ProductID} → Quantity
Assume every cell contains one value.
Therefore:
ORDER_DATA is in 1NF.
Parts of the composite candidate key are:
OrderID
ProductID
Partial dependencies:
OrderID → OrderDate, CustomerID
ProductID → ProductName, UnitPrice
Full dependency:
{OrderID, ProductID} → Quantity
Therefore, the relation is not in 2NF.
Create:
ORDER_HEADER(
OrderID,
OrderDate,
CustomerID,
CustomerName
)
PRODUCT(
ProductID,
ProductName,
UnitPrice
)
ORDER_ITEM(
OrderID,
ProductID,
Quantity
)
Now the partial dependencies have been removed from ORDER_ITEM.
In ORDER_HEADER:
OrderID → CustomerID
CustomerID → CustomerName
Therefore:
OrderID → CustomerID → CustomerName
This is a transitive dependency.
ORDER_HEADER is not in 3NF.
Final relations:
CUSTOMER(
CustomerID,
CustomerName
)
ORDER_HEADER(
OrderID,
OrderDate,
CustomerID
)
PRODUCT(
ProductID,
ProductName,
UnitPrice
)
ORDER_ITEM(
OrderID,
ProductID,
Quantity
)
| Relation | Primary Key | Foreign Key |
|---|---|---|
| CUSTOMER | CustomerID | None |
| ORDER_HEADER | OrderID | CustomerID |
| PRODUCT | ProductID | None |
| ORDER_ITEM | OrderID + ProductID | OrderID, ProductID |
The final design is in 3NF under the stated dependencies.
Relation:
CLASS(Student, Subject, Teacher)
Functional dependencies:
{Student, Subject} → Teacher
Teacher → Subject
Candidate keys:
{Student, Subject}
{Student, Teacher}
Prime attributes:
Student
Subject
Teacher
Yes.
For:
Teacher → Subject
The prime-attribute exception allows this dependency in 3NF.
No.
BCNF requires every determinant to be a super key.
The determinant:
Teacher
is not a super key.
Therefore, BCNF is violated.
TEACHER_SUBJECT(
Teacher,
Subject
)
STUDENT_TEACHER(
Student,
Teacher
)
Identify the highest normal form problem described.
One cell contains several phone numbers.
Answer:
Violates 1NF
A non-prime attribute depends on part of a composite candidate key.
Answer:
Violates 2NF
A non-prime attribute depends on another non-prime attribute.
Answer:
Violates 3NF
A non-trivial FD has a determinant that is not a super key, even though its dependent is prime.
Answer:
May satisfy 3NF but violates BCNF
Table:
| EmpID | EmpName | DeptID | DeptName |
|---|---|---|---|
| 1 | Asha | 10 | Computer |
| 2 | Ravi | 10 | Computer |
| 3 | Mohan | 20 | Science |
If Computer is renamed to Computer Science, every matching employee row must be changed.
If one is missed, inconsistent values appear.
A new department cannot easily be stored until it has an employee.
If Mohan is the only Science employee and his row is deleted, Science department information is lost.
Decompose:
EMPLOYEE(EmpID, EmpName, DeptID)
DEPARTMENT(DeptID, DeptName)
Requirement:
┌────────────┐ 1 N ┌───────────┐
│ DEPARTMENT │────────────────────────│ TEACHER │
└────────────┘ HAS └─────┬─────┘
│
((PhoneNumber))
│
│ M
│
◇ TEACHES ◇
│
(Semester)
│
│ N
│
┌─────┴─────┐
│ COURSE │
└───────────┘
DEPARTMENT(
DeptID,
DeptName
)
TEACHER(
TeacherID,
TeacherName,
DeptID
)
TEACHER_PHONE(
TeacherID,
PhoneNumber
)
COURSE(
CourseID,
CourseName
)
TEACHES(
TeacherID,
CourseID,
Semester
)
Possible composite key of TEACHES:
{TeacherID, CourseID, Semester}
Answer these aloud without reading the main notes.
Use your score from the 25 MCQs.
| Correct Answers | Level | Required Action |
|---|---|---|
| 23–25 | Excellent | Revise the Quick Revision Sheet tomorrow |
| 20–22 | Good | Review incorrect answers once |
| 16–19 | Developing | Repeat keys and normalization |
| 10–15 | Weak | Restudy architecture, ER model and normalization |
| 0–9 | Beginning | Read all five sections again slowly |
| Questions Missed | Topic to Revise |
|---|---|
| 1–5 | DBMS overview, users and data models |
| 6–9 | Three-schema architecture and data independence |
| 10–14 | ER model |
| 15–19 | Relational model and keys |
| 20–25 | Functional dependencies and normalization |
Study:
Write this comparison from memory:
| File System | DBMS |
|---|---|
| Separate files | Integrated database |
| High redundancy | Controlled redundancy |
| Weak security | Detailed authorization |
| Difficult recovery | Backup and recovery |
| Low data independence | Higher data independence |
Memorize the DBA mnemonic:
SABER
Security
Authorization
Backup
Efficiency
Recovery
Practice:
Draw the three-schema architecture:
External level
User views
│
v
Conceptual level
Complete logical design
│
v
Internal level
Physical storage
Memorize:
ECI
External = Eyes of users
Conceptual = Complete structure
Internal = Inside storage
Practice classification:
Add index → Physical
Move data to a new disk → Physical
Change file organization → Physical
Split a table → Logical
Add a logical attribute → Logical
Add a new relation → Logical
Remember:
Physical independence is generally easier.
Logical independence is generally harder.
Study:
Draw from memory:
Entity = Rectangle
Weak entity = Double rectangle
Attribute = Oval
Multivalued = Double oval
Derived = Dashed oval
Relationship = Diamond
Key = Underlined attribute
Practice:
Study:
Write from memory:
Super key = Unique
Candidate key = Minimal unique
Primary key = Chosen candidate
Alternate key = Unchosen candidate
Composite key = Multiple attributes
Foreign key = Reference to another key
Practice with this relation:
EMPLOYEE(EmpID, Email, AadhaarNo, Name, DeptID)
Assume EmpID, Email and AadhaarNo are unique.
Identify:
Study:
Memorize:
APTD
Atomic → 1NF
Partial removed → 2NF
Transitive removed → 3NF
Determinant is super key → BCNF
Use this checking order:
Step 1: Are all values atomic?
No → Fix 1NF
Step 2: Is there a composite candidate key?
Yes → Check partial dependencies
Found → Fix 2NF
Step 3: Does a non-key determine another non-key?
Yes → Fix 3NF
Step 4: Is every determinant a super key?
No → BCNF violation
Practice:
DBMS = Software that manages a database
Main benefits:
Reduced redundancy
Better consistency
Security
Integrity
Concurrency
Backup and recovery
Data independence
DBA:
Security, authorization, backup, recovery, performance
Designer:
Entities, attributes, relationships
Programmer:
Applications and queries
End user:
Uses database application
Hierarchical = Tree
Network = Graph
Relational = Tables
ER = Conceptual design
Object-oriented = Objects
External = User views
Conceptual = Complete logical design
Internal = Physical storage
Physical independence:
Change storage, keep tables
Logical independence:
Change tables, keep views
Entity = Real-world object
Attribute = Property
Relationship = Association
Simple = Cannot divide
Composite = Can divide
Multivalued = Many values
Derived = Calculated
Key = Unique identifier
1:1 = One to one
1:N = One to many
M:N = Many to many
1:N → Foreign key on N side
M:N → Intersection table
Relation = Table
Tuple = Row
Attribute = Column
Domain = Permitted values
Degree = Columns
Cardinality = Rows
Super = Any unique set
Candidate = Minimal unique set
Primary = Chosen candidate
Alternate = Unchosen candidate
Composite = Multiple attributes
Foreign = Reference
FD:
X → Y means X determines Y
1NF:
Atomic values
2NF:
No partial dependency
3NF:
No improper transitive dependency
BCNF:
Every determinant is a super key
Every BCNF relation is in 3NF.
Not every 3NF relation is in BCNF.