-:CHAPTER 2:-

DATABASE SYSTEM CONCEPTS AND ARCHITECTURE

* 2 Marks Questions
1. Define Client module and server module?
The client–server model is a distributed application structure that partitions tasks or workloads between the
providers of a resource or service, called servers, and service requesters, called clients
A server host runs one or more server programs which share their resources with clients. A client does not share
any of its resources, but requests a server's content or service function.
2. What is data model?
Data model is a collection of concepts that can be used to describe the structure of a
database which provides the necessary means to achieve the abstraction. The structure of a database means that holds the data.
3. Describe schema and instances?
Includes descriptions of the database structure, data types, and the constraints on the database. A component of the schema or
an object within the schema, e.g., STUDENT,COURSE.
The actual data stored in a database at a particular moment in time. This includes the collection of all the data in the
database.Also called database instance
4. List the categories of data module?
Types of Data Models
1. High Level- Conceptual data model.
2. Low Level – Physical data model.
3. Relational or Representational
4. Object-oriented Data Models:
5. Object-Relational Models:
5. What is database state?
• Database State:
Refers to the content of a database at a moment in time.
• Initial Database State:
Refers to the database state when it is initially loaded into the system.
• Valid State:
A state that satisfies the structure and constraints of the database.
The database state changes every time the database is updated
• Schema
* 5 Marks Questions
1. With neat diagram explain 3 Schema Architecture?
1. Physical Level
2. Conceptual Level
3. External Level

In the above diagram,

 It shows the architecture of DBMS.
 Mapping is the process of transforming request response between various database levels of architecture.
 Mapping is not good for small database, because it takes more time.
 In External / Conceptual mapping, DBMS transforms a request on an external schema against the conceptual schema.
 In Conceptual / Internal mapping, it is necessary to transform the request from the conceptual to internal levels.
1. Physical Level
 Physical level describes the physical storage structure of data in database.
 It is also known as Internal Level.
 This level is very close to physical storage of data.
 At lowest level, it is stored in the form of bits with the physical addresses on the secondary storage device.
 At highest level, it can be viewed in the form of files.
 The internal schema defines the various stored data types. It uses a physical data model.
2. Conceptual Level
 Conceptual level describes the structure of the whole database for a group of users.
 It is also called as the data model.
 Conceptual schema is a representation of the entire content of the database.
 These schema contains all the information to build relevant external records.
 It hides the internal details of physical storage.
3. External Level
 External level is related to the data which is viewed by individual end users.
 This level includes a no. of user views or external schemas.
 This level is closest to the user.
 External view describes the segment of the database that is required for a particular user group and hides the rest of the
database from that user group.
2. What is data independence and explain their types?
Data Independence
A database system normally contains a lot of data in addition to users’ data. For example, it stores data about data, known as
metadata, to locate and retrieve data easily. It is rather difficult to modify or update a set of metadata once it is stored in the
database. But as a DBMS expands, it needs to change over time to satisfy the requirements of the users. If the entire data is
dependent, it would become a tedious and highly complex job.

Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another
level. This data is independent but mapped to each other.
Logical Data Independence
Logical data is data about database, that is, it stores information about how data is managed inside. For example, a table
(relation) stored in the database and all its constraints, applied on that relation.
Logical data independence is a kind of mechanism, which liberalizes itself from actual data stored on the disk. If we do some
changes on table format, it should not change the data residing on the disk.
Physical Data Independence
All the schemas are logical, and the actual data is stored in bit format on the disk. Physical data independence is the power to
change the physical data without impacting the schema or logical data.
For example, in case we want to change or upgrade the storage system itself − suppose we want to replace hard-disks with
SSD − it should not have any impact on the logical data or schemas.
3. Briefly explain DBMS languages?
The Data Definition Language is used for specifying the database schema and the Data Manipulation Language is
used for both reading and updating the database. These languages are called data sub-languages as they do not
include constructs for all computational requirements.
Data Definition Language
Data Definition Language (DDL) statements are used to classify the database structure or schema. It is a type of language that
allows the DBA or user to depict and name those entities, attributes, and relationships that are required for the application
along with any associated integrity and security constraints. Here are the lists of tasks that come under DDL:
 CREATE – used to create objects in the database
 ALTER – used to alters the structure of the database
 DROP – used to delete objects from the database
 TRUNCATE – used to remove all records from a table, including all spaces allocated for the records are removed
 COMMENT – used to add comments to the data dictionary
 RENAME – used to rename an object
Data Manipulation Language
A language that offers a set of operations to support the fundamental data manipulation operations on the data held in the
database. Data Manipulation Language (DML) statements are used to manage data within schema objects. Here are the lists of
tasks that come under DML:
 SELECT – It retrieve data from the a database
 INSERT – It inserts data into a table
 UPDATE – It updates existing data within a table
 DELETE – It deletes all records from a table, the space for the records remain
 MERGE – UPSERT operation (insert or update)
 CALL – It calls a PL/SQL or Java subprogram
 EXPLAIN PLAN – It explains access path to data
 LOCK TABLE – It controls concurrency
Data Control Language
There is another two forms of database sub-languages. The Data Control Language (DCL) is used to control privilege in
Database. To perform any operation in the database, such as for creating tables, sequences or views we need privileges.
Privileges are of two types,
 System – creating session, table etc are all types of system privilege.
 Object – any command or query to work on tables comes under object privilege. DCL is used to define two commands.
These are:
 Grant – It gives user access privileges to database.
 Revoke – It takes back permissions from user.
Transaction Control Language (TCL)
Transaction Control statements are used to run the changes made by DML statements. It allows statements to be grouped
together into logical transactions.
 COMMIT – It saves the work done
 SAVEPOINT – It identifies a point in a transaction to which you can later roll back
 ROLLBACK – It restores database to original since the last COMMIT
 SET TRANSACTION – It changes the transaction options like isolation level and what rollback segment to use

4. List and explain DBMS interfaces?

DBMS Interfaces
• Stand-alone query language interfaces
Example: Entering SQL queries at the DBMS interactive SQL interface (e.g.
SQL*Plus in ORACLE)
• Programmer interfaces for embedding DML in programming languages
• User-friendly interfaces
• Menu-based, forms-based, graphics-based, etc.
DBMS Programming Language Interfaces
• Programmer interfaces for embedding DML in a programming languages:
• Embedded Approach: e.g embedded SQL (for C,C++, etc.), SQLJ (for Java)
• Procedure Call Approach: e.g. JDBC for Java, ODBC for other programming
languages
• Database Programming Language Approach:
e.g. ORACLE has PL/SQL, a programming language based on SQL;
language incorporates SQL and its data types as integral components/
User-Friendly DBMS Interfaces
• Menu-based, popular for browsing on the web
• Forms-based, designed for naïve users
• Graphics-based (Point and Click, Drag and Drop, etc.)
• Natural language: requests in written English
• Combinations of the above:For example, both menus and forms usedextensively
in Web database interfaces
Other DBMS Interfaces
• Speech as Input and Output
• Web Browser as an interface
• Parametric interfaces, e.g., bank tellers using function keys.
• Interfaces for the DBA:
• Creating user accounts, granting authorizations
• Setting system parameters
• Changing schemas or access paths
5. What are the classifications of database management system?
 Hierarchical Databases (DBMS)

In the Hierarchical Database Model we have to learn about the databases. It is very fast and simple. In a
hierarchical database, records contain information about there groups of parent/child relationships, just like as a
tree structure. The structure implies that a record can have also a repeating information. In this structure Data
follows a series of records, It is a set of field values attached to it. It collects all records together as a record type.
These record types are the equivalent of tables in the relational model, and with the individual records being the
equivalent of rows. To create links between these record types, the hierarchical model uses these type

Relationships.
Network Database

A network databases are mainly used on a large digital computers. It more connections can be made between different types of
data, network databases are considered more efficiency It contains limitations must be considered when we have to use this
kind of database. It is Similar to the hierarchical databases, network databases .Network databases are similar to hierarchical
databases by also having a hierarchical structure. A network database looks more like a cobweb or interconnected network of
records.
In network databases, children are called members and parents are called occupier. The difference between each child or
member can have more than one parent.

The Approval of the network data model similar with the esteem of the hierarchical data model. Some data were more
naturally modeled with more than one parent per child. The network model authorized the modeling of many-to-many
relationships in data.
The network model is very similar to the hierarchical model really. Actually the hierarchical model is a subset of the network
model. However, instead of using a single-parent tree hierarchy, the network model uses set theory to provide a tree-like
hierarchy with the exception that child tables were allowed to have more than one parent. It supports many-to-many
relationships.
Relational Databases

In relational databases, the relationship between data files is relational. Hierarchical and network databases require the user to
pass a hierarchy in order to access needed data. These databases connect to the data in different files by using common data
numbers or a key field. Data in relational databases is stored in different access control tables, each having a key field that
mainly identifies each row. In the relational databases are more reliable than either the hierarchical or network database
structures. In relational databases, tables or files filled up with data are called relations (tuples) designates a row or record, and
columns are referred to as attributes or fields.
Relational databases work on each table has a key field that uniquely indicates each row, and that these key fields can be used
to connect one table of data to another.

Object-Oriented Model

In this Model we have to discuss the functionality of the object oriented Programming .It takes more than storage of
programming language objects. Object DBMS's increase the semantics of the C++ and Java .It provides full-featured database
programming capability, while containing native language compatibility. It adds the database functionality to object
programming languages.This approach is the analogical of the application and database development into a constant data
model and language environment. Applications require less code, use more natural data modeling, and code bases are easier to
maintain. Object developers can write complete database applications with a decent amount of additional effort.
The object-oriented database derivation is the integrity of object-oriented programming language systems and consistent
systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent data, as found in
databases, and transient data, as found in executing programs.

6. Briefly Describe database System Utilities?

• A data loading utility:
Which allows easy loading of data from the external format without writing
programs.
• A backup utility:
Which allows to make copies of the database periodically to help in cases of
crashes and disasters.
• Recovery utility:
Which allows to reconstruct the correct state of database from the backup and
history of transactions.
• Monitoring tools:
Which monitors the performance so that internal schema can be changed and
database access can be optimized.
• File organization:
Which allows restructuring the data from one type to another?