Databases and DBMS

• Databases are collections of data organized for access, retrieval, modification, and usage.
• An online store database exemplifies this, storing customer details, products, and prices as XML or JSON documents.
• Database design and management are crucial for IT and cybersecurity, as well-structured databases ensure efficient data storage, retrieval, and security.
• Understanding ERDs, SQL and data relationships is essential for building scalable systems, preventing data breaches and supporting informed decision making in industry.
• Databases and database management systems (DBMS) are closely related, and the term 'database' often refers to both.

Database Management Systems (DBMS)

• A DBMS is software that manages databases, serving as an interface between users and the database.
• It allows users to create, read, update, and delete data.
• Core functionalities include data storage, retrieval, and updates, along with transaction support, concurrency, and recovery mechanisms.
• DBMS also handle access authorization, remote support, and constraint enforcement for data integrity.

Main Database Types

• Relational Databases (RDB): Data stored in interrelated tables (e.g., Microsoft SQL Server, Oracle, MySQL).
• Object-Oriented Databases (OODB): Allows definition of objects that can be referenced later as a unit; used in applications requiring high performance and faster results (e.g., ObjectBox).
• Network Database: First-generation database model linking multiple member records to multiple owner files (e.g., IMAGE).
• Non-Relational Databases (NoSQL): Handles unstructured data, ideal for big data applications (e.g., Apache Cassandra).

Relational Databases

• Relational databases organize data into tables with rows (records/tuples) and columns (fields/attributes).
• Data is structured and related, enabling efficient querying and manipulation using SQL.
• SQLite is used in the topic, it is a small, simplified relational database that doesn't require a dedicated server and is popular in mobile apps.

Database Design Steps

• Four steps for effective database design:
• Model the problem into an entity–relationship diagram (ERD).
• Convert the ERD into a set of tables.
• Create the tables in the database using SQL and import any data.
• Create queries to extract information.

Entity-Relationship Diagrams (ERDs)

• ERDs model problems visually using symbols and connectors.
• They define major entities and their interrelationships within the system.
• Entities include business objects like people (e.g., student), tangible items (e.g., product), and intangible items (e.g., log).
• Entities are represented as rectangles, and relationships as diamonds with connecting lines, indicating one-to-many (1:M) relationships.

ERD Entities

• Entities relate to each other.
• Relation is expressed as a verb (action word).
• For an online shop:
• Customer: the person who is buying the products.
• Product: the thing to buy.
• Order: a collection of things the customer is buying.
• Brand: the company that made the product.

Adding Attributes to Entities

• Attributes provide information about each entity and are drawn as ovals, with names that should not contain spaces or symbols.
• Unique attributes serve as primary keys, identified with an underline.
• Primary keys are enforced by the database to prevent duplicate records.

Primary Keys

• Unique data identifiers are presented as a data type with an underline.
• The database enforces the primary key.
• It will refuse to add a record if it violates the primary key.
• It is often best to make up a number to use as a primary key, known as a surrogate key,
• When designing a database, primary keys should not have duplicates or undergo changes.
• Types:
• Database-generated sequence number: The database creates IDs for you. As a result, the database can perform performance enhancements.
• Large random numbers: Also known as universally unique identifiers (UUIDs).

Adding Attributes to Relationships

• Relationships can also have attributes, such as including a quantity attribute in the relationship between order and product for an online shop.
• Relationships can also have attributes, including a quantity attribute in the relationship between order and product, to reduce redundancies and add extra efficiencies.

Converting ERDs to Tables

• Involves turning entities into tables and their attributes into columns.
• Table definitions are written as TABLENAME (attribute1, attribute2, attribute...), with the primary key underlined.
• Singular names are conventionally used for table names (e.g., 'customer' instead of 'customers').

Foreign Keys

• Foreign keys link tables by representing relationships, such as one-to-many, many-to-many, and one-to-one.
• In one-to-many relations, the M entity receives a foreign key, which is an attribute containing the primary key of the other entity.
• Foreign keys do not appear on ERDs.

Many-to-Many Relations

• Require the creation of a junction table containing the primary keys of both tables involved.
• A primary key with multiple columns is known as a composite primary key, which must be unique for each record.

One-to-One Relations

• Implemented for access control with confidential attributes or table inheritance for adding more attributes to a table.
• Examples:
• Confidential data placed in separate table
• PERSON (PersonID, FirstName, LastName, DOB, Address)
• STUDENT (PersonID, StudentNo, Degree, ATAR)
• STAFF (PersonID, StaffNo, Office, Salary).

Creating Tables Using SQL

• Tables are created using Structured Query Language (SQL).
• Most relational databases support basic SQL operations, though some may have extra features.
• SQL is a popular language in database design as it is standard.
• However, some database software may have extra features that others do not. And there may be slight differences in the commands available.

CREATE TABLE Syntax

• Defines the structure of a new table, including column names, data types, constraints, and indexes.
• Example:

CREATE TABLE Customer (
  CustomerID INTEGER ,
  FirstName TEXT ,
  LastName TEXT ,
  Address TEXT ,
  Phone TEXT , PRIMARY KEY (CustomerID)
);

• Ensures that the data type for foreign keys matches the corresponding primary key's data type.

DROP TABLE

• The command for deleting a table is: DROP TABLE MyCoolTable;

INSERT INTO Syntax

• Used to insert data into a table.
• Example:

INSERT INTO Customer VALUES ( 1 , 'Fred' , 'Banks' , '3134 Hudson Lane' , '405195595' ); INSERT INTO Customer VALUES ( 2 , 'Nicholas' , 'Ramos' , '9 Washington Center' , '946151556' ); INSERT INTO Customer VALUES ( 3 , 'Anne' , 'Hansen' , '8604 Burrows Pass' , '129895116' ); INSERT INTO Customer VALUES ( 4 , 'Douglas' , 'Mendoza' , '9 Monument Avenue' , '898719584' ); INSERT INTO Customer VALUES ( 5 , 'Bonnie' , 'Lawson' , '9 Oneill Avenue' , '887514739' ); INSERT INTO Customer VALUES ( 6 , 'Elizabeth' , 'Ryan' , '1289 Del Mar Street' , '887514733' );

Extracting Data with SQL

• SQL commands: Allows you to set up and fill your database.
• Critical for interacting with data stored in our database.

SELECT Command

• Is the primary command in SQL that allows you to retrieve data from the database.
• Retrieves data from the database.
• SELECT * FROM TableName; is used to select all data from a table.
• Specific columns can be selected by listing their names after the SELECT command.

WHERE Keyword

• Filters rows based on specified expressions.
• SELECT * FROM Customer WHERE LastName = 'Wood'; only shows the row if it is true.
• WHERE always comes after the FROM keyword

LIKE Operator

• Matches text based on patterns, using wildcards like % (zero or more characters) and _ (exactly one character).
• The LIKE operator is used to check if text matches a particular pattern.
• In SQLite, the LIKE operator is not case-sensitive, but some databases are.

SELECT * FROM Customer WHERE LastName LIKE 'W%';

IN Operator

• Checks if a value is in a list of possible values.
• Example:

SELECT * FROM Customer
WHERE CustomerID IN (2,3,4);

ORDER BY

• Sorts results in a specific order, either ascending (ASC) or descending (DESC).
• For this, the ORDER BY keyword can be used.
• ORDER BY has two extra keywords:
• ASC — ascending order (smallest to largest; this is a default one)
• DESC — descending order (largest to smallest).

SELECT * FROM Customer
ORDER BY LastName ASC;

AS (Alias)

• Renames a column in the result set.
• Example:

SELECT FirstName AS FN, LastName AS LN
FROM Customer;

LIMIT and OFFSET

• Restrict the number of rows returned.
• LIMIT determines the number of rows the query returns in this syntax.
• OFFSET clause skips the offset rows before returning data from the rows.
• These two keywords appear at the end of the SQL statement and are often used with ORDER BY.

Aggregate Data

• Functions that operate on multiple rows to combine them into a single row.
• COUNT() — count all the values
• SUM() — add all the values together and return the sum
• AVG() — compute the average
• MIN() — get the lowest value
• MAX() — get the highest value.

COUNT()

• Returns the number of rows in the table whether using COUNT(1) or COUNT(*), but COUNT(ProductID) will only count rows where ProductID is not null.
• Always returns a single row (unless GROUP BY is used).

SELECT COUNT (ProductID) FROM Product;

AVG(), MIN(), MAX()

• Compute average, minimum, and maximum values respectively which is shown in the example below:

SELECT AVG(Price), MIN(Price), Max(Price) FROM Product;

GROUP BY Keyword

• Used to determine average price per brand.

SELECT AVG(Price), BrandID
FROM Product 
GROUP BY BrandID;

WHERE Clause

• WHERE clause is processed first
• In this query, rows with a BrandID that is < 3 are removed first, and then the AVG() is applied to the remaining rows.

SELECT ProductID,AVG(Price)
FROM Product
WHERE BrandID < 3 
GROUP BY ProductID;

• Aggregate not useable:
• WHERE AVG(Price) > 14 is invalid because the AVG occurs after the where clause has been processed.

HAVING Clause

• Similar to WHERE, but occurs after aggregates are finished.
• This example only shows brands that have an average price of over $20:

SELECT ProductID,AVG(Price)
FROM Product
WHERE BrandID < 3 
GROUP BY ProductID;

VIEW

• A saved query that works like a regular table, except the rows cannot be edited.
• Useful for large and complex queries.
• Command to make a view

CREATE VIEW MyCoolView AS
SELECT*FROM Mycool table;

Combining Tables

• Uses JOINs to connect and retrieve information from multiple tables.
• JOINs occur after the FROM section and usually have an ON clause to specify connection conditions.

JOIN Types

• INNER JOIN: Most common, returns rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table

ON Clause

• Connects primary and foreign keys during a JOIN operation.
• Note that if you forget to provide the ON clause, the database will return all possible combinations of the rows from each table.

SELECT Product.ProductID, Description, Price, OrderID 
FROM Product 
LEFT JOIN OrderProduct 
ON OrderProduct.ProductID = Product.ProductID;

USING Clause

• Replaces the ON clause and makes the query easier to read, only when the column (filed) in the brackets appears in both tables (Product and OrderProduct).
• Example query:

SELECT Product.ProductID, Description, Price, OrderID 
FROM Product 
LEFT JOIN OrderProduct 
USING (ProductID);