UNIT-1

Data Vs Information, Data base System Applications, data

base System vs file System, View of Data, Data
Abstraction, Instances and Schemas, data Models – the
ER Model – Relational Model – Other Models, Database
Languages – DDL – DML, database Access for
applications Programs, data base Users and Administrator,
Transaction Management, data base System Structure –
Storage Manager – the Query Processor.
Data base design and ER diagrams – Entities, Attributes
and Entity sets – Relationships and Relationship sets –
Additional features of ER Model – Concept Design with
the ER Model –Data Modeling checklist.
1
Data Vs Information

Data and information is interrelated

Data usually refers to raw data, or unprocessed data, data

that hasn’t been analyzed or processed in any manner

Once the data is analyzed, it is considered as information

Processed data is called information

2
What is a DBMS?

A database-management system (DBMS) is a collection of

interrelated data and a set of programs to access the data

The goal of a DBMS is to store and retrieve database

information

Database System Applications

Banking: For customer and accounts information

Airlines: For reservations and schedule information

3
Universities:For student information, course registrations

Credit card transactions

Sales: For customer, product, and purchase information

Manufacturing: For production, inventory, orders

Human resources: For employees information, salaries,

tax deductions

Data base system vs File system

File system has a number of major disadvantages

4
Data redundancy and inconsistency
-Multiple file formats, duplication of information in different
files
-Example: Savings account and Current account

Difficulty in accessing data

-Need to write a new program to carry out each new task
-Example: when new query is asked

Data isolation
-Multiple files and formats

Integrity problems
-Values in the database must satisfy constraints
-Hard to add new constraints or change existing ones 5
Atomicity of updates
-Failures may leave database in an inconsistent state with
partial updates carried out
-Example: Transfer of funds from one account to another
should either complete or not happen at all (atomic)

Concurrent access by multiple users

-Concurrent access needed for performance
-Uncontrolled concurrent accesses can lead to
inconsistencies
-Example: Two people reading a balance and updating
account at the same time

Security problems
-Hard to provide user access to some, but not all, data
6
Database systems offer solutions to all the above problems

View of Data

A major purpose of a database system is to provide users

with an abstract view of the data. That is the system hides
certain details of how the data is stored and maintained

Data Abstraction
Looking at the necessary information

Developers hide the complexity from users through

several levels of abstraction, to simplify users interactions
with the system
7
 View level
. . . . . . .
View 1 View 2 View n

Logical level

Physical level

Figure 1.1 The three levels of data abstraction

8
Physical level

The lowest level of abstraction describes how the data is

actually stored

The physical level describes complex low-level data

structures in detail

Logical level

The next-higher level of abstraction describes what data

is stored in the database, and what relationships exist
among those data
9
The logical level describes the entire database in terms of a small
number of relatively simple structures

Programmers and Administrators work at this level

View level

The highest level of abstraction describes only part of the entire

database

The view level of abstraction exists to simplify users interaction

with the system

10
Example:

struct customer
{
customer_id: string;
customer_name: string;
customer_street: string;
customer_city: string;
};

At the physical level, a customer record can be described

as a block of consecutive storage locations

11
At the logical level, each such record is described by a
structure definition, as shown above

At the view level, computer users see a set of application

programs that hide details of the data types

At the view level, several views of the database are

defined, and database users see these views

12
The views also provide a security mechanism to prevent
users from accessing certain parts of the database

For example, students can access only their details, they

cannot able to access other students details.

Instances and Schemas

The collection of information stored in the database at a

particular moment is called an instance of the database

13
The overall design of the database is called the database
schema

The concept of database schemas and instances can be

understood by analogy to a program written in a
programming language

A database schema corresponds to the variable declarations

in a program

The values of the variables in a program at a point in time

correspond to an instance of a database schema

Database systems have several schemas, partitioned

according to the levels of abstraction 14
The physical schema describes the database design at the
physical level. (i.e. how data is to be represented and
stored (files, indices, etc.) in secondary storage using a
particular database management system (DBMS) (e.g.,
Oracle RDBMS, Sybase SQL Server, etc.))

The logical schema (conceptual schema) describes the

database design at the logical level

A database may also have subschemas at the view level,

that describe different views of the database

Programmers develop applications by using the logical

schema
15
Physical schema is hidden beneath the logical schema,
and can be changed easily without affecting application
programs

Data Models

Data model describes the data (that is to be stored in the

database), data relationships and consistency constraints

16
Types of Data Models

Relational Model

The Entity-Relationship Model

Object-based data models (Object-oriented and Object-

relational)

Semistructured data model

Network model

Hierarchical model
17
Relational Model

The relational model uses a collection of tables to represent

both data and the relationships among those data

Example

Figure 1.2 Customer table

18
The relational model is an example of a record-based
model

Record-based models are so named because the database is

structured in fixed-format records of several types

Each table contains records of a particular type. Each

record type defines a fixed number of fields, or attributes

The columns of the table correspond to the attributes of

the record type

The relational data model is the most widely used data

model
19
Entity-Relationship Model

The entity-relationship (E-R) data model is a collection of

entities and relationships among these entities

An entity is a “thing” or “object” in the real world that is

distinguishable from other objects

For example, each person is an entity, and bank accounts

can be considered as entities

Entities are described in a database by a set of attributes

20
For example, the attributes account-number and
balance may describe one particular account in a bank,
and they form attributes of the account entity set

Attributes customer-name, customer-street and

customer-city may describe a customer entity

A relationship is an association among several entities

For example, a depositor relationship associates a

customer with each account that he has

21
The set of all entities of the same type is called an entity
set

The set of all relationships of the same type is called

relationship set

The overall logical structure (schema) of a database can

be expressed graphically by an E-R diagram, which is
built up from the following components:

Rectangles, which represent entity sets

Ellipses, which represent attributes

22
Diamonds, which represent relationships among entity
sets

Lines, which link attributes to entity sets and entity

sets to relationships
Example

Figure 1.3 23
Object-based data models (Object-oriented and Object-
relational)
In Object-oriented data model data is represented in the
form of objects (OOP principles can be applied among
the data)
Object-relational data model is essentially a relational
model with object-oriented features
-A field may also contain an object with attributes and
operations
-Complex objects can be stored in relational tables

Semistructured data model

Individual data items of the same type may have

different sets of attributes
24
This is in contrast with the data models mentioned
earlier, where every data item of a particular type must
have the same set of attributes

The extensible markup language (XML) is widely used

to represent semistructured data

Network model

Data in this model is represented by collection of

records and relationships among data are connected by
links

25
 22 Dustin 7 45.0 101 10/10/98

31 Lubber 8 55.5 102 11/10/98

74 Horatio 9 35.0 103 09/08/98

Hierarchical model

Hierarchical model is same as the network model

In this model records are represented in the form of

tree

26
 22 Dustin 7 45. 31 Lubber 8 55.
0 5
101 10/10/98 102 11/10/98

Database Languages
-Data Definition Language (DDL)
-Data Manipulation Language (DML)
-Data Control Language (DCL)
-Data Transaction Language (DTL)

These are not separate languages; instead they simply

form parts of a single database language, such as SQL
language 27
Data Definition Language (DDL):

This supports the creation, deletion, and modification of

definitions for tables, views and indexes

Integrity constraints can be defined on tables, either when

the table is created or later

After executing the DDL statement, it updates a special

set of tables called the data dictionary or data directory

A data dictionary contains metadata-that is, data about

data

The schema of a table is an example of metadata 28

A database system consults the data dictionary before
reading or modifying actual data

The data stored in the database must satisfy certain

consistency constraints

The database systems check these constraints every time

the database is updated

29
DDL Commands
-CREATE
-ALTER
-DROP
-TRUNCATE

CREATE command

Used to create table

Syntax:
CREATE TABLE table-name (Fieldname1 data_type,
Fieldname2 data_type , …. ….. ….)

30
Example
CREATE TABLE Sailors (sid NUMBER(2), sname
VARCHAR2(20), rating NUMBER(2))

ALTER command

Adding a new field in to the existing relation

Syntax: ALTER TABLE tablename

ADD (fieldname Field_datatype)

Example: ALTER TABLE Sailors

ADD (age NUMBER(2))

31
Modifying an existing field

Syntax: ALTER TABLE tablename

MODIFY (fieldname new_field_datatype)

Example
ALTER TABLE Sailors MODIFY (age NUMBER(3,1))
Deleting an existing field

Syntax: ALTER TABLE tablename

DROP COLUMN Fieldname

Example: ALTER TABLE Sailors DROP COLUMN age

32
DROP command

Used to delete an existing table

Syntax: DROP TABLE tablename
Example: DROP TABLE Sailors

TRUNCATE command

TRUNCATE Removes all rows from a table without

backup

Syntax: TRUNCATE table tablename

Example: TRUNCATE table Sailors

33
Data-Manipulation Language
A data-manipulation language (DML) is a language
that enables users to access or manipulate data

Data manipulation is
--The retrieval of information stored in the database
--The insertion of new information into the database
--The deletion of information from the database
--The modification of information stored in the database

There are of two types:

Procedural DMLs require a user to specify what data is
needed and how to get those data
34
Declarative DMLs (also referred to as nonprocedural
DMLs) require a user to specify what data is needed
without specifying how to get those data

SQL is nonprocedural Language

DML Commands

-INSERT
-DELETE
-UPDATE
-SELECT

35
INSERT command

Inserting record into a table

Syntax: INSERT INTO table-name VALUES
(field1,field2,…)
Example:
INSERT INTO Sailors values (22,'Dustin',7,45.0)

Inserting a record that has some null attributes

Requires identifying the fields that actually get data
Syntax: INSERT INTO table-name (field1,field4) VALUES
(value1,value2)

36
Inserting records from another table
Syntax: INSERT INTO table_name1 SELECT * FROM
table_name2

UPDATE command
For modifying attribute values of (some) tuples in a table
Syntax: UPDATE tablename SET column1=value1,…,
columnn=valuen WHERE condition
Example: UPDATE Sailors SET age=34.5 WHERE sid=22

DELETE command
Removing specified rows from a table
Syntax: DELETE FROM tablename WHERE condition
Example: DELETE FROM Sailors WHERE sid=22

37
Removing all rows from a table
Syntax: DELETE FROM tablename
Example: DELETE FROM Sailors

Data-Control Language

This subset of SQL controls a database, including

administrative privileges and saving data

38
DCL Commands
-GRANT
-REVOKE

GRANT Command

It is used to provide access rights or privileges on the

database objects to the users.

Syntax:
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC}

39
privilege_name is the access right or privilege granted
to the user. Some of the access rights are ALL,
EXECUTE, and SELECT

object_name is database object name like TABLE,

VIEW, STORED PROCEDURE

user_name is the name of the user to whom an access

right is being granted

--PUBLIC is used to grant access rights to all users

40
Example:

GRANT SELECT ON employee TO user1;

REVOKE Command:

The REVOKE command removes user access rights

or privileges to the database objects

41
Syntax:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC}

Example:
REVOKE SELECT ON employee FROM user1;

REVOKE a SELECT privilege on employee table from

user1

user1 will not be able to SELECT data from that table

42
Data-Transaction Language

It is used to manage transactions in the database

It is used to manage the changes made to the data in a

table by DML statements

DTL Commands
-COMMIT
-ROLLBACK

COMMIT Command

The COMMIT command is used to save changes invoked

by a transaction to the database. 43
Syntax:

COMMIT;

Example:

44
DELETE FROM CUSTOMERS WHERE AGE = 25;

COMMIT;

Thus, two rows from the table would be deleted and the
SELECT statement would produce the following result.

45
ROLLBACK Command

The ROLLBACK command is used to undo transactions

that have not already been saved to the database.

Syntax:
ROLLBACK;

Example:

46
DELETE FROM CUSTOMERS WHERE AGE = 25;

ROLLBACK;

Thus, the delete operation would not impact the table and
the SELECT statement would produce the following
result.

47
Database Access for Application Programs

Application programs are used to interact with the

database

Application programs are usually written in a language,

such as C, C++, or Java

Examples in a banking system are programs that debit

accounts, credit accounts, or transfer funds between
accounts

To access the database, DML statements need to be

executed from the host language
48
There are two ways to do this:

By providing an application program interface (set of

procedures) that can be used to send DML statements to
the database, and retrieve the results

--The Java Database Connectivity (JDBC) with the

Java language is a commonly used application program
interface standard

By extending the host language syntax to embed DML

calls within the host language program

49
 --Usually, a special character prefaces DML calls, and
a preprocessor, called the DML precompiler, converts the
DML statements to normal procedure calls in the host
language

Database Users and Administrator

People who work with a database can be categorized as

database users or database administrator

50
Database Users and User Interfaces

There are four different types of database-system users

Different types of user interfaces have been designed for

the different types of users

1.Naive users: users who interact with the system by

using one of the application programs that have been
written previously

(Naive users are users who do not have knowledge about

the system)
51
They uses forms interface, where the users can fill in
appropriate fields of the form

Ex1:transfer $50 from account A to account B

-user has to enter amount of money to be transferred,

-account from which the money is to be transferred
-account to which the money is to be transferred

Ex2: finding account balance over the Internet. user

may access a form, where he enters his account number

52
2.Application programmers: computer professionals
who write application programs

Application programmers can choose from many tools to

develop user interfaces

-Rapid Application Development (RAD) tools enable an

application programmer to construct forms and reports
without writing a program

-Fourth-generation languages, include features to

facilitate the generation of forms and the display of data
on the screen
-Most major commercial database systems include a
fourth-generation language 53
3.Sophisticated users interact with the system without
writing programs

-They form their requests in a database query language.

-Analysts who submit queries to explore data

-Online Analytical Processing (OLAP) tools simplify

analysts tasks by letting them view summaries of data in
different ways

Ex: an analyst can see total sales by region (for example,

North, South, East, and West), or by product, or by a
combination of region and product (that is, total sales of
54
each product in each region)

-Data mining tools, help analysts to find certain kinds of

patterns in data.

4.Specialized users: users who write specialized database

applications

-Computer-aided-design(CAD) data, which includes

spatial information about how objects - such as buildings,
cars, or aircraft - are constructed.

-Geographic information systems stores geographic data

(such as road maps, land-usage maps, satellite images
etc,.) 55
Database Administrator

A person who has central control over both the data and
the programs that access the data is called a database
administrator (DBA)

The functions of a DBA include:

-Schema definition. The DBA creates the original

database schema by executing a set of data definition
statements in the DDL

-Storage structure and access-method definition.

Storage devices used to store the data and type of
indexing used 56
-Schema and physical-organization modification. The
DBA carries out changes to the schema to reflect the
changing needs of the organization, or to improve the
performance

-Granting of authorization for data access. By granting

different types of authorization, the DBA can regulate
which parts of the database various users can access.

--The authorization information is kept in a file that the

database system consults whenever someone attempts to
access the data in the system

57
-Routine maintenance.

Activities such as:

-Periodically backing up the database, either onto tapes or

onto remote servers, to prevent loss of data in case of
disasters such as flooding

-Ensuring that enough free disk space is available for

normal operations, and upgrading disk space as required

-Monitoring jobs running on the database and ensuring

that performance is not degraded by very expensive tasks
submitted by some users
58
Transaction Management

A transaction is a collection of operations that performs

a single logical function in a database application

Ex: -Funds transfer in which one account (say A) is

debited and another account (say B) is credited
-Transaction: Funds transfer
-Operations: debit and credit

Each transaction possess ACID properties

Atomicity: All operations of a transaction has to execute

or no operations at all
59
Consistency: Database must be consistent after
execution of a transaction

Isolation: Each transaction is unaware of executing of

other concurrent transactions

Durability: After successful execution of a transaction

the new values must persist despite of system failures

Ensuring atomicity and durability properties is the

responsibility of transaction-management component

Consistency is the responsibility of programmer

60
Isolation is the responsibility of concurrency-control
manager to control the interaction among the concurrent
transactions

Ex: -Funds transfer in which one account (say A) is

debited and another account (say B) is credited

Atomicity - execute both debit and credit operations

Consistency - sum of (A+B) must be same before and
after execution of funds transfer
Durability - new values of A and B must persist after
execution of funds transfer
Isolation - no concurrent transactions

61
Database
System
Structure

Fig 1.4 DBMS structure 62

Storage Manager

A storage manager is a module that provides the

interface between the low-level data stored in the
database and the application programs and queries
submitted to the system

The raw data is stored on the disk

The storage manager is responsible for storing,

retrieving, and updating data in the database

63
The storage manager components include:

-Authorization and integrity manager, which tests for

the satisfaction of integrity constraints and checks the
authority of users to access data

-Transaction manager, which ensures that the database

remains in a consistent (correct) state despite system
failures, and that concurrent transaction executions
proceed without conflicting

-File manager, which manages the allocation of space on

disk storage and the data structures used to represent
information stored on disk
64
-Buffer manager, which is responsible for fetching data
from disk storage into main memory, and deciding what
data to cache in main memory

The storage manager implements several data structures

as part of the physical system implementation:

-Data files, which store the database itself

-Data dictionary, which stores metadata about the

structure of the database, in particular the schema of the
database

-Indices, which provide fast access to data items that hold

particular values 65
The Query Processor

The query processor components include

-DDL interpreter, which interprets DDL statements and

records the definitions in the data dictionary

-DML compiler, which translates DML statements in a

query language into an evaluation plan consisting of low-
level instructions that the query evaluation engine
understands

--A query can usually be translated into any of a number

of alternative evaluation plans that all give the same result
66
--The DML compiler also performs query optimization,
that is, it picks the lowest cost evaluation plan from
among the alternatives ("lowest cost plan" we mean an
access path to the data that takes the least amount of time)

-Query evaluation engine, which executes low-level

instructions generated by the DML compiler

67
Database Design

The database design process can be divided into six

steps. The ER model is most relevant to the first three
steps:

(1) Requirements Analysis:

The first step in designing a database application is to

understand what data is to be stored in the database

We must find out what the users want from the database

This is usually an informal process

68
 --That involves discussions with user groups,

--Study of the current operating environment and

how it is expected to change,

--Analysis of any available documentation on

existing applications that are expected to be replaced

Automated tools are used for organizing and presenting

the information gathered in this step

69
(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

design
70
The task in the 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

(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, and to refine it

71
Schema can be refined using normalization

(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

72
This step may simply involve building indexes on some
tables and clustering some tables, or

it may involve a redesign of parts of the database

schema obtained from the earlier design steps

(6) Security Design:

We identify different user groups and different roles

played by various users (e.g., the development team for a
product, the customer support representatives, the
product manager)

73
For each role and user group, assign the privileges

Complete database design will probably require a

subsequent tuning phase in which all six kinds of design
steps are interleaved and repeated until the design is
satisfactory

74
ENTITIES, ATTRIBUTES, AND ENTITY SETS

An entity is an object in the real world that is

distinguishable from other objects

Examples: toy, the toy department, the manager of the

toy department

An entity set is a collection of similar entities

Ex: entity set -- Employees that contains employee

entities

75
An entity is described using a set of attributes

Ex: Employees entity set could use name, social security

number (ssn), and parking lot (lot) as attributes

For each attribute associated with an entity set, we must

identify a domain of possible values

Ex: Domain associated with the attribute name of

Employees might be the set of 20-character strings

76
Ex: Domain values for attribute rating consists of
integers 1 through 10

Further, for each entity set, we choose a key

A key is a minimal set of attributes whose values

uniquely identify an entity in the set

There could be more than one candidate key; if so, we

designate one of them as the primary key

77
The Employees entity set with attributes ssn, name, and
lot is shown in Figure 2.1

An entity set is represented by a rectangle, and an

attribute is represented by an ellipse

Each attribute in the primary key is underlined

The key is ssn

78
RELATIONSHIPS AND RELATIONSHIP SETS

A relationship is an association among two or more

entities

For example, we may have the relationship that John works

in the pharmacy department
79
As with entities, we may wish to collect a set of similar
relationships into a relationship set

A relationship set can be thought of as a set of n-tuples:

Each n-tuple denotes a relationship involving n entities e1

through en, where entity e1 is in entity set E1

Figure 2.2 shows the relationship set Works_In, in which

each relationship indicates a department in which an
employee works
80
A relationship can also have descriptive attributes

81
Descriptive attributes are used to record information about
the relationship, rather than about any one of the
participating entities

For example, we may wish to record that John works in the

pharmacy department as of January 1991

This information is captured in Figure 2.2 By adding an

attribute, since, to Works_In

A relationship must be uniquely identified by the

participating entities, without reference to the descriptive
attributes

82
In the Works_In relationship set, for example, each
Works_In relationship must be uniquely identified by the
combination of employee ssn and department did

An instance of a relationship set is a set of relationships

An instance of the Works_In relationship set is shown in

Figure 2.3

83
84
As another example of an ER diagram, suppose that each
department has offices in several locations and we want to
record the locations at which each employee works

This relationship is ternary because we must record an

association between an employee, a department, and a
location

The ER diagram for this variant of Works_In, which we

call Works_In2, is shown in Figure 2.4

85
86
For example, consider the Reports_To relationship set that
is shown in Figure 2.5

Since employees report to other employees, every

relationship in Reports_To is of the form (emp1,emp2),
where both emp1 and emp2 are entities in Employees

However, they play different roles: emp1 reports to the

managing employee emp2, which is reflected in the role
indicators supervisor and subordinate in Figure 2.5

87
If an entity set plays more than one role, the role indicator
concatenated with an attribute name from the entity set
gives us a unique name for each attribute in the relationship
set 88
For example, the Reports_To relationship set has attributes
corresponding to the ssn of the supervisor and the ssn of the
subordinate, and the names of these attributes are
supervisor_ssn and subordinate_ssn

89
ADDITIONAL FEATURES OF THE ER MODEL
Key Constraints:

Consider relationship set called Manages between the

Employees and Departments entity sets such that each
department has at most one manager, although a single
employee is allowed to manage more than one department

The restriction that each department has at most one

manager is an example of a key constraint, means
Departments entity appears in at most one Manages
relationship

This restriction is indicated in the ER diagram of Figure

2.6 by using an arrow from Departments to Manages 90
An instance of the Manages relationship set is shown in
Figure 2.7

91
92
A relationship set like Manages is sometimes said to be
one-to-many

In contrast, the Works_In relationship set is said to be

many-to-many

If we add the restriction that each employee can manage at

most one department to the Manages relationship set, which
would be indicated by adding an arrow from Employees to
Manages in Figure 2.6, we have a one-to-one relationship
set

93
Key Constraints for Ternary Relationships

To indicate a key constraint on entity set E in relationship

set R, we draw an arrow from E to R

In Figure 2.8, we show a ternary relationship with key

constraints

Here key constraint specifies that, each employee works in

at most one department, and at a single location

94
95
An instance of the Works_In3 relationship set is shown in
Figure 2.9

Notice that each department can be associated with several

employees and locations, and each location can be
associated with several departments and employees;
however, each employee is associated with a single
department and location

96
97
Participation Constraints

Every department is required to have a manager. This

requirement is an example of a participation constraint

The participation of the entity set Departments in the

relationship set Manages (Fig 2.6) is said to be total

A participation that is not total is said to be partial

As an example, the participation of the entity set

Employees in Manages (Fig 2.6) is partial, since not every
employee gets to manage a department

98
In the Works_In relationship set (Fig 2.3), the participation
of both Employees and Departments is total

The ER diagram in Figure 2.10 shows both the Manages

and Works_In relationship sets and all the given constraints

If the participation of an entity set in a relationship set is

total, the two are connected by a thick line; the presence of
an arrow indicates a key constraint

99
100
Weak Entities

Entity set which does not have a key is called weak entity
set

Dependents is an example of a weak entity set

A weak entity can be identified uniquely only by

considering some of its attributes in conjunction with the
primary key of another entity set, which is called the
identifying owner

101
The following restrictions must hold:

-The owner entity set and the weak entity set must
participate in a one-to-many relationship set. This
relationship set is called the identifying relationship set of
the weak entity set

-The weak entity set must have total participation in the

identifying relationship set

102
For example, a Dependents entity can be identified
uniquely by key ssn of Employees entity set and the
attribute pname of the Dependents entity set

The set of attributes of a weak entity set that uniquely

identify a weak entity for a given owner entity is called a
partial key of the weak entity set. In our example pname is
a partial key for Dependents

The Dependents weak entity set and its relationship to

Employees is shown in Figure 2.11

The total participation of Dependents in Policy is indicated

by linking them with a dark line
103
The arrow from Dependents to Policy indicates that each
Dependents entity appears in at most one Policy
relationship

To identify Dependents is a weak entity and Policy is its

identifying relationship, we draw both with dark lines

104
To indicate that pname is a partial key for Dependents, we
underline it using a broken line

Class Hierarchies

Sometimes it is natural to classify the entities in an entity

set into subclasses

For example, we might want to talk about an Hourly_Emps

entity set and a Contract_Emps entity set to distinguish the
basis on which they are paid

105
We might have attributes hours_worked and hourly_wages
defined for Hourly_Emps and an attribute contractid defined
for Contract_Emps

The attributes defined for an Hourly_Emps entity set are

the attributes for Employees plus Hourly_Emps

The attributes for the entity set Employees are Inherited

by the entity set Hourly_Emps, and that Hourly_ Emps ISA
(read is a) Employees

Figure 2.12 illustrates the class hierarchy

The entity set Employees may also be classified using a

different criterion 106
107
For example, we might identify a subset of employees as
Senior_Emps

A class hierarchy can be viewed in one of two ways:

--Employees (the superclass) is specialized into
subclasses. subclass-specific attributes are then added
--Hourly_Emps and Contract_Emps are generalized by
Employees. As another example, two entity sets Motorboats
and Cars may be generalized into an entity set Motor_
Vehicles

We can specify two kinds of constraints with respect to ISA

hierarchies, namely, overlap and covering constraints

108
Overlap constraints determine whether two subclasses are
allowed to contain the same entity

For example, can John be both an Hourly_Emps entity and

a Contract_Emps entity? …no

Can he be both a Contract_Emps entity and a Senior_Emps

entity? …yes

We denote this by writing 'Contract_Emps OVERLAPS

Senior_Emps'

Covering constraints determine whether the entities in the

subclasses collectively include all entities in the superclass
109
For example, does every Employees entity have to belong
to one of its subclasses? …no

Does every Motor_Vehicles entity have to be either a

Motorboats entity or a Cars entity? …yes

We denote this by writing 'Motorboats AND Cars COVER

Motor_Vehicles'

Aggregation
Use aggregation when we need to express a relationship
among relationships

Aggregation allows us to indicate that a relationship set

(identified through a dashed box) participates in another
relationship set.This is illustrated in Figure 2.13 110
111