Relational Databases

Databases and Database Management Systems (DBMS)

Database Models

Relational: tables & relationships
Hierarchical: tree structures
Object Oriented: objects & properties
Document: JSON like documents
Key-value: key, value pairs
Network: interconnected entities
Graph: data points & relationships

What are Relational Databases (RDBs)?

Relational databases provides a structured way to store, manage, and access large amounts of data. RDBs excel at organizing data into related tables, allowing for efficient retrieval and analysis.

Benefits:

Organization: Keeps data structured and easy to find, like a well-maintained filing system.
Efficiency: Allows quick access and manipulation of data using powerful query languages.
Data Integrity: Maintains data consistency and prevents duplication with built-in data validation rules.
Scalability: Easily accommodates growing data volumes as organizations expand.

id,name,email,course_id,course
1,Jon,jb@mail.com,GEO101,Intro to Geology
1,Jon,jb@mail.com,MAT200,College Math II
2,Alice,abell@u.edu,BIO250,Intermediate Bio
2,Alice,abell@u.edu,GEO101,Intro to Geology
2,Alice,abell@u.edu,MAT200,College Math II
3,Bob,bob@work.com,BIO250,Intermediate Bio
4,Maria,maria@u2.edu,CSE100,Intro to Python

CSV file

Enrollments

student_id	course_id
1	GEO101
1	MAT200
2	BIO250
2	GEO101
2	MAT200
3	BIO250
4	CSE100

Students

id	name	email
1	Jon	jb@mail.com
2	Alice	abell@u.edu
3	Bob	bob@work.com
4	Maria	maria@u2.edu

DB Tables

Courses

id	course
GEO101	Intro to Geology
MAT200	College Math II
BIO250	Intermediate Bio
CSE200	Intro to Python

Conceptual Model

Entity: A real-world object or concept that you want to represent in your database (e.g., customer, product, order).
Attribute: A characteristic or property of an entity (e.g., customer name, product price, order date).
Relationship: An association between two entities (e.g., a customer has orders, a product belongs to a category).

Relational Database Terms and Definitions

Relational Model: A structured data model where data is organized in tables with relationships between them.
Table: A basic unit of data storage in a relational database, consisting of rows and columns.
Row (Entity): A record in a table, representing a single data entity.
Column (Attribute): A vertical field in a table, representing a specific characteristic of the data entity.
Primary Key: A unique identifier for each row in a table, ensuring data integrity.
Foreign Key: A column that references the primary key of another table, establishing relationships between tables.
Schema: The overall structure of a relational database, defining tables, columns, data types, and relationships.

Entity-Relationship Diagram (ERD)

An ERD is a graphical representation of a database schema that shows the entities (tables) within a database, their attributes (columns), and the relationships between them.

What is Structured Query Language (SQL)?

Structured Query Language is the language used to communicate with RDBs. It allows us to perform a wide range of tasks, including:

Data Retrieval: Extract specific information from database tables.
Data Manipulation: Add, edit, or delete data entries within the database.
Data Analysis: Perform complex calculations and aggregations to gain insights from data.

Benefits:

SQL empowers you to extract valuable insights and make informed decisions.
SQL proficiency is highly sought-after in today's data-driven world.
SQL works with a wide range of RDB systems, making it a transferable skill.

What courses did Alice enroll in?


        SELECT course_id from Enrollments WHERE student_id = 2

The SQL query above returns BIO250, GEO101, MAT200