DPG SCHOOL OF TECHNOLOGY & MANAGEMENT

PRACTICAL FILE

of

Database Management System

Submitted in partial fulfillment of the requirements for the award of Degree.

of
Bachelor of Business Administration (BBA)

Submitted by: Submitted to:

NAME: Ms. Shweta Yadav
FATHER’S NAME: (Assistant Professor)
SEMESTER: IV
 INDEX

SR. NO. NAME OF PROGRAM FACULTY

SIGNATURE

1 What is Database ?

2 Database Security

3 ER (Entity Relationship) Diagram in DBMS

4 What is a Data Warehouse?

5 Difference between Data Warehousing and Data

Mining

6 Data Ware House Architecture

7 Database Schemas

8 Database Instance

9 How to Save a Database and a Table.

10 How to create a table?

11 How to add various datatypes in Tables?

12 How to make form for tables?

13 How to Edit a form?

14 What are Various buttons uses in Forms? Give
Examples.

15 How to Create Reports?

16 How to Apply these Queries:

a. Simple Query.
b. Query Using Parameters.

17 How MS-Access is useful in our Offices?

18 How we can add various buttons in Forms?

 What is Database ?
The database is a collection of inter-related data which is used to retrieve, insert and
delete the data efficiently. It is also used to organize the data in the form of a table,
schema, views, and reports, etc.
For example: The college Database organizes the data about the admin, staff, students
and faculty etc.
Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

o Database management system is a software which is used to manage the database.
For example: MySQL, Oracle, etc are a very popular commercial database which
is used in different applications.
o DBMS provides an interface to perform various operations like database creation,
storing data in it, updating data, creating a table in the database and a lot more.
o It provides protection and security to the database. In the case of multiple users, it
also maintains data consistency.
DBMS allows users the following tasks:
o Data Definition: It is used for creation, modification, and removal of definition
that defines the organization of data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the
actual data in the database.
o Data Retrieval: It is used to retrieve the data from the database which can be
used by applications for various purposes.
o User Administration: It is used for registering and monitoring users, maintain
data integrity, enforcing data security, dealing with concurrency control,
monitoring performance and recovering information corrupted by unexpected
failure.
Characteristics of DBMS
o It uses a digital repository established on a server to store and manage the
information.
 o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures.
o It contains ACID properties which maintain data in a healthy state in case of
failure.
o It can reduce the complex relationship between data.
o It is used to support manipulation and processing of data.
o It is used to provide security of data.
o It can view the database from different viewpoints according to the requirements
of the user.
Advantages of DBMS
o Controls database redundancy: It can control data redundancy because it stores
all the data in one single database file and that recorded data is placed in the
database.
o Data sharing: In DBMS, the authorized users of an organization can share the
data among multiple users.
o Easily Maintenance: It can be easily maintainable due to the centralized nature
of the database system.
o Reduce time: It reduces development time and maintenance need.
o Backup: It provides backup and recovery subsystems which create automatic
backup of data from hardware and software failures and restores the data if
required.
o multiple user interface: It provides different types of user interfaces like
graphical user interfaces, application program interfaces
Disadvantages of DBMS
o Cost of Hardware and Software: It requires a high speed of data processor and
large memory size to run DBMS software.
o Size: It occupies a large space of disks and large memory to run them efficiently.
o Complexity: Database system creates additional complexity and requirements.
 o Higher impact of failure: Failure is highly impacted the database because in
most of the organization, all the data stored in a single database and if the
database is damaged due to electric failure or database corruption then the data
may be lost forever.

Database Security
Database Security means keeping sensitive information safe and prevent the loss of
data. Security of data base is controlled by Database Administrator (DBA).
The following are the main control measures are used to provide security of data in
databases:
1. Authentication
2. Access control
3. Inference control
4. Flow control
5. Database Security applying Statistical Method
6. Encryption
These are explained as following below.
1. Authentication :
Authentication is the process of confirmation that whether the user log in only
according to the rights provided to him to perform the activities of data base. A
particular user can login only up to his privilege but he can’t access the other
sensitive data. The privilege of accessing sensitive data is restricted by using
Authentication.
By using these authentication tools for biometrics such as retina and figure prints
can prevent the data base from unauthorized/malicious users.
2. Access Control :
The security mechanism of DBMS must include some provisions for restricting
access to the data base by unauthorized users. Access control is done by creating
user accounts and to control login process by the DBMS. So, that database access
of sensitive data is possible only to those people (database users) who are allowed
to access such data and to restrict access to unauthorized persons.
 The database system must also keep the track of all operations performed by
certain user throughout the entire login time.
3. Inference Control :
This method is known as the countermeasures to statistical database security
problem. It is used to prevent the user from completing any inference channel.
This method protect sensitive information from indirect disclosure.
Inferences are of two types, identity disclosure or attribute disclosure.
4. Flow Control :
This prevents information from flowing in a way that it reaches unauthorized
users. Channels are the pathways for information to flow implicitly in ways that
violate the privacy policy of a company are called convert channels.
5. Database Security applying Statistical Method :
Statistical database security focuses on the protection of confidential individual
values stored in and used for statistical purposes and used to retrieve the
summaries of values based on categories. They do not permit to retrieve the
individual information.
This allows to access the database to get statistical information about the number
of employees in the company but not to access the detailed confidential/personal
information about the specific individual employee.
6. Encryption :
This method is mainly used to protect sensitive data (such as credit card numbers,
OTP numbers) and other sensitive numbers. The data is encoded using some
encoding algorithms.
An unauthorized user who tries to access this encoded data will face difficulty in
decoding it, but authorized users are given decoding keys to decode data.

ER (Entity Relationship) Diagram in DBMS

o ER model stands for an Entity-Relationship model. It is a high-level data model.
This model is used to define the data elements and relationship for a specified
system.
o It develops a conceptual design for the database. It also develops a very simple
and easy to design view of data.
o In ER modeling, the database structure is portrayed as a diagram called an entity-
relationship diagram.
For example, Suppose we design a school database. In this database, the student will be
an entity with attributes like address, name, id, age, etc. The address can be another
entity with attributes like city, street name, pin code, etc and there will be a relationship
between them.

Component of ER Diagram
1. Entity:
An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.
Consider an organization as an example- manager, product, employee, department etc.
can be taken as an entity.

a. Weak Entity
An entity that depends on another entity called a weak entity. The weak entity doesn't
contain any key attribute of its own. The weak entity is represented by a double
rectangle.
2. Attribute
The attribute is used to describe the property of an entity. Eclipse is used to represent an
attribute.
For example, id, age, contact number, name, etc. can be attributes of a student.

a. Key Attribute
The key attribute is used to represent the main characteristics of an entity. It represents a
primary key. The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute
An attribute that composed of many other attributes is known as a composite attribute.
The composite attribute is represented by an ellipse, and those ellipses are connected
with an ellipse.
c. Multivalued Attribute
An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.
For example, a student can have more than one phone number.

d. Derived Attribute
An attribute that can be derived from other attribute is known as a derived attribute. It
can be represented by a dashed ellipse.
For example, A person's age changes over time and can be derived from another
attribute like Date of birth.
3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is
used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known
as one to one relationship.
For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an entity
on the right associates with the relationship then this is known as a one-to-many
relationship.
For example, Scientist can invent many inventions, but the invention is done by the
only specific scientist.

c. Many-to-one relationship
When more than one instance of the entity on the left, and only one instance of an entity
on the right associates with the relationship then it is known as a many-to-one
relationship.
For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship
When more than one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then it is known as a many-to-many
relationship.
For example, Employee can assign by many projects and project can have many
employees.

What is a Data Warehouse?

A Data Warehouse (DW) is a relational database that is designed for query and analysis
rather than transaction processing. It includes historical data derived from transaction
data from single and multiple sources.
A Data Warehouse provides integrated, enterprise-wide, historical data and focuses on
providing support for decision-makers for data modeling and analysis.
A Data Warehouse is a group of data specific to the entire organization, not only to a
particular group of users.
It is not used for daily operations and transaction processing but used for making
decisions.
A Data Warehouse can be viewed as a data system with the following attributes:
o It is a database designed for investigative tasks, using data from various
applications.
o It supports a relatively small number of clients with relatively long interactions.
o It includes current and historical data to provide a historical perspective of
information.
 o Its usage is read-intensive.
o It contains a few large tables.
"Data Warehouse is a subject-oriented, integrated, and time-variant store of information
in support of management's decisions."

Characteristics of Data Warehouse

Subject-Oriented
A data warehouse target on the modeling and analysis of data for decision-makers.
Therefore, data warehouses typically provide a concise and straightforward view around
a particular subject, such as customer, product, or sales, instead of the global
organization's ongoing operations. This is done by excluding data that are not useful
concerning the subject and including all data needed by the users to understand the
subject.

Integrated
A data warehouse integrates various heterogeneous data sources like RDBMS, flat files,
and online transaction records. It requires performing data cleaning and integration
during data warehousing to ensure consistency in naming conventions, attributes types,
etc., among different data sources.
Time-Variant
Historical information is kept in a data warehouse. For example, one can retrieve files
from 3 months, 6 months, 12 months, or even previous data from a data warehouse.
These variations with a transactions system, where often only the most current file is
kept.

Non-Volatile
The data warehouse is a physically separate data storage, which is transformed from the
source operational RDBMS. The operational updates of data do not occur in the data
warehouse, i.e., update, insert, and delete operations are not performed. It usually
requires only two procedures in data accessing: Initial loading of data and access to data.
Therefore, the DW does not require transaction processing, recovery, and concurrency
capabilities, which allows for substantial speedup of data retrieval. Non-Volatile defines
that once entered into the warehouse, and data should not change.

History of Data Warehouse

The idea of data warehousing came to the late 1980's when IBM researchers Barry
Devlin and Paul Murphy established the "Business Data Warehouse."
In essence, the data warehousing idea was planned to support an architectural model for
the flow of information from the operational system to decisional support environments.
The concept attempt to address the various problems associated with the flow, mainly
the high costs associated with it.
In the absence of data warehousing architecture, a vast amount of space was required to
support multiple decision support environments. In large corporations, it was ordinary
for various decision support environments to operate independently.
Goals of Data Warehousing
o To help reporting as well as analysis
o Maintain the organization's historical information
o Be the foundation for decision making.
Data Mart:
A data mart is a subset of a main data warehouse that is segmented to serve business
needs, typically with a focus on a particular purpose.
For Example: If we assume an hons college as data warehouse then,
1. Geography Dept
2. History Dept.
3. English Dept.
4. Bengali Dept.
5. CSE Dept.
These are all departments. And each department is a data mart of a data warehouse.
There may be distinct data marts for finance, sales, production, or marketing.
Departments comprise the software, hardware, programs, and data related to a particular
department inside the firm.
o Although each of these data marts is unique, they may all be coordinated.
o The data marts of several departments differ from one another.
o A departmentally planned tiny warehouse is called a data mart.

Meta Data
Your data warehouse's contents are listed in a directory called Meta Data.
Forms of meta data
Three main types may be found in meta data in a data warehouse:
1. Operational metadata
2. Extraction and transformation metadata
3. End user meta data
1. Operational Metadata
Data for the data warehouse originates from several operational systems inside the
organization, since operational metadata encompasses all relevant information about the
operational data sources.
2. Metadata Extraction and Transformation
It includes details on every data transformation that has ever occurred.
3. End-User Metadata (Index)
The data warehouse's navigational map is the end user information. It makes it possible
for the end user to locate data warehouse information.
Special Significance of Metadata
o Initially, it serves as the link or glue between each component of the data
warehouse.
o It then gives the developer information about the structure's content.
o In the end, it makes the material recognized and lets the user in.
o The answers to the queries on the data in a data ware house are found in the
metadata.

Data WareHouse Architecture

Back-End Tools and Utilities
o They are employed to feed data from operational databases or other external
sources into the data warehouse (bottom layer).
o These tools and utilities carry out load and refresh operations to update the data
warehouse in addition to data extraction, cleansing, and transformation (e.g.,
merging comparable data from several sources into unified format).
Bottom Tier
o Relational database systems are mostly often found on the warehouse database
server.
o A data warehouse was created by connecting many datamart.
o Additionally, this layer has a metadata repository that houses data on the content
of the data warehouse.
o Additionally, there are integrators and monitors on this layer, which constantly
combine data.
The Middle Tier
o An OLAP server is the intermediate tier.
o Typically, MOLAP or ROLAP are used to implement it.
o ROLAP is the name of the server that manages relational databases.
o MOLAP is a unique kind of server that is specifically designed for
multidimensional data and operations.
The Top Tier
It is a front-end client layer that includes data mining, analysis, and query and reporting
capabilities.
Need for Data Warehouse
Data Warehouse is needed for the following reasons:
 1. Business User: Business users require a data warehouse to view summarized data
from the past. Since these people are non-technical, the data may be presented to
them in an elementary form.
2. Store historical data: Data Warehouse is required to store the time variable data
from the past. This input is made to be used for various purposes.
3. Make strategic decisions: Some strategies may be depending upon the data in
the data warehouse. So, data warehouse contributes to making strategic decisions.
4. For data consistency and quality: Bringing the data from different sources at a
commonplace, the user can effectively undertake to bring the uniformity and
consistency in data.
5. High response time: Data warehouse has to be ready for somewhat unexpected
loads and types of queries, which demands a significant degree of flexibility and
quick response time.
Benefits of Data Warehouse
1. Understand business trends and make better forecasting decisions.
2. Data Warehouses are designed to perform well enormous amounts of data.
 3. The structure of data warehouses is more accessible for end-users to navigate,
understand, and query.
4. Queries that would be complex in many normalized databases could be easier to
build and maintain in data warehouses.
5. Data warehousing is an efficient method to manage demand for lots of
information from lots of users.
6. Data warehousing provide the capabilities to analyze a large amount of historical
data.

Difference between Data Warehousing and Data Mining

A Data Warehouse is built to support management functions whereas data mining is
used to extract useful information and patterns from data. Data warehousing is the
process of compiling information into a data warehouse. The main purpose of data
warehousing is to consolidate and store large datasets from various sources for efficient
retrieval and analysis, supporting reporting and decision-making. Data mining focuses
on analyzing data to discover patterns, trends, and insights, while data warehousing
focuses on storing and managing data in a centralized location.

What is Data Warehousing?

It is a technology that aggregates structured data from one or more sources so that it can
be compared and analyzed rather than transaction processing. A data warehouse is
designed to support the management decision-making process by providing a platform
for data cleaning, data integration, and data consolidation. A data warehouse contains
subject-oriented, integrated, time-variant, and non-volatile data. The Data warehouse
consolidates data from many sources while ensuring data quality, consistency, and
accuracy. Data warehouse improves system performance by separating analytics
processing from transactional databases. Data flows into a data warehouse from the
various databases. A data warehouse works by organizing data into a schema that
describes the layout and type of data. Query tools analyze the data tables using schema.
Advantages of Data Warehousing
 The data warehouse’s job is to make any form of corporate data easier to
understand. The majority of the user’s job will consist of inputting raw data.
 The capacity to update continuously and frequently is the key benefit of this
technology. As a result, data warehouses are perfect for organizations and
entrepreneurs who want to stay current with their target audience and customers.
 It makes data more accessible to businesses and organizations.
 A data warehouse holds a large volume of historical data that users can use to
evaluate different periods and trends in order to create predictions for the future.
Disadvantages of Data Warehousing
 There is a great risk of accumulating irrelevant and useless data. Data loss and
erasure are other potential issues.
 Data is gathered from various sources in a data warehouse. Cleansing and
transformation of the data are required. This could be a difficult task.
What is Data Mining?
It is the process of finding patterns and correlations within large data sets to identify
relationships between data. Data mining tools allow a business organization to predict
customer behavior. Data mining tools are used to build risk models and detect fraud.
Data mining is used in market analysis and management, fraud detection, corporate
analysis, and risk management.

Advantages of Data Mining

 Data mining aids in a variety of data analysis and sorting procedures. The
identification and detection of any undesired fault in a system is one of the best
implementations here. This method permits any dangers to be eliminated sooner.
 In comparison to other statistical data applications, data mining methods are both
cost-effective and efficient.
 Companies can take advantage of this analytical tool by providing appropriate
and easily accessible knowledge-based data.
 The detection and identification of undesirable faults that occur in the system are
one of the most astonishing data mining techniques.
Disadvantages of Data Mining
  Data mining isn’t always 100 percent accurate, and if done incorrectly, it can lead
to data breaches.
 Organizations must devote a significant amount of resources to training and
implementation. Furthermore, the algorithms used in the creation of data mining
tools cause them to work in different ways.

Difference Between Data Mining and Data Warehousing

Basis of
Comparison Data Warehousing Data Mining

A data warehouse is a
database system that is
Data mining is the process of
Definition designed for analytical
analyzing data patterns.
analysis instead of
transactional work.

Process Data is stored periodically. Data is analyzed regularly.

Data warehousing is the

process of extracting and Data mining is the use of pattern
Purpose
storing data to allow easier recognition logic to identify patterns.
reporting.

Managing Data warehousing is solely Data mining is carried out by business

Authorities carried out by engineers. users with the help of engineers.

Data warehousing is the

Data Data mining is considered as a process
process of pooling all
Handling of extracting data from large data sets.
relevant data together.
Basis of
Comparison Data Warehousing Data Mining

Subject-oriented,
AI, statistics, databases, and machine
integrated, time-varying
Functionality learning systems are all used in data
and non-volatile constitute
mining technologies.
data warehouses.

Data warehousing is the

process of extracting and
Pattern recognition logic is used in
Task storing data in order to
data mining to find patterns.
make reporting more
efficient.

It extracts data and stores

This procedure employs pattern
it in an orderly format,
Uses recognition tools to aid in the
making reporting easier
identification of access patterns.
and faster.

Data mining aids in the creation of

When a data warehouse is
suggestive patterns of key parameters.
connected with operational
Customer purchasing behavior, items,
business systems like
Examples and sales are examples. As a result,
CRM (Customer
businesses will be able to make the
Relationship Management)
required adjustments to their
systems, it adds value.
operations and production.

Database Schemas
A database schema defines the structure and organization of data within a database. It
outlines how data is logically stored, including the relationships between different tables
and other database objects. The schema serves as a blueprint for how data is stored,
accessed, and manipulated, ensuring consistency and integrity throughout the system. In
this article, we will explore the concept of database schema, its types, and how it plays a
crucial role in designing efficient and scalable databases
What is Schema?
A schema is the blueprint or structure that defines how data is organized and stored in a
database. It outlines the tables, fields, relationships, views, indexes, and other elements
within the database. The schema defines the logical view of the entire database and
specifies the rules that govern the data, including its types, constraints, and
relationships.

Database Schema
A database schema is the design or structure of a database that defines how data is
organized and how different data elements relate to each other. It acts as a blueprint,
outlining tables, fields, relationships, and rules that govern the data.
Key points about a database schema:
 It defines how data is logically organized, including tables, fields, and
relationships.
 It outlines the relationships between entities, such as primary and foreign keys.
 It helps resolve issues with unstructured data by organizing it in a clear, structured
way.
 Database schemas guide how data is accessed, modified, and maintained.
In simple terms, the schema provides the framework that makes it easier to understand,
manage, and use data in a database. It’s created by database designers to ensure the data
is consistent and efficiently organized.

Types of Database Schemas

Physical Database Schema
 A physical schema defines how data is stored in the storage system, including the
arrangement of files, indices and other storage structures. It specifies the actual
code and syntax needed to create the database structure. Essentially, it determines
where and how the data is stored in the physical storage medium.
 The database administrator decides the storage locations and organization of data
within the storage blocks. This schema represents the lowest level of abstraction
Logical Database Schema
 A logical database schema defines the logical structure of the data, including
tables, views, relationships, and integrity constraints. It describes how data is
organized in tables and how the attributes of these tables are connected. The
logical schema ensures that the data is stored in an organized manner, while
maintaining data integrity.
 Using Entity-Relationship (ER) modeling, the logical schema outlines the
relationships between different data components. It also defines integrity
constraints to ensure the quality of data during insertion and updates.
 This schema represents a higher level of abstraction compared to the physical
schema, focusing on logical constraints and how the data is structured, without
dealing with the physical storage details.
View Database Schema
 The view schema is the highest level of abstraction in a database, focusing on
how users interact with the database. It defines the interface through which users
can access and manipulate data, without needing to understand the underlying
storage mechanisms.
  A database can have multiple view schemas, also known as subschemas, each
providing a different perspective of the data. These schemas describe only a part
of the database.
Creating Database Schema
For creating a schema, the statement “CREATE SCHEMA” is used in every database.
But different databases have different meanings for this. Below we’ll be looking at some
statements for creating a database schema in different database systems:
1. MySQL: In MySQL, we use the “CREATE SCHEMA” statement for creating the
database, because, in MySQL CREATE SCHEMA and CREATE DATABASE, both
statements are similar.
2. SQL Server: In SQL Server, we use the “CREATE SCHEMA” statement for
creating a new schema.
3. Oracle Database: In Oracle Database, we use “CREATE USER” for creating a new
schema, because in the Oracle database, a schema is already created with each database
user. The statement “CREATE SCHEMA” does not create a schema, instead, it
populates the schema with tables & views and also allows one to access those objects
without needing multiple SQL statements for multiple transactions.
Database Schema Designs
There are many ways to structure a database and we should use the best-suited schema
design for creating our database because ineffective schema designs are difficult to
manage & consume extra memory and resources.
Schema design mostly depends on the application’s requirements. Here we have some
effective schema designs to create our applications, let’s take a look at the schema
designs:
1. Flat Model
2. Hierarchical Model
3. Network Model
4. Relational Model
5. Star Schema
6. Snowflake Schema
Flat Model
A flat model schema is a 2-D array in which every column contains the same type of
data/information and the elements with rows are related to each other. It is just like a
table or a spreadsheet. This schema is better for small applications that do not contain
complex data.

Hierarchical Model
Data is arranged using parent-child relationships and a tree-like structure in the
Hierarchical Database Model. Because each record consists of several children and one
parent, it can be used to illustrate one-to-many relationships in diagrams such as
organizational charts. A hierarchical database structure is great for storing nested data.

Network Model
The network model is similar to the hierarchical model in that it represents data using
nodes (entities) and edges (relationships). However, unlike the hierarchical model,
which enforces a strict parent-child relationship, the network model allows for more
flexible many-to-many relationships. This flexibility means that a node can have
multiple parent nodes and child nodes, making the structure more dynamic.
The network model can contain cycles which is a situation where a path exists that
allows you to start and end at the same node. These cycles enable more complex
relationships and allow for greater data interconnectivity.

Relational Model
The relational model is mainly used for relational databases, where the data is stored as
relations of the table. This relational model schema is better for object-oriented
programming.
Star Schema
Star schema is better for storing and analyzing large amounts of data. It has a fact table
at its center & multiple dimension tables connected to it just like a star, where the fact
table contains the numerical data that run business processes and the dimension table
contains data related to dimensions such as product, time, people, etc. or we can say,
this table contains the description of the fact table. The star schema allows us to
structure the data of RDBMS.

Snowflake Schema
Just like star schema, the snowflake schema also has a fact table at its center and
multiple dimension tables connected to it, but the main difference in both models is that
in snowflake schema – dimension tables are further normalized into multiple related
tables. The snowflake schema is used for analyzing large amounts of data.
Difference between Logical and Physical Database Schema
Physical Schema Logical Schema

Logical schema provides the conceptual

Physical schema describes the way of
view that defines the
storage of data in the disk.
relationship between the data entities.

Having Low level of abstraction. Having a high level of abstraction.

Physical Schema Logical Schema

The design of a database must work with

The design of database is independent to
a specific database management system
any database management system.
or hardware platform.

Any changes made in logical schema

Changes in Physical schema effects the
have minimal effect in the physical
logical schema
schema

Physical schema does not include

Logical schema includes attributes.
attributes.

Physical schema contains the attributes Logical schema does not contain any
and their data types. attributes or data types.

Examples: Data definition Examples: Entity Relationship diagram,

language(DDL), storage structures, Unified Modeling Language, class
indexes. diagram.

Advantages of Database Schema

 Providing Consistency of data: Database schema ensures the data consistency
and prevents the duplicates.
 Maintaining Scalability: Well designed database schema helps in maintaining
addition of new tables in database along with that it helps in handling large
amounts of data in growing tables.
 Performance Improvement: Database schema helps in faster data retrieval
which is able to reduce operation time on the database tables.
 Easy Maintenance: Database schema helps in maintaining the entire database
without affecting the rest of the database
  Security of Data: Database schema helps in storing the sensitive data and allows
only authorized access to the database.

Database Instance
A database instance is a snapshot of a database at a specific moment in time, containing all
the properties described by a database schema as data values. Unlike database schemas,
which are considered the “blueprint” of a database, instances can change over time whereas it
is very difficult to modify the schema because the schema represents the fundamental
structure of the database. Database instance does not hold any information related to the
saved data in database.

Database schema versus database instance

Aspect Database Schema Database Instance

Blueprint or design of the database Actual data stored in the

Definition structure database at a given time

Dynamic (changes with every

Static (does not change frequently)
Nature data modification)
Aspect Database Schema Database Instance

Structure (tables, columns, data State of the data in the

Represents types, relationships) database

Table definitions, data types, Actual rows of data in the

Example constraints tables

Change Changes infrequently (e.g., during Changes frequently with

Frequency schema design changes) transactions

Q.1 How to Save a Database and a Table.

To save a database and a table, follow these steps:

1. Open the database management system (DBMS) software that you are using, such

as MySQLor Microsoft Access.

2. Navigate to the database that you want to save and select it.

3. Click on the "Export" button or option in the menu. This will open a window that

allows you to select the format and location for saving the database.

4. Choose the format for the export, such as SQL or CSV, and select a location to

save the file.

5. Name the file and click on the "Save" button to save the database.

To save a table:

1. Navigate to the table that you want to save and select it.
 2. Click on the "Export" button or option in the menu. This will open a window that

allows you to select the format and location for saving the table.

3. Choose the format for the export, such as CSV or Excel, and select a location to

save the file.

4. Name the file and click on the "Save" button to save the table.

Q. 2 How to create a table?

To create a table in a database, you can follow these general steps:

1. Open the database management system or tool of your choice, such as Microsoft

Access or MySQL Workbench.

2. Create a new database or open an existing one in which you want to create the

table.

3. Navigate to the "Tables" section or tab, where you should see options to create a

new table.

4. Select the "Create Table" option, which will usually open a new window or

prompt for you to enter the details of the table.

5. Enter a name for the table and define the columns or fields that you want to

include. For each column, you should specify a name, data type, and any

additional properties such as length, precision, or default value.

 6. Set a primary key for the table if needed. The primary key is a unique identifier

for each record in the table, and can be a single column or a combination of

columns.

7. Save the table and close the table creation window.

Q.3 How to add various datatypes in Tables?

To add various datatypes in tables, follow these steps:

1. Decide on the data types you want to use for each column in your table. Common

data types include integer, float, character, date, and boolean.

2. Open the database management system (DBMS) software you are using to create

the table.

3. Choose the option to create a new table.

4. Give the table a name and define the number of columns you want it to have.

5. Define the name, data type, and any constraints for each column in the table.

6. For example, to create an integer column, you would specify the data type as

"integer" and define the maximum and minimum values allowed for that column.

7. For a character column, you would specify the data type as "character" and define

the maximum length of the string that can be stored in that column.

8. Save the table once you have defined all of the columns and their data types.

Q.4 How to make form for tables?

Here are the general steps to create a form for a table:

 1. Open the database management software and select the table for which you want

to create a form.

2. Go to the "Create" tab and select "Form Wizard".

3. In the Form Wizard, select the table you want to use and add the fields you want

to include in the form.

4. Choose a layout option for the form, such as a columnar or tabular layout.

5. Select a style option for the form, such as a classic or contemporary style.

6. Customize the form design and layout as desired, including font, color, and style

options.

7. Preview the form and make any necessary adjustments.

8. Save the form and give it a descriptive name.

Q.5 How to Edit a form?

To edit a form, you will need to follow these steps:

1. Open the form in the form builder or editor.

2. Make the desired changes to the form fields, such as editing or deleting existing

fields, or adding new ones.

3. Modify any settings or options for the form, such as changing the form layout,

adjusting form field validation rules, or setting up email notifications.

4. Save the changes to the form by clicking the "Save" button or the equivalent

option in your form builder/editor.

 Q.6 What are Various buttons uses in Forms? Give Examples.

Here are some common examples of buttons and their uses:

1. Submit button: This button is used to submit the form data to the database or

server.

2. Reset button: This button is used to clear all the fields in the form and reset it to

its original state.

3. Cancel button: This button is used to cancel the form submission or close the

form without saving any changes.

4. Save button: This button is used to save the data entered in the form to the

database or server.

5. Add button: This button is used to add a new record or row to the table.

6. Delete button: This button is used to delete a record or row from the table.

7. Edit button: This button is used to edit or update a record or row in the table.

8. Next/Previous buttons: These buttons are used to navigate to the next or previous

page of the form or table.

9. Search button: This button is used to search for specific data in the form or table.

10.Print button: This button is used to print the data entered in the form or the

contents of the table.

Q.7 How to Create Reports?

To create a report, follow these steps:

 1. Open the database in Microsoft Access.

2. Click on the "Create" tab.

3. Click on the "Report Design" button.

4. Select the table or query you want to base your report on.

5. Choose the fields you want to include in the report.

6. Arrange the fields and add any grouping, sorting, or formatting options you want.

7. Preview the report to ensure it looks the way you want it to.

8. Save the report.

Q.8 How to Apply these Queries:

a. Simple Query.

b. Query Using Parameters

To apply a simple query in a database, follow these steps:

1. Open the database in Microsoft Access.

2. Click on the "Create" tab on the ribbon at the top of the screen.

3. Click on "Query Design" in the "Queries" section of the ribbon.

4. Choose the table or tables you want to include in the query and add them to the

query design.

5. Add the fields you want to include in the query by dragging them from the table

to the query design.

 6. Define the criteria for the query by selecting the field you want to filter by and

adding the criteria in the "Criteria" row of the query design.

7. Run the query by clicking the "Run" button on the ribbon or by pressing the F5

key.

To apply a query using parameters, follow these steps:

1. Open the database in Microsoft Access.

2. Click on the "Create" tab on the ribbon at the top of the screen.

3. Click on "Query Design" in the "Queries" section of the ribbon.

4. Choose the table or tables you want to include in the query and add them to the

query design.

5. Add the fields you want to include in the query by dragging them from the table

to the query design.

6. Define the criteria for the query by selecting the field you want to filter by and

adding the parameter in the "Criteria" row of the query design, using a question

mark (?) as a placeholder for the parameter.

7. Save the query and close the query design view.

8. Create a form or report that uses the query as its data source.

9. Add a parameter control to the form or report, such as a text box or drop-down

list, and link it to the parameter in the query.

 10. Run the form or report, and enter a value for the parameter when prompted to see

the filtered results based on the parameter value.

Q.9 How MS-Access is useful in our Offices?

MS-Access is a powerful database management system that can be used in various ways

to increase efficiency and productivity in offices. Here are some of the ways MS-

Access can be useful in offices:

1. Data management: MS-Access can be used to manage and organize large

amounts of data efficiently. It can store, retrieve, and update data easily,

providing a centralized location for data that can be accessed by multiple users.

2. Reporting: MS-Access can be used to generate reports from the data stored in the

database. Reports can be customized to show only specific data and can be

exported in various formats such as PDF, Excel, and Word.

3. Automation: MS-Access can automate routine tasks, such as data entry, data

validation, and report generation, saving time and reducing errors.

4. Integration: MS-Access can be integrated with other Microsoft Office

applications such as Excel, Word, and Outlook. This integration allows for easy

transfer of data and the creation of complex reports.

5. Security: MS-Access provides robust security features, allowing for the creation

of user accounts with different levels of access to the database. This ensures that

sensitive data is only accessible by authorized personnel.

Q.10 How we can add various buttons in Forms?

To add various buttons in forms in MS Access, follow these steps:

1. Open the form in design view.

2. Go to the "Design" tab on the ribbon.

3. Click on the "Button" control from the "Controls" group.

4. Click on the form where you want to place the button. This will open the

"Command Button Wizard" dialog box.

5. Select the desired option from the dialog box, such as "Open Form", "Run

Macro", "Send Email", etc.

6. Follow the wizard steps to configure the button's function, such as selecting the

form or report to open or selecting the macro to run.

7. Once done, click "Finish" to create the button.

8. To modify the appearance of the button, right-click on it and select "Properties".

From there, you can change the button's label, font, color, etc.

Examples of commonly used buttons in forms are:

 "Save Record" button to save data entered in the form

 "Delete Record" button to delete a selected record

 "New Record" button to create a new blank record in the form

 "Print Report" button to print a report based on the data in the form

 "Close Form" button to close the current form.