UNIT-

III
RELATIONAL DATABASE
DESIGN
Dr. Yogita Pagar (Bhise)

DEPARTMENT OF COMPUTER ENGINEE

RING
K.K.W.I.E.E.R., Nashik
Relational Model


Advantages :
 Ease of use,
 Flexibility,
 Security
 Data Independence
 DML

Basic Concepts:
 Tables
 Tuple
 Attribute
 Domain
 CODD’s 12 RULES OF
RELATIONAL DATABASE
OVERVIEW OF CODD’s RULE

 A relational database management system (RDBMS)

is a database management system (DBMS) that is based on
the relational model as introduced by E. F. Codd.
 A short definition of an RDBMS may be a DBMS in whc ih
data is stored in the form of tables and the relationship
among the data is also stored in the form of tables.
 E . F . Codd, the famous mathematician has introduced 12
rules (0-12)for the relational model for databases
commonly known as Codd's rules. The rules mainly define
what is required for a DBMS for it to be
considered relational, i.e., an RDBMS.
 Rule 0: Foundation Rule


This rule states that all subsequent rules are
based on the notation that in order for a
database to be considered relational, it must
use it’s relational facilities exclusively to
manage the database.
Rule 1: INFORMATION RULE

 All information in the database is to be represented in o

neand
only one way.
 A l l information in an RDB is represented as values in
het tables.
 This is achieved by values in column and rows of tables.
 A l l information including table names, column names
nad column data types should be available in same table
within the database.
 T h e basic requirement of the relational model.
Rule 2: GUARANTEED ACCESS RULE

 Each unique piece of data should

be accessible by:table name+primary key(row) +
attribute(column).
 A l l data are uniquely identified and accessible via
htsi identity.
 M o s t RDBMS do not make the definition of the primary
key mandatory and are deficient to that extent .
Rule 3 : Systematic treatment of null values

 " N u l l values (distinct from the empty character string or

a string of blank characters and distinct from zero or any
other number) are supported in fully relational DBMS for
representing missing information and inapplicable
information in a systematic way, independent of data type."
 NULLs may mean: Missing data, Not applicable
 Should be handled consistently - Not Zero or Blank
 Primary keys — Not NULL
 Expressions on NULL should give NULL.
 S e p a r a t e handling of missing and/or non applicable
data.
 T h i s is distinct to zero or empty strings
Rule 4:DATABASE DESCRIPTION
RULE -Active Online Catalog

 T h e data base description is represented at the logical

elvel in the same way as-ordinary data, so that authorized
users can apply the same relational language to its
interrogation as they apply to the regular data.
 T h e authorized users can access the database structure
by using common language i.e. SQL
Rule 5: COMPREHENSIVE DATA
SUBLANGUAGE

 A relational system may support several languages nad

various modes of terminal use .However, there must be at
least one language whose statements are expressible, per
some well-defined syntax, as character strings and that is
comprehensive in supporting all the following items :
 D a t a Definition (create,insert,update)
 V i e w Definition
 D a t a Manipulation (alter,delete,truncate)
 Integrity Constraints (primary key,foreign key,null values)
 Authorization (GRANT , REVOKE)
 Tr a n s a c t i o n boundaries (begin,commit,rollbacketc)

Every RDBMS should provide a language to allow the user

to query the contents of the RDBMS and also manipulate the
contents of the RDBMS.
Rule 6: VIEW UPDATING RULE

 View = ”Virtual table”,temporarily

derived from base tables.
 Example: If a view is formed as join of 3 tables,
changes to view should be reflected in base tables.
 N o t updatable: View does not have NOT-NULL attribute
of base table.
 I t allow the update of simple theoretically updatable views,
but disallow attempts to update complex views.
BELOW IS A TABLE NAMED ‘STUDENT‘.
RDBMS GIVES US THE FACILITY TO VIEW
ONLY SOME PARTICULAR FIELDS ACCORDING
TO OUR NEED WHICH ARE DIRECTLY
ACCESSED FROM BASE TABLES WHEN
REQUIRED.

name class Marks PUPIN

NUMBER
SONALI BCA-2 95 17231
TAMANNA BCA-2 90 17236
RAJWINDER BCA-2 90 17267
SAKSHI BCA-2 86 17893
SADHANA BCA-2 82 17453
TO VIEW ONLY THE NAME AND MARKS
OF THE STUDENT TABLE WE CAN WRITE
THE FOLLOWING SYNTAX:

CREATE VIEW RECORD

AS SELECT NAME,MARKS FROM STUDENT;

VIEW IS CREATED.

SELECT * FROM RECORD;

n
a
m
e

M
a
r
k
s
 RULE 7 : HIGH-LEVEL INSERT ,
UPDATE AND DELETE
 T h i s rule states that insert, update, and delete operations
should be supported for any retrievable set rather than just
for a single row in a single table.
 I t also perform the operation on multiple ow
r
simultaneously .
 T h e r e must be delete, updating and insertion at the each
level of operation. Set operation like union, all union ,
insertion and minus should also supported.
 EXAMPLE:
Suppose if we need to change ID
then it will reflect everywhere automatically.
 EXAMPLE

TABLE CREATED
NAME ROLL_N
 Create table:
CLASS O
SQL>CREATE TABLE STUDENT_DATA
{
NAME VARCHAR 2(20),
ROLL_NO VARCHAR 2(10
),
CLASS VARCHAR 2(20);
};
INSERT ION:
SQL>INSERT INTO STUDENT_DATA(‘&NAME’,&ROLL_NO,’&CLASS’);
SQL>ENTER VALUE FOR NAME:KIRAN
SQL>ENTER VALUE FOR ROLL_NO:4556
SQL>ENTER VALUE FOR CLASS:BCA
SQL>/
NAME ROLL_N
CLASS O
KIRAN 4566 BCA
RAHU 3455
L BCA
RULE 8 :LOGICAL DATA INDEPENDENCE RULE

 W h a t is independence?
The ability to modify schema definition in on level without
affecting schema definition in the next higher level is called
data independence

 The ability to change the logical (conceptual) schema without

changing the External schema (User View) is called logical
data independence.
 EXAMPLE:
The addition or removal of new entities, attributes, or
relationships to the conceptual schema should be possible
without having to change existing external schemas or having
to rewrite existing application programs.
RULE 9:PHYSICAL DATA INDEPENDENCE

 T h e ability to change the physical schema without changing

hte logical schema is called physical data independence.

 T h i s is saying that users shouldn’t be concerned about how hte

data is stored or how it’s accessed. In fact, users of the data need
only be able to get the basic definition of the data they need.

 EXAMPLE:
A change to the internal schema, such as using different file
organization or storage structures, storage devices, or indexing
strategy, should be possible without having to change the
conceptual or external schemas.
 RULE 10 : INTEGRITY
INDEPENDENCE
RULE

 Integrity constraints must be specified independently orfm

application program.
 W e should able to make changes in
integrity constraints without changing application
program
 D a t a integrity refers to maintaining assuring the
accuracy and consistency of data over its entire life cycle.
 F i r s t insure that correct data type is used.

 C h e c k constraints: these allow column value to be checked

agenized other column before insertion is allowed.
 RULE 11 : DISTRIBUTION INDEPENDECE
RULE

 “ T H E RELATION DATA BASE MANAGEMENT HAS

DISTRIBUTION INDEPENDENCE”

 Distribution independence implies that user should not

have to be aware of whether a database is distributed at
different sites or not.
 A p p l i c a t i o n program and adhoc request are not affected
by the change in distribution of physical data. Application
program will work even if the programs and data are
moved on different site

 T h e RDBMS may spread across the more one system or

 RULE 12 : NON-SUBVERSION RULE

 There should be no way to modify to database

structure other then through the multiple row data base
language(SQL).

Example:

A relational system has a low-level (single-record-at-a-time)

language, that low level cannot be used to subvert or bypass
the integrity Rules and constraints expressed in the higher
level relational language (multiple-records-at-a-time).”
Question 1

What is true regarding NULL value requirements

confirming to Codd rule?
a. Null value should be zero.
b. Null value should be space.
c. Null value should represent missing information.
 d. Either a or b.
Answer :
 c. Null value should represent missing
information.
According to Codd rule, Null value should not be any
regular data like zero, spaces etc, but should
represent that data is not available.
Question 2

According to Codd rule, how one should be able to access

information about data structures like databases, tables
etc.

a. Should be directly accessible via all programming languages.

[Link] be directly accessible via same query language used for
data manipulation (e.g. select, update statements etc)
c. Should be directly accessible via XML
 d. All of the above
Answer :
 b. Should be directly accessible via same query language used
for data manipulation. For example, SQL can be used to
retrieve information about the tables, the column types etc.
Question 3

Which is true regarding multi row update?

a. Multiple row updates should be prohibited.

[Link] row updates should be allowed only on
tables without null values.
c. Multiple row updates should be possible.
 d. Multiple row updates should be allowed only on
integer data types

Answer :
 c. Multiple row updates should be possible.
Relational Integrity


Domain Integrity Constraints

 Not Null
 Unique
 Default
 Check


Referential Integrity Constraints

 Foreign Key with On delete cascade and On delete set null


Enterprise Constraints
 Features of Good Relational Design

● Avoid Redundant data – distribute information

● Easy access – data available in multiple
● table Ensure relationship among attributes
● Preservation of database integrity constraints
● Provide functionality – data processing and reporting
needs
● Data entry, updates, deletions, retrieval,
summarization and reporting should be efficient
● Self documenting
 Normalization & Need
● Database Normalization is a technique of
– organizing the data in the database.
– decomposing tables to eliminate data redundancy and
undesirable characteristics like Insertion, Update and
Deletion Anomalies
– multi-step process that puts data into tabular form by
removing duplicated data from the relation tables.
● Normalization is used for mainly two purpose,
– Eliminating redundant (useless) data.
– Ensuring data dependencies make sense i.e
data is logically stored.
 Anomalies
●
Consider Student (S_id, S_name,S_Address, S_subject
opted)
● Update Anomaly : To update address of a student who occurs
twice or more than twice in a table, we will have to update
S_Address column in all the rows, else data will become
inconsistent.
● Insert Anomaly : Suppose for a new admission, we have a
Student id(S_id), name and address of a student but if student
has not opted for any subjects yet then we have to insert NULL
there, leading to Insertion Anamoly.
● Delete Anomaly : If (S_id) 401 has only one subject and
temporarily he drops it, when we delete that row, entire student
record will be deleted along with it.
 First Normal Form
● First Normal Form is defined in the definition of
relations (tables) itself. This rule defines that all the
attributes in a relation must have atomic domains.
The values in an atomic domain are indivisible
units.
 Second Normal Form
●
Prime attribute − An attribute, which is a part of the prime-key, is
known as a prime attribute.
●
Non-prime attribute − An attribute, which is not a part of the
prime-key, is said to be a non-prime attribute.
● If we follow second normal form, then every non-prime attribute
should be fully functionally dependent on prime key attribute.
That is, if X → A holds, then there should not be any proper
subset Y of X, for which Y → A also holds true.
 Second Normal Form
● We see here in Student_Project relation that the prime key attributes
are Stu_ID and Proj_ID. According to the rule, non-key attributes, i.e.
Stu_Name and Proj_Name must be dependent upon both and not on
any of the prime key attribute individually. But we find that Stu_Name
can be identified by Stu_ID and Proj_Name can be identified by Proj_ID
independently. This is called partial dependency, which is not allowed
in Second Normal Form.
 Second Normal Form
●
A relation is said to be in a second normal form
if and only if,
– it's in first normal form
– Every non-key attributes identified by the
are use of primary key
– All subset of data, which applies to have multiple
rows in a table must be removed and placed in a
new table. And this new table and the parent
table should be related by the use of foreign key.
●
Second Normal Form
 Third Normal Form
●
For a relation to be in Third Normal Form, it must be in
Second Normal form and the following must satisfy −
– No non-prime attribute is transitively dependent on prime
key attribute.
– For any non-trivial functional dependency,
X → A, then either −
● X is a superkey or,
● A is prime attribute.

●
There should not be the case that a non-
prime attribute is determined by another non-prime
attribute
–
 Third Normal Form

● We find that in the above Student_detail relation,

Stu_ID is the key and only prime key attribute. We
find that City can be identified by Stu_ID as well as
Zip itself. Neither Zip is a superkey nor is City a
prime attribute.
● Additionally, Stu_ID → Zip → City, so there exists
transitive dependency.
 Third Normal Form
● To bring this relation into third normal form,
we break the relation into two relations as
follows
●
Third Normal Form
 BCNF
● Let us consider another example - consider each
student who has taken major subjects has different
advisory lecturers. Each student will have different
advisory lecturers for same Subjects. There exists
following relationship, which is violation of BCNF.
(STUDENT_ID, MAJOR_SUBJECT) -> ADVISORY_LECTURER
ADVISORY_LECTURER -> MAJOR_SUBJECT

● i.e. Major_Subject which is a part of composite

candidate key is determined non-key attribute of
the same table, which is against the rule.
 BCNF
● If we delete any student from below table, it deletes
information
lecturer's too. If we add any new lecturer/student to the database, it
needs other related information also. Also, if we update subject for any
student, his lecturer info also needs to be changed, else it will lead to
inconsistency.
 BCNF
●
Hence we need to decompose the table so
that eliminates so that it eliminates such relationship.
 BCNF
●
This normal form is also referred as 3.5 normal
forms. This normal form
– Meets all the requirement of 3NF
– Any table is said to be in BCNF, if its candidate
keys do not have any partial dependency on the
other attributes. i.e.; in any table with (x, y, z)
columns, if (x, y)->z and z->x then it's a violation of
BCNF. If (x, y) are composite keys and
(x, y)->z, then there should not be any reverse
dependency, directly or partially.