Explore fundamental relational data concepts
7 minute read
https://docs.microsoft.com/en-us/learn/modules/explore-relational-data-offerings/
The relational model provides a standard way of representing and querying data that can be used by any application. One of the key advantages of the relational database model is its use of tables, which are an intuitive, efficient, and flexible way to store and access structured information.
Understand relational data
- Collections of entities as tables
- An entity can be anything you want to record
- For example, a table of customer details, a table of products, and a table of orders placed
- Each row represents a single instance of an entity
- This is structured data
- Each table has the same columns
- Not all columns require data; NULL
- Each column has a data type which are standardized according to the American Standards Institute (ANSI)
- Character-based
- variable or fixed-length
- Decimal numeric (numbers with decimal point; 3.12)
- Integer numeric (whole numbers ;3)
- Data/time
- Character-based
Understand normalization
https://docs.microsoft.com/en-us/learn/modules/explore-relational-data-offerings/3-normalization
Schema design process that minimizes data duplication and enforces data integrity
Example rules
- Separate each entity into its own table.
- Separate each discrete attribute into its own column.
- Uniquely identify each entity instance (row) using a primary key.
- Use foreign key columns to link related entities.
Instances of each entity are uniquely identified by an ID or other key value, known as a primary key; and when one entity references another (for example, an order has an associated customer), the primary key of the related entity is stored as a foreign key. You can look up the address of the customer (which is stored only once) for each record in the Order table by referencing the corresponding record in the Customer table. Typically, a relational database management system (RDBMS) can enforce referential integrity to ensure that a value entered into a foreign key field has an existing corresponding primary key in the related table – for example, preventing orders for non-existent customers.
In some cases, a key (primary or foreign) can be defined as a composite key based on a unique combination of multiple columns. For example, the LineItem table in the example above uses a unique combination of OrderNo and ItemNo to identify a line item from an individual order.
Explore SQL
https://docs.microsoft.com/en-us/learn/modules/explore-relational-data-offerings/4-query-with-sql
SQL stands for Structured Query Language, and is used to communicate with a relational database. It’s the standard language for relational database management systems. SQL statements are used to perform tasks such as update data in a database, or retrieve data from a database. Some common relational database management systems that use SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle.
You can use SQL statements such as SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP to accomplish almost everything that you need to do with a database.
Extensions provide functionality not covered by the SQL standard, and include areas such as security management and programmability. For example, Microsoft SQL Server, and Azure database services that are based on the SQL Server database engine, use Transact-SQL. This implementation includes proprietary extensions for writing stored procedures and triggers (application code that can be stored in the database), and managing user accounts. PostgreSQL and MySQL also have their own versions of these features.
Some popular dialects of SQL include:
- Transact-SQL (T-SQL). This version of SQL is used by Microsoft SQL Server and Azure SQL services.
- pgSQL. This is the dialect, with extensions implemented in PostgreSQL.
- PL/SQL. This is the dialect used by Oracle. PL/SQL stands for Procedural Language/SQL.
SQL statement types
SQL statements are grouped into three main logical groups:
- Data Definition Language (DDL)
- Data Control Language (DCL)
- Data Manipulation Language (DML)
Data Definition Language (DDL)
- DDL statements to create, modify, and remove tables and other objects in a database
- Most commonly
- CREATE: Create a new object in the database, such as a table or a view
- ALTER: Modify the structure of an object. For instance, altering a table to add a new column
- DROP: Remove an object from the database
- RENAME: Rename an existing object.
! DROP’ing a table deletes all rows in the table
- In the example below we…
- Create a new database table
- Values between () specify the details of each column
- Name
- Data type
- Whether the column must contain a value (NOT NULL)
- Referred to as ‘mandatory columns’
- Without NOT NULL you can create columns with no data
- Whether the data in the column is used to uniquely identify a row (PRIMARY KEY)
- Each table should have a primary key but this is not enforced
- Data types
- INT: integer
- DECIMAL
- VARCHAR: string type
CREATE TABLE Product
(
ID INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Price DECIMAL NULL
);
Data Control Language (DCL)
- DCL statements to manage access to objects in a database by granting, denying, or revoking permissions to specific users or groups.
- Most commonly
- GRANT
- DENY
- REVOKE
In the example below we…
- Grant user1
- Read, insert and modify rights
- To the product table
GRANT SELECT, INSERT, UPDATE
ON Product
TO user1;
Data Manipulation Language (DML)
- DML statements to manipulate the rows in tables
- Most commonly
- SELECT
- INSERT
- UPDATE
- DELETE
- Insert is one row at a time
- Select, update and delete are all rows
- Where is used to specify criteria
In the example below we…
- Select all columns
- From the customer table
- where the city column value is Seattle
SELECT *
FROM Customer
WHERE City = 'Seattle';
In the example below we…
- Select all only from specific columns
- From the customer table
- where the city column value is Seattle
SELECT FirstName, LastName, Address, City
FROM Customer
WHERE City = 'Seattle';
In the example below we…
- Select all only from specific columns
- From the customer table
- where the city column value is Seattle
- Order the results by the LastName column
SELECT FirstName, LastName, Address, City
FROM Customer
WHERE City = 'Seattle'
ORDER BY LastName;
You can also run SELECT statements that retrieve data from multiple tables using a JOIN clause. Joins indicate how the rows in one table are connected with rows in the other to determine what data to return. A typical join condition matches a foreign key from one table and its associated primary key in the other table.
In the example below we…
- Select data from the o. (order table) and the c. (customer table)
SELECT o.OrderNo, o.OrderDate, c.Address, c.City
FROM Order AS o
JOIN Customer AS c
ON o.Customer = c.ID
Describe database objects
https://docs.microsoft.com/en-us/learn/modules/explore-relational-data-offerings/5-database-objects
Relational database can contain other structures that help to optimize data organization, encapsulate programmatic actions, and improve the speed of access. In this unit, you’ll learn about three of these structures in more detail: views, stored procedures, and indexes.
What is a view?
- A virtual table based on the results on a SELECT query
- You can query and filter a view the same as a database table
Create a view
CREATE VIEW Deliveries
AS
SELECT o.OrderNo, o.OrderDate,
c.FirstName, c.LastName, c.Address, c.City
FROM Order AS o JOIN Customer AS c
ON o.CustomerID = c.ID;
Query a view
SELECT OrderNo, OrderDate, LastName, Address
FROM Deliveries
WHERE City = 'Seattle';
What is a stored procedure?
- Stored SQL statement that can be run on command
- Programmatic logic stored in the database
- Supports parameters
Create the stored procedure to change the name of a product based on a specified ID
CREATE PROCEDURE RenameProduct
@ProductID INT,
@NewName VARCHAR(20)
AS
UPDATE Product
SET Name = @NewName
WHERE ID = @ProductID;
Execute the stored procedure
EXEC RenameProduct 201, 'Spanner';
What is an index?
- Helps search for data in a table
- A sorted copy of the data from a column in a table with pointers to the corresponding row
- Used by the WHERE clause
- Saves having to scan the entire table row by row
Create an index named idx_ProductName based on all the names from the product column
CREATE INDEX idx_ProductName
ON Product(Name);