In this section we present the steps involved in the conceptual database design phase. The main focus of the conceptual database design phase is to produce a conceptual data model that fulfils the enterprise requirements. Our discussion on this design methodology are based on Connolly and Begg (2005) guideline. We are going to use the Product Ordering case study for our discussion.
Step 1: Build Conceptual Data Model
Among the key elements that need to be identified for a conceptual model include:
• entity types,
• relationship types,
• attributes and attribute domains,
• primary and alternate keys, and
• integrity constraints.
Depending on the size of the database application to be build, we may produce one local conceptual data model for each user view. In our discussion, we assume that we only need to build one conceptual data model.
The following are the steps that we need to perform to build the conceptual data model.
7.2
1. Briefly explain what is design methodology.
2. Identify the phases of design methodology.
3. Identify three critical success factors in database design.
SELF-CHECK 7.1
Step 1.1: Identify Entity Types
Identifying the main objects also referred to as entity types, which are required for the model, is the first step to be performed. This information can be obtained from the userÊs requirement specification.
We have identified seven entity types for the conceptual data model:
Customer, Employee, Product, Order, Invoice, Delivery, Supplier Step 1.2: Identify Relationship Types
Next, we need to determine the important relationships that exist between the entity types that have been identified. Relationships are identified by examining the transactions that are needed by the users in the requirements specification. The relationships are typically described using a verb. Use of ER diagram helps to visualise the relationship and the model more effectively. We need also to include the cardinality and the participation constraints of relationship types in the diagram. The descriptions of this information need to be documented for the refinement and validation purposes.
For our product ordering case study, we have identified the following relationships:
• Between Customer and Order : Customer makes Order
• Between Product and Order : Order has Product
• Between Supplier and Product : Supplier supplies Product
• Between Order and Invoice : Order has Invoice
• Between Employee and Order : Employee takes Order
• Between Order and Delivery : Order sends for Delivery
To visualise the relationship between the entity types we use ER diagram based on UML notation, as shown in Figure 7.1.
Step 1.3: Identify and Associate Attributes with Entity or Relationship Types After identifying the entity and relationship types, the next step is to identify their attributes. It is important to determine the type of these attributes. As discussed in Topic 2, attributes can be categorised as simple or composite, single or multi-valued, and derived attributes. Again, we need to document the details of each identified attribute.
For our case study, the list of attributes for the defined entities is as follows:
Customer CustNo, Name, CustAddress,TelNo, Balance
Employee EmpNo, Name, TelNo, Position, Gender, DOB, Salary
Order OrderNo, OrderDate, OrderAddress Invoice InvoiceNo, Date, DatePaid, OrderNo;
Product ProductNo,Name,UnitPrice, QtyOnHand, ReorderLevel, SuppNo Delivery DeliveryNo, DeliveryDate, OrderNo, ProductNo;
Supplier SuppNo, Name, SuppAddress, TelNo, ContactPerson
Figure 7.1 : Initial ER Diagram showing entity and relationship types Step 1.4: Determine Attribute Domains
Next, we need to determine domains for each attributes and document the details of each domain. If we have more than one user view, the domain of an attribute for each user view might be different.
Step 1.5: Determine candidate, primary, and alternate key attributes
As we have mentioned in the Topic 2, a relation must have a key that can uniquely identify each of the tuples. In order to identify the primary key, we need first to determine the candidate key for each of the entity types.
The primary key for each of the entity types are underlined as listed below.
Customer CustNo, Name, CustAddress,TelNo, Balance
Employee EmpNo, Name, TelNo, Position, Gender, DOB, Salary Order OrderNo, OrderDate, OrderAddress, CustNo
Invoice InvoiceNo, Date, DatePaid, OrderNo;
Product ProductNo,Name,UnitPrice, QtyOnHand, ReorderLevel, SuppNo Delivery DeliveryNo, DeliveryDate, OrderNo, ProductNo;
Supplier SuppNo, Name, SuppAddress, TelNo, ContactPerson Step 1.6: Consider use of enhanced Modelling Concepts (optional step)
This step is involved with the use of enhanced modeling concepts, such as specialisation / generalisation, aggregation, and composition. These concepts are beyond the scope of our discussion.
Step 1.7: Check Model for Redundancy
Check for the presence of any redundancy in the model as an important step to perform. The common checking for the redundancy is to re-evaluate the 1:1 relationship. If the entities in the relationship are similar, then we need to merge them together as one entity, and may need to redefine the primary key. This type of problem typically exists when we have more than one user view.
Step 1.8: Validate Conceptual Model against User Transactions
We have to ensure that the conceptual model supports the transactions required by the user view.
Step 1.9: Review Conceptual Data Model with User
User involvement during the review of the model is important to ensure that the model is a ÂtrueÊ representation of the userÊs view of the enterprise.
SELF-CHECK 7.2
1. Identify the steps involved in building conceptual data model.