7. ENTITY RELATIONSHIP MODELLING
Some attributes, or combinations of attributes, serve to identify individual entities. For instance, suppose an educational institution assigns each student a student number that is different from all other student numbers. We say the student number attribute is a key attribute; student numbers are unique and distinguish students.
In an ERD, keys are shown underlined:
We define a key to be a minimal set of attributes that uniquely identify entities in an entity type. By minimal we mean that all of the attributes are required – none can be omitted.
For instance, a typical key for an invoice line entity type would be the combination of invoice number and invoice line number. Both attributes are required to identify a particular invoice line.
It is not unusual for an entity type to have several keys. For instance, suppose an educational institution has many departments such as Mathematics, Physics, and Computer Science. Each department is given a unique name and as well the institution assigns each one a unique code: MATH, PHYS, and CS. Both attributes would be underlined to show this in the ERD:
1) Suppose a company that sells products has a product entity type with the following attributes: prodNum, prodDesc, prodPrice. Suppose all three attributes are single- valued and that prodNum is a key attribute – each product has a different product number. Illustrate this information in an ERD.
2) Suppose a company assigns each employee a staff number and each employee has a government assigned identification number. For each employee the company records their first name, last name, and birth date. Illustrate this information in an ERD.
3) Consider a banking application where each account is identified first by an account number and then by its type (Savings, Chequing, and Loan). This scheme allows the customer to remember just one number instead of three, and then to pick a specific account by its type. Other attributes to be considered are the date the account was opened and the account’s current balance. Draw an ERD for the entity type Account with the attributes account number, account type, date opened, current balance. What is the key for the entity type? Is there an attribute that is likely a derived attribute? Show these attributes appropriately in the ERD.