relational database

• A database management system (DBMS) is the technology for creating and managing databases
• DBMS is a software tool used to create, retrieve, update, and manage data in a database
• The main goal of a DBMS is to provide a way to store up and retrieve database information in a convenient, consistent, secure, and efficient manner
• A DBMS helps store information and data in a manner that is easy to read, edit, delete, and scale
• DBMS has a backend storage system as well as a front-end user interface
• Examples of DBMSs include Oracle, MySQL, Microsoft Access, MongoDB, Microsoft SQL Server, IBM Db2, Amazon RDS, PostgreSQL, Apache Cassandra, Red Brick, MariaDB, Couchbase, Amazon DynamoDB
• Advantages of DBMS include offering techniques to store & retrieve data efficiently, balancing the needs of multiple applications, reducing Application Development Time, providing uniform administration procedures, improving data consistency and security
• Challenges associated with DBMS include high costs of hardware and software, complexity requiring technical skills, and potential data loss with multiple users accessing the same program
• DBMS can be categorized into centralized systems, distributed systems, and federated systems
• A federated system is a special type of distributed DBMS that consists of a database instance operating as a federated server
• Functions of a DBMS include granting multiple users concurrent access, establishing security rules, backing up data, setting up database rules, and providing data descriptions
• Data processing involves performing specific operations on a set of data or a database
• Data processing consists of input, processing, and output stages
• Data processing can be manual or automatic, involving transforming data into useful outputs
• Data processing methods include manual, mechanical, and electronic data processing
• Types of data processing include batch processing, real-time processing, online processing, multiprocessing, and time-sharing
• Stages of data processing in the organization include data collection, data preparation, data input, processing, data output, and interpretation
• Data processing in an organization involves six stages:

  1. Data collection - gathering raw data

  2. Data input - entering data into the computer

  3. Data processing - interpreting the input data

  4. Data output/interpretation - making data usable to non-data scientists

  5. Data storage - storing processed data for future use
• SQL is a language used to manage data in a relational database management system
• SQL comprises rules and commands for interacting with databases
• SQL is an ANSI/ISO standard with different versions based on the DBMS
• Categories of SQL commands include:

  • Data Query Language (DQL) for searching data
  • Data Manipulation Language (DML) for adding, updating, or deleting data
  • Data Control Language (DCL) for managing database rights
  • Data Definition Language (DDL) for creating and managing database objects
  • Transaction Control Language (TCL) for transaction management
• Microsoft SQL Server consists of two main components:

  • Database Engine with a relational engine and a storage engine
  • SQLOS providing operating system services
• SQL Server uses Transact-SQL (T-SQL) as an extension of SQL for managing data
• SQL Server provides data management and business intelligence tools and services
• SQL Server has different editions like Developer, Express, Enterprise, and Standard with varying features
• To create a database in SQL Server, use the CREATE DATABASE statement with a unique name complying with SQL Server rules
• To remove a database in SQL Server, use the DROP DATABASE statement, ensuring you have backups and the database is not in use
• A schema in SQL Server is a collection of database objects associated with a username, owned by the schema owner
• Tables in SQL Server are created using the CREATE TABLE statement with associated data types and constraints
• To create a table named "sales.visits" to track customer in-store visits:
• To add a new column to a table in SQL Server:
• To add multiple columns to a table at once using a single ALTER TABLE statement:
• To create a new table named "sales.quotations":
• To add a new column named "description" to the "sales.quotations" table:
• To add more columns named "amount" and "customer_name" to the "sales.quotations" table:
• SQL Server allows changes to an existing column of a table such as modifying the data type, changing the size, and adding a NOT NULL constraint
• To modify the data type of a column in SQL Server:
• To create a new table with a nullable column:
• Commands that can be used to adjust a table's structure include DROP, TRUNCATE, SELECT INTO, RENAME