SQL: Standard Query Language

Standard Query Language (SQL)

• SQL is a standard language for accessing and manipulating databases
• SQL became a standard of the American National Standards Institute (ANSI) in 1986
• SQL became a standard of the International Organization for Standardization (ISO) in 1987
• Despite being an ANSI/ISO standard, different versions of the SQL language exist
• All versions support the major commands in a similar manner for ANSI compliance
• Major commands include SELECT, UPDATE, DELETE, INSERT, WHERE

SQL Capabilities

• Accessing & manipulating databases is possible
• Executing queries against a database
• Retrieving data is possible
• Records can be inserted
• Records can be updated
• Records can be deleted
• New databases can be created
• New tables in a database can be created
• Creating views in a database
• Setting permissions on tables, procedures, and views

MySQL

• First released in 1995, it is a relational database management system (RDBMS)
• It is open-source and free
• Compliant with the ANSI SQL standard
• Ideal for both small and large applications
• Notable users include Facebook, Twitter, Airbnb, Uber, Github, Youtube, Wordpress, Drupal, and Joomla

Data

• Defined as facts or figures, or information stored or used by a computer
• Can be processed into different forms of information
• Sorting and summing transactions takes time
• Up-to-date information can provide instant answers
• A representation of facts or ideas in a formalized manner capable of being communicated or manipulated by some process
• Examples include customer data, such as custID, name, and address
• Examples include employee data, such as employeeID, salary, and department

Database

• An organized collection of structured information or data
• Typically stored electronically in a computer system
• Stored in the form of tables with rows and columns
• Generated and manipulated manually or computerized
• The main purpose is to operate on a large amount of information by storing, retrieving, and managing data

Types of Databases

• Relational databases
  • Organized as tables with columns and rows
  • Provides the most efficient and flexible way to access structured information
• Object-oriented databases
  • Represented in the form of objects
• Distributed databases
  • Consists of two or more files located in different sites
  • Data is stored in multiple locations
• Data warehouses
  • A central repository for data specifically designed for fast query and analysis
• NoSQL databases
  • Allows unstructured and semi-structured data to be stored and manipulated
  • Grew popular as web applications became more common and complex
• Graph databases
  • Stores data in terms of entities and their relationships.
• OLTP databases
  • A speedy, analytic database designed for large numbers of transactions performed by multiple users
• Open source databases
  • The source code is open source
  • Can be SQL or NoSQL databases
• Cloud databases
  • A collection of data, structured or unstructured, that resides on a private, public, or hybrid cloud computing platform
  • Includes traditional and database as a service (DBaaS) models
  • With DBaaS, administrative tasks and maintenance are performed by a service provider
• Document/JSON database
  • Designed for storing, retrieving, and managing document-oriented information
  • Stores data in JSON format rather than rows and columns
• Self-driving databases
  • Cloud-based and use machine learning to automate database tuning, security, backups, updates, and other routine management tasks

Database Management System (DBMS)

• Software used to manage databases
• Examples include MySQL and Oracle
• Provides an interface to perform operations like database creation, data storage, updating, and table creation
• Provides protection and security to the database
• Maintains data consistency, especially with multiple users
• A computerized system to maintain information and make it available on demand

Advantages of DBMS

• Controls data redundancy by storing all data in one file
• Allows authorized users to share data
• Easily maintained due to the centralized nature
• Reduces development time and maintenance
• Provides backup and recovery subsystems for automatic data backup and restoration
• Provides multiple user interfaces

Disadvantages of DBMS

• Requires high-speed data processors and large memory sizes
• Occupies a large space of disks and memory
• Creates additional complexity and requirements
• A failure has a high impact due to the central storage of data
• Damage can lead to data loss

Database Challenges

• Absorbing significant increases in data volume efficiently
• Ensuring data security against increasingly inventive hackers
• Keeping up with the demand for real-time data access for timely decision-making

Additional Database Concerns

• Managing and maintaining the database and infrastructure through preventative maintenance and software updates
• Addressing the expense of hiring talent to monitor and tune databases as complexity and data volumes grow
• Removing scalability limits to support business growth
• Ensuring data residency, data sovereignty, or latency requirements, especially for on-premises solutions

Relational Data Base Management System (RDBMS)

• Modern database management systems are based on RDBMS
• Introduced by E.F. Codd
• Stores data in a well-organized composite arrangement of rows and columns
• A relational database is made up of a set of tables with data that fits into a predefined category
• Contains N number of tables
• Each table has its own unique primary key
• Tables consist of rows and columns
• A row called a Record or horizontal entity holds information about the individual entry
• A column called a field or vertical entity holds information about a specific field
• When the user fires a query, it shows results for specific queries
• Checks constraints before creating a table or data

RDBMS Constraints

• Not Null: Ensures that every column should not have a null value (i.e., empty cell)
• Unique: Ensures that every column contains unique data
• Check: Ensures that every entry in a column or row satisfies a specified condition
• Primary Key: Identifies each row of the table, must be unique and not contain null values.
• One table can only contain one primary key and it links two or more tables
• Foreign Key: Links two tables
• Filed in one table that refers to a primary key located in another table
• Creates a relation between two tables containing the primary key

Data Integrity

• Checks integrity before creating data
• Entity Integrity: Ensures no duplicate row in the table
• Referential Integrity: Ensures that rows that re-linked with other tables cannot be deleted
• Domain Integrity: Ensures the entry of data in a table is based on a specific condition
• User-Defined Integrity: Ensures that integrity in the table satisfies the user-defined conditions

Advantages of RDBMS

• Maintainability: Provides easy usability and allows database admins to maintain, control, and update data easily
• Backing up of data becomes easy and automation tools automate these tasks
• Flexibility: Saves time as updating data in one place is enough
• Updating one student's detail updates the database automatically
• Data Structure: Easily understood as it stores data structured in a table format
• Privileges: Allows database administrators to control activities over the database
• Administrators can give specific access to users
• Can stop user access
• Data Safety: Data will be safe even when the program crashes
• Fault Tolerance: By replicating the Database enables recovery with sudden power failures, accidental shutdown etc and provides Concurrent Access

Database Administrator

• Responsible for authorizing access to the database
• Coordinates and monitors its use
• Acquiring software and hardware resources as needed
• Maintains and designs the database daily
• Directs or performs all activities related to maintaining a successful database environment
• Ensures an organization's database and its applications operate functionally and efficiently

Actors Involved in DBMS

• Database Administrator
• Database Architect
• Application DBA
• Task Oriented DBA
• End User

Database Architect

• Primarily works on the design and implementation of new databases
• Designs new databases and structures for new or existing applications
• Rarely involved in the maintenance and tuning of established databases and applications
• Typical tasks include modeling logical data, translating logical data models into a physical database design, analyzing data access requirements, and creating backup and recovery strategies

Application DBA

• Focuses on database design and ongoing database support
• Administration for a specific application or subset of the application
• Experienced at writing and debugging complex SQL
• Understands the best ways to incorporate database requests into application programs
• General-purpose DBAs are still required to support the overall database environment and infrastructure
• General-purpose DBAs support specific applications while maintaining the organization's database environment

Task-oriented DBAs

• Specialized DBAs that focus on a specific administrative task
• Backup-and-recovery DBAs ensure an organization's databases are recoverable
• Includes creating backup plans, building and testing backup scripts, testing recovery scripts, and driving recovery tasks when required
• Participates in building and testing disaster contingency plans for the organization's databases

End Users

• Wishes to store and use data in a database
• Jobs require access to the database for querying, updating, and generating reports
  • Casual End Users: Occasionally access the database and may need different information each time
  • Naive or Parametric End Users: Revolved around constantly querying and updating the database
  • Sophisticated End Users: Familiarize and implement their applications to meet their complex requirements
  • Stand-alone End users: Maintain personal databases by using ready-made program packages