Big Data and AI

  Chapter 12: Keys and Indexes Designing a robust schema goes beyond defining tables and columns. The right keys and indexes enforce data integrity, prevent anomalies, and turbocharge query performance. In this chapter, you’ll learn how to: Distinguish and implement primary keys and surrogate keys Configure foreign keys to uphold referential integrity Create the most effective indexes for your queries Balance speed gains against storage and maintenance costs By the end, you’ll know how to architect schemas that both safeguard your data and serve queries at lightning speed. 1. Primary Keys and Surrogate Keys A table’s primary key uniquely identifies each row. Choosing the right key type sets the foundation for joins, lookups, and integrity. 1.1 Natural vs. Surrogate Keys Natural Key An existing attribute or set of attributes that uniquely describes the entity (e.g., a country’s ISO code, a user’s email). • Pros: No extra column needed; human-readable. • Cons: Subject to business-...

  Part V: Designing Your Schema Chapter 11: Creating and Altering Tables Building a robust database schema starts with thoughtfully created tables and evolves with carefully applied modifications. In this chapter, you’ll learn how to: Define tables using CREATE TABLE Choose appropriate data types and sizes for each column Enforce data-quality rules through constraints ( NOT NULL , UNIQUE , CHECK ) Evolve schemas safely with ALTER TABLE operations By following these best practices, you’ll lay the foundation for reliable, scalable databases that adapt to changing business needs. 1. Defining Tables with CREATE TABLE 1.1 Core Syntax The CREATE TABLE statement introduces a new table and its columns to the database. Always specify columns explicitly along with their data types and constraints: sql CREATE TABLE table_name ( column1_name data_type [constraints], column2_name data_type [constraints], …, columnN_name data_type [constraints] ); table_name : your chosen table identif...

  Designing a robust database schema is crucial for data integrity, performance, and long‐term maintainability. In this module, we’ll cover: Creating and altering tables Defining keys and leveraging indexes By the end, you’ll know how to structure your data model to support reliable applications and fast queries. Creating and Altering Tables A well‐designed table lays the foundation of your database. Start by defining clear columns, appropriate data types, and necessary constraints. 1. CREATE TABLE Syntax sql CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, hire_date DATE NOT NULL, email VARCHAR(100) UNIQUE, salary NUMERIC(10, 2) CHECK (salary > 0) ); Key takeaways: Use SERIAL or IDENTITY for auto-incrementing primary keys. Choose string lengths ( VARCHAR ) based on real data. Apply NOT NULL to mandatory columns. Enforce business rules with CHECK constraints. 2. A...