Chapter 2, Problem 1RQ

Problem

Define the following terms: data model, database schema, database state, internal schema,
conceptual schema, external schema, data independence, DDL, DML, SDL, VDL, query
language, host language, data sublanguage, database utility, catalog, client/server architecture,
three-tier architecture, and n-tier-architecture.

Step-by-step solution

Step 1 of 19

Data model

The data model describes the logical structure of the database and it introduces abstraction in
the DBMS (Database Management System). The data model provides a tool to describe the data
and their relationships.

Comment

Step 2 of 19

Database Schema
The database schema describes the overall design of the database. It is a basic structure to
define how the data is organized in the database. The database schema can be depicted by the
schema diagrams.

Comment

Step 3 of 19

Database state

The actual data stored in the database in a moment in time is called the database state.

Comment

Step 4 of 19

Internal Schema

It is also referred as the Physical level schema. The internal schema represents the structure of
the data as viewed by the DBMS and it describes the physical storage structure of the database.

Comment

Step 5 of 19

Conceptual Schema
It is also referred to as the Logical level schema. It describes the logical structure of the whole
database for a group of users. It hides the internal details of the physical storage structure.

Comment

Step 6 of 19

External Schema

The external schema referred as User level schema. It describes the data which is viewed by the
end users. This schema describes the part of the database for a user group and it hides the rest
of the database from that user group.

Comment

Step 7 of 19

Data independence

The capacity to change the schema at the physical level of a database system without affecting
the schema at the conceptual or external level is called data independence.

Comment

Step 8 of 19

DDL
DDL stands for Data Definition Language. It is used to create, alter, and drop the database
tables, views, and indexes.

Comment

Step 9 of 19

DML

DML stands for Data Manipulation Language. It is used to insert, retrieve, update, and delete the
records in the database.

Comment

Step 10 of 19

SDL

SDL stands for Storage Definition Language. It is used to specify the internal schema of the
database and specify the mapping between two schemas.

Comment

Step 11 of 19

VDL
VDL stands for View Definition Language. It specifies the user views and their mappings to the
logical schema in the database.

Comment

Step 12 of 19

Query Language

The query language is a high-level language used to retrieve the data from the database.

Comment

Step 13 of 19

Host Language

The host language is used for application programming in a database. The DML commands are
embedded in a general-purpose language to manipulate the data in the database.

Comment

Step 14 of 19

Data Sublanguage

The data manipulation language commands are embedded in a general-purpose language to

manipulate the data such as insert, update, and delete operations in the database, here the DML
is referred as a data sublanguage.

Comment

Step 15 of 19

Database utility

The database utility is a software module to help the DBA (Database Administrator) to manage
the database.

Comment

Step 16 of 19

Catalog

The catalog stores the complete description of the database structure and its constraints.

Comment

Step 17 of 19

Client/server architecture

The client/server architecture is a database architecture and it contains two modules. A client
module usually a PC that provides the user interface. A server module can respond the user
queries and provide services to the client machines.
Comment

Step 18 of 19

Three-tier architecture

The three-tier architecture consists of three layers such as client, application server, and
database server. The client machine usually contains the user interface and the intermediate
layer (application layer) running the application programs and storing business rules. The
database layer stores the data.

Comment

Step 19 of 19

n-tier architecture

The n-tier architecture consists of four or five tiers. The intermediate layer or business logic layer
is divided into multiple layers. And distributing programming and data throughout a network.

Comment
Chapter 2, Problem 2RQ

Problem

Discuss the main categories of data models. What are the basic differences among the relational
model, the object model, and the XML model?

Step-by-step solution

Step 1 of 2

The three main categories of data models are as follows:

• High-level or Conceptual data model

• Representational or implementational data model

• Low -level or Physical data model

Comment

Step 2 of 2

The Differences between relational model, the object model and XML model are as
follows:
Relational Model Object Model XML Model

The data in relational model It refers to the model which The data in the XML model is in
is represented logically and deals with how applications hierarchical mode. We can
information about the will interact with the resources define different types of the
relationship types. from any external resource. data in a single XML document.

It also deals with the

The data is defined in
relationship between the
columns with the field name The data in XML document
classes, methods and
and the entire data in a does not have any inherent
properties of the classes.
column must be in the same ordering.
type. It is closer to conceptual data
models.

The relational database The classes in the object

Data is represented in the form
uses high-level query model are designed in acyclic
of tags known as elements.
language graph manner.

Example: Document Object

Example: SQL Example: Stylus studio
Model (DOM)

Comment
Chapter 2, Problem 3RQ

Problem

What is the difference between a database schema and a database state?

Step-by-step solution

Step 1 of 1

Difference between a database schema and a database state:-

Database schema is a description of the database and the database state is the database it
self.

The description of a database is called the database schema, which is specified during
database design and is not expected to change frequently. Most data models have certain
convention for displaying schemas as diagram. A displayed schema is called a schema diagram
schema diagram displays the structure of each record type but not the actual instances of
records. A schema diagram displays only some aspects of a schema, such as the names of
record types and data items, and some types of constraints.

The data in the database at a particular moment in time is called a database state. It is also
called the current set of occurrences are instances in the data base. In a given database state,
each schema construct has its own current set of instances many database states can be
constructed to covers pond to a particular data base schema. Every time we insert are delete a
record are change the value of a data item in a record we change one state of the database into
another state.
When we define a new database we specify its database schema only to the DBMS. At this
point, the covers pending database state in the empty state with no data. The DBMS in partly
responsible for ensuring the every state of the database is a valid state. – that is , a state that
satisfies the structure and constraints specified in the schema.

The schema is sometimes called the intension, and a database state is called an extension of
the schema.

Comment
Chapter 2, Problem 4RQ

Problem

Describe the three-schema architecture. Why do we need mappings among schema levels? How
do different schema definition languages support this architecture?

Step-by-step solution

Step 1 of 3

Three-schema architecture :-

The goal of he three-schema architecture is to separate the user applications and the physical
database. In this architecture schemas can be defined at the following three levels.

(1) internal level :-

it has an internal schema, which describes the physical storage structure of the database.

(2) Conceptual level :-

It has a conceptual schema, which describes the structure of the whole database for a
community of users. The conceptual schema hides the details of physical storage structures and
concentrates on describing entities, data types, relationships, user operations and constraints.

Comment
 Step 2 of 3

(3) External level :-

It includes a number of external schema are user views. Each external schema describes the
part of the database that a particular user group is interested in and hides the rest of the
database from that group. A high-level data model on an implementation data model can be used
at this level.

Need of mapping :-

The process of transforming requests and results between levels are called mappings.

The conceptual internal mapping define the coverspondence between the conceptual view and
the stared database. It specifies how conceptual records and fields are represented at the
internal level.

An external conceptual mapping defines the covers pondence between a particular external view
and the conceptual view.

Comment

Step 3 of 3

Different schema definition language :-

DDL :-

Data definition language is used to specify conceptual and internal schemas for the database
and any mappings between the two, the DBMS will have a DDL compiler whose function is to
process DDL statements in order to identify descriptions of the schema constructs and to store
the schema description in the DBMS catalog.

SDL :-
Storage definition language is used to specify the internal schema. The mappings between the
two schemas may be specified in either one of these languages. In mast relational DBMS’s to
day, there is no specific language that performs the sale of SDL. Instead the internal schema is
specified by a combination of parameters and specifications related to storage.

VDL :-

View Definition Language is used to specify user view and their mappings to the conceptual
schema but in most DBMS’s the DDL is used to define both conceptual and external schemas. In
relational DBMS’s SQL is used in the sale of VDL to define user are application views as results
of predefined queries.

Comment
Chapter 2, Problem 5RQ

Problem

What is the difference between logical data independence and physical data independence?
Which one is harder to achieve? Why?

Step-by-step solution

Step 1 of 3

The data independency refers to the task of changing a level of schema without affecting the
other levels or the levels at higher level.

There are following two different ways in which data independence is achieved:

• Logical data independence

• Physical data independence

Comment

Step 2 of 3

Logical data independence is the capacity to change the conceptual schema without changing
the external schema. This only requires changing the view definition and the mappings. For
example, changing the constraints of an attribute that does not affect the external schema,
insertion and deletion of data items that changes the table size but does not affect the external
schema.

Physical data independence is the capacity to change the internal schema without changing the
conceptual schema or the external schema. For example, reorganization of files on the physical
storage to enhance the operations on the database and since the data is the same and only the
files are relocated, the conceptual/external schema remains unaffected.

Comment

Step 3 of 3

The logical data independence is harder to achieve. Changing the attribute constraints and the
structure of the table might result in invalid data for the changed attributes. The table or the
application program that references the modified table will get affected which should not be the
case in logical data independence.

Comment
Chapter 2, Problem 6RQ

Problem

What is the difference between procedural and nonprocedural DMLs?

Step-by-step solution

Step 1 of 2

Difference between procedural and nonprocedural DML “-

Procedural DML :-

Procedural data manipulation language is called low level DML. Procedural DML must be
embedded in a general purpose programming language. This type of DML typically retrieves.
Individual records are objects from the database and process each separately. Therefore, it
needs to use programming language. Constructs, such as looping to retrieve and process each
record form a set of records.

Procedural DMLs are also called record –at-a-time DML.

Comment

Step 2 of 2
Non-procedural DML :-

Non-procedural is called high level DML. Non-procedural DML can be used on its own to specify
complex database operations concisely many DBMS’s allow high-level DML statements either to
be entered interactively from a display monitor ore terminal are to be embedded in a general-
purpose programming language.

A query in a high level DML often specifies which data to retrieve rather than how to retrieve it.
Therefore such languages are also called declarative.

Non-procedural DML requires a user to specify what data are needed without specifying low to
get these data.

Comment
Chapter 2, Problem 7RQ

Problem

Discuss the different types of user-friendly interfaces and the types of users who typically use
each.

Step-by-step solution

Step 1 of 7

User friendly interfaces provided by the DBMS are as follows:

(a)

Menu-Based interfaces:

• These interfaces contain the lists of options through which the user can send the request.

• Pull-down menus are a very popular technique in web-based user interfaces.

User who use the interface:

• These types of interfaces are used by the web browsing users and web clients.

Comment

Step 2 of 7
(b)

Forms-based interfaces:

• These types of interfaces display a form to each user.

• The user can fill the entries to insert new data.

• These Forms are usually designed and programmed for naive users as interfaces to recorded
transactions.

User who use the interface:

• User who wants to submit the online information by filling and submitting the details.

• Mostly used to create accounts on a website, or enrolling into some institution etc.

Comment

Step 3 of 7

(c)

Graphical user interfaces:

• A graphical user interfaces contain a diagrammatic form that comprises a schema to the user.

• The user can ask a query by manipulating the diagram.

• These interfaces use mouse as pointing device to pick certain parts of the displayed schema
diagram.

User who use the interface:

• Mostly used by the users who uses the electronic gadgets such as mobile phones and touch
screens.

• Users who uses the applications that are accessed by pointing devices.
Comment

Step 4 of 7

(d)

Natural language interfaces:

• These interfaces accept the request from the user and tries to interpret it.

• The natural language interfaces have its own schema which is like the database conceptual
schema.

User who use the interface:

• The Search engines in these days are using natural language interfaces.

• The users can use these search engines that accepts the words and retrieves the related
information.

Comment

Step 5 of 7

(e)

Speech input and output:

• These interfaces accept speech as an input and outputs the speech as a result.

User who use the interface:

• These types of interfaces are used in the inquiry for telephone directory or to get the flight
information over the smart gadgets, etc.
Comment

Step 6 of 7

(f)

Interfaces for parametric users:

• Paramedic users such as bank tellers have a small set of operations that they must perform
repeatedly.

• These interfaces contain some commands to perform a request with minimum key strokes.

User who use the interface:

• These can be used in bank transactions to deposit or withdrawal of money.

Comment

Step 7 of 7

(g)

Interfaces for the DBA:

• These interfaces contain some commands for creating accounts, to manipulate the database
and to perform some operations on the database.

User who use the interface:

• These interfaces are specifically used by the Database administrators.

Comment
Chapter 2, Problem 8RQ

Problem

With what other computer system software does a DBMS interact?

Step-by-step solution

Step 1 of 7

Database management system (DBMS):

A database management system (DBMS) is a set of program that empowers users to build and
maintain a database.

It is a general-purpose software system that enables the processes to define, construct,

manipulate, and share databases among various applications and users.

Comment

Step 2 of 7

List of other computer system software a database management system (DBMS) interacts
with:
The following are the list of other computer system software a database management system
(DBMS) interacts with:

• Computer-Aided Software Engineering (CASE) tools.

• Data dictionary systems.

• Application development environments.

• Information repository systems.

• Communication software.

Comment

Step 3 of 7

CASE tools:

The design phase of the database system often employs the CASE tools.

Comment

Step 4 of 7

Data dictionaries:

Data dictionaries are similar to database management system catalog, however, they include
variety of information.

• Typically, data dictionaries can be directly accessed by the database administrator (DBA)
whenever required.
Comment

Step 5 of 7

Application development environments:

Typically, application development environments often provide an environment to develop

database application and have facilities that aid in many features of database systems, including
graphical user interface (GUI) development, database design, querying, update, and application
program development.

• Examples of application development environments are listed below:

o JBuilder (Borland)

o PowerBuilder (Sybase)

Comment

Step 6 of 7

Information repository systems:

• The information repository is a kind of data dictionary that can also stores information like
design decisions, application program descriptions, usage standards, and user information.

• Like data dictionaries, information repository can also be directly accessed by the database
administrator.

Comment
 Step 7 of 7

Communication software:

• The database management system also requires interfacing with communication software.

• The main function of the communication software is to enable users residing remote from the
database system to access the database through personal computers, or workstations.

• The communication software are connected to the database system through communications
hardware like routers, local networks, phone lines, or satellite communication devices.

Comment
Chapter 2, Problem 9RQ

Problem

What is the difference between the two-tier and three-tier client/server architectures?

Step-by-step solution

Step 1 of 2

The difference between a two-tire architecture and a three tire architecture is that of a layers
through which data and queries pass at time of processing, for any database.

In two tire architecture there is two layers viz., Client layer (user interface) and query server or
transaction server. Application programs run on client side and when data processing is required
connection is established with the server (DBMS), where data is stored. Once connection is
established, transaction and query requests are sent using Open Database Connectivity’s API’s,
which are then processed at server side. It may also happen that client side takes care of user
interaction and query processing while server stores data, manages disks etc. Exact distribution
of functionality differs but two - tire architecture has two layers.

Comment

Step 2 of 2
In three- tire architecture there are three layers, and a new application or web layer is between
client and database service layer. The idea behind three tire architecture is to partition roles in
different layers and each layer has specific task. In three-tire architecture, user layer or client
layer provide user interface from where user can run query. Query gets processes at application
or web server layer. This layer also checks for any business constraints that may be imposed on
type of query user can send or verify credentials of user so has verify access permissions that
user has. This layer can also be called as Business logic layer. Finally Database server manages
storage of data in the system.

Comment
Chapter 2, Problem 10RQ

Problem

Discuss some types of database utilities and tools and their functions.

Step-by-step solution

Step 1 of 2

Few categories of database utilities and tools and their functions are:

1. Loading:

Load existing data files such as text files into the database.

• Transfer data from one dbms to another dbms easily used in many organizations.

• Vendors are offering the conversion tools. Those tools are useful loading programs.

2. Backup:

It is one of the utility that organize a backup copy of the database.

• Put entire database onto tape and those database backup copies can be used in the case of
catastrophic loss for recovering system state.

Comment
 Step 2 of 2

3. Database storage reorganization:

It is a utility that can be used to restructure a set of database files into a different file organization
to raise the performance of the database.

4. CASE tools:

CASE tools are used to produce a plan for a database application.

5. Data Dictionary system:

Information repository plays main role in data dictionary system.

• It is one of the repository is used to store design process, user information and application
program description.

• This information can be accessed by user when it is required.

• Information repository contains additional information than the DBMS catalog.

6. Performance monitoring:

It is used to control database usage and maintain stats.

• Those stats are used by the DBA in making selection, those selections are related to file
restructure and indexing for raise the performance of database.

There are several utilities are available those are

• Sorting the text files in the database.

• Data compression techniques handled by database.

Comment
Chapter 2, Problem 11RQ

Problem

What is the additional functionality incorporated in n-tier architecture (n > 3)?

Step-by-step solution

Step 1 of 1

It is customary to divide the layer between the user and the stored data in three tire architecture
into finer components, thereby giving rise to an n-tire architecture, where n may be 4 or 5.
Typically, the business logic layer is divided into multiple layer.

1. N-tire architecture distributes data and programming over the network.

2. Each tire can run on appropriate processor or operating system platform and can be handled
independently.

Another layer that is typically used by vendors of ERP and CRM packages is the middleware
layer which accounts for the front-end modules communicating with a number of back-end
databases.

Comment
Chapter 2, Problem 13E

Problem

Choose a database application with which you are familiar. Design a schema and show a samp
database for that application, using the notation of Figures 1.2 and 2.1. What types of additional
information and constraints would you like to represent in the schema? Think of several users o
your database, and design a view for each.
 Step-by-step solution

Step 1 of 2

Consider Flight Reservation system.

• Each flight is identifies by Number, and consists of one or more FLIGHT_LEGs with Leg_no.
And flies on certain weekdays.

• Each FLIGHT_LEG has scheduled arrival and departure time and arrival and departure airport
and one or more LEG_INSTANCEs – one for eachDate on which flight travels.

• FARE is kept for each flight and there are certain set of restrictions on FARE.

• For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are AIRPLANE used on
each leg and the actual arrival and departure times and airports.

• AIRPLANE is identified by an airplane id, and is of a particular AIRPLANE_TYPE. It has a fixe

no. of seats.

• CAN_LAN relates AIRPLANE_TYPE to the AIRPORTS at which they can land.

• AIRPORT is identified by airport code.

Comment

Step 2 of 2

Following constraints hold good on schema:

a. Asked flight number or flight leg is available on given date. Data can be checked from
LEG_INSTANCE table.

b. A non reserved seat must exist for specifies date and flight. We can get total number of seats
available from AIRPLANE.
c. Fligh_leg can correspond to existing flight number.

d. Arrival and code must be of existing airports.

e. Leg_instance can have entries only for valid Flight_number and leg_number combination.

f. Flight_number in any relation is of a valid flight that has its entry in FLIGHT table.

g. Airplane_type_name in CAN_LAND must be a vlaid name from AIRPLANE_TYPE.

Comment
Chapter 2, Problem 14E

Problem

If you were designing a Web-based system to make airline reservations and sell airline tickets,
which DBMS architecture would you choose from Section 2.5? Why? Why would the other
architectures not be a good choice?

Step-by-step solution

Step 1 of 4

There are four architectures discussed in section 2.5 in the textbook. They are

1. Centralized DBMS architecture

2. Basic Client/Server Architecture

3. Two-Tier Client/Server Architecture

4. Three-Tier Client/Server Architecture

Comment

Step 2 of 4

For designing a Web-based system to make airline reservations and sell airline tickets, Three-tie
client/server architecture will be the best choice.

• A web user interface is necessary as different types of users such as naive users or casual
users will interact with the system.

• Web user interface is placed in the client system.

• User can interact with user interface and submit the transactions.

• Web server can handle those transactions, validate the data and manipulate database
accordingly.

• Webserver/application server will handle the application logic of the system.

• The database server contains the DBMS.

Comment
 Step 3 of 4

In centralized DBMS architecture, DBMS functionality and user interface are performed on the
same system. But for a Web-based system, they must be on different systems.

Hence centralized DBMS architecture is not appropriate for web-based system.

Comment

Step 4 of 4

In three-tier Client/Server Architecture, the business logic is placed in application server or web
server.

Basic Client/Server architecture or Two-Tier Client/Server architecture can be considered

appropriate for web server if the business logic can be placed in database server or client. But if
business logic is placed in database server or client, it will be a burden.

Hence, Basic Client/Server architecture and Two-Tier Client/Server architecture are not
appropriate for web-based system.

Comment
Chapter 2, Problem 15E

Problem

Consider Figure 2.1. In addition to constraints relating the values of columns in one table to
columns in another table, there are also constraints that impose restrictions on values in a
column or a combination of columns within a table. One such constraint dictates that a column o
a group of columns must be unique across all rows in the table. For example, in the STUDENT
table, the Student_number column must be unique (to prevent two different students from havin
the same Student_number). Identify the column or the group of columns in the other tables that
must be unique across all rows in the table.

Step-by-step solution

Step 1 of 2

By using schema diagram of the database, the database tables are constructed. Each data bas
table contains column and those columns are unique.

Comment

Step 2 of 2

Group of columns that will be unique in each table are:

1. STUDENT: Student_number

2. COURSE: Course_number. If course name is separate for each course Course_name can
also be a column.
3. PREREQUISITE: Course_number can be a unique identifier but only if a course has single
PREREQUISITE or else Course_number and Prerequisite_number will together form unique
combination.

4. SECTION: Section_identifier

• Consider that no two sections can have the same Section_identifier.

• Look at that Section_identifier is unique only within a given course allow in a given term.

5. GRADE_REPORT: Section_identifier and Student_number.

• The Section_identifier will be different if a student takes the same course or different course in
other term.

Comment