Lab Questions

Below is an example scenario similar to the one for the ERD lab; this will show you how to create the ERD's, etc for the assignment. Consider:

Our store sells new and used music on CD. Customers browse our inventory and then make purchases from the employees; prices for each CD vary depending on their particular title and the condition of the disk. We need to track inventory and transactions, and maintain a list of customers with their email addresses.

This scenario contains several entities that we can spot right away (Customers, Employees, CD's), and we can see that they have a clear relationship (Customers buy CD's from Employees). Also, we can infer that individual CD's each have a CD Title, which can be duplicated (multiple copies of the same CD Title can be in inventory). Rather than leave this information wastefully duplicated (and vulnerable to inconsistency), we can break it out into its own separate entity. Here is a simple ERD with the relationships named:

The "Buys" relationship is ternary (it involves three entities), and has a cardinality of many-to-many (i.e. customers can buy multiple CD's from different employees, employees can sell many CD's to many customers); in order to represent this in an ERD, we need to break apart the M:N relationship into multiple 1:M relationships. We can do this by creating an intersection entity such that only one instance of the other entites is related to it (i.e. it represents the intersection of these entities). We'll call it Transaction:

This limits the scope of each "transaction" to a single CD. We can accommodate multiple-CD transactions by breaking this model apart even further, adding a new entity to represent a transaction line item:

Notice that we have added the crow's feet to represent cardinality of the 1:M relationships (the "many-" side has crow's feet), and we're also showing mandatory/optional cardinality by using hash-marks and ovals respectively. So, to interpret the relationship lines with sentences (as per the assignment), we can say:

A customer may have zero to many transactions / A transaction has exactly one customer

If we wanted to turn these entities into relations (as per the assignment), we could do it like so:

EMPLOYEE(EmpID, LastName, FirstName, Salary)

TRANSACTION( FK_EmpID , FK_CustID, TransactionDate)

In the case of the EMPLOYEE relation, we've selected a primary key called EmpID (notice that the primary key is listed first and underlined), along with some other relevant attributes. The TRANSACTION relation is more complicated. It has a composite primary key composed of FK_EmpID, FK_CustID, and TransactionDate, of which the first two are foreign keys (represented in italics); these are the primary keys of the EMPLOYEE and CUSTOMER relations. The rule to remember in foreign key placement: always put the foreign key in the "many-" side of a one-to-many relationship, since the attribute can only hold one value. In other words, for a given transaction, there can be only one employee and one customer. If the customer is buying multiple CD's, he will have multiple entries in the LINE_ITEM relation which point to the same TRANSACTION entry.

Why do we need this third column as part of the primary key? Couldn't we simply use FK_EmpID and FK_CustID as the composite primary key? Well, if a customer comes back later for another transaction with the same employee, we would not be able to insert a new record, since that combination of values already exists. Is there a better strategy for the primary key of the TRANSACTION relation?

The process above, of creating a "simple" ERD, using it to create a more detailed ERD with cardinality, defining the relationships in both directions, and then creating relations in which the keys are defined - this process is very similar to that for the lab assignment. :-/