introduction database concepts - swatitechsol.com · introduction database concepts co attained :...
TRANSCRIPT
![Page 1: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/1.jpg)
Introduction Database Concepts
CO attained : CO1
Hours Required: 05
Self Study: 08
Prepared and presented by :
Ms. Swati Abhang
![Page 2: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/2.jpg)
Contents
Introduction
Characteristics of databases,
File system V/s Database system,
Users of a Database system
Data Models, Schemas, and Instances,
Three-Schema Architecture and Data Independence,
Database Administrator (DBA), Role of a DBA
![Page 3: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/3.jpg)
What is a Database System?
1-3
Application
program
End-user
DBMS
![Page 4: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/4.jpg)
What is a DATABASE?
Definition
A database is a well organized collection of data that
are related in meaningful way which can be accessed in a
different logical orders but are stored only once. The data in
the database is therefor integrated, structured and shared.
![Page 5: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/5.jpg)
What is a Database System? (cont.)
1-5
Major components of a database system: Data: integrated and shared.
Hardware: disk, CPU, Main Memory, ...
Software: DBMS
Users:
1. Application programmers
2. End users
3. Database administrator (DBA)
Defining external schema
Defining conceptual schema
Defining internal schema
Liaison with users
Defining security and integrity checks
Defining backup and recovery procedures
Monitoring performance and changing requirements
![Page 6: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/6.jpg)
Why Database ?
1-6
Redundancy can be reduced
Inconsistency can be avoided
The data can be shared
Standards can be enforced
Security restrictions can be applied
Integrity can be maintained
Provision of data independence
objective !
![Page 7: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/7.jpg)
Characteristics of databases
Persistent Data
Metadata and self-describing Nature
Data Independence
Access flexibility and Security
![Page 8: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/8.jpg)
Characteristics of dbms
To incorporate the requirements of the organization,
system should be designed for easy maintenance.
Information systems should allow interactive access to
data to obtain new information without writing fresh
programs.
System should be designed to co-relate different data to
meet new requirements.
An independent central repository, which gives
information and meaning of available data is required.
1-8
![Page 9: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/9.jpg)
Characteristics of dbms
Integrated database will help in understanding the inter-
relationships between data stored in different applications.
The stored data should be made available for access by
different users simultaneously.
Automatic recovery feature has to be provided to
overcome the problems with processing system failure.
1-9
![Page 10: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/10.jpg)
1-10
Functions of the DBMS
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Security and Integrity
Data Recovery and Concurrency
Data Dictionary
Performance
![Page 11: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/11.jpg)
History of Database Systems
1950s and early 1960s:
Data processing using magnetic tapes for storage
Tapes provided only sequential access
Punched cards for input
Late 1960s and 1970s:
Hard disks allowed direct access to data
Network and hierarchical data models in widespread use
Ted Codd defines the relational data model
Would win the ACM Turing Award for this work
IBM Research begins System R prototype
UC Berkeley begins Ingres prototype
High-performance (for the era) transaction processing
![Page 12: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/12.jpg)
History (cont.)
1980s: Research relational prototypes evolve into commercial systems
SQL becomes industrial standard
Parallel and distributed database systems
Object-oriented database systems
1990s: Large decision support and data-mining applications
Large multi-terabyte data warehouses
Emergence of Web commerce
Early 2000s: XML and XQuery standards
Automated database administration
Later 2000s: Giant data storage systems
Google BigTable, Yahoo PNuts, Amazon, ..
![Page 13: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/13.jpg)
Database: Historical Roots
Manual File System
to keep track of data
used tagged file folders in a filing cabinet
organized according to expected use
e.g. file per customer
easy to create, but hard to
locate data
aggregate/summarize data
Computerized File System
to accommodate the data growth and information need
manual file system structures were duplicated in the computer
Data Processing (DP) specialists wrote customized programs to
write, delete, update data (i.e. management)
extract and present data in various formats (i.e. report)
S511 Session 2, IU-SLIS 13
![Page 14: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/14.jpg)
File System: Example
S511 Session 2, IU-SLIS 14
Database Systems: Design, Implementation, & Management: Rob & Coronel
![Page 15: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/15.jpg)
File System: Weakness Weakness
“Islands of data” in scattered file systems.
Problems Duplication
same data may be stored in multiple files
Inconsistency
same data may be stored by different names in different format
Rigidity
requires customized programming to implement any changes
cannot do ad-hoc queries
Implications Waste of space
Data inaccuracies
High overhead of data manipulation and maintenance
S511 Session 2, IU-SLIS 15
![Page 16: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/16.jpg)
File System: Problem Case
S511 Session 2, IU-SLIS 16
CUSTOMER file AGENT file SALES file
A_Name (15 char)
Carol Johnson
A_Name (20 char)
Carol T. Johnson
AGENT (20 char)
Carol J. Smith
- inconsistent field name, field size - inconsistent data values - data duplication
![Page 17: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/17.jpg)
Database System vs. File System
S511 Session 2, IU-SLIS 17
Database Systems: Design, Implementation, & Management: Rob & Coronel
![Page 18: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/18.jpg)
Hierarchical Database
Background
Developed to manage large amount of data for complex manufacturing projects
e.g., Information Management System (IMS)
IBM-Rockwell joint venture
clustered related data together
hierarchically associated data clusters using pointers
Hierarchical Database Model
Assumes data relationships are hierarchical
One-to-Many (1:M) relationships
Each parent can have many children
Each child has only one parent
Logically represented by an upside down tree
S511 Session 2, IU-SLIS 18
![Page 19: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/19.jpg)
Hierarchical Database: Example
S511 Session 2, IU-SLIS 19
Database Systems: Design, Implementation, & Management: Rob & Coronel
![Page 20: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/20.jpg)
Hierarchical Database: Pros & Cons
Advantages Conceptual simplicity
groups of data could be related to each other
related data could be viewed together
Centralization of data
reduced redundancy and promoted consistency
Disadvantages Limited representation of data relationships
did not allow Many-to-Many (M:N) relations
Complex implementation
required in-depth knowledge of physical data storage
Structural Dependence
data access requires physical storage path
Lack of Standards
limited portability
S511 Session 2, IU-SLIS 20
![Page 21: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/21.jpg)
Data Manipulation Language (DML)
Language for accessing and manipulating the data
organized by the appropriate data model
DML also known as query language
Two classes of languages
Procedural – user specifies what data is required and how to
get those data
Declarative (nonprocedural) – user specifies what data is
required without specifying how to get those data
SQL is the most widely used query language
![Page 22: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/22.jpg)
Data Definition Language (DDL)
Specification notation for defining the database schema Example: create table account (
account_number char(10),
branch_name char(10),
balance integer)
DDL compiler generates a set of tables stored in a data dictionary
Data dictionary contains metadata (i.e., data about data) Database schema
Data storage and definition language Specifies the storage structure and access methods used
Integrity constraints Domain constraints
Referential integrity (e.g. branch_name must correspond to a valid branch in the branch table)
Authorization
![Page 23: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/23.jpg)
Users of Database system
![Page 24: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/24.jpg)
Database Users
Database Administrators
In a database environment, the primary resource is the
database itself and the secondary resource is the DBMS and
related software
authorizing access to the database
coordinating and monitoring its use
acquiring software and hardware resources as needed
Database Designers
identifying the data to be stored in the database
choosing appropriate structures to represent and store this
data undertaken before the database is actually implemented
and populated with data
![Page 25: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/25.jpg)
Database Users …..
communicate with all prospective database users, in order to understand their requirements
develop a view of the database that meets the data and processing requirements for each group of users
These views are then analyzed and integrated with the views of other user groups. The final database design must be capable of supporting the requirements of all user groups
End Users
access to the database for querying, updating, and generating reports
Casual end users: occasionally access the database
need different information each time
learn only a few facilities that they may use repeatedly.
![Page 26: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/26.jpg)
Database Users …..
use a sophisticated database query language to specify their requests
typically middle- or high-level managers or other occasional browsers
Naive or parametric end users constantly querying and updating the database, using standard types of queries and
updates called canned transactions that have been carefully programmed and
tested
need to learn very little about the facilities provided by the DBMS
Bank tellers check account balances and post withdrawals and deposits
Reservation clerks for airlines, hotels, and car rental companies check availability
for a given request and make reservations
Clerks at receiving stations for courier mail enter package identifications via bar
codes and descriptive information through buttons to update a central database of
received and in-transit packages
![Page 27: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/27.jpg)
Database Users …..
Sophisticated end users Engineers, scientists, business analysts, and others who
thoroughly familiarize themselves with the facilities of the DBMS so as to implement their applications to meet their complex requirements
Try to learn most of the DBMS facilities in order to achieve their complex requirements
Stand-alone users Maintain personal databases by using ready-made program
packages that provide easy-to-use menu- or graphics-based interfaces. An example is the user of a tax package that stores a variety of personal financial data for tax purposes
Typically become very proficient in using a specific software package
![Page 28: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/28.jpg)
Database Users …..
System Analysts and Application Programmers
Determine the requirements of end users, especially naive and
parametric end users, and develop specifications for canned transactions
that meet these requirements
Application programmers implement these specifications as programs;
then they test, debug, document, and maintain these canned transactions
Workers behind the Scene
Typically do not use the database for their own purposes
DBMS system designers and implementers
design and implement the DBMS modules (for implementing the catalog,
query language, interface processors, data access, concurrency control,
recovery, and security. ) and interfaces as a software package
![Page 29: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/29.jpg)
Database Users …..
Tool developers
Tools are optional packages that are often purchased
separately
include packages for database design, performance monitoring,
natural language or graphical interfaces, prototyping, simulation,
and test data generation.
Operators and maintenance personnel
system administration personnel who are responsible for the
actual running and maintenance of the hardware and software
environment for the database system
![Page 30: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/30.jpg)
Data Models
A collection of tools for describing Data Data relationships Data semantics Data constraints
Relational model
Entity-Relationship data model (mainly for database design)
Object-based data models (Object-oriented and Object-relational)
Semistructured data model (XML)
Other older models: Network model Hierarchical model
![Page 31: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/31.jpg)
Relational Model
All the data is stored in various tables.
Example of tabular data in the relational model Columns
Rows
![Page 32: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/32.jpg)
A Sample Relational Database
![Page 33: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/33.jpg)
Instances and Schemas
Similar to types and variables in programming languages
Logical Schema – the overall logical structure of the database Example: The database consists of information about a set of customers
and accounts in a bank and the relationship between them Analogous to type information of a variable in a program
Physical schema– the overall physical structure of the database
Instance – the actual content of the database at a particular point in time Analogous to the value of a variable
Physical Data Independence – the ability to modify the physical schema without changing the logical schema Applications depend on the logical schema
In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others.
![Page 34: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/34.jpg)
Architecture of DBMS
Following are the three levels of database
architecture,
1. Physical Level
2. Conceptual Level
3. External Level
![Page 35: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/35.jpg)
Three Levels
Architecture
of DBMS
![Page 36: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/36.jpg)
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.
![Page 37: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/37.jpg)
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.
![Page 38: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/38.jpg)
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.
![Page 39: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/39.jpg)
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.
![Page 40: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/40.jpg)
Architec
ture of
DBMS
/Databa
se
System
Internal
s
![Page 41: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/41.jpg)
![Page 42: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/42.jpg)
Responsibilities of DBA
Installation, configuration and upgradation of databases like Microsoft SQL/ MySQL/ Oracle Server Software.
Evaluating the features of various databases.
Establishing and maintaining sound backup and recovery policies and procedures.
Taking care of database design and implementation.
Implementing and maintaining the database security.
Database tuning, application tunning and performance monitoring.
Maintaining documentation and standards.
DBA does some technical trouble shooting and consultation to development teams.
![Page 43: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/43.jpg)
Skill set is required to be a successful
Database Administrator
Problem Management
Incident Management
Chain Management
Capacity Planning
![Page 44: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/44.jpg)
Types of DBA
1. Administrative DBA
2. Development DBA
3. Architect
4. Data Warehouse DBA
5. OLAP DBA
![Page 45: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/45.jpg)
Types of DBA
1. Administrative DBA
Administrative DBA maintains the work on the server and
keeps it running.
Administrative DBA is mostly concerned with backups,
security, replication etc.
2. Development DBA
Development DBA builds queries, stored procedures etc.
which mostly meet business needs.
Development DBA is equivalent to a programmer.
![Page 46: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/46.jpg)
Types of DBA
3. Architect
Architect builds table, design schema, foreign keys, primary keys
etc. which meets the business needs.
4. Data Warehouse DBA
Data Warehouse DBA is responsible for merging the data from
multiple sources into a data warehouse.
5. OLAP DBA
OLAP DBA builds multi-dimensional cubes for decision
support or OLAP systems.
The primary language in SQL Server is MDX.
![Page 47: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/47.jpg)
Important Questions
1. What is a database ? Discuss its main features and explain the importance of each feature .
2. List the characteristics of databases.
3. Describe the disadvantages of using a file processing system compared to a database system ?
4. What is DBMS ? What task does a DBMS carry out ?
5. What are classes of users use database systems?
6. List three views for database system in three level architecture. Describe role of these views.
7. List the major components of DBMS architecture and describe their functions.
8. Describe role of DBA
![Page 48: Introduction Database Concepts - swatitechsol.com · Introduction Database Concepts CO attained : CO1 Hours Required: 05 Self Study: 08 Prepared and presented by : Ms. Swati Abhang](https://reader034.vdocuments.us/reader034/viewer/2022043013/5fadae1a96180e1b45043602/html5/thumbnails/48.jpg)
What is meant by logical and physical structure of data ?
Explain difference between logical and physical
independence ?
Explain difference between external, internal and
conceptual view in three-tier database architecture. How
are these different schema layers related to the concept
of logical and physical data independence.