[[SQL Commands and Clauses]] [[Data Normalization]]

Relational Model Constructs

• Entity: A representation of things in an environment. Databases capture information about entities
  • Represents a class of things
  • Entities are modeled in a Database
  • ex1: People, Organizations, Objects, Concepts etc
  • ex2: Bank accounts. A bank account has an account type (checking/savings) as well as its UID and a holder name
  • Entity Instance: person, place object, even, concept. Each instance of an entity holds its own attributes
    • Instances are Recorded in a Database
    • Instances are the actual thing being looked at
  • Every Entity Instance must have an Identifier, they usually already have one
• Attribute: A discrete data element that describes an entity
  • A property or characteristic of an entity instance. Usually corresponds to a field or table column
  • Describes an instance of an Entity.
• Relationships: Correlation between entities
  • Relationship instance: the actual relationship between entity instances

Relationships

• A relationship is modeled with lines going between two entity types
• A relationship instance is between specific entity instances
• Relationships can hold their own attributes
  • These are simply just features that describe the relationship between the entities. (this is a JOIN statement criteria btw)
• Two entities can have multiple relationships between them
  • eg: The president of a student org is still a student but is also the President

Relationship Cardinatily

• One-to-One
  • Each instance in the relationship have exactly one related instance
• One-to-many
  • One instance has many different related instances.
• Many-to-Many
  • Instances on both sides can have many related instances
• Cardinality represents the relationship in both directions

One-to-Many (1:m) Relationships

• Most common in RDBMS design
• One instance of an entity is related to as many or as little instances of another entity as required.
• Can be very simple or very complex with many different relationship connections
• Often will see “Crows feet”: This just refers to a visual representation of tables where the “crows feet” symbolize a connection in a MANY relationship.
• These are usually done by splitting 2 entities (tables) and matching primary keys. ie, a field in each table that are the same

Foreign Keys

• A foreign key is simply put, a single column that references the primary key of another table
• The value of the field in the many tab;e determines which record is being references in the new single table

SQL JOIN

• We use the JOIN clause to put 2 or more tables into one
• We match the Primary key and the Foreign key; PK=FK

Many-to-Many Relationships (m:m)

• Relational databases cannot have m:m relationships
• We must use Associative Entities, a new entity that mediates m:m relationships
• A m:m is just 2 or more 1:m relationships
  • One CourseSection has many students but one Student enrolls in many CourseSections
• The Associative entity MUST have foreign keys from both tables in the m:m relationship