Terms for Project 1

Conceptual Data model

Features of conceptual data model include:

• Includes the important entities and the relationships among them.
• No attribute is specified.
• No primary key is specified.

At this level, the data modeler attempts to identify the highest-level relationships among the different entities.

--1keydata.com

---

Logical Data Model

Features of logical data model include:

• Includes all entities and relationships among them.
• All attributes for each entity are specified.
• The primary key for each entity specified.
• Foreign keys (keys identifying the relationship between different entities) are specified.
• Normalization occurs at this level.

At this level, the data modeler attempts to describe the data in as much detail as possible, without regard to how they will be physically implemented in the database.

In data warehousing, it is common for the conceptual data model and the logical data model to be combined into a single step (deliverable).

The steps for designing the logical data model are as follows:

1. Identify all entities.
2. Specify primary keys for all entities.
3. Find the relationships between different entities.
4. Find all attributes for each entity.
5. Resolve many-to-many relationships.
6. Normalization.

--1keydata.com

---

UML Notation

---

Business Rules

The descriptive statements of how a business is run. These statements describe how the various entities relate to each other. One example would be the rule that states an Accounts Payable vendor could be one of three types: supplier, transportation provider, or governmental agency.

--olenick.com

---

Strong vs Weak Entities

An entity set that does not possess sufficient attributes to form a primary key is called a weak entity set.

One that does have a primary key is called a strong entity set.

For example,

• The entity set transaction has attributes transaction-number, date and amount.
• Different transactions on different accounts could share the same number.
• These are not sufficient to form a primary key (uniquely identify a transaction).
• Thus transaction is a weak ent
  

For a weak entity set to be meaningful, it must be part of a one-to-many relationship set. This relationship set should have no descriptive attributes. (Why?)

The idea of strong and weak entity sets is related to the existence dependencies seen earlier.

• Member of a strong entity set is a dominant entity.
• Member of a weak entity set is a subordinate entity.
  

A weak entity set does not have a primary key, but we need a means of distinguishing among the entities.

The discriminator of a weak entity set is a set of attributes that allows this distinction to be made. The primary key of a weak entity set is formed by taking the primary key of the strong entity set on which its existence depends (see Mapping Constraints) plus its discriminator.

To illustrate:

• Transaction is a weak entity. It is existence-dependent on account.
• The primary key of account is account-number.
• Transaction-number distinguishes transaction entities within the same account (and is thus the discriminator).
• So the primary key for transaction would be (account-number, transaction-number).

Just remember: The primary key of a weak entity is found by taking the primary key of the strong entity on which it is existence-dependent, plus the discriminator of the weak entity set.

--CMPT-354-Han-95.3 Lecture Notes

---

Cardinality & Participation

Cardinality: business rules that specify how many times (min and max) an entity can be related to another entity in a given relationship.

Participation: The participation constraint represents whether all entity occurences are involved in a particular relationship (mandatory participation) or only some (optional participation).

---

Generalization/Specification

specification is the process of maximizing the differences between member of and entity by identifying their distinguishing characteristics.
generalization is the process of minimizing the differences bewteen entities by identifying their common chartacteristics.

---

Referential Integrity

Referential Integrity is an integrity constraint that specifies that the value (or existence) of an attribute in one relation depends on the value (or existence) of an attribute in the same or another relation.

If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in it's home relation or the foreign key value must be wholly null.


For example, branchNo in the Staff relation is a foreign key targeting the branchNo attribute in the home relation, Branch. It should not be possible to create a staff record with branch number B025. However we should be able to create a new staff record with a null branch number, to cater for the situation where a new member of staff has joined the company but has not yet been assigned to a particular branch office.