Unit 2: Relational Data Model

2.1 Relational Structure :

RDBMS Concepts
A Relational Database management System(RDBMS) is a database management system based on
relational model introduced by E.F Codd. In relational model, data is represented in terms of
tuples(rows).
RDBMS is used to manage Relational database. Relational database is a collection of organized set
of tables from which data can be accessed easily. Relational Database is most commonly used
database. It consists of number of tables and each table has its own primary key.

1. What is Table ?
In Relational database, a table is a collection of data elements organised in terms of rows and columns.
A table is also considered as convenient representation of relations. But a table can have duplicate tuples
While a true relation cannot have duplicate tuples. Table is the most simplest form of data storage.
Below is an example of Employee table.
Student_ID S_Name AGE Year
101 Satish Pise 20 2016
102 Sandip Patil 20 2016

2. What is a Record ?

A single entry in a table is called a Record or Row. A Record in a table represents set of related
data. For example, the above Employee table has 4 records. Following is an example of single
record.

3. What is Field ?

A table consists of several records(row), each record can be broken into several smaller entities
known asFields. The above Employee table consist of four fields, ID, Name, Age and Salary.

4. What is a Column ?

In Relational table, a column is a set of value of a particular type. The term Attribute is also used to
represent a column. For example, in Employee table, Name is a column that represent names of
employee.
 2.2 Key Concepts

1. Define super key.

Super Key : - Super Key is defined as a set of attributes within a table that uniquely identifies each
record within a table. Super Key is a superset of Candidate key.

2. Explain candidate key and primary key. OR What is Primary Key? Give example.
OR
Q. Define the term.
(i) Candidate key
(ii) Primary key

i) Candidate Key

Candidate keys are defined as the set of fields from which primary key can be selected. It is an
attribute or set of attribute that can act as a primary key for a table to uniquely identify each record in
that table.A table could contain multiple candidate keys. This key cannot store a NULL. It must
contain Unique values There can be more than one candidate keys for a table.

Example : In Employee relation Emp(empid, name, lastname, age, adharno, emailId),

EmpId, Adharno and emailed are Candidate Key

 ii) Primary Key

Primary key is a candidate key that is most appropriate to become main key of the table. It is a key that
uniquely identify each record in a table.
Primary key: Within a relation there is always one attribute which has values that are unique in a
relation and thus can be used to identify tuple of that relation. Such a unique identifier is called the
primary key.
E.g. In the Student(Rollno,name,class,address) relation Rollno is the primary key.

2.3 Constraints
1. Explain not null constraints by suitable example.
Not null constraint: It is a type of Domain integrity constraint. It is used to maintain the value

according to user specification. By default columns allows null values. When not null constraint is

assigned on a column or set of column, then it will not allow null values.
Syntax:

Create table <tablename> (column1 datatype(size), column2 datatype(size) not null);

Example:

Create table Emp (EmpId number(4), Empname varchar(20) not null);

2. Explain Domain integrity constraint with syntax and example. OR
Q. Explain any four integrity constraints

1. Not Null constraint: This constraint ensures all rows in the table contain a definite value

for the column which is specified as not null. Which means a null value is not allowed.

Syntax: create table <table name>( Column_name Datatype (Size) [CONSTRAINTconstraint name]
NOT NULL );

Example: To create a employee table with Null value, the query would be like
CREATE TABLE employee ( id number(5), name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10), age number(2), salary number(10), location char(10));

OR

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee ( id number(5), name char(20) NOT NULL,dept char(10),age number(2),

salary number(10),location char(10));

2) Check constraint: it defines a condition that each row must satisfy. A singlecolumn can have

multiple check constraints that reference the column in its definition.

Syntax at table creation:

Create table <table_name> (column_name1 datatype(size) constraint <constraint_name>

check <condition> Or logical expression>,

---

column_name n datatype(size)

);
 Example:

create table emp( empno number(5), ename varchar2(25), salary number(7,2)constraint

emp_sal_ck check(salary > 5000), job varchar2(15) );

After table creationSyntax:

Alter table <table_name> add constraint<constraint_name> check <condition>;

Example:

alter table emp add constraint emp_deptno_ck check(deptno>5);

3. Primary Key constraint: It is use to avoid redundant/duplicate value entry within the row of
specified column in table. It restricts null values too.

Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE, COLUMN_NAME
DATA_TYPE CONSTRAINT CONSTRAINT_NAME PRIMARY KEY);

Example: SQL> CREATE TABLE EMP (ID NUMBER (5) CONSTRAINT ID_PK PRIMARY
KEY, NAME VARCHAR2 (10), SAL NUMBER (10));

4. Unique Constraint: The UNIQUE constraint uniquely identifies each record in a database
table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for
uniquenessfor a column or set of columns. It allows null value.

Syntax:
CREATE TABLE <TABLE_NAME> (COLUMN_NAME DATA_TYPE,
COLUMN_NAME DATA_TYPE CONSTRAINT CONSTRAINT_NAME UNIQUE);

Example:
CREATE TABLE PERSONS (P_ID NUMBER CONSTRAINT P_UK UNIQUE,
FIRSTNAME VARCHAR (20), CITY VARCHAR (20));
5. Referential Integrity Constraint: It is a relational database concept in which multiple tables share a
relationship based on the data stored in the tables, and that relationship must remain consistent.
A value of foreign key is derived from primary key which is defined in parent table.

Syntax:

Create table <table_name>( column_name datatype (size), foreign key(column_name) references

parent table_name(column_name));

Ex :

Create table department( empno number (5), foreign key(emp_no) references emp(emp_no));

3. Explain entity integrity constraints with syntax and example.

There are two Entity constraints:
1. Primary Key constraint
2. Unique Constraint

1. Primary Key constraint: It is use to avoid redundant/duplicate value entry within the row of specified
column in table. It restricts null values too.

Syntax:

CREATE TABLE <TABLE_NAME> (COLUMN_NAME DATA_TYPE(SIZE),

COLUMN_NAME DATA_TYPE (SIZE) CONSTRAINT CONSTRAINT_NAME PRIMARYKEY);
Example:

SQL> CREATE TABLE EMP (ID NUMBER (5) CONSTRAINT ID_PK PRIMARY KEY,

NAME VARCHAR2 (10), SAL NUMBER (10));

Unique Constraint: The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column

or set of columns.
 Syntax:

CREATE TABLE <TABLE_NAME> (COLUMN_NAME DATA_TYPE,

COLUMN_NAME DATA_TYPE CONSTRAINT CONSTRAINT_NAME UNIQUE);

Example:

CREATE TABLE PERSONS (P_ID NUM CONSTRAINT P_UK UNIQUE ,

FIRSTNAME VARCHAR(20), CITY VARCHAR(20) );

4. Explain referential integrity constraints with example.

Referential integrity constraint:

It is used to established a parent child relationship between two tables.

 A value of foreign key is derived from the primary key.

 Primary key is defined in a parent table and foreign key is defined in child table. The child

table contains the values for foreign key column which are present in parent tablesprimary

key column but not other than that.

Syntax:

Create table tablename (column datatype size references parenttablename (primarykey attribute)….)
Example:

Create table product (EmpId number (4) references Emp (EmpId), PNamevarchar2(10));

After table creation the foreign key is added as:

Alter table product add constraint fk_prod foreign key (EmpId) references Emp (EmpId);
 ER Diagram
Q. List and draw any four symbols used in ER Model.
Symbols and Notations
Components of E-R Diagram
Q. Explain strong and weak entity set with example. OR
Q. Explain strong entity and weak entity set.

Strong Entity Set: An entity set that has sufficient attributes to form a primary key is called
asstrong entity set.
Example: Cust_id is a primary key of Customer Entity so customer is strong entity set..

Weak Entity set: An entity set that does not have sufficient attribute to form a primary key is
called as Weak Entity Set.
Example: transaction is not having sufficient attribute to form a primary key so, transaction a
weak entity set
Q. What is the difference between weak entity set and strong entity set?
 Attribute
Entities are represented by means of their properties, called attributes. All attributes have
values. For example, a student entity may have name, class, and age as attributes.

Types of Attributes
 Simple attribute − Simple attributes are atomic values, which cannot be divided further.
For example, a student's phone number is an atomic value of 10 digits.

 Composite attribute − Composite attributes are made of more than one simple attribute.
For example, a student's complete name may have first_name and last_name.

 Derived attribute − Derived attributes are the attributes that do not exist in the physical
database, but their values are derived from other attributes present in the database. For
example, average_salary in a department should not be saved directly in the database,
instead it can be derived. For another example, age can be derived from data_of_birth.

 Single-value attribute − Single-value attributes contain single value. For

example − Social_Security_Number.

 Multi-value attribute − Multi-value attributes may contain more than one values. For
example, a person can have more than one phone number, email_address, etc.

Q. Define attribute and entity.

Entity: An entity is a thing or object in the real world with an independent existence. An entity
maybe an object with a physical existence.
Attribute: Describing properties of an entity is called attributes. For example, a student entity
mayhave name, class, and age as attributes.
Q. Explain single value and multi valued attribute of E-R model.
i) Single Valued attribute: Attributes that can have single value at a particular instance of time
are called single valued.
Example: age of a person is a single-valued attribute.

ii) Multi valued attributes: A multi-valued attribute can have more than one value at one time.
Example: phone_no of a person is a multi-valued attribute since a person can have more than
one phone_no.

An Attribute describes a property or characterstic of an entity. For example, Name, Age, Address etc can
be attributes of a Student. An attribute is represented using eclipse.
 Key Attribute

Key attribute represents the main characterstic of an Entity. It is used to represent Primary key.

Ellipse with underlying lines represent Key Attribute.

Composite Attribute

An attribute can also have their own attributes. These attributes are known as Composite attribute.

Multivalued Attribute
 Q. Draw E-R diagram for customer and loan. Assume suitable attribute.

Q. Draw E-R diagram for airline reservation system. Here a passenger can
book ticket from personal for a flight on same date.
 Q. Draw an E-R diagram of hospital management system.

Q. Draw an ER diagram for Library Management System

Relationship

A Relationship describes relations between entities. Relationship is represented using diamonds.

There are three types of relationship that exist between Entities.

 Binary Relationship

 Recursive Relationship

 Ternary Relationship
Binary Relationship

Binary Relationship means relation between two Entities. This is further divided into three types.
1. One to One : This type of relationship is rarely seen in real world.

The above example describes that one student can enroll only for one course and a course will also have
only one Student. This is not what you will usually see in relationship.

2. One to Many : It reflects business rule that one entity is associated with many number of same entity.
The example for this relation might sound a little weird, but this menas that one student can enroll to
many courses, but one course will have one Student.

The arrows in the diagram describes that one student can enroll for only one course.

3. Many to One : It reflects business rule that many entities can be associated with just one entity. For example,
Student enrolls for only one Course but a Course can have many Students.
 4. Many to Many :

The above diagram represents that many students can enroll for more than one courses.

Mrs. Trupti Amol Bhokare [DMS] Page 18

 Normalization

1. Explain 2 NF with example.

Second Normal Form (2NF): A relation is said to be in the second normal form if it is in first
normal form and all the non key attributes are fully functionally dependent on theprimary key.
Example: If in the relation Supp(SNO,SNAME, LOCATION,PNO,QTY),the atrributes
SNAME AND LOCATION depends on SNO and QTY dependence on (SNO, PNO) so the
table can be split up into two tables as Supplier (SNO,SNAME,LOCATION) and
SP(SNO,PNO,QTY) and now both the tables are in second normal form.

Supplier:

SP:

2. Define normalization. Or What is meant by database normalization?

“Normalization can be defined as process of decomposition of database

tables to avoid the dataredundancy.”

Mrs. Trupti Amol Bhokare [DMS] Page 19

 3. List advantages of Normalization. What is First Normal Form? Explain
with example

Advantages of the normalization.

 More efficient data structure.

 Avoid redundant fields or columns.
 More flexible data structure i.e. we should be able to add new rows and data values
easily
 Better understanding of data.
 Ensures that distinct tables exist when necessary.

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.

We re-arrange the relation (table) as below, to convert it to

First Normal Form. Each attribute must contain only a single
value from its pre-defined domain.

Second Normal Form

Before we learn about the second normal form, we need to understand the following −

 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-

Mrs. Trupti Amol Bhokare [DMS] Page 20

 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.

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 beidentified 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.

We broke the relation in two as depicted in the above picture. So there exists no partial
dependency.

Third Normal Form

For a relation to be in Third Normal Form, it must be in Second Normal form
and the followingmust satisfy −

Mrs. Trupti Amol Bhokare [DMS] Page 21

  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

Q. Explain Third normal form.

Q.3NF (Third 3NF
Explain normal form)
with example.
1) After removing all transitive dependencies and making separate relations, relations get into
3NF.

2) Transitive dependency is can be stated as, let R be relation and A, B and C be

the set of attributes then, they are transitive dependent if C depends on B, B
depends on A and therefore Cdepends on A.
Example

Student_Detail Table :

Mrs. Trupti Amol Bhokare [DMS] Page 22

 Student_name DOB treet City State Zip
Student_id
In this table Student_id is Primary key, but street, city and state depends upon Zip.
The dependency between zip and other fields is called transitive dependency.
Hence to apply 3NF, we need to move the street, city and state to new table, with
Zip as primary key.
New Student_Detail Table :

Student_name DOB zip

Student_id

Address Table :

Zip Street City state

Mrs. Trupti Amol Bhokare [DMS] Page 23