A well-structured database underpins all professional work in applied statistics, providing the foundation for storing, organizing, and analyzing data efficiently. In Kenyan workplaces such as county hospitals, SACCOs, and research institutions, the ability to create and manage reliable tables is crucial for evidence-based decision-making. This chapter explores the practical steps, considerations, and standards for table creation, equipping applied statistics professionals to design robust databases that support data integrity and effective analysis.

3.1 Creation of Tables

The creation of tables is a core task in database management, especially for applied statistics professionals who handle diverse datasets from surveys, experiments, and administrative records. Proper table design ensures that data is organized logically, supports accurate analysis, and adheres to best practices for data quality and security. In Kenya, for example, a county health department might use database tables to track patient admissions, treatments, and outcomes, enabling rapid reporting and policy interventions.

3.1.1 Identifying Entities and Attributes

Before creating tables in a database, it is essential to identify the entities you wish to represent and the attributes that describe them. Entities are the objects or concepts about which you want to store data, while attributes are the specific pieces of information you collect about each entity. Applied statistics professionals often manage entities such as patients, survey respondents, products, or transactions.

Steps to Identify Entities and Attributes

1. List all types of information required: Begin by outlining all the data you need for your analysis, such as client details, transaction dates, or laboratory results.
2. Group information into logical categories: Organize the data into entity types like "Student," "Farm," or "Loan Application," each representing a table.
3. Define attributes for each entity: For every entity, specify the attributes, such as "Name," "Date of Birth," "Amount," or "Location."
4. Check for redundancy: Ensure that each attribute is listed only once and is relevant to its entity to avoid duplication.
5. Determine relationships among entities: Identify how entities relate, such as a "Student" enrolled in a "Course," which may require a linking table.

Importance of Proper Entity and Attribute Identification

• Prevents data duplication: Careful planning ensures that each piece of information is stored only once, reducing errors and inconsistencies.
• Facilitates efficient queries: Well-defined entities and attributes make it easier to retrieve and analyze data, as seen in a hotel chain tracking guest stays and payments.
• Supports normalization: Accurate identification is the foundation for normalization, which improves database reliability and flexibility.
• Enables data integration: When attributes are clearly defined, combining data from different sources, such as merging datasets from multiple clinics, becomes simpler.
• Improves reporting: Structured entities and attributes allow for straightforward generation of reports, vital for organizations like the Kenya National Bureau of Statistics.

3.1.2 Defining Data Types for Table Attributes

Each attribute in a database table must be assigned a data type, which determines the kind of data it can store and how it is processed. The choice of data type impacts storage efficiency, data validation, and retrieval speed, which are all critical for statistical analysis.

Common Data Types in Applied Statistics Databases

• CHAR: Stores fixed-length character strings, suitable for codes of a known length such as "County Code" or "Gender" (e.g., CHAR(1) for M/F).
• VARCHAR: Stores variable-length character strings, ideal for names or descriptions where length varies, such as "Farmer Name" or "Product Description."
• TEXT: Used for large amounts of text, such as qualitative comments in a survey or interview transcripts.
• INT: Stores whole numbers, positive or negative, suitable for counts, IDs, or age fields.
• FLOAT/DOUBLE: Accommodates floating-point numbers, essential for measurements, scores, or monetary values.
• DATE/TIME: Stores dates and times, used for recording events like "Date of Admission" or "Transaction Time."
• BOOLEAN: Represents true/false values, such as "Is_Active" or "Has_Paid."

Criteria for Selecting Appropriate Data Types

• Nature of the attribute: Choose data types that match the kind of information (e.g., INT for age, VARCHAR for address).
• Expected data range: Consider the maximum and minimum values to avoid overflow or wasted space.
• Frequency of updates: Attributes updated frequently, such as "Stock Level," benefit from efficient data types.
• Analysis requirements: Numeric types facilitate calculations, while text types are suitable for qualitative analysis.
• Storage efficiency: Select data types that minimize storage without sacrificing accuracy, which is crucial in large datasets used by research institutions.

Impact of Data Type Selection

• Data integrity: Proper data types prevent entry of invalid data, such as letters in a numeric field.
• Performance: Efficient data types improve query performance, supporting rapid analysis for time-sensitive reports.
• Compatibility: Standard data types facilitate data sharing and integration across different systems, such as between a county office and a national database.
• Data validation: Choosing restrictive data types helps catch errors at the point of entry, improving overall data quality.

3.1.3 Table Creation Procedures and Best Practices

Creating tables in a database involves more than simply naming columns; it requires careful planning to ensure the structure supports current and future needs. The process typically uses Structured Query Language (SQL) statements or database management system interfaces.

Steps to Create a Table

1. Define the table name: Choose a clear, descriptive name, like "Survey_Responses" or "Lab_Results."
2. List all attributes (columns): Specify every field to be included, such as "Respondent_ID," "Age," "Score," "Comments."
3. Assign data types to each attribute: Use appropriate data types for each column, as discussed above.
4. Identify the primary key: Select a unique identifier for each record, such as "Patient_ID" or "Invoice_Number."
5. Set constraints: Define rules for the data, including NOT NULL (mandatory fields), UNIQUE (no duplicates), and DEFAULT values.
6. Write the CREATE TABLE statement: Use SQL or the database interface to create the table with all specifications.
7. Review and test the structure: Insert sample data to check for errors and ensure all constraints work as intended.

Example: Creating a Table for a SACCO Loan Database

Suppose a SACCO wants to manage loan applications. The table might be created as follows:

SQL Statement:

CREATE TABLE Loan_Applications (
    Application_ID INT PRIMARY KEY,
    Member_ID INT NOT NULL,
    Loan_Amount FLOAT NOT NULL,
    Application_Date DATE,
    Status VARCHAR(20)
);

This structure ensures that each loan application is uniquely identified, all key information is captured, and data types match the expected data.

Best Practices in Table Creation

• Use meaningful names: Table and column names should be self-explanatory to facilitate teamwork and ongoing maintenance.
• Avoid reserved words: Do not use database reserved keywords as table or column names to prevent errors.
• Normalize data: Apply normalization rules to avoid redundancy and improve flexibility.
• Document the schema: Maintain clear documentation of each table’s purpose, columns, and relationships.
• Test with sample data: Before deploying, use realistic data to ensure the table meets practical needs.

3.1.4 Normalization in Table Design

Normalization is a systematic process for organizing data in a database to reduce redundancy and improve data integrity. For applied statistics professionals, normalization ensures that datasets are flexible, reliable, and easier to analyze, especially when dealing with large-scale surveys or administrative records.

Forms of Normalization

• First Normal Form (1NF): Each table cell must contain a single value, and each record must be unique. For example, a "Survey_Responses" table should not have multiple answers in one cell.
• Second Normal Form (2NF): All non-key attributes must depend on the entire primary key. In a "Course_Enrollments" table, "Course_Name" should depend on "Course_ID," not just on "Student_ID."
• Third Normal Form (3NF): Non-key attributes should depend only on the primary key, not on other non-key attributes. In a "Patient_Records" table, "Ward_Name" should not depend on "Ward_ID" if both are present.

Benefits of Normalization

• Eliminates redundancy: Data is stored only once, saving space and reducing inconsistencies.
• Improves data integrity: Updates are made in one place, ensuring all records remain accurate.
• Facilitates flexible queries: Data can be combined and analyzed in various ways without structural changes.
• Supports scalability: As data grows, normalized structures handle expansion smoothly, as seen in university student databases.
• Reduces update anomalies: Changes do not cause unintended errors elsewhere in the database.

Typical Mistakes:

• Over-normalization: Creating too many tables can make queries complex and slow, especially for frequent summary reports.
• Under-normalization: Failing to separate data leads to duplication and inconsistency, making error correction difficult.
• Ignoring relationships: Not defining foreign keys or links between tables can cause data isolation, hindering analysis.
• Weak primary keys: Using non-unique fields as primary keys can result in duplicate records.
• Neglecting documentation: Without clear records of normalization decisions, future maintenance becomes challenging.

Chapter Summary

This chapter explored the essential processes involved in creating and manipulating a database, beginning with the design and creation of tables to organize data efficiently. It examined the roles of primary and secondary keys, highlighting their importance in ensuring uniqueness and facilitating data retrieval within database systems. The discussion then moved to linking tables, demonstrating how relationships between datasets are established to maintain data consistency and support complex queries. The concept of data variables was addressed, explaining their significance in defining, storing, and managing different types of information within the database. Database integration was covered to show how various systems and applications can interact seamlessly, promoting data sharing and operational efficiency. Finally, the chapter provided an introduction to database querying using SQL, equipping students with foundational skills to extract, update, and manage data through structured commands. By mastering these topics, learners are prepared to build robust databases and perform essential data operations confidently.