Informal design guidelines for

relational schemas

1
 Informal design guidelines for
relational schemas

1. Semantics of the attributes should be clear in the relation

schema
2. Reducing redundant information in tuples
3. Reducing NULL values in tuples
4. Do not allow spurious (invalid / false) tuples

2
Semantics of the attributes should be clear in the
relation schema

3
Semantics of the attributes should be clear in the
relation schema

Guideline 1
Design a relation schema so that it is easy to explain its
meaning. Do not combine attributes of different real
world objects together in a single relation schema.
Design of a schema is good if its semantics is well-defined.

Violating this guideline results in data redundancy & update

anomaly – insertion anomaly, deletion anomaly and update
anomaly.

4
Violating guideline 1

5
Redundancy!

6
 Poor Design
Employee
Employee Employee
Employee Employee
Employee Department
Department Department
Department Department
Department
Employee Employee Employee Department Department Department
SSN
SSN Name
Name Salary
Salary Number
Number Name
Name Location
Location
SSN Name Salary Number Name location
111
111 Sam
Sam 2000
2000 123
123 SCOPE
SCOPE SJT
SJT
112
112 Ram
Ram 3000
3000 123
123 SCOPE
SCOPE SJT
SJT
113
113 Tom
Tom 1000
1000 123
123 SCOPE
SCOPE SJT
SJT
114
114 Jeff
Jeff 5000
5000 123
123 SCOPE
SCOPE SJT
SJT
115
115 Chris
Chris 7000
7000 124
124 SBST
SBST SMV
SMV
116
116 Mike
Mike 1000
1000 124
124 SBST
SBST SMV
SMV

29/10/22 7
Poor Design

• Redundant Data
• Update Anomalies

29/10/22 8
Insertion anomaly
To insert new employee tuple, the employee has to work in
some dept. Also inserting new department needs some
employee to work in the department.
(dname, mgr_ssn) each time an employee data is added to
the relation.
Deletion anomalies.
Deleting the last employee, deletes details of the department
as well.
Modification anomalies
Changing the manager for dept 5 should be done all places
or else the database becomes inconsistent.

9
 Insertion Anomalies
Employee
Employee Employee
Employee Employee
Employee Department
Department Department
Department Department
Department
Employee Employee Employee Department Department Department
SSN
SSN Name
Name Salary
Salary Number
Number Name
Name Location
Location
SSN Name Salary Number Name Location
111
111 Sam
Sam 2000
2000 123
123 SCOPE
SCOPE SJT
SJT
111 Sam 2000 123 SCOPE
112 Ram 3000
112 Ram 3000
113 Tom 4000
113 Tom 4000
124 SBST SMV

Null Values waste storage space

Null can have multiple interpretations
•Attributes does not apply to this tuple
•Attribute value is unknown
•The value is known but absent. Not recorded yet.

29/10/22 10
 Deletion Anomalies
Employee Employee Employee Department Department Department
SSN Name Salary Number Name Location
111 Sam 2000 123 SCOPE SJT

29/10/22 11
Modification Anomalies
Employee Employee Employee Department Department Department
SSN Name Salary Number Name Location
111 Sam 2000 123 SCOPE SJT

112 Ram 3000 123 SCOPE SJT

113 Tom 1000 123 SCOPE SJT

114 Jeff 5000 123 SCOPE SJT

115 Chris 7000 124 SBST SMV

116 Mike 1000 124 SBST SMV

29/10/22 12
Informal design guidelines

Guideline 2 (a restatement of guideline 1)

Design the relation schemas so that no insertion,
deletion, or modification anomalies are present in
the relations.
If any anomalies are present, note
them clearly and make sure that the programs
that update the database will operate correctly.

13
Informal design guidelines

Guideline 3
As far as possible, avoid placing attributes in a relation
whose values may frequently be NULL.
If NULLs are
unavoidable, make sure that they apply in exceptional cases
only and do not apply to a majority of tuples in the relation.

STUDENT
Reg_no, Fname, …, Visa_status, Address…
STUDENT
Reg_no, Fname, …, Address, …
STUDENT_VISA
Reg_no, Visa_status

14
Spurious tuples

15
State of EMP_LOCS & EMP_PROJ1

16
Natural join

17
Informal design guidelines

Guideline 4
Design relation schemas so that they can be joined with
equality conditions on attributes that are appropriately
related by (foreign key, primary key) pairs in a way that
guarantees that no spurious tuples are generated.
Avoid relations that contain matching attributes that are not
(foreign key, primary key) combinations because joining on
such attributes may produce spurious tuples.

18
Problems you may encounter in relation schema
design (Summary)

• Anomalies that cause redundant work to be done during

insertion into and modification of a relation, and that may
cause accidental loss of information during a deletion from a
relation
• Waste of storage space due to NULLs and the difficulty of
performing selections, aggregation operations, and joins due
to NULL values
• Generation of invalid data during joins on relations with
matched attributes that may not represent a proper (foreign
key, primary key) relationship

19
Example

Consider the following relational schema

STUDENT_COURSE(Reg_no, Sname, DoB, Address,
Crs_Code, Crs_name, Credits, Hours).
Explain, with example, update anomaly, deletion anomaly and insertion
anomaly that may be present in a state of the above table.

20