As you are studying databases, strive to read as many database examples as you can. This resource includes an example E-R model for a SQL movie database. Attempt the questions in the green boxes and at the "Quiz Questions on ER Diagrams" link.
Sub-queries are very powerful and expressive. You can use them in at least three ways:
Devise a query to list the name of the youngest person in the WMDB. The oldest?
Let's discuss the WMDB tables for a few minutes:
Thinking of them as sets, what is the relationship between movies and directors, at least in the WMDB? What is the relationship between movies and actors?
What is a SQL query to look up all movies directed by Clint Eastwood?
Entities are the objects or nouns we are interested in. Relationships are the connections or verbs we care about.
No standard drawing, although some are very common.
Afterwards, convert ER diagrams to CREATE TABLE statements
keys and key constraints
What attributes would you have for a movie in the WMDB? What would the key be?
What attributes would you have for cars at a new car dealership? What would the key be?
createtable person( nm int, namevarchar(30), birthdate date, primary key (nm)); -- as of MySQL 5, "release" is a reserved word; -- have to put the field name in backquotes. createtable movie( tt int primary key, title varchar(50), `release`char(4));
Remember this fact about the word
What about directors? That's a relationship between the two entities:
In our world, a movie only has 1 director, so this is a one-to-many relationship between Movies and People. Therefore, we draw an arrow from Movie to Directs, because Movie is a key of the relationship set. This is the key constraint.
createtable movie( tt int, title varchar(50), `release`char(4), director int, primary key (tt), foreign key (director) references person(nm) ondeletesetnullonupdatecascade);
We also have a one-to-many relationship between People and Staff via the AddedBy relationship. Similarly for movies. We have a one-to-one relationship between Staff and UserPass:
In this case the relationship disappears. This doesn't always happen with a one-to-one relationship, but almost always. (In our case, we could even absorb the password into the staff table.
When would we not want to do this?
createtable userpass( idint, username varchar(20), passwordchar(15), primary key (id), unique (username));
What about a many-to-many relationship? How about movies and people?
Here, we have to have the intermediate table representing the relationship. Essentially, the relationship is two one-to-many relationships.
createtable credit( tt int, nm int, primary key (tt,nm), foreign key (tt) references movie(tt) ondeletecascadeonupdatecascade foreign key (nm) references person(nm) ondeletecascadeonupdatecascade);
A relationship can have attributes as well. Here, we record the date that a student declared a particular major.
createtable majorsin( sidint, did int, decl date, primary key (sid,did), foreign key (sid) references student(sid) foreign key (did) references department(did));
Roles can be labeled. Here's an example.
The roles usually become column names:
createtableadvise( advisor int, advisee int, foreign key (advisor) references person(ssn), foreign key (advisee) references person(ssn), unique (advisor), unique (advisee));
Here's a (heterosexist, sorry) example:
createtable marriedto( husband int, wife int, foreign key (husband) references person(ssn), foreign key (wife) references person(ssn), unique (husband), unique (wife));
What if we had the primary key in
(husband,wife)? What would the implications be?
Relationships can be Ternary as well as Binary, though this is rare.
Here's an example. The idea is that there are generic
parts in the world, such as 2-inch #6 bolts or AA batteries. There are
suppliers, such as Eveready, Duracell and others. There are
projects that need parts like bolts and batteries. If the project leader buys a particular product from a particular supplier for a given price on a given day, that event creates a relationship among three entities.
The table representing the relationship just has three foreign keys. Yikes, look at that primary key!
createtableproject (proj_id int, proj_title varchar(50)); createtable part (part_id int, sku char(10)); createtable supplier (supp_id int, namevarchar(50), address varchar(50)); createtable sold( customer int, product int, supplier int, `date`date, price float, primary key (customer,product,supplier,`date`), foreign key (customer) referencesproject(proj_id), foreign key (product) references part(part_id), foreign key (supplier) references supplier(sup_id));
Sketch an ER diagram for a student work-study program. What entities are there? What relationships? Are those relationships 1-1 or 1-many or many-many? Note that there's no single right answer to this.
If every X must have a Y, this is called a participation constraint. Examples:
In an ER diagram, this is represented with a thick line or (sometimes) a double line:
In SQL, we can enforce a participation constraint with a "not null" and "foreign key" constraint. For example:
droptableifexists movie; createtable movie( tt int, title varchar(50), `release`char(4), director intnotnull, primary key (tt), foreign key (director) references person(nm));
However, this can be annoying:
Pictured using triangles:
Where would you put the following attributes?
ISA hierarchies are converted to either of the following:
However, if something participates in relationships, it must be an entity. Don't have a
dept attribute for a Student if there's a
Department entity. Instead, put that entity in a relationship with the Department entity.
directoras an entity, or
directsas a relationship?
For the following, assume we have (at least) two tables:
Prof (SSN,Name) Teaches Course(Dept,Number,Title)
Sketch the ER diagram you would want:
Please remember the following syntax rules for ER diagrams, the way we're doing them:
A few more rules to keep in mind when converting ER diagrams to DDL:
You can, of course, draw these diagrams with pencil and paper and either hand those in or scan them and email them to me. You can also create electronic drawings using Fireworks, Photoshop, the GIMP, or any of many other drawing applications. I did these with Xfig and with Inkscape.
Many students find that Google Slides and MS PowerPoint are sufficient.
The Wikipedia page lists several applications that have special support for ER diagrams. One I've played with is Dia which you can install on Windows and Macs. (There may even be implementations for Linux.) Here is a picture drawn using Dia:
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 1.0 License.