Big Data and AI

  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...

Final Project – Part 5: Final Review and Export Congratulations — you have reached the final phase of the Excel Basic Course Final Project. In this part, you will refine your dashboard, check for errors, ensure professional quality, and export your work in a clean, shareable format. A dashboard is only complete when it is: Accurate Clear Consistent Easy to read Ready for presentation This final review ensures your work meets professional standards used in business reporting, corporate training, and international certifications. 1. Review the Dataset Start by revisiting your original dataset to ensure everything is clean and consistent. Checklist: No blank rows or columns No inconsistent capitalization No duplicated records No formatting inconsistencies No errors in numeric or date fields Use the following tools if needed: 2.5 Basic Data Cleaning 4.5 Removing Duplicates 6.3 Find and Replace / Go To S...

Final Project – Part 4: Building the Dashboard Layout In this fourth part of the Final Project, you will assemble all the elements created so far — dataset, calculations, KPIs, and charts — into a clean, professional, and visually balanced Sales Dashboard . This is the phase where your work becomes a real analytical tool, suitable for presentations, reporting, and decision-making. A well-designed dashboard is not just a collection of charts. It is a structured, intentional layout that communicates insights clearly and instantly. 1. Create a New Sheet for the Dashboard Start by creating a new worksheet named Dashboard . This sheet will contain only the final visual output — no raw data, no formulas, no helper tables. Review layout best practices in Lesson 6.5 – Best Practices for Clean Spreadsheets . 2. Set Up the Dashboard Grid A clean grid helps you align elements perfectly. Follow these steps: Increase column width to...

Final Project – Part 3: Creating Visualizations In this third part of the Final Project, you will transform your calculations and summary tables into clear, professional, and visually effective charts. These visualizations will form the core of your Sales Dashboard and will help communicate trends, comparisons, and key performance indicators at a glance. You will create three essential chart types used in business reporting: Column Chart – for comparing categories or regions Line Chart – for showing monthly trends Pie Chart – for showing category distribution Each chart will be built using the summary tables created in Part 2 – Calculations Layer . 1. Prepare Your Data for Charting Before creating charts, make sure your summary tables are clean, complete, and properly formatted. Review the following lessons if needed: 4.1 Creating Excel Tables 4.4 Conditional Formatting 6.5 Best Practices for Clean Spreadsheets En...