Database Design Concepts and ER Diagrams

Database Design

• Database design involves analysis, entity-relationship diagrams (ER-diagrams), schema design (DDL), and data manipulation (DML).
• Analysis focuses on identifying entities, attributes, and relationships between them.
• Entities represent objects or concepts (e.g., Student, Course).
• Attributes describe characteristics of entities (e.g., student name, course name).
• Relationships depict connections between entities (e.g., student takes courses).

Entity Relationship Diagrams (ER diagrams)

• ER diagrams visually represent the entities, attributes, and relationships in a database.
• Key attributes: unique identifiers for entities (e.g., student ID). These help identify and distinguish one entity from another
• Composite attributes: attributes that consist of multiple smaller attributes (e.g., an address has city, street, etc.).
• Multi-valued attributes: attributes that can hold multiple values for an entity (e.g., phone numbers for a student).
• Derived attributes: attributes that can be calculated from other attributes (e.g., age from date of birth).

Relationships

• Cardinality ratio defines the number of entities in one entity set that can be related to an entity in another entity set. Cardinality ratios are 1:1, 1:M, or M:N (sometimes written as M:M).
• Participation constraint: total or partial participation of an entity in a relationship indicates whether every entity takes part in the relationship (total) or if some entities can be excluded from a specific relationship (partial). The cardinality ratio and participation constraint work together to dictate how entities map during schema design

Entity Types

• Strong entities exist independently and have a unique primary key.
• Weak entities depend on a strong entity and don't have their own primary key. The primary key of the weak entity includes at least one identifying attribute from the related strong entity.

Database Management Systems (DBMS)

• DBMS is software that manages databases.
• SQL Server is a popular relational database system.
• SQL is a structured query language used to interact with relational databases.
• DDL (Data Definition Language): used to create, modify, and delete databases and tables. Common commands include CREATE, DROP, ALTER.
• DML (Data Manipulation Language): used to insert, retrieve, and update data in tables. Common commands include INSERT, SELECT, UPDATE, DELETE.

Constraints

• Constraints define rules and restrictions on data in a table.
• Primary key: a unique identifier for each record in a table. Each table must have a primary key.
• Foreign key: a field in one table that refers to the primary key of another table, creating a link between the two tables.
• Unique: a constraint that enforces uniqueness of a column's values.
• Not null: a constraint that enforces that a column cannot contain null values.
• Check: a constraint that specifies a condition that values in a column must satisfy.
• Default: a constraint that specifies a default value for a column if no other value is given.

SQL Operations (DML)

• SELECT: retrieve data from tables based on specified criteria (filtering rows and/or columns).
• INSERT: add new data to a table.
• UPDATE: modify existing data in a table.
• DELETE: remove data from a table.

SQL Functions (aggregate functions)

• COUNT: calculates the number of rows in a table.
• AVG: calculates the average of values in a column.
• SUM: calculates the sum of values in a column.
• MIN: finds the smallest value in a column.
• MAX: finds the largest value in a column.
• These aggregate function with the 'group by' clause are used to retrieve information about multiple groups of data rather than just the whole table

SQL Clauses (additional features)

• ORDER BY: sorts the retrieved data based on column values.
• GROUP BY: groups rows that have the same values in specified columns.
• HAVING: filters grouped rows based on conditions.
• TOP/LIMIT specifies how many rows to return from the selected result.
• LIKE: searches for patterns in column values (wildcards, e.g., % for any character sequence, _ for any single character).