ZeePedia Add to Favourites   |   Contact us


Database Management Systems

<<< Previous Entity-Relationship Data Model, Classification of entity types, Attributes Next >>>
 
img
Database Management System (CS403)
VU
Lecture No. 07
Reading Material
"Database Systems Principles, Design and Implementation"
Section: 5.2 5.3
written by Catherine Ricardo, Maxwell Macmillan.
Hoffer
Page: 85 - 95
Overview of Lecture
Entity
o
Different types of Entities
o
Attribute and its different types
o
In the previous lecture we discussed the importance and need of data models.
o
From this lecture we are going to start detailed discussion on a data model, which
is the entity relationship data model also known as  E-R data model.
Entity-Relationship Data Model
It is a semantic data model that is used for the graphical representation of the conceptual
database design. We have discussed in the previous lecture that semantic data models
provide more constructs that is why a database design in a semantic data model can
contain/represent more details. With a semantic data model, it becomes easier to design
the database, at the first place, and secondly it is easier to understand later. We also know
that conceptual database is our first comprehensive design. It is independent of any
particular implementation of the database, that is, the conceptual database design
expressed in E-R data model can be implemented using any DBMS. For that we will have
to transform the conceptual database design from E-R data model to the data model of the
particular DBMS. There is no DBMS based on the E-R data model, so we have to
transform the conceptual database design anyway.
A question arises from the discussion in the previous paragraph, can we avoid this
transformation process by designing our database directly using the data model of our
selected DBMS. The answer is, yes we can but we do not do it, because most commercial
DBMS are based on the record-based data models, like Hierarchical, Network or
Relational. These data models do not provide too much constructs, so a database design
70
img
Database Management System (CS403)
VU
in these data models is not so expressive. Conceptual database design acts as a reference
for many different purposes. Developing it in a semantic data model makes it much more
expressive and easier to understand, that is why we first develop our conceptual database
design in E-R data model and then later transform it into the data model of our DBMS.
Constructs in E-R Data Model
The E-R data model supports following major constructs:
·  Entity
·  Attribute
·  Relationship
We are going to discuss each one of them in detail.
The Entity
Entity is basic building block of the E-R data model. The term entity is used in three
different meanings or for three different terms and that are:
·  Entity type
·  Entity instance
·  Entity set
In this course we will be using the precise term most of the time. However after knowing
the meanings of these three terms it will not be difficult to judge from the context which
particular meaning the term entity is being used in.
Entity Type
The entity type can be defined as a name/label assigned to items/objects that exist in an
environment and that have similar properties. It could be person, place, event or even
concept, that is, an entity type can be defined for physical as well as not-physical things.
An entity type is distinguishable from other entity types on the basis of properties and the
same thing provides the basis for the identification of an entity type. We analyze the
things existing in any environment or place. We can identify or associate certain
properties with each of the existing in that environment. Now the things that have
common or similar properties are candidates of belonging to same group, if we assign a
name to that group then we say that we have identified an entity type.
Generally, the entity types and their distinguishing properties are established by nature,
by very existence of the things. For example, a bulb is an electric accessory, a cricket bat
is a sports item, a computer is an electronic device, a shirt is a clothing item etc. So
identification of entity types is guided by very nature of the things and then items having
properties associated with an entity type are considered to be belonging to that entity type
or instances of that entity type. However, many times the grouping of things in an
environment is dictated by the specific interest of the organization or system that may
supersede the natural classification of entity types. For example, in an organization, entity
71
img
Database Management System (CS403)
VU
types may be identified as donated items, purchased items, manufactured items; then the
items of varying nature may belong to these entity types, like air conditioners, tables,
frying pan, shoes, car; all these items are quite different from each other by their
respective nature, still they may be considered the instances of the same entity type since
they are all donated or purchased or manufactured.
What particular properties of an entity type should be considered or which particular
properties jointly form an entity type? The answer to this question we have discussed in
detail in our very first lecture, where we were discussing the definition of database. That
is, the perspective or point of view of the organization and the system for which we are
developing the database is going to guide us about the properties of interest for a
particular group of things. For example, if you have a look around you in your bedroom,
you might see tube light, a bulb, fan, air conditioner, carpet, bed, chair and other things.
Now fan is an item that exists in your room, what properties of the fan we are interest in,
because there could be so many different properties of the fan. If we are developing the
database for a manufacturer, then we may be interested in type of material used for wings,
then the thickness of the copper wire in the coil, is it locally manufactured or bought
ready made, what individual item costs, what is the labor cost, what is the total cost,
overhead, profit margin, net price etc. But if we are working for a shopkeeper he might
be interested in the name of the company, dealer price, retail price, weight, color of fan
etc. From the user perspective; company name, color, price, warranty, name of the dealer,
purchase date and alike. So the perspective helps/guides the designer to associate or
identify properties of things in an environment.
The process of identifying entity types, their properties and relationships between them is
called abstraction. The abstraction process is also supported by the requirements gathered
during initial study phase. For example, the external entities that we use in the DFDs
provide us a platform to identify/locate the entity types from. Similarly, if we have
created different cross reference matrices, they help us to identify different properties of
the things that are of interest in this particular system and that we should the data about.
Anyway, entity types are identified through abstraction process, then the items possessing
the properties associated with a particular entity type are said to be belonging to that
entity type or instances of that entity type.
While designing a system, you will find that most of the entity types are same as are the
external entities that you identified for the DFDs. Sometimes they may be exactly the
same. Technically, there is a minor difference between the two and that is evident from
their definitions. Anything that receives or generates data from or to the system is an
external entity, where as entity type is name assigned to a collection of properties of
different things existing in an environment. Anything that receives or generates data is
considered as external entity and is represented in the DFD, even if it is a single thing. On
the other hand, things with a single instance are assumed to be on hand in the
environment and they are not explicitly identified as entity type, so they are not
represented in the E-R diagram. For example, a librarian is a single instance in a library
system, (s)he plays certain role in the library system and at many places data is generated
72
img
Database Management System (CS403)
VU
from or to the librarian, so it will be represented at relevant places in the DFDs. But the
librarian will not be explicitly represented in the E-R diagram of the library system and
its existence or role is assumed to be there and generally it is hard-coded in the
application programs.
Entity Instance
A particular object belonging to a particular entity type and how does an item becomes an
instance of or belongs to an entity type? By possessing the defining properties associated
with an entity type. For example, following table lists the entity types and their defining
properties:
Entity Types
Properties
Instances
EMPLOYEE
Human being, has name, has father name,
M. Sharif, Sh. Akmal
has a registration number, has qualification,
and many others
designation
FURNITURE
Used to sit or work on, different material,
Chair, table etc.
having legs, cost, purchased
ELECTRIC
Need electricity to work, purchased
Bulb, fan, AC
APPLIANCES
OFFICE
Used for office work, consumable or non- Papers, pencil, paper
EQUIPMENT
consumable,
weight etc.
Table 1: Entity types, their properties and instances
Each entity instance possesses certain values against the properties with the entity type to
which it belongs. For example, in the above table we have identified that entity type
EMPLOYEE has name, father name, registration number, qualification, designation.
Now an instance of this entity type will have values against each of these properties, like
(M. Sajjad, Abdul Rehman, EN-14289, BCS, and Programmer) may be one instance of
entity type EMPLOYEE. There could be many others.
Entity Set
A group of entity instances of a particular entity type is called an entity set. For example,
all employees of an organization form an entity set. Like all students, all courses, all of
them form entity set of different entity types
As has been mentioned before that the term entity is used for all of the three terms
mentioned above, and it is not wrong. Most of the time it is used to mention an entity
type, next it is used for an entity instance and least times for entity set. We will be precise
most of the time, but if otherwise you can judge the particular meaning from the context.
73
img
Database Management System (CS403)
VU
Classification of entity types
Entity types (ETs) can be classified into regular ETs or weak ETs. Regular ETs are also
called strong or independent ETs, whereas weak ETs are also called dependent ETs. In
the following we discuss them in detail.
Weak Entity Types
An entity type whose instances cannot exist without being linked with instances of some
other entity type, i.e., they cannot exist independently. For example, in an organization
we want to maintain data about the vehicles owned by the employees. Now a particular
vehicle can exist in this organization only if the owner already exists there as employee.
Similarly, if employee leaves the job and the organization decides to delete the record of
the employee then the record of the vehicle will also be deleted since it cannot exist
without being linked to an instance of employee.
Strong Entity Type
An entity type whose instances can exist independently, that is, without being linked to
the instances of any other entity type is called strong entity type. A major property of the
strong entity types is that they have their own identification, which is not always the case
with weak entity types. For example, employee in the previous example is an
independent or strong entity type, since its instances can exist independently.
Naming Entity Types
Following are some recommendations for naming entity types. But they are just
recommendations; practices considered good in general. If one, some or all of them are
ignored in a design, the design will still be valid if it satisfies the requirements otherwise,
but good designs usually follow these practices:
·
Singular noun recommended, but still plurals can also be used
·
Organization specific names, like customer, client, gahak anything will work
·
Write in capitals, yes, this is something that is generally followed, otherwise will
also work.
·
Abbreviations can be used, be consistent. Avoid using confusing abbreviations, if
they are confusing for others today, tomorrow they will confuse you too.
Symbols for Entity Types
A rectangle is used to represent an entity type in E-R data model. For strong entity types
rectangle with a single line is used whereas double lined rectangle is drawn to represent a
weak entity type as is shown below:
74
img
Database Management System (CS403)
VU
EMPLOYEE
Strong Entity Type
Weak Entity Type
DEPENDENTS
Figure 1: Symbols used for entity types
We have discussed different types of entity types; in the next section we are going to
discuss another component or E-R data model, that is, the attribute.
Attribute
An attribute of an entity type is a defining property or quality of the instances of that
entity type. Entity instances of same entity type have the same attributes. (E.g. Student
Identification, Student Name). However, values of these attributes may be same or
different. For example, all instances of the entity type STUDENT may have the attributes
name, father name, age; but the values against each of these attributes for each instance
may be different. Like, one instance may have the values (M. Hafeez, Noor Muhammad,
37) other may have others. Remember one thing, that the values of the attributes may be
same among different entity instances. The thing to remember at this stage is that
attributes are associated with an entity type and those attributes then become applicable
/valid for all the instances of that entity type and instances have values against these
attributes.
An attribute is identified by a name allocated to it and that has to be unique with respect
to that entity type. It means one entity type cannot have two attributes with the same
name. However, different entity types may have attributes with the same name. The
guidelines for naming an attribute are similar to those of entity types. However, one
difference is regarding writing the names of attributes. The notation that has been adopted
in this course is that attribute name generally consists of two parts. The name is started in
lower case, and usually consists of abbreviation of the entity types to which the attribute
belongs. Second part of the attribute name describes the purpose of attribute and only
first letter is capitalized. For example empName means name attribute of entity type
EMPLOYEE, stAdrs means address attribute of the entity type STUDENT and alike.
Others follow other notations, there is no restriction as such, and you can follow anyone
that you feel convenient with. BUT be consistent.
Domain of an Attribute
75
img
Database Management System (CS403)
VU
We have discussed in the previous section that every attribute has got a name. Next thing
is that a domain is also associated with an attribute. These two things, name and the
domain, are part of the definitions of an attribute and we must provide them. Domain is
the set of possible values that an attribute can have, that is, we specify a set of values
either in the form of a range or some discrete values, and then attribute can have value
out of those values. Domain is a form of a check or a constraint on attribute that it cannot
have a value outside this set.
Associating domain with an attribute helps in maintaining the integrity of the database,
since only legal values could be assigned to an attribute. Legal values mean the values
that an attribute can have in an environment or system. For example, if we define a salary
attribute of EMPLOYEE entity type to hold the salary of employees, the value assigned
to this attribute should be numeric, it should not be assigned a value like `Reema', or
`10/10/2004', why, because they are not legal salary values  1 . It should be numeric.
Further, even if we have declared it as numeric it will have numeric values, but about a
value like 10000000000. This is a numeric value, but is it a legal salary value within an
organization? You have to ask them. It means not only you will specify that the value of
salary will be numeric but also associate a range, a lower and upper limit. It reduces the
chances of mistake.
Domain is normally defined in form of data type and some additional constraints like the
range constraint. Data type is defined as a set of values along with the operations that can
be performed on those values. Some common data types are Integer, Float, Varchar, Char,
String, etc. So domain associates certain possible values with an attribute and certain
operations that can be performed on the values of the attribute. Another important thing
that needs to be mentioned here is that once we associate a domain to an attribute, all the
attributes in all entity instances of that entity type will have the values from the same
domain. For example, it is not possible that in one entity instance the attribute salary has
a value 15325.45 and in another instance the same attribute has a value `Reema'. No. All
attribute will have values from same domain, values may be different or same, whatever,
but the domain will be the same.
1
Sometimes when some coding has been adopted, then such strange values may be legal but here we are
discussing the general conditions
76
img
Database Management System (CS403)
VU
Symbols for Attributes
Each represented as an oval, linked with an
ET symbol
Figure 2: Symbol used for attribute in E-R diagram
Types of Attributes
Attributes may be of different types. They may be:
·
Simple or Composite
·
Single valued or multi-valued
·
Stored or Derived
Simple or Composite Attributes:
An attribute that is a single whole is a simple attribute. The value of a simple attribute is
considered as a whole, not as comprising of other attributes or components. For example,
attributes stName, stFatherName, stDateOfBorth of an entity type STUDENT are
example of simple attributes. On the other hand if an attribute consists of collection of
other simple or composite attributes then it is called a composite attributes. For example,
stAdres attribute may comprise of houseNo, streetNo, areaCode, city etc. In this case
stAdres will be a composite attribute.
Single valued or multi-valued Attributes:
Some attribute have single value at a time, whereas some others may have multiple
values. For example, hobby attribute of STUDENT or skills attribute of EMPLOYEE,
since a student may have multiple hobbies, likewise an employee may have multiple
skills so they are multi-valued attributes. On the other hand, name, father name,
designation are generally single valued attributes.
Stored or Derived Attributes:
Normally attributes are stored attributes, that is, their values are stored and accessed as
such from the database. However, sometimes attributes' values are not stored as such,
rather they are computed or derived based on some other value. This other value may be
stored in the database or obtained some other way. For example, we may store the name,
father name, address of employees, but age can be computed from date of birth. The
77
img
Database Management System (CS403)
VU
advantage of declaring age as derived attribute is that whenever we will access the age,
we will get the accurate, current age of employee since it will be computed right at the
time when it is being accessed.
How a particular attribute is stored or defined, it is decided first by the environment and
then it has to be designer's decision; your decision. Because, the organization or system
will not object rather they will not even know the form in which you have defined an
attribute. You have to make sure that the system works properly, it fulfills the
requirement; after that you do it as per your convenience and in an efficient way.
Symbols for Attributes
Simple
Composite
Multi-valued
Derived
Figure 3: Symbol used for different types of attributes in E-R diagram
An example diagram representing all types of attributes is given below:
78
img
Database Management System (CS403)
VU
Example
Experience
empId
empName
address
EMPLOYEE
dateHired
emp_Qual
street
houseNo
Figure 4: Example entity type with attributes of different types
This concludes this lecture.
Summary:
In this lecture we have discussed entity and attribute. We discussed that there are three
different notions for which the term entity is used and we looked into these three terms in
detail. They are entity type, entity instance and entity set. An entity type is name or label
assigned to items or objects existing in an environment and having same or similar
property. An entity instance is a particular item or instance that belongs to a particular
entity type and a collection of entity instances is called an entity set. We also discussed in
this lecture the attribute component of the E-R data model and its different types. The
third component the E-R data model, that is, the relationship will be discussed in the next
lecture.
Exercises:
·
Take a look into the system where you work or study or live, identify different
entity types in that environment. Associate different types of attributes with these
entity types.
·
Look at the same environment from different possible perspectives and realize the
difference that the change of perspective makes in the abstraction process that
results in establishing different entity types or/and their different properties.
79
Table of Contents:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules