Conceptual Database Design 1

1. Conceptual Database Design

The process of constructing a model of the data used in an enterprise, independent of all physical considerations.

• The goal of this phase is to produce a conceptual schema (which includes identification of the important entity types, relationship types, and attributes) for the database that is independent of a specific DBMS.

• We will use Entity Relationship (ER) modelling during this phase.

2. Build conceptual data model

2.1 Identify entity types

Identify entity types that we need to represent in the database.

Typical examples:

• People: staff, clients/customers, patients, members, owners, contacts, other individuals

• Objects: stock items, real estate, offices

• Organisations: firms (suppliers), departments, charities, clubs, committees

• Object classes: recordings, films, books, types of stock, biological species, work roles

• Events: concerts, examinations, lecture courses, consultations, sales

write it down clearly before drawing ER model

2.2 Identify relationship types

Identify the relationships between entity types that need to be recorded.

Typical examples:

• Ownership: person owns object

• Lines of command: person supervises person

• Participation: person participates in event

• Part of relationship: item is part of order; person belongs to organisation

• Location: house is located in region

• Personal: person is married to person; person is parent of person

sometimes we need to consider the time dimension of the relationship

2.3 Identify and associate attributes with entity or relationship types

Attributes can be identified where a noun or a noun phrase is a property, quality, identifier, or characteristic of one of the entity or relationship types previously found.

Identify whether attributes are:

• Simple/composite.

• Single/multi-valued.

• Derived. 

multi-valued attributes cannot be the primary key.

2.4 Documenting attributes

Record the following information for each attribute:

• attribute name and description;

• data type and length;

• any aliases that the attribute is known by;

• whether the attribute must always be specified (in other words, whether the attribute allows or disallows nulls);

• whether the attribute is multi-valued;

• whether the attribute is composite, and if so, which simple attributes make up the composite attribute;

• whether the attribute is derived and, if so, how it should be computed;

• default values for the attribute (if specified）

2.5 Determine attribute domains

A domain is a pool of values from which one or more attributes draw their values.

A domain specifies:

• allowable set of values for the attribute;

• size and format of the attribute.

2.6 Guidelines for choosing a primary key

• Select the candidate key
• with the minimal set of attributes;
• that is less likely to have its values changed;
• that is less likely to lose uniqueness in the future;
• with fewest characters (for those with textual attribute(s));
• with the smallest maximum value (for numerical attributes);
• that is easiest to use from the users’ point of view.