DBMS - MODULE - V - ADVANCED TOPICS

Data warehousing-Data mining and knowledge discovery-OODBMS-

Object Relational Databases –XML Data Base - Cloud based systems –
NOSQL introduction -Hbase data model -Database Tuning -Case Study for
Design and Manage the Database for any Project.

Data Warehouse:
A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection
of data in support of management's decision making process.
 Subject-Oriented: A data warehouse can be used to analyze a particular subject area.
For example, "sales" can be a particular subject.
 Integrated: A data warehouse integrates data from multiple data sources. For example,
source A and source B may have different ways of identifying a product, but in a data
warehouse, there will be only a single way of identifying a product.
 Time-Variant: Historical data is kept in a data warehouse. For example, one can
retrieve data from 3 months, 6 months, 12 months, or even older data from a data
warehouse. This contrasts with a transactions system, where often only the most
recent data is kept. For example, a transaction system may hold the most recent
address of a customer, where a data warehouse can hold all addresses associated with
a customer.
 Non-volatile: Once data is in the data warehouse, it will not change. So, historical data
in a data warehouse should never be altered.

Data Warehouse Design Process:

A data warehouse can be built using a top-down approach, a bottom-up approach, or a
combination of both.
 The top-down approach starts with the overall design and planning. It is useful in cases
where the technology is mature and well known, and where the business problems that
must be solved are clear and well understood.
 The bottom-up approach starts with experiments and prototypes. This is useful in the
early stage of business modeling and technology development. It allows an organization to
move forward at considerably less expense and to evaluate the benefits of the technology
before making significant commitments.
 In the combined approach, an organization can exploit the planned and strategic nature
of the top-down approach while retaining the rapid implementation and opportunistic
application of the bottom-up approach.
The warehouse design process consists of the following steps:
 Choose a business process to model, for example, orders, invoices, shipments, inventory,
account administration, sales, or the general ledger. If the business process is
organizational and involves multiple complex object collections, a data warehouse model
should be followed. However, if the process is departmental and focuses on the analysis of
one kind of business process, a data mart model should be chosen.
 Choose the grain of the business process. The grain is the fundamental, atomic level of
data to be represented in the fact table for this process, for example, individual
transactions, individual daily snapshots, and so on.
1
 DBMS - MODULE - V - ADVANCED TOPICS
 Choose the dimensions that will apply to each fact table record. Typical dimensions are
time, item, customer, supplier, warehouse, transaction type, and status.
 Choose the measures that will populate each fact table record. Typical measures are
numeric additive quantities like dollars sold and units sold.

A Three Tier Data Warehouse Architecture:

Tier-1: The bottom tier is a warehouse database server that is almost always a relational
database system. Back-end tools and utilities are used to feed data into the bottom tier
from operational databases or other external sources (such as customer profile information
provided by external consultants). These tools and utilities perform data extraction,
cleaning, and transformation (e.g., to merge similar data from different sources into a
unified format), as well as load and refresh functions to update the data warehouse . The
data are extracted using application program interfaces known as gateways. A gateway is
supported by the underlying DBMS and allows client programs to generate SQL code to be
executed at a server. Examples of gateways include ODBC (Open Database Connection) and
OLEDB (Open Linking and Embedding for Databases) by Microsoft and JDBC (Java Database
Connection). This tier also contains a metadata repository, which stores information about
the data warehouse and its contents.
2
 DBMS - MODULE - V - ADVANCED TOPICS

Tier-2: The middle tier is an OLAP server that is typically implemented using either a
relational OLAP (ROLAP) model or a multidimensional OLAP. OLAP model is an extended
relational DBMS that maps operations on multidimensional data to standard relational
operations.
A multidimensional OLAP (MOLAP) model, that is, a special-purpose server that directly
implements multidimensional data and operations.

Tier-3: The top tier is a front-end client layer, which contains query and reporting tools,
analysis tools, and/or data mining tools (e.g., trend analysis, prediction, and so on).

Data Warehouse Models:

There are three data warehouse models.

1. Enterprise warehouse: An enterprise warehouse collects all of the information about
subjects spanning the entire organization. It provides corporate-wide data integration,
usually from one or more operational systems or external information providers, and is
cross-functional in scope. It typically contains detailed data as well as summarized data,
and can range in size from a few gigabytes to hundreds of gigabytes, terabytes, or beyond.
An enterprise data warehouse may be implemented on traditional mainframes, computer
super servers, or parallel architecture platforms. It requires extensive business modeling
and may take years to design and build.

2. Data mart: A data mart contains a subset of corporate-wide data that is of value to a
specific group of users. The scope is confined to specific selected subjects. For example, a
marketing data mart may confine its subjects to customer, item, and sales. The data
contained in data marts tend to be summarized. Data marts are usually implemented on
low-cost departmental servers that are UNIX/LINUX- or Windows-based. The
implementation cycle of a data mart is more likely to be measured in weeks rather than
months or years. However, it may involve complex integration in the long run if its design
and planning were not enterprise-wide.
Depending on the source of data, data marts can be categorized as independent or
dependent. Independent data marts are sourced from data captured from one or more
operational systems or external information providers, or from data generated locally
within a particular department or geographic area. Dependent data marts are sourced
directly from enterprise data warehouses.

3. Virtual warehouse: A virtual warehouse is a set of views over operational databases.

For efficient query processing, only some of the possible summary views may be
materialized. A virtual warehouse is easy to build but requires excess capacity on
operational database servers

3
 DBMS - MODULE - V - ADVANCED TOPICS

Data Mining :

What Is Data Mining?

Data mining refers to extracting or mining knowledge from large amounts of data. The term
is actually a misnomer. Thus, data mining should have been more appropriately named as
knowledge mining which emphasis on mining from large amounts of data.
It is the computational process of discovering patterns in large data sets involving methods
at the intersection of artificial intelligence, machine learning, statistics, and database
systems. The overall goal of the data mining process is to extract information from a data
set and transform it into an understandable structure for further use.

The key properties of data mining are

 Automatic discovery of patterns
 Prediction of likely outcomes
 Creation of actionable information
 Focus on large datasets and databases

The Scope of Data Mining

Data mining derives its name from the similarities between searching for valuable business
information in a large database — for example, finding linked products in gigabytes of store
scanner data — and mining a mountain for a vein of valuable ore. Both processes require
either sifting through an immense amount of material, or intelligently probing it to find
exactly where the value resides.

Given databases of sufficient size and quality, data mining technology can generate new
business opportunities by providing these capabilities:

 Automated prediction of trends and behaviors.

Data mining automates the process of finding predictive information in large databases.
Questions that traditionally required extensive hands on analysis can now be answered
directly from the data — quickly. A typical example of a predictive problem is targeted
marketing. Data mining uses data on past promotional mailings to identify the targets most
likely to maximize return on investment in future mailings. Other predictive problems
include forecasting bankruptcy and other forms of default, and identifying segments of a
population likely to respond similarly to given events.

 Automated discovery of previously unknown patterns.

Data mining tools sweep through databases and identify previously hidden patterns in one
step. An example of pattern discovery is the analysis of retail sales data to identify
seemingly unrelated products that are often purchased together. Other pattern discovery
problems include detecting fraudulent credit card transactions and identifying anomalous
data that could represent data entry keying errors.
4
 DBMS - MODULE - V - ADVANCED TOPICS
Tasks of Data Mining
Data mining involves six common classes of tasks:
 Anomaly detection (Outlier/change/deviation detection) – The identification of
unusual data records, that might be interesting or data errors that require further
investigation.
 Association rule learning (Dependency modelling) – Searches for relationships
between variables. For example a supermarket might gather data on customer
purchasing habits. Using association rule learning, the supermarket can determine
which products are frequently bought together and use this information for marketing
purposes. This is sometimes referred to as market basket analysis.
 Clustering – is the task of discovering groups and structures in the data that are in
some way or another "similar", without using known structures in the data.
 Classification – is the task of generalizing known structure to apply to new data. For
example, an e-mail program might attempt to classify an e-mail as "legitimate" or as
"spam".
 Regression – attempts to find a function which models the data with the least error.
 Summarization – providing a more compact representation of the data set, including
visualization and report generation.

Architecture of Data Mining

A typical data mining system may have the following major components.

5
 DBMS - MODULE - V - ADVANCED TOPICS

1. Knowledge Base: This is the domain knowledge that is used to guide the search or
evaluate the interestingness of resulting patterns. Such knowledge can include
concept hierarchies, used to organize attributes or attribute values into different
levels of abstraction. Knowledge such as user beliefs, which can be used to assess a
pattern’s interestingness based on its unexpectedness, may also be included. Other
examples of domain knowledge are additional interestingness constraints or
thresholds, and metadata (e.g., describing data from multiple heterogeneous
sources).
2. Data Mining Engine: This is essential to the data mining system and ideally consists
of a set of functional modules for tasks such as characterization, association and
correlation analysis, classification, prediction, cluster analysis, outlier analysis, and
evolution analysis.
3. Pattern Evaluation Module: This component typically employs interestingness
measures interacts with the data mining modules so as to focus the search toward
interesting patterns. It may use interestingness thresholds to filter out discovered
patterns. Alternatively, the pattern evaluation module may be integrated with the
mining module, depending on the implementation of the data mining method used.
For efficient data mining, it is highly recommended to push the evaluation of pattern
interestingness as deep as possible into the mining processor as to confine the
search to only the interesting patterns.
4. User interface: This module communicates between users and the data mining
system, allowing the user to interact with the system by specifying a data mining
query or task, providing information to help focus the search, and performing
exploratory data mining based on the intermediate data mining results. In addition,
this component allows the user to browse database and data warehouse schemas or
data structures, evaluate mined patterns, and visualize the patterns in different
forms.

Classification of Data mining Systems:

The data mining system can be classified according to the following criteria:
 Database Technology
 Statistics
 Machine Learning
 Information Science
 Visualization
 Other Disciplines

Some Other Classification Criteria:

 Classification according to kind of databases mined
 Classification according to kind of knowledge mined
 Classification according to kinds of techniques utilized
 Classification according to applications adapted

6
 DBMS - MODULE - V - ADVANCED TOPICS
Knowledge Discovery in Databases (KDD)
Some people treat data mining same as Knowledge discovery while some people view data mining
essential step in process of knowledge discovery.
Here is the list of steps involved in knowledge discovery process:
 Data Cleaning - In this step the noise and inconsistent data is removed.
 Data Integration - In this step multiple data sources are combined.
 Data Selection - In this step relevant to the analysis task are retrieved from the database.
 Data Transformation - In this step data are transformed or consolidated into forms
appropriate for mining by performing summary or aggregation operations.
 Data Mining - In this step intelligent methods are applied in order to extract data patterns.
 Pattern Evaluation - In this step, data patterns are evaluated.
 Knowledge Presentation - In this step, knowledge is represented.

Major Issues In Data Mining:

 Mining different kinds of knowledge in databases..
 Interactive mining of knowledge at multiple levels of abstraction.
 Incorporation of background knowledge.
 Data mining query languages and ad hoc data mining.
 Presentation and visualization of data mining results.
 Handling noisy or incomplete data.
 Pattern evaluation.
 Efficiency and scalability of data mining algorithms
 Parallel, distributed, and incremental mining algorithms.

7
 DBMS - MODULE - V - ADVANCED TOPICS

OODBMS - object-oriented database management system

An object database (also object-oriented database management system) is

a database management system in which information is represented in the form of objects
as used in object- oriented programming. Object databases are different from relational
databases which are table-oriented. Object-relational databases are a hybrid of both
approaches.
Object-oriented database management systems (OODBMSs) combine database capabilities
with object-oriented programming language capabilities. OODBMSs allow object-oriented
programmers to develop the product, store them as objects, and replicate or modify
existing objects to make new objects within the OODBMS. Because the database is
integrated with the programming language, the programmer can maintain consistency
within one environment, in that both the OODBMS and the programming language will use
the same model of representation. Relational DBMS projects, by way of contrast, maintain a
clearer division between the database model and the application.

class Dbms()
Creates a Dbms object. Dbms objects support basic operations on a database.

Dbms objects have the following methods:

begin()

Begins a transaction on the database.

close()

Closes the database object. It is safe to try to close a database object even if it is not open.
commit()
Commits the current transaction.
compact()

Compacts the database, reclaiming unused space in the database file. create(dbname)

Creates a database with path dbname. execute(query)

Executes an SQL query. On success, returns 0 if a DDL (SQL schema update) statement was
executed. Returns the number of rows inserted, updated, or deleted, if a DML (SQL data
update) statement was executed.

8
 DBMS - MODULE - V - ADVANCED TOPICS
open(dbname)

Opens the database in file dbname. This should be a full Unicode path name, for example,
u'c:\\[Link]'.
rollback()

Rolls back the current transaction.

Object Relational Databases

 Object-relational databases (ORDBMS) combine the features of both relational
databases and object-oriented databases, aiming to provide a flexible and powerful
means of data storage and retrieval. Here are some key notes about object-relational
databases:
 Hybrid Model: ORDBMS combines the relational model's simplicity and scalability
with the object-oriented model's ability to handle complex data types and
relationships.
 Data Abstraction: ORDBMS allows for the abstraction of complex data types into
objects, which can include methods, attributes, and relationships, mirroring real-world
entities more closely.
 Structured Query Language (SQL): ORDBMS typically supports SQL for querying and
manipulating data, allowing developers to leverage their existing knowledge of SQL
while incorporating object-oriented concepts.
 Object Types and Methods: ORDBMS supports the definition of user-defined object
types, allowing for the creation of custom data types with associated methods and
behaviors.
 Inheritance: Object-relational databases often support inheritance, allowing objects to
inherit attributes and behaviors from other objects, facilitating code reuse and
promoting a hierarchical organization of data.
 Complex Relationships: ORDBMS can model complex relationships between objects,
including one-to-one, one-to-many, and many-to-many relationships, providing greater
flexibility in data modeling compared to traditional relational databases.
 Performance: While ORDBMS offers enhanced data modeling capabilities,
performance can sometimes be a concern due to the overhead associated with
managing complex object structures and relationships.
 Vendor Specific Implementations: Different ORDBMS vendors may implement
object-relational features differently, leading to variations in syntax and functionality
across platforms.
 Use Cases: ORDBMS is well-suited for applications with complex data models, such as
multimedia databases, geographic information systems (GIS), and scientific
applications where data structures are inherently hierarchical or object-oriented.
9
 DBMS - MODULE - V - ADVANCED TOPICS
 Examples: Some examples of ORDBMS include Oracle, PostgreSQL (with support for
custom types and functions), and IBM's Informix.
 Migration: Migrating from a relational database to an object-relational database
requires careful planning and consideration of the differences in data modeling and
querying approaches.
 Overall, object-relational databases offer a compelling solution for applications that
require the flexibility of the object-oriented model combined with the robustness of the
relational model. However, they require careful consideration of performance
implications and compatibility with existing systems.

XML Data Base

Introduction to XML Databases:

o XML (eXtensible Markup Language) databases are designed to store, query, and
manipulate XML data efficiently.
o Unlike traditional relational databases, XML databases are optimized for handling
semi-structured and hierarchical data.
 XML Basics:
o XML is a markup language that uses tags to define the structure and content of data.
o Elements, attributes, and text nodes are the basic building blocks of XML documents.
o Example:

xml
Copy code
<bookstore>
<book>
<title>Harry Potter</title>
<author>J.K. Rowling</author>
</book>
<book>
<title>The Lord of the Rings</title>
<author>J.R.R. Tolkien</author>
</book>
</bookstore>

 Types of XML Databases:

o Native XML Databases: Specifically designed to store and query XML documents
efficiently.
o XML-Enabled Databases: Traditional relational databases with added support for
storing and querying XML data.
o Hybrid Databases: Combine features of both native XML databases and relational
databases.
 Features of XML Databases:
o Hierarchical Data Model: XML databases support the hierarchical structure of XML
documents, enabling efficient storage and retrieval of nested data.
10
 DBMS - MODULE - V - ADVANCED TOPICS
o XPath and XQuery Support: XML databases provide query languages like XPath and
XQuery for querying XML data based on its structure and content.
o Indexing: Efficient indexing mechanisms are used to speed up the retrieval of XML
documents and nodes.
o Schema Validation: Some XML databases support XML schema validation to ensure
data integrity and conformity to predefined structure rules.
 Querying XML Data:
o XPath: A query language used to navigate through elements and attributes in XML
documents.
o XQuery: A more powerful query language for querying and transforming XML data,
similar to SQL for relational databases.
o Example XPath Query: //bookstore/book[author='J.K. Rowling']/title
o Example XQuery:
xquery
Copy code
for $book in //bookstore/book
where $book/author = 'J.K. Rowling'
return $book/title
 Use Cases:
o Content Management Systems (CMS): XML databases are suitable for managing and
storing structured content, such as articles, blogs, and documentation.
o Web Services: XML is widely used for representing data exchanged between web
services, making XML databases a natural choice for storing and processing web
service data.
o Scientific Data Management: XML databases can efficiently handle complex scientific
data, which often has a hierarchical and semi-structured nature.

 Challenges and Considerations:

o Performance: Efficient indexing and query optimization are crucial for handling large
volumes of XML data.
o Schema Evolution: XML databases should support schema evolution, allowing for
changes in the structure of XML documents over time.
o Integration with Existing Systems: Integration with other systems, such as relational
databases and web applications, may require careful planning and implementation.

 Examples of XML Databases:

o ExistDB
o BaseX
o MarkLogic
o Oracle XML DB

XML databases provide a specialized solution for storing and querying XML data
efficiently. Understanding the hierarchical nature of XML data and leveraging
appropriate query languages are essential for effective use of XML databases in various
applications.

11
 DBMS - MODULE - V - ADVANCED TOPICS
Cloud based systems
Cloud-based systems refer to computing systems and services that are delivered over the
internet. Instead of storing data or running applications on a local physical server or
computer, cloud computing allows users to access resources such as storage, processing
power, and software applications remotely, typically via a web browser or specialized
client application.

There are several key characteristics of cloud-based systems:

1. On-Demand Self-Service : Users can provision computing resources, such as server

time and storage, as needed without requiring human interaction with service
providers.
2. Broad Network Access : Cloud services are accessible over the internet from a variety
of devices, including laptops, smartphones, and tablets.
3. Resource Pooling : Cloud providers pool computing resources to serve multiple
customers, allowing them to dynamically allocate and reallocate resources according to
demand.
4. Rapid Elasticity : Cloud services can quickly scale up or down to accommodate changes
in workload or user demand. This ensures that users have access to the resources they
need without overprovisioning or underprovisioning.
5. Measured Service : Cloud computing resources are typically metered and billed based
on usage, allowing users to pay only for the resources they consume.

Cloud-based systems can be divided into different types of services:

1. Infrastructure as a Service (IaaS) : Provides virtualized computing resources over the
internet, such as virtual machines, storage, and networking. Users have control over
the operating system and applications, while the cloud provider is responsible for
managing the underlying infrastructure.
2. Platform as a Service (PaaS) : Offers a platform allowing customers to develop, run,
and manage applications without the complexity of building and maintaining the
underlying infrastructure. PaaS typically includes development tools, middleware, and
database management systems.
3. Software as a Service (SaaS) : Delivers software applications over the internet on a
subscription basis. Users access the application through a web browser or API, and the
software is centrally hosted and maintained by the provider.

Cloud-based systems offer numerous benefits, including cost savings, scalability, flexibility,
and improved collaboration and accessibility. However, they also raise concerns around
data security, privacy, and vendor lock-in, which organizations must address when
adopting cloud technologies.

12
 DBMS - MODULE - V - ADVANCED TOPICS

Introduction to NoSQL databases

NoSQL, which stands for "Not Only SQL," is a term used to describe a variety of database
technologies that are designed to handle large volumes of unstructured or semi-structured
data. Unlike traditional relational databases (SQL databases), NoSQL databases are typically
schema-less, meaning they don't require a fixed schema and can accommodate flexible data
models.

Key Characteristics and features of nosql databases:

1. Flexible Data Models : NoSQL databases can handle various types of data, including
structured, semi-structured, and unstructured data. They can store documents, key-value
pairs, column-family data, and graphs, among other formats.
2. Scalability : NoSQL databases are designed to scale horizontally, meaning they can
easily distribute data across multiple servers or nodes in a cluster. This allows them to
handle large amounts of data and high read/write loads.
3. High Performance : NoSQL databases are optimized for specific use cases, such as high-
speed reads or writes, and can often outperform traditional SQL databases in these
scenarios.
4. No Schema Enforcement : Unlike SQL databases, which enforce a rigid schema, NoSQL
databases typically do not enforce a fixed schema. This allows for greater flexibility in data
modeling and schema evolution.
5. CAP Theorem : NoSQL databases are often designed with the CAP theorem in mind,
which states that it's impossible for a distributed system to simultaneously guarantee
Consistency, Availability, and Partition tolerance. NoSQL databases typically prioritize
either consistency and availability (CA systems) or consistency and partition tolerance (CP
systems), depending on the specific requirements of the application.
6. Types of NoSQL Databases :
- Document Stores : Examples include MongoDB, Couchbase, and CouchDB. They store
data as JSON-like documents.
- Key-Value Stores : Examples include Redis, Amazon DynamoDB, and Riak. They store
data as key-value pairs.
- Column-Family Stores : Examples include Apache Cassandra and HBase. They store
data in columns rather than rows.
- Graph Databases : Examples include Neo4j, Amazon Neptune, and JanusGraph. They
are optimized for storing and querying graph data.
7. Use Cases : NoSQL databases are commonly used in scenarios where traditional SQL
databases may struggle, such as big data analytics, real-time web applications, content
management systems, IoT data management, and social networks.

Overall, NoSQL databases offer flexibility, scalability, and performance advantages for
handling large volumes of unstructured or semi-structured data in modern applications.
However, it's essential to choose the right NoSQL database based on the specific
requirements and characteristics of your application.
13
 DBMS - MODULE - V - ADVANCED TOPICS
Hbase data model

HBase is a distributed, column-oriented database built on top of the Hadoop Distributed

File System (HDFS). It's designed to handle large volumes of structured data and is part of
the Apache Hadoop ecosystem. The data model of HBase is unique compared to traditional
relational databases.

Overview of the key components of the HBase data model:

1. Tables: In HBase, data is organized into tables. Each table consists of rows and
columns. Tables in HBase are sparse, meaning they can have a large number of rows,
but not all rows need to contain data for every column.
2. Rows: Rows in HBase are uniquely identified by a row key, which is a byte array. The
row key is used to retrieve and store data efficiently. Rows are sorted lexicographically
based on their row keys, which allows for efficient range scans.
3. Columns and Column Families: Columns in HBase are grouped into column families.
Each column family is a logical grouping of columns and is stored together on disk.
When you create a table in HBase, you must specify one or more column families.
Unlike traditional relational databases, HBase does not require you to define column
names upfront; instead, columns are defined dynamically as data is inserted into the
table.
4. Column Qualifiers: Within each column family, columns are identified by a column
qualifier, which is a byte array. Column qualifiers are used to distinguish different
columns within a column family.
5. Cells: The combination of a row key, column family, column qualifier, and timestamp
uniquely identifies a cell in HBase. Cells are the basic unit of data storage in HBase and
can contain a value along with a timestamp indicating when the value was last
modified.
6. Timestamps: Each cell in HBase can have multiple versions, each with its own
timestamp. This allows you to store and retrieve historical versions of data. By default,
HBase keeps all versions of a cell, but you can configure it to retain only a certain
number of versions or versions within a specific time range.
7. Table Schema Evolution: HBase allows for schema evolution, meaning you can add or
remove column families and columns from a table without needing to modify existing
data. This flexibility makes it easy to adapt the data model to changing requirements
over time.
8. Secondary Indexes: HBase does not support secondary indexes out of the box.
However, you can implement secondary indexes using techniques such as maintaining
separate tables or using Apache Phoenix, which provides SQL-like querying capabilities
on top of HBase and supports secondary indexes.

Overall, the HBase data model is optimized for storing and retrieving large volumes of
structured data efficiently in a distributed environment. It provides flexibility, scalability,
and high availability, making it suitable for a wide range of use cases, including real-time
analytics, time-series data storage, and serving as a backend for web applications.
14
 DBMS - MODULE - V - ADVANCED TOPICS
Database Tuning
1. Introduction to Database Tuning:
- Definition: Database tuning refers to the process of optimizing the performance of a
database.
- Importance: Efficient database performance is crucial for ensuring that applications run
smoothly and respond quickly to user queries.

2. Understanding Database Performance:

- Factors affecting performance: Disk I/O, CPU utilization, memory usage, network
latency, query complexity, indexing, etc.
- Performance metrics: Throughput, response time, resource utilization, etc.

3. Identifying Performance Bottlenecks:

- Profiling tools: Using database profiling tools to identify areas of poor performance.
- Monitoring: Continuously monitoring database performance to detect bottlenecks.

4. Query Optimization:
- Query structure: Optimizing query structure to minimize resource usage.
- Indexing: Properly indexing tables to speed up data retrieval.
- Joins: Optimizing join queries to reduce execution time.
- Use of hints: Providing hints to the query optimizer to improve execution plans.

5. Indexing Strategies:
- Types of indexes: B-tree, hash, bitmap, etc.
- Index selection: Choosing appropriate columns for indexing based on query patterns.
- Index maintenance: Regularly updating and maintaining indexes to ensure optimal
performance.

6. Database Schema Design:

- Normalization: Properly normalizing database schema to minimize redundancy and
improve query performance.
- Denormalization: Denormalizing schema when necessary to improve read
performance, especially for read-heavy applications.

7. Hardware and Software Optimization:

- Hardware configuration: Optimizing hardware resources such as CPU, memory, and
storage for database performance.
- Database configuration: Configuring database settings and parameters for optimal
performance.
- Use of caching: Utilizing caching mechanisms to reduce database load and improve
response time.

8. Query Caching and Query Plans:

- Caching: Caching frequently executed queries and their results to reduce execution
time.
15
 DBMS - MODULE - V - ADVANCED TOPICS
- Query plans: Understanding and optimizing query execution plans generated by the
database optimizer.

9. Concurrency Control and Locking:

- Managing concurrency: Implementing strategies such as locking, optimistic
concurrency control, etc., to handle concurrent access to data.
- Locking mechanisms: Understanding different locking mechanisms and their impact on
database performance.

10. Monitoring and Performance Tuning Tools:

- Database monitoring tools: Using tools to monitor database performance in real-time.
- Performance tuning tools: Leveraging tools for analyzing and optimizing database
performance.

11. Case Studies and Best Practices:

- Real-world examples: Analyzing case studies of database tuning efforts and their
outcomes.
- Best practices: Identifying and discussing best practices for database tuning in various
scenarios.

These are some of the key topics that might be covered in a lecture on database tuning.
Each topic can be explored in further detail based on the specific needs and level of the
audience.

16
 DBMS - MODULE - V - ADVANCED TOPICS

Case Study for Design and Manage the Database for any Project.

[Link] STUDY: BOOK PUBLISHING COMPANY

AIM: A publishing company produces scientific books on various subjects. The books
are written by authors who specialize in one particular subject. The company
employs editors who, not necessarily being specialists in a particular area, each take
sole responsibility for editing one or more publications.

A publication covers essentially one of the specialist subjects and is normally

written by a single author. When writing a particular book, each author works
with on editor, but may submit another work for publication to be supervised by
other editors. To improve their competitiveness, the company tries to employ a
variety of authors, more than one author being a specialist in a particular subject.

LAB ASSIGNMENT:
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model using E-R diagrams

17
 DBMS - MODULE - V - ADVANCED TOPICS

[Link] STUDY: GENERAL HOSPITAL

AIM: A General Hospital consists of a number of specialized wards (such as

Maternity, Paediatry, Oncology, etc). Each ward hosts a number of patients, who
were admitted on the recommendation of their own GP and confirmed by a
consultant employed by the Hospital. On admission, the personal details of every
patient are recorded.
A separate register is to be held to store the information of the tests
undertaken and the results of a prescribed treatment. A number of tests may be
conducted for each patient. Each patient is assigned to one leading consultant but
may be examined by another doctor, if required. Doctors are specialists in some
branch of medicine and may be leading consultants for a number of patients, not
necessarily from the same ward.

LAB ASSIGNMENT:
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model using E-R diagrams

18
 DBMS - MODULE - V - ADVANCED TOPICS

3. CASE STUDY: CAR RENTAL COMPANY

AIM: A database is to be designed for a Car Rental Co. (CRC). The information required
includes a description of cars, subcontractors (i.e. garages), company expenditures,
company revenues and customers. Cars are to be described by such data as: make, model,
year of production, engine size, and fuel type, number of passengers, registration number,
purchase price, purchase date, rent price and insurance details. It is the company policy
not to keep any car for a period exceeding one year.
All major repairs and maintenance are done by subcontractors (i.e. franchised garages),
with whom CRC has long-term agreements. Therefore the data about garages to be kept in
the database includes garage names, addresses, range of services and the like. Some
garages require payments immediately after a repair has been made; with others CRC has
made arrangements for credit facilities. Company expenditures are to be registered for all
outgoings connected with purchases, repairs, maintenance, insurance etc.
Similarly the cash inflow coming from all sources - car hire, car sales, insurance claims -
must be kept of [Link] maintains a reasonably stable client base. For this privileged
category of customers special credit card facilities are provided. These customers may
also book in advance a particular car. These reservations can be made for any period of
time up to one month. Casual customers must pay a deposit for an estimated time of
rental, unless they wish to pay by credit card. All major credit cards are accepted. Personal
details (such as name, address, telephone number, driving license, number) about each
customer are kept in the database.

LAB ASSIGNMENT:
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model using E-R diagrams

19
 DBMS - MODULE - V - ADVANCED TOPICS

4. CASE STUDY: STUDENT PROGRESS MONITORING SYSTEM

AIM: A database is to be designed for a college to monitor students' progress

throughout their course of study. The students are reading for a degree (such as BA,
BA(Hons) MSc, etc) within the framework of the modular system. The college
provides a number of module, each being characterised by its code, title, credit value,
module leader, teaching staff and the department they come from. A module is co-
ordinated by a module leader who shares teaching duties with one or more lecturers.
A lecturer may teach (and be a module leader for) more than one
module. Students are free to choose any module they wish but the following rules
must be observed: some modules require pre-requisites modules and some
degree programmes have compulsory modules. The database is also to contain
some information about students including their numbers, names, addresses,
degrees they read for, and their past performance (i.e. modules taken and
examination results).

LAB ASSIGNMENT:
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model using E-R diagrams

---------------------------------------------------------------------------------------------------
20