📖 MySQL Key Concepts
Introduction to Databases
A database is a structured repository designed to store, manage, and retrieve data efficiently. It organizes information into tables, providing a systematic way to store and access data, allowing for both retrieval and long-term storage.
Database Client/Server System
The database server stores the files and data and provides services to the users of the database. The database server may be a standalone machine or may be on the same computer as the web server or even the client. The clients are the computers, tablets, etc of the users that interact with the data on the server. When you install and run your MySQL server software on your machine at home, and then use the Workbench to interact with this installation, one machine is functioning as both the client and the server.
Database Structure
- Tables
- Data is organized into tables. Each table represents a specific entity (e.g., students, courses) and is composed of rows and columns. Each table is a collection of data about a specific type of entity.
- Rows
- Each row in a table represents a unique record or item, identified by a primary key. Rows are individual records within a table.
- Columns
- Each column represents a specific attribute or field of the entity. All rows in a column contain data of the same type. Columns define the attributes of the entity, and rows represent the data records.
Relational Database
In a Relational Database Management System (RDBMS), tables are interconnected through relationships:
- Primary Key
- A unique identifier for each record in a table. For example,
StudentIDin theStudenttable. Primary keys ensure each record is uniquely identifiable. - Foreign Key
- A field in one table that links to the primary key in another table, establishing a relationship between the tables. Foreign keys create connections between tables to enable complex queries and maintain data integrity.
Example
This is a simple example of a database with 3 related tables.
| StudentID (primary key) | LastName | FirstName | Major | GPA | GradDate |
|---|---|---|---|---|---|
| 0000001 | Blow | Joe | CIT | 3.8 | 2014 |
| 0000002 | Doe | Jane | COSC | 3.2 | 2015 |
| 0000003 | Johnson | James | CIT | 2.75 | 2016 |
| 0000004 | Smith | Sally | Undecided | 3.5 | 2015 |
| 0000005 | Taylor | Tom | CIT | 2.9 | 2013 |
| CourseID (primary key) | CourseNumber | CourseName | Semester | Schedule | Instructor |
|---|---|---|---|---|---|
| 00001 | IMED 1416 | Web Design I | Spring 2014 | MW 10:30AM | Burks |
| 00002 | ITSE 1406 | Intro to PHP | Spring 2014 | TR 10:30AM | Burks |
| 00003 | ITSE 1303 | Intro to MySQL | Spring 2014 | TR 12:00PM | Lackey |
| RosterID (primary key) | CourseID (foreign key) | StudentID (foreign key) |
|---|---|---|
| 01 | 00001 | 0000001 |
| 02 | 00001 | 0000003 |
| 03 | 00001 | 0000004 |
| 04 | 00002 | 0000002 |
| 05 | 00002 | 0000003 |
| 06 | 00002 | 0000005 |
Explaining the 3 Tables
A student table, a courses table and a roster table. The student table contains information about students and has the StudentID as the primary key or unique identifier for each student. The course table contains information about courses and has the CourseID as the primary key. The roster table identifies which students are enrolled in which course. This is a linking (relational) table that has a primary key of RosterID which uniquely identifies the individual record. Then it contains the 2 primary keys from the other 2 tables serving as foreign keys in the roster table to link them together. So RosterID 01 shows that student 0000001 is enrolled in course 00001.
Key Concepts
- Database
- A structure that contains categories of information and relationships between these categories.
- Database Management System
- Software program used to create a database and add, update, delete, view and sort data in the database.
- Table
- A structure within a database that contains rows (sometimes referred to as records) and columns (sometimes referred to as fields). The book uses the terms rows and columns and so does MySQL. Tables describe an entity, such as Vendor in an accounts payable system, or Film in an internet streaming system.
- Columns
- The attributes of an entity. vendorName, VendorID, filmTitle, filmReleaseYear.
- Rows
- The collection of attributes that form one instance of an entity.
- Values
- The data stored in individual cells (row and column intersection) that pertain to the specific entity and attribute.
- Primary key
- One or more columns that uniquely define each row of a table. vendorID for example
- Unique key
- A column not defined as a primary key that uniquely defines a row in a table. perhaps vendorName
- Index
- An efficient way to search a database, (think of book index). primary and unique keys automatically create an index. Additional indices can be created over columns that are queried frequently such as zip codes.
- Foreign key
- One or more columns in one table that comprise the primary key of another table.
- Relationship
- The association between entities. For example, a student may be related to a course; an invoice may be related to a customer; an artist may be related to an album, etc. Relationships can be:
- One-to-many
- One row in one table is related to multiple rows in another table.
- Example: a department may have many courses, but each course only has one department.
- One-to-one
- One row in one table is related to one row in another table.
- Example: a course only has one description and each course description applies to only one course.
- In many cases, if there is a one-to-one relationship, the tables could be combined into a single table.
- Many-to-many
- Multiple rows in one table may relate to multiple rows in another table.
- Example: a student may take multiple courses and a course may have multiple students.
- These are usually implemented using a linking table that has a one-to-many relationship with each of the two tables in the many-to-many relationship. In our example, we might have a class Roster table that lists the course and student which would make up the composite primary key for that table. It would have a one-to-many relationship with the student table (each student Could have multiple class roster records) and the course table (each course would have multiple class roster records).
- Column definitions
- In addition to the name of the column, columns are defined by data types, whether the column accepts NULL values (i.e. not required), a default value if defined, and whether the column is a key.
- Auto increment column
- A numeric column whose value is generated when a row is added.
- Entity-relationship diagram
- A visual depiction of the tables and columns of a database, and a visual description of how the tables are related.
- MySQL
- Open source database management software.
- SQL - Structured Query Language
- Language for retrieving and manipulating database data.
Basic SQL Statements
- SELECT
- INSERT
- UPDATE
- DELETE
- DDL - Data Definition Language
- Language for manipulating database structure.
Basic SQL/DDL Statements
- CREATE DATABASE
- CREATE TABLE
- CREATE INDEX
- ALTER TABLE
- ALTER INDEX
- DROP DATABASE
- DROP TABLE
- DROP INDEX
- Clause
- A portion of a statement. Think of the statement as a sentence and the clause as one of the verbs within the sentence.