Reality
Reality involves a very large number of details.
Model
A model is a representation of reality that retains
only selected details. Database incorporates a
model of reality.
DBMS manages the database so that each user
can record, access, and manipulate the data constitutes
models of reality.
Models are powerful tools for eliminating irrelevant
details and understanding the reality of individual
users.
Conceptual data models
A model representing real work entities as
objects rather than records.
The principal elements of conceptual data model are
objects and relationships.
Object
Objects represent things that are important to
users.
Ex:
People, automobiles, books, computers
etc
Conceptual objects would be companies,
skills, organization, transactions etc.
Object set to refer to a set of things of the
same kind and object instance to refer to a
single member (or element) of an object set.
Ex: Employee (Emp no, empname, dept, sal)
Here, Employee is defined as objects.
Empno, Empname, Dept, Sal are object
instance.
Entity
An Entity is a person, place, object, event or concept in the
user environment about which the organization want to
maintain data.
Example
Person: EMPLOYEE, STUDENT, CUSTOMER
Place: CITY, STATE, COUNTRY
Object: COMPUTER, PEN, BOOK
Event : SALE, REGISTRATION, RENEWAL
Concept: ACCOUNT, COURSE, WORK CENTER
Entity type
It is a collection of entities that share common properties or
characteristics. The name represents a collection (or set) of
items, it is always singular.
Entity instance
It is a single occurrence of an entity type.
Strong Entity
A strong entity type is one that exists independent of
other entity types.
Instance of a strong entity type always have a unique
identifier (Primary Key)
Ex: EMPLOYEE (Emp_id, Emp_name, Sal)
Here Employee is a Entity name.
Emp_id, Emp_name, Sal.. are attributes
Emp_id is a Primary Key.
Weak Entity
A weak entity is one that depends on their entity
types i.e. instances of an entity type does not have a
Primary Key.
Ex: DEPENDENT (Dep_name, DOB)
Here, DEPENDENT is a entity name.
E-R Model
An entity-relationship is detailed, logical representation
of the data for an organization or for a business area.
The E-R model is expressed in terms of entities in the
business environment, the relationships among those
entities, and the attributes of both the entities and their
relationships diagram which is a graphical
representation of an E-R model.
E-R Model Notation
E-R Diagram
Attributes
An Attributes is a property or characteristics of
an entity types that is of interest to the organisation.
Ex: STUDENT (stud_id, stud_name, address)
Here, STUDENT is a entity name.
Attributes are stud_id, stud_name, address.
In naming attributes, we use an initial capital letter
followed by lowercase letters.
If an attributes name consists of two words, we
use an underscore character to connect the words
and we start each word with the capital letter.
Simple Attribute
A simple attribute is an attribute that cannot be
broken down into smaller components.
For example: All of the attributes associated with an
entity STUDENT
Simple atttributes: Student_id, Sname, Dob
Composite Attributes
A composite attributes is an attribute which is having
more than one fields. such attributes can be divided in
to number of sub attributes
Example address can be broken down in to
component parts.
Multi valued Attributes
A multivalued attributes is an attribute that may take
on more than one value for a given entity instance.
We indicate a multivalued attribute with an ellipse with
double lines.
EMPLOYEE (Emp_no, Emp_name, Sal, Addr, Skill)
In above example EMPLOYEE is a table/entity SKILL
is attributes with so many values
Tuple
The row of a relation is called tuples.
Identifier
It is an attribute (or combination of attributes) that uniquely
identifies individual instances of entity types. The identifier for
the STUDENT entity type is stud_no.
A composite identifier: it is an identifier that consists of a
composite attributes.
Ex: FLIGHT (FLIGHT_number, FLIGHT_id, Date,
No.of_passenger)
In the above example FLIGHT_Number_id combines to form an
IDENTIFIER.
Relationship
A Relationship is an association among the
instances of one or more entity types that is of
interest of the organization.
Relationship type is a meaningful association
between two or three entities.
A relationship is denoted by a diamond
symbol containing the name of the relationship.
Degree of Relationship
The degree of a relationship is the number of entity
type that participates in that relationship.
There are 3types of Relationships in E.R.Model:
i. UNARY (degree 1)
ii. BINARY (degree 2)
iii. TRINARY (degree3)
Unary Relationship
It is relationship between the instances of a
single entity type.
Unary relationships are also called Recursive
Relationship.
Binary Relationship
It is a relationship between the attributes of
two entity types and is the most common
types of relationship in data modeling.
This relationship has 3 types:
One-one
One-to-many
Many to many
Ternary Relationship
A Ternary relationship is simultaneous
relationship among the instance of 3 entity
types.
Cardinality Constraints
A cardinality constraint is a constraint that
specifies is constraints that specify the
number of instances of entity B that may be
associated with each instance of Entity A.
cardinality constraints normal specify the
minimum and maximum cardinality of one
specifies mandatory participation.
Minimum Cardinality
The minimum number of instances of one entity
that may be associated with each instances of
another entity.
Maximum Cardinality
The maximum number of instances of one
entity that may be associated with a single
occurrence of another entity.
Degree of a relation
The number of attributes in relations is called
the degree of the relation.
Example:
EMPLOYEE(Employee_id, Name,Skill)
EMPLOYEE is entity name.
Attributes are Employee_id,Name,Skill
No. of attributes are 3.
So, degree of a relation is 3.
Super type/ sub type
General entity type is called as super type
Specialized entity types are called as sub type.
Null Values
The values given an attributes in a tuple.
If the attribute is inapplicable or its value is
unknown.
Composite key
Ex: ASSIGNMENT(Worker_id, Bildg_id, Start_date,
days)
In the above table, keys consists of the
WORKER_ID alone uniquely identifies every row, but
the two attributes together do provide the unique key. Such
a key is called composite key.
A key consisting of more than one attributes is called
Composite key.
Candidate key
There may be more than one set of
attributes that could be chosen as a key.
These are called candidate keys.
When one of the candidate keys is selected as
the relation key. It may be primary key
Foreign key
A set of attributes in one relation that is key in another
relation.
These are used to indicate logical links between relations.
Ex:
WORKER(Worker_id, Name,Skill_Type, Sup_id)
ASSIGNMENT(Worker_id, Build_id, Start_date, days)
SKILL(Skill, Bonus, Hours_per_week)
ASSIGNMENT table has two key attributes, both of
them are underlined. This means that Worker_id and
Bldg_id together are key for ASSIGNMENT.
Skill_type in the WORKER relation,
WORKER_ID in ASSIGNMENT are foreign keys.
FOREIGN key attributes need not have the same
name as the key attributes to which they correspond.
Specialization
Specialization is the process of defining one or
more subtypes of the super type and forming
super type/subtype relationship.
Specialization is the process of defining a set of
subclasses of an entity type
Generalization
Generalization is the process of defining a more
general entity type from a set of more specialized
entity types.
Generalization is a bottom-up process.
It may be noted that generalization process is
inverse of the specialization process
Aggregation
A relationship set is an association between entity
sets.
Aggregation allows us to indicate that a
relationship set participates in another relationship
set
Integrity constraints
Domain constraints
Entity integrity
Referential integrity
Operational constraints
Domain constraints
A domain is a set of value that may be assigned to
an attribute. A domain definition usually consists
of the following components
Domain Name, meaning or description, data type,
size, and all allowable values.
Generally under domain constraints we have
NOT NOLL and CHECK constraints.
Entity integrity
The Entity integrity rule is assured that every
relation has a primary key and the data values
for primary key are valid
Under this entity integrity we have UNIQUE
and Primary Key
Referential integrity
A referential integrity is a rule that maintain
consistency among the rows of two relations.
The rule states that if there is a Foreign Key
in one relation, either each foreign key value
must match a primary key value in another
relation or the foreign key value must be
NULL
Operational constraints
The most active area of business rules
development is in the area of operational
constraints
