An entity is represented by (consists of) a value for each of its attributes.
Elvis Presley | 121212121 | Graceland |
Note that this representation forces attributes to always be written in the same order.
Example: an ER database for a fly shop
conceptual scheme (entity sets and their attributes):
attribute | domain |
---|---|
pattern | string[30] |
size | positive integer |
color | {black, olive, natural} |
fly-stock-num | positive integer |
attribute | domain |
---|---|
manufacturer | string[30] |
length | positive real (feet) |
line-weight | positive integer |
flyrod-stock-num | positive integer |
attribute | domain |
---|---|
name | string[30] |
address | string[50] |
cust-num | positive integer |
Example: the relationship between a customer and a Sage 2-weight flyrod purchased at the flyshop
A relationship set is a mathematical relationship on two or more entity sets. A relationship set R on entity sets is a a subset of:
where is a relationship.
The value of is the set of all possible relationships between entities in , entities in , ..., and entities in .
The set
is the cartesian product of .
Example: the cartesian product of customer and flyrod is:
Any subset of this set is an instance of relationship set Purchased.
A relationship may have attributes, and so a relationship set can have attributes. For example, relationship set Purchased could have a date attribute recording the date of purchase.
A mapping cardinality is a data constraint that specifies how many entities an entity can be related to in a relationship set.
Example: each customer can purchase as many kinds of flyrods as they want (a constraint on Purchased), but can have only one favorite flyrod/fly combination (a constraint on Fave-Combo).
A binary relationship set is a relationship set on two entity sets. Mapping cardinalities on binary relationship sets are simplest.
Consider a binary relationship set R on entity sets A and B. There are four possible mapping cardinalities in this case:
Example: if the fly shop stocks flies by customer request only, then relationship set Requested-By has one-to-many mapping cardinality because no one else need request a fly once the shop stocks it.
Relationship set Purchased has many-to-many mapping cardinality because a customer can buy many different kinds of flyrods, and each kind of flyrod can be purchased by multiple customers.
The mapping cardinality of a relationship set depends on the real world relationships it is modeling.
Another kind of mapping constraints, called existence dependencies, occur when the existence of an entity in an entity set depends on that entity being related to an entity in another entity set.
Issue: distinguishing between entities in an entity set (and relationships in a relationship set).
A superkey is a set of one or more attributes that allow entities (or relationships) to be uniquely identified.
All of the following are superkeys for entity set fly :
A superkey must allow entities (or relationships) to be distinguished across all possible instances. This often requires outside knowledge about what attributes mean.
A candidate key is a superkey that has no superkeys as proper subsets. A candidate key is a minimal superkey.
Candidate keys for fly:
The primary key is the (one) candidate key chosen (by the database designer or database administrator) as the primary means of uniquely identifying entities (or relationships).
Example: {fly-stock-num} is the most likely primary key.
The primary key of a relationship set is the primary means of uniquely identifying any relationship in the set.
entity set | primary key |
---|---|
customer | {cust-num} |
flyrod | {flyrod-stock-num} |
fly | {fly-stock-num} |
then the superkeys for our relationship sets are:
relationship set | superkey |
---|---|
Purchased | {cust-num, flyrod-stock-num, date} |
Requested-By | {cust-num, fly-stock-num} |
Fave-Combo | {cust-num, flyrod-stock-num, fly-stock-num} |
The primary key of a relationship set depends on the mapping cardinality and the meaning of any attributes of the relationship set.
Case 1: the relationship set has no attributes
Example: the primary key of Requested-By is {fly-stock-num}.
Example: the primary key of Purchased (without the date attribute) is {cust-num, flyrod-stock-num}.
Case 2: the relationship set has attributes
Example: the date attribute of Purchased would be included in the primary key, because the same customer could purchase the same make and model of flyrod multiple times. The full primary key is {cust-num, flyrod-stock-num, date}.
The conceptual scheme of an E-R database can be represented graphically by an E-R diagram as follows:
Mapping cardinalities are represented in an E-R diagram as follows
Entity-Relationship Diagram for the Flyshop DBMS:
E-R diagrams are used in designing other kinds of databases (especially relational databases) and in modeling software systems.
Idea: represent the data and relationships in an E-R database in a collection of tables.
The table corresponding to an entity set has a column for each attribute of the entity set and a row for each entity in the entity set. The table has the same name as the entity set.
Example: the table for entity set fly.
pattern | size | color | fly-stock-num |
---|---|---|---|
woolly bugger | 10 | olive | 5 |
gold-ribbed hare's ear | 14 | natural | 2 |
woolly bugger | 2 | black | 4 |
Dahlberg Diver | 2 | natural | 1 |
Discussion: does the order of rows matter?
The table for a relationship set will have a column for each attribute of the superkey of that relationship set (i.e. one for each attribute of the primary key of each entity set involved in the relationship, and one for each attribute of the relationship set). These attributes are renamed if necessary to avoid name clashes. The table will have one row for each relationship, and will have the same name as the relationship set.
Example: relationship set Requested-By becomes:
cust-num | fly-stock-num |
---|---|
1 | 2 |
3 | 5 |
2 | 4 |
2 | 1 |
Example: relationship set Purchased (including the date attribute) becomes:
cust-num | flyrod-stock-num | date |
---|---|---|
1 | 3 | 9/4/1994 |
3 | 3 | 9/3/1994 |
2 | 2 | 8/8/1992 |
3 | 2 | 9/2/1994 |
3 | 1 | 9/1/1994 |