DP-900 Part 5

Hey!

We are going to explore Relational Structure

• Relational Data
• Normalization : Minimize data duplication and enforce data integrity. Rules for normalization:
• Separate each entity into its own table
• Separate each discrete attribute to its own column
• Uniquely identify each row using primary key
• Use Foreign key column to link related entities

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 popular dialects of SQL

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.

Database Objects

1. Views

Virtual table based on result of SELECT query.

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.Customer = c.ID;

1. Stored Procedure

Defines SQL statements that can run on command.
Stores program logic when working with data.

CREATE PROCEDURE RenameProduct
    @ProductID INT,
    @NewName VARCHAR(20)
AS
UPDATE Product
SET Name = @NewName
WHERE ID = @ProductID;

1. Index

It helps to search data in a table.
Index has a copy of column in sorted order, with pointer corresponding row in a table.
Quickly scan the data.
We can create many index on a table.
Index consume storage space.

Thanks for reading <3