ER, Schema and Tables Through One COMPANY

The same COMPANY drawn as three layers. ① ER (ch2) → ② the converted schema (ch3 3.4) → ③ tables filled with data (ch3 3.1). The tables below only point to where each concept is in the three figures. The exam's "draw the RDB" = look at ① and draw ②

1. ER diagram

2. Relational schema

• 6 tables = 4 rectangles + 1 M:N diamond (WORKS_ON) + 1 double oval (DEPT_LOCATIONS)
• Every arrow is FK → PK. 7 arrows = 2 1:N diamonds (Dno, Dnum) + 1 1:1 diamond (Mgr_ssn) + 1 recursive (Super_ssn) + 2 from M:N (Essn, Pno) + 1 double diamond (DEPENDENT.Essn) + 1 double oval (Dnumber)
• The double diamond (DEPENDENTS_OF) gets no table of its own; it just adds the owner's PK (Essn) to the weak entity DEPENDENT. That Essn is an FK and also part of DEPENDENT's PK
• Watch the renamed columns: Number → Dnumber/Pnumber, Name → Dname/Pname, EMPLOYEE.Ssn taken elsewhere becomes Essn, Mgr_ssn, Super_ssn

3. Tables (relation state)

• Header row = schema, the rows under it = state. Adding or deleting rows leaves the header unchanged
• A value in an FK cell must be a value that exists in the other table's PK, or NULL. The five lines at the bottom right of the figure judge insert and delete violations

4. Entities and attributes

Concept	Meaning	① ER	② Schema	③ Table
Entity type	The kind of "thing" you want to keep as data. Like employee, department, project: the template for entities with the same attributes. One rectangle in ER, one table once converted
Entity	One real thing that fits the template. The employee John Smith. Not drawn in ER; one row in a table	Not drawn	—
Entity set	All entities of the same template. Every employee. One rectangle stands for all of them; in a table, all the rows		—
Attribute	One value an entity has. An employee's salary. In ER an oval hung off the rectangle by a line; in a table, one column
Key attribute	An attribute whose value differs for every entity, so knowing it pins down which one. Know the Ssn, get one employee. Underlined oval. A rectangle can have more than one (DEPARTMENT's Name, Number); in a table one of them becomes the PK
Composite attribute	An attribute that splits into smaller attributes. Name = Fname + Minit + Lname. Ovals branching off an oval. Only the leaf branches become columns; there's no Name column			3 columns Fname, Minit, Lname
Multivalued attribute	An attribute with several values for one entity. One department in two places, Bellaire and Sugarland. Double oval. A table cell holds only one value, so it gets its own table
Derived attribute	An attribute that isn't stored but computed from elsewhere. A department's employee count comes from counting EMPLOYEE. Dashed oval. No column in the table

5. Relationships

Concept	Meaning	① ER	② Schema	③ Table
Relationship type	How two entity types are tied together. An employee works for a department = WORKS_FOR. A diamond between rectangles. Becomes an FK column or a new table
Relationship instance	One actual fact of that relationship. Smith works for department 5. Not drawn in ER. One FK cell value in a table	Not drawn	—
Degree	How many entity types take part in one relationship. Employee–department is 2 (binary); supplier–project–part tied together at once is 3 (ternary). Count the rectangles connected to the diamond			3 FKs per row
Cardinality 1:N	One entity on one side is tied to many on the other, and one on the other side is tied to only one on this side. One department has many employees, an employee has one department. 1 and N on the lines. The FK goes on the N side
Cardinality 1:1	On both sides one entity is tied to only one on the other. One department has one manager, and that employee manages only one department. 1 and 1 on the lines. The FK goes on the double-line side
Cardinality M:N	On both sides one is tied to many. One employee on many projects, one project with many employees. M and N on the lines. One FK cell can't hold it, so a new table
Total participation	Every entity on that side, without exception, is in the relationship. No employee without a department. Double line between rectangle and diamond. That FK cell is never empty
Partial participation	Some entities may not be in the relationship. Some employees aren't managers. Single line. That cell can be NULL
(min, max)	At least and at most how many times one entity takes part in the relationship. An employee manages a department 0 or 1 times = (0,1); a department has exactly 1 manager = (1,1). min 0 means a single line, min 1 or more a double line, and whether max is 1 or N is the ratio above		min ≥ 1 → NOT NULL / max 1 → one FK column, max N → new table	—
Relationship attribute	A value attached to the relationship, not an entity. Hours belong to neither the employee nor the project but to "the employee on that project". An oval hung off the diamond. In tables it follows the FK into the table it went to
Recursive relationship	A relationship an entity type has with itself. An employee supervises an employee. Both lines of the diamond go into the same rectangle. In a table, an FK pointing at its own PK
Role name	The name of the role each side plays in a relationship. The supervising side is supervisor, the other supervisee. Text beside the line. In a recursive relationship both rectangles are the same, so without it you can't tell which line is which		Stays as the FK column name (Super_ssn)	—

6. Weak entities

Concept	Meaning	① ER	② Schema	③ Table
Weak entity type	An entity type whose own attributes can't pin down which entity it is. There are several dependents named "Alice", so you need to know whose daughter. Double rectangle. Once converted, the owner's PK column is added
Owner	The entity type a weak entity depends on. Alice's employee. The rectangle across the double diamond. Its PK goes into the weak entity's table		The EMPLOYEE that Essn points to	—
Identifying relationship	The relationship connecting a weak entity to its owner. Double diamond; the weak side's line is always double (no dependent without an owner). No table of its own; the FK column plays that role			—
Partial key	An attribute that tells entities apart only under the same owner. One employee's dependents don't share names. Oval with a dashed underline. Table PK = owner's PK + this

7. Relational model, keys and integrity

Concept	Meaning	① ER	② Schema	③ Table
Relation	One table. One ER rectangle (double rectangles included), one M:N diamond and one double oval each become one table. 1:1 and 1:N diamonds, double diamonds and recursive diamonds make no table; they end up as FK columns. Sounds like relationship (diamond) but is a different word
Relation schema	The table's template. Name + column names. EMPLOYEE(Fname, …, Dno). In ER, a rectangle plus the ovals hanging off it; this is the schema figure; in the table figure, the header row
Relation state	The rows currently in that template. Changes as rows are inserted and deleted, but the schema stays. In ER, the entity set the rectangle stands for; everything below the header in the table figure		—
Tuple	One row. One employee = one entity, or one WORKS_ON row = one relationship fact	Not drawn (one entity / one relationship instance)	—
Domain	The range of values allowed in that column. Salary is a positive integer. Values outside the range can't go in. In ER, the attribute's value set		As the type in DDL
NULL	No value in the cell. Unknown, or not applicable. Not 0 or an empty string. Borg's Super_ssn, who has no supervisor. In ER, the side with min 0 (single line) is where NULLs come from		—
Degree	In the relational model, the number of columns in a table. Count the header boxes. In ER, leaf ovals + FKs brought in. Same name as ER's degree (rectangles attached to a diamond) but a different thing
Superkey	A set of columns where no two rows can have the same values. {Dnumber} works, and so does {Dnumber, Dname} with extras added	Not drawn		—
Key	A superkey that stops being a superkey if you remove even one column. {Dname} and {Dnumber} each. If there are several, all are candidates. In ER, each underlined oval is a key			—
Primary key	The one candidate picked to represent the table. Dnumber. Underlined. A column with no duplicates and no NULLs in the table	Not drawn (ER only underlines; one of them is picked in the schema)
Foreign key	A column holding another table's (or its own table's) PK values to point at that row. EMPLOYEE.Dno = 5 is row 5 of DEPARTMENT. An arrow in the schema. In ER, 1:N and 1:1 diamonds, double diamonds, double ovals and recursive diamonds all become FKs
Both PK and FK	A column brought in from someone else's PK that is also part of its own PK. WORKS_ON's Essn, Pno come from the M:N diamond; DEPENDENT's Essn comes from the double diamond. Half yellow, half blue
Key constraint	No two rows with the same PK value. You can't insert two employees with Ssn 123456789. In ER, a key attribute (underlined oval) differs for every entity		Underline
Entity integrity	PK cells can't be NULL. An employee with an empty Ssn can't be identified. In ER, an entity without its key attribute can't be singled out		Underlined column
Referential integrity	An FK cell's value must actually exist in the other table's PK, or be NULL. Dno = 7 with no department 7 is a violation. In ER, the entity across the diamond must actually exist		That value must exist at the arrow's end
Violation checks	Insert checks all three: Key, Entity and Referential. Delete only checks whether other rows point at the deleted row through an FK. Treat update as delete + insert	Not drawn	—	Bottom right of figure ③