mt-2 RRB ms

(2) Conceptual Database Design: The information

gathered in the requirements analysis
step is used to develop a high-level description of the data to be stored in the
database, along
with the constraints that are known to hold over
this data. This step is often carried out using
the ER model, or a similar high-level data model.

(3) Logical Database Design: We must choose a DBMS to implement our database
and convert the conceptual database
design,
design into a database schema in the data model of the
chosen DBMS. We will only consider relational
DBMSs, and the task in the
therefore, logical
design step is to convert an ER schema into a relational database schema.
The result is a conceptual schema, sometimes called the logical schema, in the relational data
model.

1.13. E-R DIAGRAMS

represents
Entity

relationship

attribute

weak entity

weak entity
relationship

Multivalued
atribute
 1.14. BEYOND ER DESIGN
The ER diagram is
just an approximate description of the data, constructed through
a very subjective evaluation of the
information collected during requirements analysis.
Once we have
good logical schema, we must consider performance criteria and
a

design the physical schema. Finally, we must address security issues and ensure that users are
able to access the data they need, but not data that we wish to hide from them. The
three steps of database
remaining
design are briefly described below:
(4) Schema Refinement: The fourth step in database design is to analyze the collection of
relations in our relational database schema to
identify potential problems,
(5) Physical Database Design: In this step we must consider typical expected workloads that
our database must support and further refine the database
design to ensure that it meets
desired performance criteria

(6) Security Design: In this step, we identify di erent user groups and di erent roles played
by various users (e.g., the development team for a product, the customer support
representatives, the product manager).

For each role and user group, we must identify the parts of the database that they must be
able to access and the parts of the database that they should not be allowed to access, and take
steps to ensure that they can access only the necessary parts.

Attributes

Entities are represented by means of their properties, called atributes. All attributes have
values. For example, a student entity may have name, class, age as attributes.

There exist a domain or range of values that can be assigned to attributes. For example, a
student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be
negative, etc.

Types of attributes

Attributes are properties of entities. Attributes are represented by means of eclipses. Every
eclipse represents one attribute and is directly connected to its entity (rectangle).

Simple attribute:

Simple attributes are atomic values, which cannot be divided further. For example,
student's phone-number is an atomic value of 10 digits.
Name
BirthDate
cent
 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.

If the attributes are composite, they are further divided in a tree like structure. Every node is
then connected to its attribute. That is composite attributes are represented by eclipses that
are connected with an eclipse.

LastName
FirstNamo

Name BirthDate

Studem
R o l No.

[Image: Composite Attributes]

Single-valued attribute:

Single valued attributes contain on single value. For example:

Social_Security_Number.
Multi-value attribute:

Multi-value attribute may contain more than one values. For example, a person can
have more than one phone numbers, email_addresses etc.

Multivalued attributes are depicted by double eclipse.

astName
FirstName

Name BithDate

Student
RollLNo
PhoneNo

Derived attribute:

Derived atributes are atributes, which do not exist physical in the database, but there
values are derived from other atributes presented in the database. For example,
average salary in a department should be saved in database instead it can be derived.
For another example, age can be derived from data_of_birth.
 Derived attributes are
depicted by dashed eclipse.
1.14.2. RELATIONSHIPS AND RELATIONSHIP SETS
The association
among entities is called
has relation work relationship. For example, employee entity

s_at with
department. Another example is for student who enrolls in some course.
Here, Works_at and Enrolls are called
relationship.
Relationship Set:

Relationship of similar type is called relationship set. Like entities, a

can have attributes. These relationship too
attributes are called descriptive attributes.
Degree of relationship

The number
of participating entities in an relationship defines the degree of the
relationship.
.Binary =degree 2
Ternary = degree 3

n-ary = degree

Mapping Cardinalities:

Cardinality defines the number of entities in one entity set which can be associated to the
number of entities of other set via relationship set.

One-to-one: one entity from entity set A can be associated with at most one entity of
entity setBand vice versa.

A B

[Umage: One-to-one relation]

 One-to-many: One entity from entity set A can be associated with more than one
entities of entity set B but from entity set B one entity can be associated with at most

one entity.

O O

A B

Image: One-to-many relation]

Many-to-one: More than one entities from entity set A can be associated with at most
entity of entity set B but one entity from entity set B can be associated with more
than one entity from entity set A.

O
O
O
O

A B

Image: Many-to-one relation]

Many-to-many: one entity from A can be associated with more than one entity fromn
B and vice versa.

O
O

A B

[Image: Many-to-many relation]

 An entity is an object in the real world that is distinguishable from other objects.
the
Examples include following: the Green Dragonzord toy, the toy department, the
manager of the toy department, the home address of the manager of the toy department.

. A collection of similar entities is called an entity set.

A attribute is an property of an entity.
L.15. ADDITIONAL FEATURES OF ER MODEL

»KeyConstraints
Participation Constraints
Weak Entities
Class Hierarchies
Aggregation
1.15.1. KEY CONSTRAINTS

There must be at least one minimal subset of attributes in the relation, which can identify a
tuple uniquely. This minimal subset of attributes is called key for that relation. If there are
more than one such minimal subsets, these are called candidate keys.

since
(name dname
Ssn lot did budget

Employees Manages Pepartments

Consider Works_In:
An employee can
work in many
departments; a dept
can have manyy
employees.
In contrast, each dept
has at most one
manager, according
to the key
constraitnt on 1to-1 1to Many Many-to-1 Many4o-Many
Manages.

Key constraints forces that:

in a relation with a key attribute, no two tuples can have identical value for key
attributes.

key attribute can not have NULL values.

Key constrains are also referred to as Entity Constraints.
 - If so, this is a participation constraint the
participation of Departments in Manages is said to be
total (vs. partia).
Every Departments entity must appear in an
instance of the Manages relationship.
since
name dname
Ssn lot did budget

Employees Manage Departments

Works_in

since

1.15.3. WEAK ENTITIES

A weak entity can be identified uniquely only by considering the primary key of
another (owner) entity.
Owner entity set and weak entity set must participate in a one-to-many relationship
set (one owner, many weak entities).
Weak entity set must have total participation in this identijfying relationship set.

1.15.3.1. WEAK ENTITY SETS

A n entity set that does not have a primary key is referred to as a weak entity set.

The existence ofa weak entity set depends on the existence ofa identifying entity set

it must relate to the identifying entity set via a total, one-to-many relationship set from the
identifying to the weak entity set Identifying relationship depicted using a double diamond
The discriminator (or partial key) of a weak entity set is the set of attributes that

distinguishes among all the entities of a weak entity set. The primary key of a weak entity set
is formed by the primary key of the strong entity set on which the weak entity set is existence
dependent, plus the weak entity set's discriminator depict a weak entity set by double

rectangles.
Under line the discriminator of a weak entity set with a dashed line.

10
 1.15.3.2. More Weak Entity Set Examples

modeled as a weak
In university, a course is a strong entity and a course offering can be
a
and
entity The discriminator of course_ofering would be semester (including year)
as a strong
section_number (if there is more than one section). If we model course_offering
with course
entity we would model course_number as an attribute. Then the relationship
would be implicit in the course_number attribute.

A weak entity sets is one which does not have any primary key associated with it.

Mapping process (Algorithm):

RolINo Name Name

Student Depends Dependent

Image: Mapping Weak Entity Sets]

Create table for weak entity set

Add all its attributes to table as field

Add the primary key of identifying entity set

Declare all foreign key constraints

1.15.4. CLASS HIERARCHIESS

Classifying the entities in an entity set into sub classes

ER specialization or generalization comes in the fornm of hierarchical entity sets.

Mapping process (Algorithm):

Name Gendar

Porsen

ISA

Studen Trescher
HOlINO EmED

Image: Mapping hierarchical entities]

10
 Create tables for all higher level entities

Create tables for lower level entities

Add primary keys of higher level entities in the table of lower level entities

In lower level tables, add all other attributes of lower entities.

Declare primary key of higher level table the primary key for lower level table

Declare foreign key constraint

1.15.4.1. Generalization

As mentioned above, the process of generalizing entities, where the generalized entities
contain the properties of all the generalized entities is called Generalization. In
generalization, a number of entities are brought together into one generalized entity based on
their similar characteristics. For an example, pigeon, house sparrow, crow and dove all can be
generalized as Birds.

Pigeon SparroW Dove

Birds

[Image: Generalization]

1.15.4.2. Specialization

to generalization, as mentioned above. In

Specialization is process, which is opposite
a
on their characteristics.
specialization, a group of entities is divided into sub-groups based
etc. These
Take a group Person for example. A person has name, date of birth, gender
common in all persons, human beings.
But in a company, a person can be
properties are
customer or vendor based on what role do they play in
identified as employee, employer,
company.
Person

1S A

Studet Teacher

LImage: Specialization]
 1.15.4.3. Aggregation

Used when we have to name

model a relationship
involving (entitity sets T lot

and) a relationship set. Employees

Agareaation allows
us to treat a
relationship set as Monitors untll
an entity set for
purposes of
participation in Started_o since
cdname
pid
(other) pbudgetD dld
relationships. budget
.olects onsors epartment

AAggregation vs. ternary relationship:

Monitors is a distinct relationship, with a
descriptive attribute.
Also, can say that each sponsorship is monitored
by at most one
employee.

1.16. CONCEPTUAL DESIGN WITH ER MODEL

Developing an ER diagram presents several choices, including the following:
Should a concept be modeled as an
entity or an attribute?
Should a concept be modeled as an entity or a
relationship?
What are the relationship sets and their
participating entity sets?
Should we use binary or ternary
relationships?
Should we use aggregation?

Issues involved in making these choices.

Entity versus Attribute
Entity Versus Relationship
Binary versus Termary Relationship
Aggregation versus Ternary Relationships
1.18.1. CONCEPTUAL DESIGN FOR ILARGE ENTERPRISES
The process ofconceptual design consists of more than just describing small
fragments of the application in terms of ER diagrams.

F o r a large enterprise, the design may require the e orts of more than one
designerand span data and application code used by a number of user groups.

Using a high-level, semantic data model such as ER diagrams for conceptual design
in such an environmento ers the additional advantage that the high-level design can
be diagrammatically represented and is easily understood by the many people who
must provide input to the
design process.

An important aspect of the design process is the methodology used to structure the
development of the overall design and to ensure that the design takes into account all
user requirements and is consistent.

The usual approach is that the requirements of various user groups are considered,
any conflicting requirements are somehow resolved, and a single set of global
requirements is generated at the end of the requirements analysis phase. Generating a
single set of global requirements is a di cult task, but it allows the conceptual design
phase to proceed with the development of a logical schema that spans all the data and
applications throughout the enterprise.

1.17. RELATIONAL MODEL

Codd proposed the relational data model in 1970. At that time most database systems were
based on one of two older data models (the hierarchical model and the network model); the
relational model revolutionized the database field and largely supplanted these earlier models.

Today, the relational model is by far the dominant data model and is the foundation for the
leading DBMS products, including IBM's DB2 family, Microsof's Access and SQL-Server,
FoxBase, and Paradox.

The relational model is very simple andeleganf a database is a collection of one or more
relations, where_each relation _is a table _with rows and columns, This simple tabular
representation enables even novice users to understand the contents of a database, and it
permits the use of simple, high-level languages to query the data. The major advantages of
the relational model over the older data models are its simple data representation and the ease
with which even complex queries can be expressed.

1.17.1. Introduction to the relational model

The main construct for representing data in the relational model is a relation. A
relation
consists ofa relation schema and a relation instance. The relation instance is a table, and the
relation
 schema describes the column heads for the
table. We first describe the relation schema and then
the relation instance.

The schema specifies the relation's

name, the name of each field (or column, orattribute), and
the domain of each field. A domain is
referred to in a relation schema by the domain name and
has a set of associated values.

We use the example of student information in a

university database from
the parts ofa relation schema: Chapter 1 to illustrate

Students(sid: string, name: string, login: string,

age: integer, gpa: real)
This says, for instance, that the field
named sid has a domain named
associated with domain string is the set of all string. The set of values
character strings.We now turm to the instances of a
relation. An instance ofa relation is a set
the same number of fields as the of tuples,
also called records, in which each
tuple has
relation schema. A relation instance can be
tuple is a row, and all rows have the same number of fields.thought
in which each of as a table
instance is often abbreviate to (The term relation
just relation, when there is no confusion with other aspects of a
relation such as its schema.)

FIELDS (ATTRIBUTES, COLUMNS)

Field names
sid name
login age gpa
50000 Dave
dave@cs 19
3.3
$3666Jones jones@cs 18 3.4
TUPLES 53688 Samith smith@ee 18 3.2
(RECORDS, ROWS) $3650 Smith smith@math 19 3.8
53831 Madayan
madayan@music 11 1.8
53832 Quldu guldu@music 12 2.0
Figure 3.1 An Instance of the S1 of the Students Rclation
Creating and modify ing relations using SQL
Create
Insert
Update
Delete
 1.17.2. INTEGRITY CONSTRAINTS QVER RELATIONS
IC: condition that must be true for any instance of the database; e.g., domain constraints

ICs are specified when schema is defined.

I C s are checked when relations are modified.

A legal instance ofa relation is one that satisfies all specified ICs.

DBMS should not allow illegal instances.

If the DBMS checks ICs, stored data is more faithful to real-world meaning.

1.17.2.1 Key constraints

Candidate Key
Primary Key
Super Key
1.17.2.2. Foreign Key Constraints

Specifying Keyconstraints in SQL

1.17.2.3. General Constraints

Foreign key Primary key

cid lgrade sid sid name L login age gpa
Carnatic101 C 53831 50000 Dave | dave@cs | 19 3.3
Reggae2003 B 53832| 53666 ones jones@cs 18 3.4
Topologyl12 A |53650 53688 Smith smith@ee 18 3.2
History105 B 53666 |53650 Smith mith@math 1919 3.8
53831 Madayan madayan@music 1 1 18
53832 Guldu guldu@music 12 2.0
Enrolled (Referencing relation) Students (Referenced relation)

1.18.ENFORCING INTEGRITY CONSTRAINTS:

ICs are specified when a relation is created and enforced when a relation is modified.
The impact of domain, PRIMARY KEY, and UNIQUE constraints is straightforward: if
an insert, delete, or update command causes a violation, it is
rejected.
Potential IC violation is generally checked at the end of each SQL statement execution,
although it can be deferred until the end of the transaction executing the statement.

10
 insertion
of Students shown Figure 3.1. The following
in
Consider the instance Sl with the sid 53688,
constraint because there is already tuple
a
violates the primary key
the DBMS:
and it will be rejected by

gpa) VALUES (53688, Mike',

INSERT INTO Students
(sid, name, login, age,

mike@ee', 17, 3.4)

contain null:
insertion violates the
constraint that the primary key cannot
The following
gpa) VALUES (null, Mike,
INSERT INTO Students (sid, name, login, age,

mike@ee, 17, 3.4)

with a value in a
arises whenever we try to insert a tuple
Of course, a similar problem whenever we violatee a
associated with that field, i.e.,
field that is not in the domain key or unique
not cause a violation of domain, primary
domain constraint. Deletion does
similar to an insertion:
constraints. However, an update can cause violations,

50000 WHERE S.sid

=
53688
UPDATE Students S SET S.sid =

there is already a tuple with sid 50000.

This update violates the primary key constraint because
sometimes tries to rectifya
of foreign key constraints is complex because SQL
more
The impact
violation instead of simply rejecting the change.
foreign key constraint
that does not
doif an Enrolled row is inserted, with a sid column value
1. What should we
In this case the INSERT command simply
is
of the Students table?
appear in any
row

rejected.
Enrolled rows
is deleted? The options are: Delete all
What should we do if a Students row
2.
row. Disallow the
deletion of the Students row if an
deleted Students
that refer to the some (existing) 'default' sudent,
Enrolled row refers to it. Set
the sid column to the sid of

for every row that refers to it,

Students row. For every Enrolled
Enrolled that refers to the deleted
conflicts with the fact that sid is part
row

set the sid column to

null. In our example, this option
limited to
Enrolled and therefore cannot
be set to null. Thus, we are
of the primary key of
fourth option (setting the foreign key
to

the first three in our example, although this

options
nul) is available in the general
case.

a Students row is updated?

The options here
3. What should we do if the primary key value of
are similar to the previous case.
 CREATE TABLE Enrolled (

sid CHAR(20),

cid CHAR(20),

grade CHAR(10), PRIMARY KEY (sid, cid),

ON UPDATE NO
FOREIGN KEY (sid) REFERENCES Students ON DELETE CASCADE

ACTION)

ON
to a 'default' student by using
If a is deleted, we can switch the enrollment
Students row
student is specified as part of
the definition of the sid
DELETE SET DEFAULT. The default
field in Enrolled;

DEFAULT '53666'.
For example, sid CHAR(20)
default
in some situations (e.g.,
a
of a default value is appropriate
Although the specification not appropriate to
switch
supplier goes out of business), it is really
parts supplier if particular
a
this example is to
also delete all
enrollments to a student. The correct solution in
default
student (that is, CASCADE), or to reject the update.
enrollment tuples for the deleted
DELETE SET NULL.
of null as the default value by specifying ON
SQL also allows the
use

DATA:
1.19. QUERYING RELATIONAL consists of a
Data and the answer

Query is a Question about the

Relational data base
result.
new relation containing the
younger than
18 with the
students who are
retrieve rows corresponding to
We can

following SQL query:

S WHERE S.age < 18
SELECT
* FROM Students

retain all fields of

selected tuples in the result.
* means that we
The symbol in
variable that takes on the value of each tuple
think of S as a
To understand this query,
one tuple
after the other.
Students,
that we want to select only tuples in
the WHERE clause specifies
The condition S.age < 18 in
than 18.
has a value less
which the age field
 sid name login age|9Ppa
53831 Madayan madayan@music 11 |1.8
53832 Guldu guldu@music 12 2.0

1.20. LOGICAL DB DESIGN:ER TO RELATIONAL

Entity sets to tables:

CREATETABLEEmployees
name (ssn CHAR(I1),
Ssn
lot name CHAR(20),
lot INTEGER,
PRIMARY KEY (ssn))
Employees

Tables
Relationship Sets to
attributes of the relation must include:
In translating a relationship set to a relation,

Keys for each participating entity

set (as foreign keys).

This set of attributes forms a superkey for the relation.

All descriptive attributes.