D426 - Notes Based of Caleb Curry Database Design YT Video

Anamoly

A situation that can lead to unexpected results in data

Primary Key

A unique identifier for each record in a database table

View Mechanism

A way of presenting customized data to users without directly exposing the underlying tables

Foreign Key

An attribute in a table that refers to the primary key in another table

Things you store

Attribute

Conceptual schema

Big-picture view of what the system will contain, how it will be organized, and which business rules are involved

Logical schema

Clearly define schema objects such as table and field names, entity relationships, and integrity constraints

Entity Integrity

Condition in which each row in the table has its own unique, non-null identity

Which subset of SQL is used to define and manage the structure of a database

DDL

Which subset of SQL is used to manipulate data stored in a database

DML

Domain

Defines constraints on data type, format, and range of values

Referential Integrity

Ensures the accuracy and consistency of relationships between tables

Domain Integrity

Ensures the validity and correctness of the data within a specific domain or field in a database

Anything you store data about

Entity

This principle is primarily enforced through the use of primary keys, which are special attributes assigned to each record in a table

Entity Integrity

What does SQL do?

It defines and manipulates a database

Physical schema

Provides technical and contextual information

A property that guarantees that foreign keys in one table correctly reference primary keys in another table, thus maintaining valid links between related data.

Referential Integrity

Connection between data

Relation

Database Management System

Software that provides a systematic way to create, retrieve, update, and manage data in databases

Data Integrity

The accuracy, consistency, and reliability of data stored in a database

Frontend

What a user sees

Backend

What goes on behind the scenes

Common DDL commands

- CREATE - ALTER - DROP - TRUNCATE - RENAME

Examples of Attributes

- Customer ID - Total amount - Quantity

Examples of Entities

- Employee - Customer - Product

Examples of RDMS

- MySQL - Microsoft SQL Server - Oracle Database

Common DML commands

- UPDATE - DELETE - INSERT - SELECT