CHOSEN SCENARIO: VINCE’S VINYL

Analysis and Design

A. Conceptual Model:

A conceptual model, or a summary-level data model, is generally used on strategic data projects to describe an entire organization. As the most abstract form, it is useful in communicating ideas to a wide variety of stakeholders. Platform specific information, e.g., data types, is excluded. Other implementation details, e.g., procedures and interface definitions, are omitted. CDM (Conceptual Data Model) is typically created without consideration of database concepts and used in the initial planning phase. Business practice, rather than information similarities, defines relationships. In this phase, it is irrelevant how information is stored and managed, but primarily understanding how stored data is used is crucial.

It provides the overall structure of the database and high-level information about the subject areas and/or data structures of an enterprise. Its simple purpose is to establish the Entities, their Attributes and their ‘high-level’ relationships. In other words, there is little detail, consisting of the basic Entities along with their simple relationships. If any Attributes are included, they are loosely typed (e.g., no length settings). Connectors between Entities do not define relationships to specific Attributes. (UC Davis).

Common characteristics of a CDM (DataModel.com):

• Enterprise-wide coverage of the business concepts. (For example, Customer, Product, Store, Location, Asset.)
• Designed and developed primarily for a business audience.
• Contains around 20-50 entities (or concepts) with no or extremely limited number of attributes described (sometimes minimized for printing on one page).
• Contains relationships between entities, but may or may not include cardinality and nullability.
• Entities will have definitions.
• Designed and developed to be independent of DBMS, data storage locations or technologies. In fact, it would address digital and non-digital concepts. This means it would model paper records and artifacts as well as database artifacts.

Conceptual Model for Vince’s Vinyl: The primary entities are: the owner, vendors/suppliers, customers/buyers, employees and albums. The owner purchases albums from vendors/suppliers, sells them to customers/buyers and employs part-time help, who stocks and also sells the albums to customers/buyers. (See screenshot below.)

1. Logical Model:

A logical data model, or a fully-attributed data model that is independent of DBMS, technology, data storage or organizational constraints, typically describes data requirements from the business aspect. Also referred to as LDM, it represents the (entire or partial) business requirements of an enterprise and is developed before the physical data model. It involves using the business information discovered during CDM and translating it into a relational style, empirical layout of the data.

While the data modeling uses a relational model notation, the resulting data implementations using relational technologies are not required. This technique includes more detail, or Attributes. Nevertheless, it is still “generic” since it is not bound to a specific DBMS. It helps to define the detailed structure of the data elements in a system and the relationships between data elements. The model refines the data elements introduced by a Conceptual data model, just as it forms the basis of the physical data model.

The process of creating a logical model (based on a conceptual model) involves (DataModel.com):

• Setting the Attributes. These later become Table Columns.

• Setting the Relationships. At this level, Primary Keys, Foreign Keys, etc. are not yet set. This level can be used to verify and adjust the Connector ‘multiplicity’ (or, ‘cardinality”) details that were established for relationships in the Conceptual model.

Common characteristics of a logical data model (DataModel.com):

• Typically describes data requirements for a single project or major subject area.
• May be integrated with other logical data models via a repository of shared entities
• Typically contains 100-1000 entities, although these numbers are highly variable depending on the scope of the data model.
• Contains relationships between entities that address cardinality and nullability (optionality) of the relationships.
• Designed and developed to be independent of DBMS, data storage locations or technologies. In fact, it may address digital and non-digital concepts.
• Data attributes will typically have datatypes with precisions and lengths assigned.
• Data attributes will have nullability (optionality) assigned.
• Entities and attributes will have definitions.
• All kinds of other meta data may be included (retention rules, privacy indicators, volumetrics, data lineage, etc.). In fact, the diagram of a logical data model may show only a tiny percentage of the meta data contained within the model.
• A logical data model will normally be derived from and or linked back to objects in a conceptual data model.
• A physical data model is a fully-attributed data model that is dependent upon a specific version of a data persistence technology. The target implementation technology may be a relational DBMS, an XML document, a NoSQL data storage component, a spreadsheet or any other data implementation option.

Logical Model for Vince’s Vinyl: Necessary aspects of the DBMS are represented to address the business solution. (See screenshots below.)

1. Physical Design:

Physical level modeling involves adding “platform specific” detail to the model, i.e., detail specific to the DBMS, where the database is to be deployed. This can be accomplished by creating a copy of the logical model and by adding the physical definitions. It includes all required tables, columns, relationships, and database properties.

It visually represents the structure of the data, as implemented by a relational database schema (e.g., tables, columns, indexes, constraints, primary and foreign keys). In other words, by defining a physical data model, the database schema can be derived automatically.

The logical model, which was derived from the business concepts (or, conceptual model), is in turn adapted for database deployment. PDM, as it is sometimes known, involves the final stage of planning. This stage involves an absolute design of the database implementation, which is often completed by a data architect, or a database administrator. As such, the CDM, LDM and PDM phases are finalized.

The key aspects of PDM are (DataModel.com):

• For each ‘Class’:

• The Stereotype must be set to ‘Table’.
• The Database setting must be set to a specific DBMS.
• Update the Attributes to reflect Columns ‘Typed’ to the specific DBMS Field types.

• Add more detail to the Connectors (relationships), in order to define the Primary Key (and Foreign Key) linking.
• Further details can be added to the derived physical model. These include setting:

• Stored Procedures: (A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. They can be compiled and executed with different parameters and results, and they can have any combination of input, output and input/output parameters.)
  • Views: (A Database View defines a subset of the database, aggregated into a dynamic, ‘virtual’ table’. The information presented by a Database View is not physically replicated. It is simply automatically derived — based on the query that defines the view.)

Common characteristics of a physical data model (DataModel.com):

• Typically describes data requirements for a single project or application, sometimes even a portion of an application.
• May be integrated with other physical data models via a repository of shared entities
• Typically contains 10-1000 tables, although these numbers are highly variable depending on the scope of the data model.
• Contains relationships between tables that address cardinality and nullability (optionality) of the relationships.
• Designed and developed to be dependent on a specific version of a DBMS, data storage location or technology.
• Columns will contain precision- and length-assigned datatypes.
• Columns will have nullability (optionality) assigned.
• Tables and columns will have definitions.
• It will also include other physical objects such as views, primary key constraints, foreign key constraints, indexes, security roles, store procedures, XML extensions, file stores, etc.

• The diagram of a physical data model may show only a tiny percentage of the meta data – contained within the model.

Physical Model for Vince’s Vinyl: This model builds on the nonphysical (conceptual and logical) models that have been crafted. (See screenshot below.)

(Special note: In case the above sample is insufficient, I am including the database diagram that I also submitted for the Scenario Activity.)