Database Design and Primary Keys

Database Design

• Database design involves analysis, entity-relationship diagrams (ER-diagrams), schemas, DDL (Data Definition Language), and DML (Data Manipulation Language).
• Analysis: The initial phase of database design, examining data requirements and relationships.
• Entity-Relationship Diagrams (ER-diagrams): Visual representations of entities (objects) and relationships between them. Attributes describe the properties of entities.
• Schema: Structured representation of the database design.
• DDL (Data Definition Language): Used to define the structure and schema of a database (e.g., create, drop, alter tables).
• DML (Data Manipulation Language): Used to manipulate data within a database (e.g., insert, delete, update, and select).

Entity Types

• Entity: A real-world object or concept, like a student, course, or instructor.
• Key Attributes: Unique identifiers for entities (e.g., student ID).
• Attributes: Properties or characteristics that describe an entity, such as name, major, grade.

Relationships

• Relationships: Associations between entities, expressed as 1:1, 1:M, or M:M.
  • 1:1 (one-to-one): One entity is related to exactly one other entity.
  • 1:M (one-to-many): One entity is related to many other entities.
  • M:M (many-to-many): Many entities can be related to many other entities.

Attributes

• Single Attribute: A single piece of data (e.g., student name).
• Composite Attribute: A combination of several single attributes to represent a complex attribute (e.g., full address containing street, city, and zip code).
• Multi-valued Attribute: Represents several values for an attribute (e.g., phone numbers for a person).
• Derived Attribute: Computed from other attributes (e.g., calculating age from birthdate).
• Key Attribute: Unique identifiers for an entity (e.g., a student ID).
• Foreign Key: An attribute in one table that refers to the primary key of another table.

Cardinality Ratio

• Cardinality Ratio: The numeric relationship describing the number of instances in one entity in relation to the other entity. It explains the relationship between multiple tables.

Participation Constraints

• Total participation: Every entity in one set must be related to another entity set.
• Partial participation: Not every entity in one set is related to an entity in another entity set.

Relationship Types

• Binary relationships: Relationships involving two entities.
• Unary/Recursive relationships: Relationships between instances of the same entity set (e.g., an instructor supervising another instructor).
• Ternary relationships: Relationships involving three entities.

Entities

• Strong Entity: An entity that can exist independently. They have a unique attribute (primary key).
• Weak Entity: An entity that cannot exist independently of another entity (or strong entity). They have partial keys (not unique attributes) and depend on other entities for existence.

Converting ERDs to Relational Schemas

• Mapping algorithms are used for converting ERDs to relational schemas. Entity types become tables and relationships between entities become foreign keys in related tables.

SQL and Database Management Systems (DBMS)

• SQL (Structured Query Language): A language used to interact with databases (create, read, update, and delete data).
• DBMS (Database Management System): Software that manages the creation, maintenance, and access of a database.

Table Constraints

• Column data types: The specific type of data that column holds (e.g., integer, string, date, numeric).
• Not Null: Indicates a column cannot have a Null value.
• Unique: Ensures that values entered in a column are unique.
• Primary key: A unique key (often a column) that uniquely identifies each row in a table.
• Foreign key: A key in one table that references the primary key of another table.
• Check: A condition that restricts the values that can be entered in a column.
• Default: A value that is automatically assigned to a column if no other value is entered.
• Constraints: Rules or restrictions to enforce integrity and data validation in a table. Primary key, foreign key and unique are examples of constraints.

DML Commands

• Insert: Adds new data to a table.
• Select: Retrieves data from a table.
• Update: Modifies existing data in a table.
• Delete: Removes data from a table.
• Aggregate Functions: Calculate summary values (e.g., count, average, sum, min, max) for columns in a table.
• Grouping: Group rows with the same values in a column (usually used with aggregate functions).
• Ordering: Sort rows by values in one or more columns.
• Filtering: Filter (using conditions) rows in a table to return only those that meet the conditional criteria.