Draw your own entity-relationship diagram: drag tables, connect them with crow's foot relationships, and the studio adds the foreign keys and writes MySQL-ready SQL for you. Then run it in the SQL playground with one click.
dept_id always sits on the many side.A thing we store data about: a student, a product, an order. It becomes a table.
A fact about the entity: name, price, date. It becomes a column.
An attribute whose value never repeats and identifies each row, such as student_id.
A column pointing to another table's primary key; it is how a relationship is stored.
The link between two entities, with its cardinality: one-to-one, one-to-many, many-to-many.
Each end of a relationship carries two symbols: the one at the entity says “at most how many”, the one further out says “at least how many”. The symbol next to an entity counts that entity's rows.
There must be one, and never more than one.
Optional: there may be none, and at most one.
At least one, possibly many.
Optional, and possibly many.
Read the system description and underline the important nouns (student, course, instructor). Most become entities.
For each entity, list the facts you need. Keep each attribute atomic: one value per cell.
Every entity needs a primary key. A numeric id such as student_id is usually simplest.
Verbs point to relationships (enrols, teaches). Ask both ways: how many students per course, how many courses per student?
Turn every many-to-many into a bridge table, and make sure no fact is stored in more than one place.
Read the story, draw the diagram in the studio and press Check. The studio reviews entities, keys and relationships and tells you what is missing.
A clinic has doctors and patients. Each appointment is for one patient with one doctor. A patient can book many appointments and a doctor has many appointments. Store each appointment's date and time in a column named appointment_time.
A student enrols in many courses, and a course has many students. We also need each student's grade in every course they took. Think: where does the grade column belong? (Hint: the grade describes the enrolment itself, not the student or the course.)
Every employee belongs to one department, and a department has many employees. On top of that, each department is managed by exactly one employee, and an employee manages at most one department. So there are two different relationships between the same two tables!
Complete examples to open in the studio, edit, and see their SQL.
Departments, instructors, students, courses and an enrolment bridge holding the grade.
Customers, orders, products and categories, plus a one-to-one between order and payment.
Departments, doctors, patients, appointments and prescriptions.
Books and authors in a many-to-many relationship, and members who borrow books.
The same database from the SQL lessons, including the manager self-relationship.