Module 30

Partha Pratim
Das

Objectives &
Outline Database Management Systems
Database Design
Process Module 30: Relational Database Design/10: Design Summary and Temporal Data
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example
Partha Pratim Das
Temporal
Databases
Temporal Data
Department of Computer Science and Engineering
Uni / Bi Temporal
Indian Institute of Technology, Kharagpur
Example

Module Summary ppd@cse.iitkgp.ac.in

Database Management Systems Partha Pratim Das 30.1

 Module Recap PPD

Module 30

Partha Pratim • Understood multi-valued dependencies to handle attributes that can have multiple
Das
values
Objectives &
Outline • Learnt Fourth Normal Form and decomposition to 4NF
Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Database Management Systems Partha Pratim Das 30.2

 Module Objectives PPD

Module 30

Partha Pratim • To summarize the database design process

Das
• To explore the issues with temporal data
Objectives &
Outline

Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Database Management Systems Partha Pratim Das 30.3

 Module Outline PPD

Module 30

Partha Pratim • Database-Design Process

Das
• Modeling Temporal Data
Objectives &
Outline

Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Database Management Systems Partha Pratim Das 30.4

 PPD

Module 30

Partha Pratim
Das

Objectives &
Outline

Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example
Database Design Process
Module Summary

Database Management Systems Partha Pratim Das 30.5

 Design Goals

Module 30
• Goal for a relational database design is:
Partha Pratim
Das ◦ BCNF / 4NF
Objectives &
◦ Lossless join
Outline
◦ Dependency preservation
Database Design
Process • If we cannot achieve this, we accept one of
Normal Forms
Normalization &
De-Normalization
◦ Lack of dependency preservation
Bad Design ◦ Redundancy due to use of 3NF
LIS Example

Temporal
• Interestingly, SQL does not provide a direct way of specifying functional dependencies
Databases
Temporal Data
other than superkeys.
Uni / Bi Temporal
Example
• Can specify FDs using assertions, but they are expensive to test, (and currently not
Module Summary
supported by any of the widely used databases!)
• Even if we had a dependency preserving decomposition, using SQL we would not be
able to efficiently test a functional dependency whose left hand side is not a key

Database Management Systems Partha Pratim Das 30.6

 Further Normal Forms

Module 30
• Further NFs
Partha Pratim
Das ◦ Elementary Key Normal Form (EKNF)
Objectives &
◦ Essential Tuple Normal Form (ETNF)
Outline
◦ Join Dependencies And Fifth Normal Form (5 NF)
Database Design
Process ◦ Sixth Normal Form (6NF)
Normal Forms
Normalization &
◦ Domain/Key Normal Form (DKNF)
De-Normalization
Bad Design
• Join dependencies generalize multivalued dependencies
LIS Example
◦ lead to project-join normal form (PJNF) (also called fifth normal form)
Temporal
Databases
Temporal Data
• A class of even more general constraints, leads to a normal form called domain-key
Uni / Bi Temporal normal form.
Example

Module Summary
• Problem with these generalized constraints: are hard to reason with, and no set of
sound and complete set of inference rules exists.
• Hence rarely used

Database Management Systems Partha Pratim Das 30.7

 Overall Database Design Process

Module 30
• We have assumed schema R is given
Partha Pratim
Das ◦ R could have been generated when converting E-R diagram to a set of tables
Objectives &
◦ R could have been a single relation containing all attributes that are of interest
Outline
(universal relation)
Database Design
Process ◦ Normalization breaks R into smaller relations
Normal Forms
Normalization &
◦ R could have been the result of some ad hoc design of relations, which we then
De-Normalization
test/convert to normal form
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Database Management Systems Partha Pratim Das 30.8

 ER Model and Normalization

Module 30

Partha Pratim • When an E-R diagram is carefully designed, identifying all entities correctly, the tables
Das
generated from the E-R diagram should not need further normalization
Objectives &
Outline • However, in a real (imperfect) design, there can be functional dependencies from
Database Design non-key attributes of an entity to other attributes of the entity
Process
Normal Forms ◦ Example: an employee entity with attributes
Normalization &
De-Normalization department name and building,
Bad Design
LIS Example
and a functional dependency
Temporal department name → building
Databases
Temporal Data
◦ Good design would have made department an entity
Uni / Bi Temporal
Example
• Functional dependencies from non-key attributes of a relationship set possible, but rare
Module Summary — most relationships are binary

Database Management Systems Partha Pratim Das 30.9

 Denormalization for Performance

Module 30
• May want to use non-normalized schema for performance
Partha Pratim
Das • For example, displaying prereqs along with course id, and title requires join of course
Objectives & with prereq
Outline

Database Design
◦ Course(course id, title,. . . )
Process ◦ Prerequisite(course id, prereq)
Normal Forms
Normalization &
De-Normalization
• Alternative 1: Use denormalized relation containing attributes of course as well as
Bad Design prereq with all above attributes: Course(course id, title, prereq,. . . )
LIS Example

Temporal
◦ faster lookup
Databases
Temporal Data
◦ extra space and extra execution time for updates
Uni / Bi Temporal ◦ extra coding work for programmer and possibility of error in extra code
Example

Module Summary
• Alternative 2: Use a materialized view defined as Course ./ Prerequisite
◦ Benefits and drawbacks same as above, except no extra coding work for
programmer and avoids possible errors

Database Management Systems Partha Pratim Das 30.10

 Other Design Issues

Module 30
• Some aspects of database design are not caught by normalization
Partha Pratim
Das • Examples of bad database design, to be avoided:
Objectives & Instead of earnings (company id, year, amount ), use
Outline

Database Design
◦ earnings 2004, earnings 2005, earnings 2006, etc., all on the schema (company id,
Process earnings).
Normal Forms
Normalization &
De-Normalization
. Above are in BCNF, but make querying across years difficult and needs new
Bad Design table each year
LIS Example

Temporal
◦ company year (company id, earnings 2004, earnings 2005, earnings 2006 )
Databases
Temporal Data
. Also in BCNF, but also makes querying across years difficult and requires new
Uni / Bi Temporal attribute each year.
Example

Module Summary
. Is an example of a crosstab, where values for one attribute become column
names
. Used in spreadsheets, and in data analysis tools

Database Management Systems Partha Pratim Das 30.11

 LIS Example for 4NF

Module 30
• Consider a different version of relation book catalogue having the following attributes:
Partha Pratim
Das
◦ book title
◦ book catalogue, author lname: A book title may be associated with more than one
Objectives &
Outline author.
Database Design
Process
• book title {book title, author fname, author lname, edition}
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Figure: book catalogue

Database Management Systems Partha Pratim Das 30.12
 LIS Example 4NF (2)

Module 30

Partha Pratim
Das

Objectives &
Outline

Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Figure: book catalogue
Temporal Data
Uni / Bi Temporal
Example • Since the relation has no FDs, it is already in BCNF.
Module Summary
• However, the relation has two nontrivial MVDs
book title  {author fname, author lname} and book title  edition.
Thus, it is not in 4NF.
• Nontrivial MVDs must be decomposed to convert it into a set of relations in 4NF.
Database Management Systems Partha Pratim Das 30.13
 LIS Example 4NF (3)

Module 30

Partha Pratim
Das
• We decompose book catalogue into book author
Objectives & and book edition because:
Outline

Database Design
◦ book author has trivial MVD
Process
Normal Forms
book title  {author fname, author lname}
Normalization &
De-Normalization
◦ book edition has trivial MVD
Bad Design
Figure: book author book title  edition.
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Figure: book edition

Database Management Systems Partha Pratim Das 30.14

 Temporal Databases PPD

Module 30

Partha Pratim
Das

Objectives &
Outline

Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example
Temporal Databases
Module Summary

Database Management Systems Partha Pratim Das 30.15

 Temporal Databases

Module 30
• Some data may be inherently historical because they include time-dependent /
Partha Pratim
Das
time-varying data, such as:
◦ Medical Records
Objectives &
Outline ◦ Judicial records
Database Design
Process
◦ Share prices
Normal Forms ◦ Exchange rates
Normalization &
De-Normalization ◦ Interest rates
Bad Design
LIS Example
◦ Company profits
Temporal ◦ etc.
Databases
Temporal Data • The desire to model such data means that we need to store not only the respective
Uni / Bi Temporal
Example
value but also an associated date or a time period for which the value is valid. Typical
Module Summary
queries expressed informally might include:
◦ Give me last month’s history of the Dollar-Pound Sterling exchange rate.
◦ Give me the share prices of the NYSE on October 17, 1996.
• Temporal databases provide a uniform and systematic way of dealing with historical data
Source: https://www.cs.uct.ac.za/mit notes/database/htmls/chp18.html
Database Management Systems Partha Pratim Das 30.16
 Temporal Data

Module 30
• Temporal data have an association time interval during which the data are valid.
Partha Pratim
Das • A snapshot is the value of the data at a particular point in time
Objectives &
Outline
• In practice, database designers may add start and end time attributes to relations
Database Design • For example, course(course id, course title) is replaced by
Process
Normal Forms
course(course id, course title, start, end)
Normalization &
De-Normalization ◦ Constraint: no two tuples can have overlapping valid times and are Hard to enforce
Bad Design
LIS Example
efficiently
Temporal ◦ Foreign key references may be to current version of data, or to data at a point in
Databases
Temporal Data
time
Uni / Bi Temporal
Example
. For example, student transcript should refer to course information at the time
Module Summary
the course was taken

Database Management Systems Partha Pratim Das 30.17

 Temporal Database Theory

Module 30 • Model of Temporal Domain: Single-dimensional linearly ordered which may be

Partha Pratim
Das
◦ Discrete or dense
◦ Bounded or unbounded
Objectives &
Outline ◦ Single dimensional or multi-dimensional
Database Design ◦ Linear or non-linear
Process
Normal Forms • Timestamp Model
Normalization &
De-Normalization
Bad Design
• Temporal ER model by adding valid time to
LIS Example
◦ Attributes: address of an instructor at different points in time
Temporal
Databases ◦ Entities: time duration when a student entity exists
Temporal Data
Uni / Bi Temporal
◦ Relationships: time during which a student attended a course
Example ◦ But no accepted standard
Module Summary
• Temporal Functional Dependency Theory
• Temporal Logic
• Temporal Query Languge: TQuel [1987], TSQL2 [1995], SQL/Temporal [1996],
SQL/TP [1997]
Database Management Systems Partha Pratim Das 30.18
 Modeling Temporal Data: Uni / Bi Temporal

Module 30
• There are two different aspects of time in temporal databases.
Partha Pratim
Das
◦ Valid Time: Time period during which a fact is true in real world, provided to the
system.
Objectives &
Outline ◦ Transaction Time: Time period during which a fact is stored in the database, based
Database Design on transaction serialization order and is the timestamp generated automatically by
Process
Normal Forms the system.
Normalization &
De-Normalization • Temporal Relation is one where each tuple has associated time; either valid time or
Bad Design
LIS Example
transaction time or both associated with it.
Temporal ◦ Uni-Temporal Relations: Has one axis of time, either Valid Time or Transaction
Databases
Temporal Data
Time.
Uni / Bi Temporal
◦ Bi-Temporal Relations: Has both axis of time – Valid time and Transaction time.
Example

Module Summary
It includes Valid Start Time, Valid End Time, Transaction Start Time, Transaction
End Time.

Source: https://www.mytecbits.com/oracle/oracle-database/what-is-temporal-database
Database Management Systems Partha Pratim Das 30.19
 Modeling Temporal Data: Example (1)

Module 30
• Example.
Partha Pratim
Das ◦ Let’s see an example of a person, John:
Objectives & . John was born on April 3, 1992 in Chennai.
Outline
. His father registered his birth after three days on April 6, 1992.
Database Design
Process . John did his entire schooling and college in Chennai.
Normal Forms
Normalization &
. He got a job in Mumbai and shifted to Mumbai on June 21, 2015.
De-Normalization
Bad Design
. He registered his change of address only on Jan 10, 2016.
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example

Module Summary

Source: https://www.mytecbits.com/oracle/oracle-database/what-is-temporal-database

Database Management Systems Partha Pratim Das 30.20

 Modeling Temporal Data: Example (2)

Module 30

Partha Pratim
• John’s Data In Non-Temporal Database
Das

Objectives &
• John was born on April 3, 1992
Outline in Chennai.
Database Design • His father registered his birth af-
Process ter three days on April 6, 1992.
Normal Forms
Normalization &
• John did his entire schooling and
De-Normalization college in Chennai.
Bad Design
LIS Example
• He got a job in Mumbai and
shifted to Mumbai on June 21,
Temporal In a non-temporal database, John’s address is entered as Chennai from 1992. When he 2015.
Databases
Temporal Data
registers his new address in 2016, the database gets updated and the address field now • He registered his change of ad-
shows his Mumbai address. The previous Chennai address details will not be available. dress only on Jan 10, 2016.
Uni / Bi Temporal
Example
So, it will be difficult to find out exactly when he was living in Chennai and when he
moved to Mumbai.
Module Summary

Database Management Systems Partha Pratim Das 30.21

 Modeling Temporal Data: Example (3)

Module 30
• Uni-Temporal Relation (Adding Valid Time To John’s Data)
Partha Pratim
Das

Objectives &
Outline

Database Design • John was born on April 3, 1992

Process in Chennai.
Normal Forms
Normalization &
• His father registered his birth af-
De-Normalization ter three days on April 6, 1992.
Bad Design
LIS Example
• John did his entire schooling and
college in Chennai.
Temporal
Databases
• The valid time temporal database contents look like this:
• He got a job in Mumbai and
Temporal Data shifted to Mumbai on June 21,
Name, City, Valid From, Valid Till 2015.
Uni / Bi Temporal
Example • Johns father registers his birth on 6th April 1992, a new database entry is made: • He registered his change of ad-
Module Summary
Person(John, Chennai, 3-Apr-1992, ∞). dress only on Jan 10, 2016.
• On January 10, 2016 John reports his new address in Mumbai:
Person(John, Mumbai, 21-June-2015, ∞).
◦ The original entry is updated:
Person(John, Chennai, 3-Apr-1992, 20-June-2015).

Source: https://www.mytecbits.com/oracle/oracle-database/what-is-temporal-database
Database Management Systems Partha Pratim Das 30.22
 Modeling Temporal Data: Example (4)

Module 30
• Bi-Temporal Relation (John’s Data Using Both Valid And Transaction Time)
Partha Pratim
Das

Objectives &
Outline • John was born on April 3, 1992
Database Design in Chennai.
Process
Normal Forms
• His father registered his birth af-
ter three days on April 6, 1992.
Normalization &
De-Normalization
• John did his entire schooling and
Bad Design • The database contents look like this: college in Chennai.
LIS Example Name, City, Valid From, Valid Till, Entered, Superseded
• He got a job in Mumbai and
Temporal
Databases
• Johns father registers his birth on 6th April 1992: shifted to Mumbai on June 21,
Person(John, Chennai, 3-Apr-1992, ∞, 6-Apr-1992, ∞). 2015.
Temporal Data
Uni / Bi Temporal • On January 10, 2016 John reports his new address in Mumbai: • He registered his change of ad-
Example Person(John, Mumbai, 21-June-2015, ∞, 10-Jan-2016, ∞). dress only on Jan 10, 2016.
Module Summary ◦ The original entry is updated as:
Person(John, Chennai, 3-Apr-1992, 20-June-2015, 6-Apr-1992 ,
10-Jan-2016).

Source: https://www.mytecbits.com/oracle/oracle-database/what-is-temporal-database

Database Management Systems Partha Pratim Das 30.23

 Modeling Temporal Data: Summary

Module 30
• Advantages
Partha Pratim
Das
◦ The main advantages of this bi-temporal relations is that it provides historical and
roll back information.
Objectives &
Outline . Historical Information – Valid Time.
Database Design
Process
. Rollback Information – Transaction Time.
Normal Forms ◦ For example, you can get the result for a query on John’s history, like: Where did
Normalization &
De-Normalization John live in the year 2001?. The result for this query can be got with the valid time
Bad Design
LIS Example
entry. The transaction time entry is important to get the rollback information.
Temporal • Disadvantages
Databases
Temporal Data
◦ More storage
Uni / Bi Temporal ◦ Complex query processing
Example

Module Summary
◦ Complex maintenance including backup and recovery

Source: https://www.mytecbits.com/oracle/oracle-database/what-is-temporal-database
Database Management Systems Partha Pratim Das 30.24
 Module Summary

Module 30

Partha Pratim • Discussed aspects of the database design process

Das
• Studied the issues with temporal data
Objectives &
Outline

Database Design
Process
Normal Forms
Normalization &
De-Normalization
Bad Design
LIS Example

Temporal
Databases
Temporal Data
Uni / Bi Temporal
Example
Slides used in this presentation are borrowed from http://db-book.com/ with kind
Module Summary permission of the authors.
Edited and new slides are marked with “PPD”.

Database Management Systems Partha Pratim Das 30.25