π Lecture 10 β Extended Entity Relationship Diagram
π Overview: This lecture introduces the Extended Entity Relationship Diagram (EERD), which enhances the basic ER model by adding super types and sub types to represent complex database systems more accurately. It explains how common attributes are shared through inheritance to avoid redundancy, and covers the key properties of exhaustiveness and mutual exclusivity that govern super type-sub type relationships.
ποΈ Topics Covered
The lecture covers the definition and purpose of the Extended Entity Relationship Diagram (EERD), explains the concepts of super types and sub types with inheritance rules, provides examples of how common attributes are shared between super types and sub types, and discusses two critical properties: exhaustiveness (requiring at least two sub types with unique attributes) and mutual exclusivity (each super type instance belongs to only one sub type).
π Lecture Summary
A. Extended Entity Relationship Diagram (EERD)
The EERD is an extension of the original Entity Relationship Diagram (ERD) that incorporates the concept of reusability. It was developed to reflect more precisely the properties and constraints found in more complex systems. Common attributes that are repeated among multiple entities are placed in a Super Type (or Super Entity) and are shared with Sub Types to avoid redundancy. Attributes and relationships of super types are shared with sub types, but this relationship is not bottom-up β meaning a Super Type cannot access any attributes or relationships of its sub types. The EER model includes all concepts from the ER model and adds the concepts of Sub-Type and Super-Type. Extended ERDs are high-level conceptual models that accurately represent the requirements of complex databases.
AAAAA. Super Types
A Super Type (also called a super class) contains common attributes that are shared by multiple sub types. Many sub classes or sub types can be derived from a single super type. The details of the super types are available in the sub types, but super types do not know anything about sub types. In simple words, it is an entity type that has a relationship with one or more sub types.
π Definition β Super Type: An entity type that has a relationship with one or more sub types, containing common attributes that are shared downward through inheritance.
BBBBB. Sub Types
A Sub Type (or sub class) is derived from the super class and can have its own attributes and relationships. Besides having its own relationships and attributes, a sub type inherits all the relationships and attributes of its super class. A class derived from another class is called a subclass (also a derived class), while the class from which it is derived is called a super class (also a base class or parent class).
π Definition β Sub Type: A class derived from a super class that inherits all attributes and relationships of the super class while also having its own unique attributes and relationships.
CCCCC. Example of Super Type & Sub Type
The lecture presents a diagram where the Staff entity acts as the Super Type with common attributes: sno, sname, designation, address. Two Sub Types are derived: Full-time and Part-time. Instead of writing these four common attributes (sno, sname, designation, address) multiple times for each employee type, they are written only once in the Staff super type and are shared by the Full-time and Part-time sub types through inheritance.
π Example: Staff entity has attributes sno, sname, designation, address. Full-time and Part-time sub types inherit these attributes automatically, so they only need to define their own unique attributes.
DDDDD. Exhaustive
Exhaustive is the first property of Super & Sub Types. It states that there must be at least two sub types, and each sub type must have at least one unique attribute. A sub type with no specific attributes is not valid. In other words, every instance of the super type is also an instance of one of the sub types.
π Definition β Exhaustive: A property requiring at least two sub types derived from a super type, with each sub type having at least one specific attribute of its own.
π Example: If we remove the Salary attribute from the Full-time sub type, it is no longer a valid sub type because according to the Exhaustive rule, there must be at least one specific attribute for the sub type to qualify as valid.
EEEEE. Example of Exhaustive
The lecture uses a diagram to illustrate the Exhaustive property. A Staff super type has two sub types: Full-time (with unique attribute salary) and Part-time (with unique attribute hourly_rate). Each sub type must have at least one unique attribute to be considered valid.
FFFFF. Mutually Exclusive
Mutually Exclusive is the second property of Super & Sub Types. Sub types are mutually exclusive because they do not know about each other. Attributes and relationships that a specific sub type has with other entities are not shareable with or known to other sub types in the system. Every sub type maintains isolation of relationships and attributes from other sub types. This rule articulates that every instance of the super type belongs to one and only one sub type.
π Definition β Mutually Exclusive: A property where each instance of the super type belongs to exactly one sub type, and sub types cannot access each other's attributes or relationships.
π Example: If sno=1 is associated with the Full-time sub type at a particular time, it cannot simultaneously be associated with the Part-time sub type. The Part-time sub type is not aware of this association due to the Mutually Exclusive rule.
GGGGG. Example of Mutually Exclusive
A diagram shows a Staff super type with sno=1 that can be linked to either Full-time or Part-time, but not both at the same time. This enforces that each employee instance belongs exclusively to one employee type.
HHHHH. Example of EERD
The lecture presents a complete EERD example for a HR System:
- There are different types of employees: Full-time and Part-time.
- Only Full-time employees can avail the facility of a loan.
- All types of employees (both Full-time and Part-time) can apply for study leaves.
- The Account department prepares salary slips in the same way for all employees.
The example shows how the EERD models these business rules: common employee attributes are in the super type, while specific attributes and relationships (like loan access for Full-time employees) are placed in the appropriate sub types.
π‘ Why this matters: This example demonstrates how EERD captures real-world business rules where different employee types have different privileges and relationships, while sharing common data through inheritance.
β Key Takeaways
The EERD extends the basic ER model by introducing super types and sub types to reduce data redundancy through inheritance of common attributes. Super types contain shared attributes that are inherited downward by sub types, but super types cannot access sub type attributes. Two critical properties govern sub types: Exhaustive requires at least two sub types each with at least one unique attribute, and Mutually Exclusive ensures each super type instance belongs to exactly one sub type. These concepts allow complex databases to accurately model real-world scenarios where entities share common characteristics but have distinct properties and relationships. For exams, remember that sub types inherit from super types (not the reverse), and each sub type must justify its existence with at least one unique attribute.
π§ Quick Revision Questions
- What is the main difference between an ERD and an EERD?
- Why are common attributes placed in a super type instead of duplicating them in each sub type?
- According to the Exhaustive property, what happens if a sub type has no unique attributes of its own?
- Under the Mutually Exclusive rule, can a single employee instance belong to both Full-time and Part-time sub types simultaneously?
- In the HR System example, why can only Full-time employees access loans, and how is this modeled in the EERD?
π Lecture 11 β Example of Entity Relationship Diagram (ERD)
π Overview: This lecture demonstrates the complete process of converting real-world scenarios into Entity Relationship Diagrams (ERDs) and then into Physical Data Models. Through three detailed examplesβBuilding-Apartment Renting, Music Album Production, and Airport Managementβstudents learn how to derive entities, attributes, relationships, and finally generate physical database schemas with proper foreign keys and primary keys.
ποΈ Topics Covered
The lecture covers three comprehensive examples of ERD generation from textual scenarios. It explains how to identify entities and their attributes from descriptive narratives, how to model relationships between entities (One-to-Many, Many-to-Many, One-to-One), and the crucial step-by-step transformation of ERDs into Physical Data Models showing foreign keys, composite primary keys, and unique keys. The Building-Apartment example demonstrates complex relationships with composite keys, the Music example illustrates Many-to-Many resolution through junction tables, and the Airport example showcases One-to-One relationships with unique foreign keys.
π Lecture Summary
Example 01: Scenario and Generating ERD
This first example revisits the Building-Apartment Renting scenario from previous lectures. The scenario describes a building with multiple floors, each floor containing zero or more apartments. Customers can rent multiple apartments from the same building, but each apartment can be rented by at most one customer. At month-end, a receipt is generated against which rent is deposited.
From this scenario, the following entities and their attributes are identified:
- Building: Building Name (bname), Address, Phone Number
- Apartment: Apartment ID (aid), Covered Area, Status (available/rented), Rent
- Customer: Customer ID (cid), Name, Contact, CNIC
- Floor: Floor Number, Number of Apartments
- Receipt: Receipt ID (rid), Apartment Number, Date of Receipt, Amount
Generating Physical Data Model
The lecture explains that in an ERD, it is not possible to show foreign keys as a rule, but in the Physical Data Model, it is possible to show all attributes and foreign keys explicitly. The following rules of thumb govern this transformation:
π Definition β Foreign Key Rule: The primary key of the parent table is referred to as a foreign key in the child table in a One-to-Many relationship.
π Definition β Many-to-Many Resolution: In a Many-to-Many relationship, a new third table (junction table) is introduced where the primary keys of both tables are written as foreign keys and made part of a Composite Primary Key (CPK).
π Definition β One-to-One Rule: In a One-to-One relationship, any one table will contain a foreign key, but that foreign key must be made a Unique Key (UK) as a rule.
π‘ Why this matters: These rules ensure referential integrity in the database and prevent data anomalies during insertion, deletion, and updates.
Physical Data Model of ERD (Example 01)
The Physical Data Model for the Building-Apartment scenario is:
- Building(bname, address)
- Floor(floor#, no_apt, bname)
- Assumption: Floor# can be repeated among multiple buildings, therefore bname is made part of Composite Primary Key along with Floor# in the floor table.
- Customer(cid, name, address, contact#)
- Apartment(aid, status, floor#, bname, CA, cid)
- Receipt(rid, aid, cid, rent, dor)
π Composite Key Formula: (floor#, bname) β Together they uniquely identify a specific floor in a specific building.
π Example: If Building "A" has Floor 1 and Building "B" also has Floor 1, the composite key (1, "A") distinguishes the first from (1, "B").
Example 02: Scenario and Generating ERD
This second example involves musical bands, songs, writers, companies, and albums. The scenario states that musical bands record songs and request musical companies to launch their songs in the form of albums. Songs are written by song writers. An album can contain at least one song and a maximum of 12 songs. A writer can write multiple songs, but songs are usually written irrespective of the number of persons in the bands.
From this scenario, the following entities and attributes are identified:
- Bands: bname, total person, cat
- Songs: sname, duration, language
- Writer: wid, wname, charges, category
- Company: cname, address, phone#
- Album: aname, total_songs
Physical Data Model of ERD (Example 02)
The Physical Data Model for the Music scenario is:
- Band(bname, totalperson, cat)
- Company(cname, address, phone#)
- Songs(sname, duration, language, bname, aname)
- Writer(wid, wname, charges, category)
- Album(aname, total_songs, cname)
- Writer_song(wid, sname)
π‘ Why this matters: The Writer_song table is a junction table that resolves the Many-to-Many relationship between Writer and Songs (a writer can write multiple songs, and a song can be written by multiple writers). Its composite primary key consists of (wid, sname).
Example 03: Scenario and Generating ERD
This third example involves an airport management system. The scenario describes several flights that are either landing or taking off from an airport. There is more than one passenger on a particular flight. Passengers are allocated seat numbers when they purchase tickets. A plane can be associated with different flights during its lifecycle. Passengers can order different meals during a flight (either Asian or Non-Asian). The airport is managed by a manager.
From this scenario, the following entities and attributes are identified:
- Airport: Aname, capacity, annual traffic
- Manager: Employee ID (eid), Airport Name (aname), name
- Flight: Flight ID (fid), Date of Departure (dod), Time of Dep. (tod), destination, aname
- Passenger: Passenger ID (pid), pname, fid, contact, address, date
- Ticket: Flight ID (fid), Ticket ID (tid), Passenger ID (pid), seat number, meal
- Plane: Plane ID (pid), capacity, age
Generating Physical Model from ERD (Example 03)
The Physical Data Model for the Airport scenario is:
- Airport(aname, capacity, annual traffic)
- Manager(eid, aname, name)
- Note: aname is both FK and UK due to One-to-One relationship with Manager.
- Flight(fid, dod, tod, destination, aname)
- Passenger(pid, pname, fid, contact, address, date)
- Ticket(fid, tid, pid, seat#, meal)
- Plane(pid, capacity, age)
- Plane_flight(pid, fid)
- Note: This resolves the Many-to-Many relationship between Flight and Plane.
π Relationship Pattern: One-to-One β Manager to Airport (aname is UK); Many-to-One β Flight to Airport; One-to-Many β Flight to Passenger; Many-to-Many β Flight to Plane (resolved via Plane_flight junction table).
π Example: A specific flight (fid = "PK301") can use different planes on different days. The Plane_flight table stores each assignment as a separate row with (pid, fid) as the composite key.
β Key Takeaways
The most critical concepts from this lecture are the three rules for converting relationships from ERD to Physical Data Model: in One-to-Many relationships, the parent's primary key becomes a foreign key in the child table; in Many-to-Many relationships, a new junction table is created containing both primary keys as a composite primary key; and in One-to-One relationships, one table contains a foreign key that must be made unique. Additionally, composite keys are necessary when attributes like floor numbers can repeat across different parent entities (e.g., multiple buildings each having a floor 1). The Physical Data Model is the final step before database implementation and includes all attributes and explicit foreign key declarations that are absent from the conceptual ERD.
π§ Quick Revision Questions
- What is the rule for handling foreign keys when converting a One-to-Many relationship from an ERD to a Physical Data Model?
- In the Building-Apartment example, why is bname included with floor# as a composite primary key in the Floor table?
- How do you resolve a Many-to-Many relationship between Flight and Plane in the Physical Data Model? Name the resulting table and its primary key structure.
- In the Airport scenario, which relationship type exists between Manager and Airport, and what constraint is placed on the foreign key (aname) in the Manager table?
- What is the purpose of the Writer_song table in the Music Band example, and what foreign keys does it contain?
π Lecture 12 β Anomalies
π Overview: This lecture defines database anomalies as problems arising from poor planning and design, typically when data is stored in a single table instead of multiple tables. Understanding anomalies is critical because they lead to data redundancy, inconsistency, and serious performance issues, especially in large databases.
ποΈ Topics Covered
The lecture begins by defining what an anomaly is and why it is critical for database performance and user experience. It then explains the three specific types of anomalies: Insert Anomaly, Delete Anomaly, and Update Anomaly, providing examples of each to illustrate the problems caused by poor design.
π Lecture Summary
A. What is Anomaly?
Anomalies are defined as the problems that can occur due to poor planning and designing of databases. This usually occurs when a single table is created instead of creating multiple tables. An anomaly is an irregularity, or something which deviates from the expected or normal state.
π Definition β Anomaly: an irregularity, or something which deviates from the expected or normal state, caused by poor database planning and design, often from using a single table instead of multiple tables.
B. Why Anomalies are Critical
Anomalies are critical as they can affect the end user experience. Furthermore, duplication or redundancy in the data is increased due to anomalies, which in turn creates other serious issues. Anomalies may not have a huge impact on small databases, but a database with a large data size may have serious performance issues caused by anomalies.
C. Insert Anomaly
An Insert Anomaly occurs when certain data cannot be entered without entering unwanted data. In other words, some attributes cannot be entered without the presence of some other attributes. This results from the poor design decision of storing data in just one table instead of multiple tables. For example, if we want to enter a new product (pid, pname, price), we have to add a customer (cid) against the product, which might not be possible at the time of entering the product. This means to add a wanted row (product), we have to enter an unwanted row (customer) because CID is the primary key and it cannot be left null.
π Definition β Insert Anomaly: occurs when certain data cannot be entered into the database without also entering unwanted or unrelated data. π Example: To add a new product (pid, pname, price), a customer (cid) must also be entered because CID is the primary key and cannot be null, forcing unwanted data insertion.
D. Delete Anomaly
This anomaly occurs when data is lost due to the deletion of some other data. The reasons for this anomaly are the same as for insert anomaly, i.e., poor design decisions. Due to this anomaly, certain attributes are lost due to the deletion of other attributes. For instance, if we want to delete pid: 4, we will also be deleting cid=2 because when we delete a complete row, the only record for cid=2 is also removed. This means we might end up losing customer data when we delete product data, which is an unwanted loss of data.
π Definition β Delete Anomaly: occurs when the deletion of some data unintentionally causes the loss of other, unrelated data.
π Example: Deleting a product with pid: 4 also deletes the only customer record with cid: 2, resulting in the loss of customer data.
E. Update Anomaly
An Update Anomaly exists when one or more instances of duplicated data are updated, but not all. An update anomaly occurs when the same data item has to be updated more than once, which can lead to errors and inconsistency of data. The same information can be expressed on multiple rows; therefore, all the instances must be updated. If this is not done properly, an update anomaly arises. If there are multiple records and we fail to update all the instances, it will result in data-inconsistency across the database, leading to wrong reporting of data, which is unacceptable. For example, in the table, if we decide to update the Cname of cid=3 and fail to update all the rows containing that Cname, the system will report two different Cname values against the same CID=3, which is incorrect.
π Definition β Update Anomaly: exists when one or more instances of duplicated data are updated, but not all, leading to data inconsistency.
π Example: Updating the Cname for cid=3 in only some records, but not all, results in the system showing different names for the same customer ID, causing inconsistency.
β Key Takeaways
The most critical point from this lecture is that database anomalies arise from poor design, particularly from storing all data in a single table instead of normalizing into multiple tables. The three types of anomaliesβInsert, Delete, and Updateβeach cause specific problems: insert anomaly forces you to add unwanted data, delete anomaly causes unintended data loss, and update anomaly leads to data inconsistency. These issues not only degrade user experience but also cause serious performance problems in large databases by increasing data redundancy and requiring multiple updates.
π§ Quick Revision Questions
- What is the definition of a database anomaly?
- Why are anomalies considered critical for large databases?
- What is an Insert Anomaly, and what design flaw causes it?
- Describe a scenario where a Delete Anomaly would cause unintended data loss.
- How does an Update Anomaly lead to data inconsistency?
π Lecture 13 β Normalization
π Overview: This lecture introduces database normalization as the process of efficiently storing data to reduce redundancy and eliminate undesirable characteristics. It covers the three normal forms (1NF, 2NF, 3NF) through practical examples, demonstrating how to transform unorganized data into a consistent, anomaly-free database structure.
ποΈ Topics Covered
The lecture covers normalization basics and objectives, normalization through reports using physical data models, definitions of repeating and non-repeating groups, detailed explanation of First Normal Form (1NF) with student-course example, Second Normal Form (2NF) addressing partial dependencies, Third Normal Form (3NF) eliminating transitive dependencies, and a second comprehensive example using flight-passenger-airport data.
π Lecture Summary
A. Normalization Basics
Database normalization is the process of storing data efficiently to reduce data redundancy and undesirable characteristics from the system. The two main objectives are to reduce redundancy and ensure logical relationships/dependencies, which is why related data is stored together. This process serves as a solution to database anomalies by removing all anomalies and bringing the database to a consistent state.
π‘ Why this matters: Without normalization, databases suffer from update anomalies, insertion anomalies, and deletion anomalies that corrupt data integrity.
T. Normalization through Reports
Normalization can be done through physical data model or through reports. This course focuses on normalization through reports because it provides a clear structure of the whole process.
The process of normalization consists of three steps called Normal Forms:
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
U. First Normal Form (1NF)
First normal form (1NF) sets the most basic rules for an organized database. As per First Normal Form, no two rows of data must contain repeating group of information i.e., each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.
These rules can be summarized as:
- Data should be organized in tables
- There should be no repeating groups
- There should be Primary Keys in each table
- Primary key non-repeating group should be written with every following group as FK
- First group has to be non-repeating group
V. Definition of Non-Repeating Group
Non-repeating group: Group in which there is only one row in the given scenario. In the student example, student is a non-repeating group as there is only one row given in the report.
W. Definition of Repeating Group
Repeating group: Group in which there are more than one row in the given scenario. In the student example, course and semester are repeating groups as there are multiple rows given in the report.
L. Example of First Normal Form (1NF)
Based on Figure 24, the following 1NF entities and attributes are defined:
The primary key of the first group (non-repeating group β Student) has to be written as FK with every other following group (Course and Semester). Just like One-to-Many relationships, primary key of parent table is written as FK in child table; Student is a parent table and Course & Semester tables are child tables.
First Normal Form (1NF)
| Entity | Attributes |
|---|---|
| Student | Sid, Sname, Total_Registered_Courses, Status |
| Course | Cid, Cname, Credithrs, Sid |
| Semester | Semid, Sid, Start Date, End Date |
The data presented in the above format fulfills all criteria of being in first normal form.
Y. Second Normal Form (2NF)
2NF is derived from 1NF, meaning the prerequisite to perform 2NF is 1NF. The rule states there must not be any partial dependency of any non-key column on primary key. The objective of second normal form is to take data that is only partly dependent on the primary key and enter that data into another table.
Z. Example of Second Normal Form (2NF)
Considering the data from Figure 24, the following conditions must be fulfilled:
- First Normal Form (1NF) is achieved
- There should be no partial dependency
- Each non-key attribute should be fully-functional dependent on Key
First Normal Form (1NF)
| TableName | Attributes |
|---|---|
| Student | Sid, Sname, Total_Registered_Courses, Status |
| Course | Cid, Cname, Credithrs, Sid |
| Semester | Semid, Sid, Start Date, End Date |
Second Normal Form (2NF)
| TableName | Attributes | Comments |
|---|---|---|
| Student | Sid, Sname, Total_Registered_Courses, Status | No Change. Table with single PK is already in 2NF. |
| Course | (Cid, Cname, Credithrs, Sid) | Cid β Cname & Cid β Credit Hour. To find correct value of cname & credithr, only CID value is required. Non-key attributes (cname, credithrs) are dependent on part of Composite key β only CID; this is partial dependency. Course table is not in 2NF. |
| Course (updated) | cid, sname, credits | New Table β In 2NF now |
| Student-course | cid, id | Original table renamed from Course β In 2NF now |
| Semester | Semid, Sid, Start Date, End Date | Semid β start date, end date. Both have functional dependency on part of CPK i.e., semid only. Semester table is not in 2NF. |
| Semester_info | semid, startdate, enddate | New Table β In 2NF now |
| Semester | semid, sid | Original table β In 2NF now |
AA. Third Normal Form (3NF)
Third Normal Form applies that every non-prime attribute of table must be dependent on primary key, or there should not be the case that a non-prime attribute is determined by another non-prime attribute. This is known as transitive dependency. This transitive dependency should be removed from the table, and the table must be in Second Normal Form.
BB. Example of Third Normal Form (3NF)
Continuing with the same example (1NF and 2NF already achieved):
Third Normal Form (3NF)
| TableName | Attributes | Comments |
|---|---|---|
| Course | (cid, sname, credits) | No Change |
| Student-course | (cid, id) | No Change |
| Semester | (semid, sid) | No Change |
| Semester_info | (semid, startdate, enddate) | No Change |
| Student | Sid, Sname, Total_Registered_Courses, Status | Total Course Registered β Status. Both are non-key attributes and have transitive dependency because if value of total_course_registered is known, status can be determined (part-time or full-time), but if status is known, correct value of total_course_registered is not known. Example: if total_course_registered is 4 then status is full-time, but if status is full-time, total_course_registered could be 4, 5, or 6. This relation is not in 3NF. |
| Student-Status | Total_courses_registered, status | New Table |
| Student | sid, sname, reg_courses | New Table (update of Student table) |
After applying normalization up to Third Normal Form (3NF), the data is in the following shape:
| TableName | Attributes |
|---|---|
| Course | (cid, sname, credits) |
| Student-course | (cid, id) |
| Semester | (semid, sid) |
| Semester_info | (semid, startdate, enddate) |
| Student-Status | Total_courses_registered, status |
| Student | sid, sname, reg_courses |
CC. Second Example of Normalization
Consider Figure 25 data with the following entities:
- Flight
- Passenger
- Airport
DD. Non-repeating group:
i. Flight
EE. Repeating Groups:
i. Passenger ii. Airport
First Normal Form (1NF)
| Table Name | Attributes |
|---|---|
| Flight | Flight#, destination, origin, distance, dod, tod |
| Passenger | Flight#, pid, pname, seat#, ticket#, cost |
| Airport | aid, Flight#, gate#, aname, runway# |
Second Normal Form (2NF)
| Entities | Attributes | Comments |
|---|---|---|
| Flight | Flight#, destination, origin, distance, dod, tod | No Change β single attribute primary key |
| Passenger | Flight#, pid, pname, seat#, ticket#, cost | No Change β each non-key is fully-functionally dependent on Flight# and PID |
| Airport | aid, Flight#, gate#, aname, runway# | Aid β gate#, aname, runway# β partial dependency on CPK (aid, flight#) |
| Airport-Info | aid, gate#, aname, runway# | New Table |
| Airport | aid, fid | New Table |
Third Normal Form (3NF)
| Entities | Attributes | Comments |
|---|---|---|
| Flight | Flight#, destination, origin, distance, dod, tod | Origin, Destination β distance β transitive dependency because to find correct distance, values of origin and destination must be known, and all three attributes are non-key |
| Org-Dest | origin, destination, distance | New Table |
| Flight | Flight#, origin, destination, dod, tod | New Table |
| Passenger | Flight#, pid, pname, seat#, ticket#, cost | No Change |
| Airport-Info | aid, gate#, aname, runway# | No Change |
| Airport | aid, fid | No Change |
After applying normalization up to 3NF, the data is in the following shape:
| TableName | Attributes |
|---|---|
| Flight | Flight#, destination, origin, distance, dod, tod |
| Org-Dest | origin, destination, distance |
| Flight | Flight#, origin, destination, dod, tod |
| Passenger | Flight#, pid, pname, seat#, ticket#, cost |
| Airport-Info | aid, gate#, aname, runway# |
β Key Takeaways
Normalization is a systematic process with three successive forms where each form builds upon the previous one. First Normal Form eliminates repeating groups and ensures atomic values with primary keys. Second Normal Form removes partial dependencies by ensuring all non-key attributes are fully functionally dependent on the entire primary key, requiring table decomposition when composite keys exist. Third Normal Form eliminates transitive dependencies where non-key attributes determine other non-key attributes, further decomposing tables to ensure every non-prime attribute depends only on the primary key. The end result is a consistent database structure free from update, insertion, and deletion anomalies.
π§ Quick Revision Questions
- What are the three normal forms in database normalization, and what specific problem does each form address?
- In the student-course example, why was the Course table not in 2NF, and how was it transformed into 2NF?
- What is a transitive dependency, and how was it demonstrated in the Student table with Total_Registered_Courses and Status?
- In the flight-passenger-airport example, what partial dependency existed in the Airport table, and what two tables replaced it?
- After completing 3NF normalization in the flight example, what new table was created to resolve the transitive dependency involving origin, destination, and distance?
π Lecture 14 β Denormalization
π Overview: This lecture introduces denormalization, the systematic process of adding redundancy to a database to improve performance by reducing the number of joins required in queries. It explains when and why denormalization is needed, contrasts it with normalization, and provides several practical scenarios for its application.
ποΈ Topics Covered
The lecture defines denormalization and its purpose, discusses the need for denormalization when running time is more critical than database structure, compares normalization vs. denormalization, outlines when to denormalize, and presents specific scenarios including storing end dates, storing details in master tables, implementing short circuit keys, using current indicator columns, and storing calculated values.
π Lecture Summary
A. What is Denormalization?
Denormalization is the process of systematically adding redundancy in a database with the purpose of improving database performance. The tables are merged into each other to group data, because the more tables you have, the more joins you have to perform in queries. Joins have a negative impact on performance and more joins mean more time is required to retrieve data, because data is required from multiple tables. The purpose of denormalization is to reduce the running time, the time it takes the DBMS to calculate results.
π Definition β Denormalization: The process of systematically adding redundancy to a database to improve performance by reducing joins.
π‘ Why this matters: Denormalization sacrifices normalization principles for speed, which is essential in high-performance systems where query response time is critical.
B. Need for Denormalization
Denormalization comes in as a solution if the running time of a database is of more importance than the database structure. In a large database, there are cases which require data from multiple tables in a single query, and the application may need to perform this type of query hundreds of times in a minute. In such a situation, a fully normalized database may be unacceptably slow. In such cases, denormalization of the database increases performance. So denormalization is a tradeoff between database structure and running time.
C. Normalization vs. Denormalization
The difference between normalization and denormalization is in the focus of these two activities. Normalization focuses on decreasing the data redundancy in a database, while the purpose of denormalization is to decrease the running time of a database. As normalization reduces inconsistency, the database designer needs to ensure that the denormalized database does not become inconsistent.
π Definition β Normalization vs. Denormalization: Normalization reduces redundancy; denormalization reduces running time. Both are tradeoffs.
D. When to Denormalize?
Denormalization does not have any fixed rule like there are rules in normalization. It is totally scenario or need-based activity. As mentioned above, when the requirement is to reduce the running time of a database system, denormalization is done. It should be performed only if performance issues indicate that it is needed.
E. Scenarios for Denormalization
a. Storing End Date
The most common denormalization decision is to store the end date for periods that are consecutive; then the end date for a period can be derived from the start date of the previous period. It is appropriate when queries are needed from tables with long lists or records that are historical and the user is interested in the most current record. Add an end date column to speed up queries so that they can use a between operator, which is one of the most efficient operators that can be used to retrieve data and requires start and end range.
π Definition β Storing End Date: Adding an end date column to speed up queries that filter using the BETWEEN operator for consecutive periods.
π Example: For historical records with start dates, instead of deriving end date from previous record's start date, add a separate end date column to allow direct BETWEEN queries.
b. Storing Details in Master Table
In a situation where the number of detail records in the child table per master record is fixed (or has a fixed maximum) and where usually all detail records are queried with the master, you may consider adding the detail columns to the master table. This denormalization works best when the number of records in the detail table is small. This way you will reduce the number of joins during queries. The child table will be removed and merged with the parent table. As this denormalization occurs, an anomaly will be created.
π Definition β Storing Details in Master Table: Merging a fixed or limited number of detail records into the master table to eliminate joins.
c. Example of Storing Details in Master Table
The phenomenon of storing details in the master table can be illustrated with the help of the following figure: Considering the image, the normalization of the database resulted in two separate tables for Order and Order Details as shown in the figure. In order to speed up the query processing time, these two tables will be merged together i.e. denormalization. In this way, no joins will be required, saving space and speeding up the query processing time and ultimately improving the database performance.
π Example: Merge Order and Order Details tables into a single table, eliminating the join between them. This speeds up queries but introduces data redundancy and potential anomalies.
d. Short Circuit Key
The short circuit key ensures the relationships between the required entities. In a database that contains multiple levels of master tables and there is a need to query the lowest and highest level records only, consider creating short-circuit keys. These new foreign key definitions directly link the lowest level detail records to higher-level grandparent records. The result can produce fewer table joins when queries execute. It is appropriate when queries frequently require values from a grandparent and grandchild, but not from the parent.
π Definition β Short Circuit Key: A direct foreign key link between grandchild and grandparent tables, bypassing the parent table to reduce joins.
e. Example of Short Circuit Key
As per the following example, before denormalization of the database, all entities have parent-child relationship but no grandparent-grandchild relationship exists. In certain scenarios where grandparent-grandchild is required to process the query, a short circuit key comes in as a solution. As shown in the figure below, after denormalization, grandparent-grandchild also exists in the database. To achieve this, extra foreign keys will be required.
π Example: In a structure with Table A (grandparent) β Table B (parent) β Table C (grandchild), add a direct foreign key from Table C to Table A so queries about C and A don't need to join through B.
f. Current Indicator Column
This type of denormalization can be used in similar situations to the end date column technique. It can even be used in addition to an end date. It is a very common type of denormalization. Suppose most queries are to find the most current detail record. With this type of requirement, you could consider adding a new column to the details table to represent the currently active record. In simple words, frequently updated columns should have a last-updated timestamp and a status column is added to show updated or old record.
π Definition β Current Indicator Column: A status column added to a table to identify which record is currently active, speeding up queries for the most recent data.
g. Example of Current Indicator Column
Consider a table where the stock price of a company is being recorded. The stock price changes very frequently and the database also records the last time of update. But as the records change frequently, getting the latest stock price is difficult without having a status column in the table. This is where a current indicator column is added (as a result of denormalization) with the motive of having status of all records. This column will tell whether a specific record/stock price is the latest one or not.
π Example: In a stock_price table, add a column "is_latest" (Y/N) to instantly identify the most recent stock price without scanning all records.
h. Storing Calculated Value
When a calculation is frequently executed during queries, it can be worthwhile storing the results of the calculation. Queries that require calculation require more time to execute, and to reduce this running time, calculated fields should be pre-stored in the table. Through this type of denormalization, the calculation does not need to be performed during a query and the source values do not need to be looked up every time the calculated value is required. As a result, query processing time will be shortened.
π Definition β Storing Calculated Value: Pre-computing and storing frequently requested calculated values to avoid runtime computation during queries.
i. Example of Storing Calculated Value
Consider a database keeping records of cars with engine number, fuel tank size, and average fuel consumption per liter. A query requiring total kilometers that can be traveled will require calculation which will ultimately increase the processing time. After denormalization, a column is added calculating the number of kilometers that a car can travel as per its fuel capacity. In this way, the calculation is pre-stored and the calculation will not be required during query processing, reducing running time.
π Example: For a car table with columns fuel_tank_size (liters) and avg_consumption (km/liter), add a column total_range = fuel_tank_size * avg_consumption so queries for total kilometers don't need runtime calculation.
β Key Takeaways
Denormalization is a performance-tuning technique that trades data redundancy for faster query execution by reducing the number of joins. It should only be applied when specific performance issues indicate a need, not as a default design approach. The five key denormalization scenarios are: storing end dates for BETWEEN queries, merging fixed-size detail tables into master tables, using short-circuit keys to skip intermediate tables, adding current indicator columns for frequently updated records, and storing pre-computed calculated values to avoid runtime calculations. Each scenario introduces potential anomalies and inconsistency risks that must be managed. Denormalization is always a tradeoff between database structure integrity and query running time.
π§ Quick Revision Questions
- What is denormalization and what is its primary purpose?
- How does denormalization differ from normalization in terms of focus?
- In which scenario would you use a short circuit key, and what problem does it solve?
- How does storing a current indicator column improve performance in frequently updated tables?
- What is the tradeoff involved when storing calculated values in a table instead of computing them at query time?
π Lecture 15 β Introduction to Oracle 11g on Cloud
π Overview: This lecture introduces Oracle 11g as a cloud-hosted database service, explaining how it provides full Oracle Database features without requiring hardware maintenance, backup knowledge, or software upgrades. It provides step-by-step instructions for logging into the Oracle Cloud environment and accessing SQL commands to begin database management and development.
ποΈ Topics Covered
The lecture covers two main sections: an introduction to Oracle 11g as a cloud service hosted by Oracle, providing full database features without hardware or backup responsibilities; and a detailed step-by-step login procedure for accessing Oracle 11g on the cloud, including navigating the workspace and using SQL commands.
π Lecture Summary
B. Introduction
This section introduces Oracle 11g on Cloud as a service hosted entirely by Oracle. Users get full access to all features and operations available with Oracle Database, but Oracle manages the virtual machine (VM) and cloud storage. This means you can perform all database management and development operations without purchasing and maintaining hardware, without knowing backup and recovery commands, and without performing complex tasks like database software upgrades and patching.
π Definition β Oracle 11g on Cloud: A cloud service hosted by Oracle that provides full access to Oracle Database features, with Oracle managing the VM and cloud storage, allowing users to focus on database management and development without hardware or maintenance responsibilities.
π‘ Why this matters: This service removes the burden of infrastructure management, making database work accessible to users without system administration expertise.
C. Login to Cloud
This section provides a step-by-step guide to accessing Oracle 11g on the cloud.
Step 01: Follow the address https://apex.oracle.com/en/ to access Oracle 11g.
Step 02: Click on the Sign In button on the upper right corner of the page. Enter the following credentials:
- Workspace Name
- Username
- Password
Step 03: Click on the SQL Workshop tab at the top-mid of the page.
Step 04: Click on the SQL Commands tab from the drop-down list to access the code or enter SQL Statements/Commands, then click Run to see the results. Click on the History Tab to access saved code.
π Formula (Procedure): Login Steps: 1. Go to https://apex.oracle.com/en/ β 2. Sign In with Workspace Name, Username, Password β 3. Click SQL Workshop β 4. Click SQL Commands β 5. Enter SQL and Run β 6. Use History Tab for saved code.
π Example: To run a SQL command, after logging in, navigate to SQL Workshop > SQL Commands, type SELECT * FROM employees; in the code area, click Run, and the results will display below. To retrieve previously run commands, click the History Tab.
β Key Takeaways
This lecture introduces Oracle 11g as a fully managed cloud database service, eliminating the need for hardware, backup expertise, and software patching. The login procedure is a straightforward four-step process: accessing the Oracle apex website, signing in with workspace credentials, navigating to SQL Workshop, and using SQL Commands to execute statements. The History Tab allows users to access previously saved code, enabling efficient workflow. For exam purposes, remember that Oracle hosts the VM and storage, and that SQL Workshop contains the SQL Commands tab for writing and running queries. The key advantage is that all complex maintenance tasks are handled by Oracle.
π§ Quick Revision Questions
- What are the key responsibilities Oracle handles in the Oracle 11g Cloud service?
- List the four main steps to log into Oracle 11g on the cloud.
- What three credentials are required to sign in to Oracle 11g Cloud?
- After logging in, which tab do you click to access SQL Commands?
- Where can you find previously executed SQL commands?