What is an entity relationship model?

May/ June 2016

The entity relationship model is a collection of basic objects called entities
1.
and relationship among those objects. An entity is a thing or object in the
real world that is distinguishable from other objects.
Define weak and strong entity sets? April/May 2009, April/May 2018
Weak entity set: entity set that do not have key attribute of their own are
2. called weak entity sets.
Strong entity set: Entity set that has a primary key is termed a strong entity
set.
Give the limitations of ER model? How do you overcome this? May/
June 2007
3. The entity relationship model is a collection of basic objects called entities
and relationship among those objects. An entity is a thing or object in the
real world that is distinguishable from other objects.
Define Specialization and Aggregation.
It is the process of designating sub groupings within an entity set.It is a top
down process.
4. Specialization which is represented by triangle. The lable ISA stands for “is
a and represent, for eg that customer is a person.
Aggregation is a special kind of association that specifies a whole/part
relationship between the aggregate (whole) and a component part.
What are three characteristics of a relational database system?Nov/Dec
2008
5. Controlling redundancy
Restricting unauthorized access
Providing multiple user interfaces
Give the distinction between primary key, candidate key and super key.
Nov/Dec
2006,2009
Primary key – is used in a data base to avoid duplication of attributes and
6. also makes a relation to the other database.
Candidate key - a key which is in the data base is called as candidate key, it
might be any key attribute.
Super key – collection of keys of a database is called as super key

Define functional dependency. Nov/Dec2010, Apr/ May 2015

A functional dependency is a constraint between two sets of attributes from
the data base. A functional dependency , denoted by
X Y
Between two sets of attributes X and Y that are subsets of R specifies a
7. constraint on the possible tuples that can form a relation instance r of R.

R={ A1,A2,…,An}. The constraint states that ,for any two tuples t1 and t2
in r such that t1[X]=t2[X] , we must also have t1[Y] = t2[Y].
We can also say that Y is functionally dependent on X.
 Define an entity?
Entities:
Entity -a thing (animate or inanimate) of independent physical or
8. conceptual existence and distinguishable.
Example:
In the University database context, an individual student, faculty
member, a class room, a course are entities.
What is an entity set?
Entity Set or Entity Type-Collection of entities all having the same
properties.
9. Example:
Student entity set –collection of all student entities.
Course entity set –collection of all course entities.
What is an attribute?
Attributes:
Attributes - Each entity is described by a set of
attributes/properties.studententity
10. Example:
StudName–name of the student.
RollNumber–the roll number of the student.
Sex–the gender of the student etc.
All entities in an Entity set/type have the same set of attributes.
What is derived attributes?
Derived attributes are those created by a formula or by a summary operation
11.
on other attributes

What is a recursive relationship?

Recursive relationships A recursive relationship is an entity is associated
with itself.
Example:
An employee may manage many employees and each employee is managed
by one employee.

12.

Define cardinality?
13. Cardinality
A business rule indicating the number of times a particular object or
 activity may occur.

List the properties of a relation?

All entries in a given column are of the same kind or type
The ordering of columns is immaterial.
14. No two tuples are exactly the same.
There is only one value for each attribute of a
tuple. The ordering of tuples is immaterial.
Define a key?
A key is a set of attributes that uniquely identifies an entire tuple, a
functional dependency allows us to express constraints that uniquely
identify the values of certain attributes.
15. First, keys will typically be used as links, ie. key values will appear in other
relations to represent their associated tuples
Second, keys form the basis for constructing indexes to speed up retrieval
of tuples from a relation. Small keys will decrease the size of indexes and
the time to look up an index.
Define a relational schema?
A Relational Database Schema comprises
1. the definition of all domains
16.
the definition of all relations, specifying for each
its intension (all attribute names), and
a primary key
List the uses of functional dependencies?
We use functional dependencies to:
test relations to see if they are legal under a given set of functional
dependencies.
17. If a relation r is legal under a set F of functional dependencies, we say
that r satisfies F.
specify constraints on the set of legal relations
We say that F holds on R if all legal relations on R satisfy the set of
functional dependencies F.
Define normalization. Nov/Dec2009.April/May2010
Normalization of data is a process during which unsatisfactory relation
18.
schemas are decomposed by breaking up their attributes into smaller
relation shemas that possess desirable properties.
List the functional dependencies rules?
19.
 What is normalization?
Database normalization is the process of removing redundant data from
the tables in to improve storage efficiency, data integrity, and scalability.
In the relational model, methods exist for quantifying how efficient a
20. database is. These classifications are called normal forms (or NF), and
there are algorithms for converting a given database between them.
Normalization generally involves splitting existing tables into multiple
ones, which must be re-joined or linked each time a query is issued.

Define data
Anomalies. Data
Anomalies
21. Data anomalies are inconsistencies in the data stored in a database as a
result of an operation such as update, insertion, and/or deletion. Such
inconsistencies may arise when have a particular record stored in multiple
locations and not all of the copies are updated.
Define 1NF.
Each table has a primary key: minimal set of attributes which can uniquely
identify a record
22. The values in each column of a table are atomic (No multi-value attributes
allowed). There are no repeating groups: two columns do not store similar
information in the same table
Define 2NF.
where a key has more than one attribute, check that each non-key attribute
depends on the whole key and not part of the key
for each subset of the key which determines an attribute or group of
23. attributes create a new form. Move the dependant attributes to the new
form.
Add the part key to new form, making it the primary
key. Mark the part key as a foreign key in the original
form.
 Define 3NF.
A relation R is in Third Normal Form (3NF) if and only if it is:
24.
in Second Normal Form.
Every non-key attribute is non-transitively dependent on the primary key.
 Define BCNF
Boyce-Codd Normal Form:
• A relation is in Boyce-Codd normal form (BCNF) if for every FD A B
either
o B is contained in A (the FD is trivial), or
25. A contains a candidate key of the relation,
• In other words: every determinant in a non-trivial dependency is a
(super) key.
The same as 3NF except in 3NF we only worry about non-key Bs
If there is only one candidate key then 3NF and BCNF are the same
List the decomposition properties. April/May 2017
Decomposition Properties
• Lossless: Data should not be lost or created when splitting relations up
26.Dependency preservation: It is desirable that FDs are preserved when
splitting relations up
Normalisation to 3NF is always lossless and dependency preserving
Normalisation to BCNF is lossless, but may not preserve all dependencies
Define MVD.
A multivalued dependency is a full constraint between two sets of attributes
in a relation.
27. In contrast to the functional independency, the multivalued dependency
requires that certain tuples be present in a relation. Therefore, a multivalued
dependency is also referred as a tuple-generating dependency. The
multivalued dependency also plays a role in 4NF normalization.
Define 4NF.
4th Normal Form
A Boyce Codd normal form relation is in fourth normal form if
there is no multi value dependency in the relation or

there are multi value dependency but the attributes, which are multi value
dependent on a specific attribute, are dependent between themselves.
28. sume the following relation
a:pk1, b:pk2, c:pk3)

Recall that a relation is in BCNF if all its determinant are candidate keys, in
other words each determinant can be used as a primary key.
Because relation R has only one determinant (a, b, c), which is the
composite primary key and since the primary is a candidate key therefore R
is in BCNF.
Define 5NF
FIFTH NORMAL FORM
29. R is in 5NF if and only if every join dependency in R is implied by the
candidate keys of R
5NF is always achievable.
 a join dependency, * (A, B, …, Z), is implied by the candidate keys, K1, …,
Km of R
 the fact that K1, …, Km are candidate keys for R determine the fact that
R has the JD * (A, B, …, Z)
Show the binary relationship in diagram?

30.

List the properties of a database

relation. Properties of database relations
are:
• relation name is distinct from all other relations
31. • each cell of relation contains exactly one atomic (single) value
• each attribute has a distinct name
• values of an attribute are all from the same domain
• order of attributes has no significance
• each tuple is distinct; there are no duplicate tuples
• order of tuples has no significance, theoretically.
List the various object-based logical models.
– Entity-relationship model
32. – Object-oriented model
– Semantic model
– Functional model
various Record-based logical models
– Relational model (e.g., SQL/DS, DB2)
33.
– Network model
– Hierarchical model (e.g., IMS)
 What is a domain in a database.
The term domain is used to refer to a set of values of the same kind or type.
For example, the column, "Cname‟ and "Ccity‟ in the following figure,
both have values of type string (ie. valid values are any string). But they
denote different domains, ie."Cname‟ denotes the domain of customer
names while "Ccity‟ denotes the domain of city names. They are different
domains even if they share common values.
34.

What is candidate Keys?

Candidate keys are those keys which is candidate for primary key of a
table. Such keys will full fill all the requirements of primary key which is
35.
not null and have unique records is a candidate for primary key. So thus
type of key is known as candidate key. Every table must have at least one
candidate key
but at the same time can have several.
Define the Primary Key.
36. Unique attribute of a table will be considered as a primary key. Primary
keys are used to identify tables. There is only one primary key per table.
What is Foreign Key? April/May 2018
Foreign key are those keys which is used to define relationship between two
tables. When we want to implement relationship between two tables then
we use concept of foreign key. It is also known as referential integrity. We
37. can create more than one foreign key per table. foreign key is generally a
primary key from one table that appears as a field in another where the first
table has a relationship to the second. In other words, if we had a table A
with a primary key X that linked to a table B where X was a field in B, then
X would be a foreign key in B.
Define alternate Key.
If any table have more than one candidate key, then after choosing primary
key from those candidate key, rest of candidate keys are known as an
38. alternate key of that table. Suppose we have a table named Employee which
has two columns EmpID and EmpMail, both have not null attributes and
unique value. If EmpID is considered as a primary key to that table
then EmpMail is known as alternate key.
Define Composite Key
When a key is created on more than one column then that key is known as
39. composite key. Consider a table "Student" which has two columns Sid
and SrefNo and these two columns are used as a primary key for record
extraction, then this key is known as composite key.
 Distinguish between key and super key? April/May 2017

40.

List the similarities between primary and candidate keys.

Both Primary and Candidate keys can uniquely identify records in a table on
the database.
41. Both Primary and Candidate keys have constraints UNIQUE and
NOT NULL.
Primary key or Candidate keys can be either single column or combination
of multiple columns in a table.
What are the characteristics of primary key?
Primary key is a special kind of index in that,
42.  there can be only one;
 it cannot be nullable
 it must be unique.
Define super key.
43. A super key is a set or one of more columns (attributes) to uniquely identify
rows in a table.
What is need for normalization. Nov/Dec 2010
To ensure that the update anomalies do not occur.
Normal forms provide a formal frame work for analyzing relation shemas
based on their keys and on the functional dependencies among their
44. attributes.
A series of tests that can be carried out on individual relation schemas so
that the relation database can be normalized to any degree. When a test fails
, the relation violating that test must be decomposed into relations that
individually meet the normalization tests
Define lossless joins (or) What is non additive property. Nov/Dec 2011
Lossless join property or non additive property ensures that no
45. spurious tuples(tuples
containing wrong information) are generated when a natural join
operation is applied to the relations in the decomposition.
Explain BCNF with example(or) How to convert a relation into BCNF.
Nov/dec 2007, 2008, 2009 2010, 2011,2014
Boyce-Codd Normal form: It is stricter than 3NF, meaning that every
46. relation in BCNF is also in 3NF; however a relation in 3NF is not
necessarily in BCNF. A relation is in BCNF if and only if every determinant
is a candidate key (i.e) a relatioln schema R is in BCNF if whenever a
functional dependency X->A holds in R, then X is a superkey of R
What are pitfalls in relational database design?Nov/Dec2009 2010
47.
o Repetition of informaition
 o Inability to represent certain information
o Loss of information
State the anomalies of 1NF. Nov/ Dec 2015.
48. No repeating groups
Redundancy of data
Explain the desirable properties of decomposition.
Lossless-join decomposition
49.
Dependency preservation
Repetition of information
Outline the steps involved in query processing. April/May 2018.
Parsing and translation
50.
Optimization
Evaluation
Explain trivial dependency? Nov/Dec 2009, 2010
51. Functional dependency of the form α ->β. is trivial if α C β. Trivial
functional dependencies are satisfied by all the relations.
Why must multivalued dependencies exist in pairs? Nov/DEC 2010,
2011
Multivalued dependencies: Multivalued dependencies are a result of 1NF
which disallowed an attribute in a tuple to have a set of values. If we have
two or more multivalued independent attributes in the same relation schema,
52. we get into the problem of having to repeat every value of one of the
attributes with every value of the other attributes with every value of the
other attribute to keep the relation instances consistent. A multivalued
dependency X>Y specified on relation schema R where X and Y are
subsets of R specifies the following constraint on any relation r of R.
Comparison of BCNF and 3NF: Nov/Dec 2010, April/may2011
3NF design is always dependency preserving and lossless.
53. dependency preserving is difficult to achieve in BCNF
sometimes. BCNF strictly removes transitive dependency.
BCNF relation is in 3NF , but reverse is not possible
Why 4NF in Normal Form is more desirable than BCNF? (Nov/Dec
2014, Dec 2017)
BCNF (Boyece code normal form) has all functional dependencies A to B
are trivial of discriminator should be superkey. To get relation in BCNF,
Splitting the relation schema not necessarily preserve all functional
dependency, Loss less decomposition and dependency are main points for
54. the normalization sometime, it is not possible to get a BCNF decomposition
that is dependency, preserving. While 4NF has very similar definition as
BCNF. A relational Schema is in 4NF, if all multivalued dependencies A to
B are trivial and determinate A is superkey of schema. If a relational schema
is in 4nf, it is already in BCNF. and 4NF decomposition preserve the all
functional dependency. so 4NF is preferable than to have BCNF.