Relations Database Management System
Introduction
Databases are critical in full-stack application development. They manage structured information, support multiple users, and ensure data consistency and security. This unit introduces relational databases, and fundamental principles of database design.
Relational Databases (RDBMS)
What is a Database?
- A structured collection of data.
- Used to track and store information about entities (e.g., students, recipes, users).
Relational Model
- Data is stored in tables (relations).
- Tables have rows (records) and columns (attributes).
Properties of a relation
- A relation is a two-dimensional table that has the following characteristics:
- Rows contain data about an entity.
- Columns contain data about attributes of the entity.
- All entries in a column are of the same kind.
- Each column has a unique name.
- One cell of the table holds a single value.
- The order of the columns is unimportant.
- The order of the rows is unimportant.
- No two rows may be identical.
- Every row has a column that uniquely identifies the row
Example Entities
| Entity | Attributes |
|---|---|
| Recipes | RecipeName, RecipeText, Image, Duration, AuthorId |
| Users | UserName, Email, Password, isAdmin |
| Ingredients | IngredientName, Price |
ACID Properties of Transactions
Atomicity – All or nothing operations. Consistency – Database remains valid before and after a transaction. Isolation – Transactions occur independently. Durability – Once committed, changes are permanent.
Refer to lecture slides for examples
Keys in Databases
| Type of Key | Purpose |
|---|---|
| Primary Key | Uniquely identifies each row. Must be unique and non-null. |
| Composite Key | A primary key made up of multiple columns. |
| Surrogate Key | Artificial, auto-generated unique ID (e.g., auto-increment). |
| Foreign Key | Links one table to another, maintaining referential integrity. |
Database Normalization
Normalization ensures minimal redundancy and avoids update anomalies.
Steps:
- First Normal Form (1NF): No multi-valued attributes.
- Second Normal Form (2NF): All attributes fully depend on the primary key.
- Third Normal Form (3NF): No transitive dependencies between non-key attributes.
Memory Trick: "Data depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help me Codd."
Refer to lecture slides for examples
Data Modeling
- Entity: A distinct object (e.g., User, Recipe).
- Attributes: Characteristics (e.g., Username, RecipeName).
- Relationships: How entities are connected (e.g., A user writes recipes).
- Entity-Relationship Diagrams (ERD): Visual representations with cardinality and optionality.
Reflection Questions
- What are the ACID properties?
- What is the difference between a primary key and a foreign key?
- What is the purpose of normalization?
Summary
- Relational databases store structured information in tables.
- ACID properties ensure safe and reliable transactions.
- Normalization organizes data efficiently.
- Mastery of relational database concepts is crucial for backend development.
Disclaimer: Generative AI was used in part to generate these lecture notes.