Study Notes on Database Design and SQL

Introduction to Database Entities and Data Modeling

  • Each item can be represented as an entity in a database or data model.

  • Example: Modeling a flower shop, involving various entities.

Key Entities for the Flower Shop

  1. Customers

  2. Flowers

    • Types of flowers (e.g., roses, carnations, lilies)

  3. Employees

  4. Orders

    • Attributes:

      • Order Number

      • Order Date

      • Order Price

  5. Arrangements

    • Example: A dozen roses or a mixed arrangement with filler (e.g., baby's breath)

  6. Occasions

    • Types of events for which flowers are sold

  7. Containers

    • Vases, baskets, or other types of containers

Data Model Characteristics

  • Each entity will likely have unique attributes, referred to as characteristics of the entity.

  • Attributes must have unique names across entities to avoid confusion.

    • For instance:

      • Flower Name vs Customer Name vs Employee Name vs Arrangement Name

  • Standard practice is to prefix or suffix attributes to maintain uniqueness (e.g., Customer ID, Order Date)

Identification of Entities

  • Each entity should have a unique identifier, typically an ID number.

    • Examples:

      • Student ID

      • Flower ID

      • Account Number

  • Usage of dates as unique identifiers is generally discouraged due to their potential for duplication (e.g., multiple orders on the same date).

Attributes for Various Entities

  1. Painting Entity

    • Attributes:

      • Name

      • Artist

      • Medium

      • Size

      • Year Created

      • Style (e.g., Impressionist)

  2. Book Entity

    • Attributes:

      • Author

      • Genre

      • Publication Date

      • Length

      • Publisher

      • Price

Mapping Entities to Relational Schema

  • Transition from an Entity-Relationship Diagram (ERD) to a relational schema involves listing entity attributes as column headers.

  • Primary key attributes are indicated with an asterisk (*) or a star when drawn manually.

  • It’s preferable to list the identifier as the first attribute.

SQL Basics

  • SQL (Structured Query Language):

    • Language used to manage and manipulate relational databases.

  • Basic SQL structure includes:

    • SELECT [columns] FROM [table] WHERE [conditions]

    • ORDER BY [columns] (for sorting results)

Filtering and Sorting in SQL

  • WHERE clause: Filters rows based on specified conditions.

  • ORDER BY clause: Sorts results based on specified columns in ascending or descending order.

Using Aggregate Functions

  • Querying grouped data to perform calculations such as counts or averages, employing GROUP BY and HAVING clauses:

    • GROUP BY: Divides results into groups based on one or more columns.

    • HAVING: Filters groups based on aggregate conditions.

Using Comparison Operators

  • Common SQL comparison operators include:

    • Equal: =

    • Not Equal: != or <>

    • Greater than: >

    • Less than: <

    • Between: (e.g. BETWEEN 2 AND 3 includes endpoint values)

    • IN: Checks if a value matches any value in a set.

Best Practices in SQL

  • Always use the correct order of SQL clauses:

    • SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

  • Use consistent naming conventions and meaningful attribute names for clarity.

  • When using single vs double quotes, ensure uniformity; do not mix.

Conclusion

  • Understanding entities, attributes, and how to translate them into SQL queries is fundamental to database design and operation.

  • Emphasis on correctness in logic alongside syntax in SQL queries.