DBA Notes Lecture 1

 

DBA Notes Lecture -1

Data- Information of any object

Data - Information that is stored and organized in a database. SQL is a programming language used for managing and manipulating relational databases.

SQL data is normally organised into tables, which are made up of rows and columns. A table's rows represent individual records or entries, whereas each column represents a specific attribute or piece of information connected to the data. A table in a Hospital database, for example, might contain columns such as "Hospital_id," "name," and "address," with each entry holding data for a specific Hospital.

Database – container for tables, views, indexes, SP, & other database objects.

Database – It is a systematic collection of related data. A database is a container for tables, views, indexes, stored procedures, and other database objects.

Examples of SQL databases include MySQL, Oracle, Microsoft SQL Server, PostgreSQL, and SQLite. These databases provide tools and functionalities to create, modify, and interact with the database structures and data using SQL statements.

RDBMS- Easily accessible and manageable database

An RDBMS organizes and stores data in the form of tables, which consist of rows and columns. 

 

12 Rules of Database

1.     Database should be stored in table format

2.     Data should be fetched by using tablename and column.

3.     Primary Key: Each table should have a primary key that uniquely identifies each record in the table.

4.     Foreign Key Relationships: Establishing relationships between tables using foreign keys to maintain data consistency and enforce referential integrity.

5.     Indexing: Creating appropriate indexes on frequently accessed columns to improve query performance.

6.     Data Security: Protect sensitive data, including user authentication, authorization, and encryption.

7.     Backup and Recovery: Regularly backing up the database and having a well-defined recovery plan to safeguard against data loss and enable restoration.

8.     Data Consistency: Data remains consistent during concurrent transactions.

9.     Performance Optimization: Monitoring and tuning the database performance through techniques such as query optimization, proper indexing, and database parameter configuration.

10. Scalability: Designing the database to handle growing data volumes and increasing user loads by employing strategies like horizontal or vertical scaling.

11. Documentation: Maintaining thorough documentation about the database schema, relationships, constraints, and other essential details to facilitate understanding and maintenance.

12. Regular Maintenance: Performing routine maintenance tasks such as database optimization, data purging, and index rebuilding to keep the database running smoothly.

ACID

ACID stands for Atomicity, Consistency, Isolation, and Durability.

1.     Atomicity: Atomicity guarantees that the database remains consistent and that partial or incomplete transactions do not affect data integrity.

2.     Consistency: Consistency ensures that the data remains valid and meaningful throughout the transaction process.

3.     Isolation: Isolation ensures that concurrent transactions operate independently and do not interfere with each other.

4.     Durability: Changes made by committed transactions are persistent and recoverable.

SQL- Structured Query Language

SQL to communicate with database.

Data Model:

Data model represents the data, how it stored in the database and how data is accessible and updated in Database.

 

OLTP                                                                          OLAP 

Handle large no. of small transactions                         Handle large volume of data

Real time execution such as banking                            useful for data mining

Text-messaging, ATM, etc.

Transactions                                                                                    Aggregated Data

Normalised data                                                                 DE normalised data

 

Why MS SQL Server for Clients

Feature Wise- It provides support for advanced functionalities like high availability, data replication, backup and recovery, business intelligence, and analysis services.

Cost Wise- Offers a free edition of SQL Server called SQL Server Express.

Scalability - MSSQL provides features like partitioning, distributed databases, and resource management, allowing for the scalability of databases as data volumes and user loads increase.

Security Wise - It supports authentication mechanisms, access control, encryption, and auditing.

 

NOTE: Understand Business Requirement