Good afternoon everyone.

i approached one of the well established members of this forum through pm requesting for some help with this project that im doing. I was asked to post a thread, so here it is

The database project that im suppose to be doing isto go through the typical key stages of a normal database design, when responding a problem / case study.


1) Build a Conceptual Model
2) Build a Logical Model
3) Normalise
4) Establish a normalised E-R model / diagram
5) Create your table
6) Create your SQL queries
7) Evaluate.

----

Instead of just going through the normal 1 ) -> 7), i decided to start building the database in 3rd normal form, and just go backwards. Dont ask me why, i just find this bests suits my ability =).

The problem ive come to now, is that im unable to determine weather or not the conceptual / logical models i have created are appropiate. Do i conform to the typical methods of model design? *shrugS*

Basically what i have done for the conceptual stage, is simply list the key user requirements and initial database design through the use of identifying key entites, primary keys & foriegn keys and justification behind it.

Moving onto the logical stages, ive basically broken down the entities into individuals and simply stated the relationships between 1 or more entity(s) stating justifications & assumptions behind my conclusions. At the end of the ER break down, ive shown an overal view of the entity-relation diagram.

for logical model example, please see my attachment(S)

is this appropiate? am i missing a fundamental point? :/ normally it would be aparent, but due to the way ive approached this assignment.. im just not able to come to a firm conclusion.

I appreciate any help & advice given.

- aow

This is a common situation for an experienced DBA in that they will often find a system that is in actual use but has no design documents.

While it is very possible to "start from the middle and work backwards" to get working and usable documents, they will be a long way from being complete. The problem is that a large number of issues/factors/considerations never make it to the final design, but they are still important considerations in the design process.

One example of this is when you have an attribute with many design considerations, some of those considerations may incorporate other considerations, so that only one of these items makes it to the actual implementation. As a grossly simplified example, a given numeric attribute may actually need to be evenly divisible by two for one consideration and evenly divisible by five for a separate consideration... What makes it to the final design is that the attribute needs to be evenly divisible by ten (so that it is evenly divisible by both two and five), even though ten was never one of the real design considerations and it actually hides both the two and the five!

You can certainly create a working set of documentation by reverse engineering from the third normal form, but you run a risk of missing important details.

-PatP

The only way to verify that a model accurately represents the business domain (AKA Universe of Discourse or "UoD") is by understanding that business domain yourself.

For example if you are using methods like Object Role Modelling you can convert a conceptual model directly into a corresponding database model (and possibly do the reverse) but you still need to know enough to create or validate the conceptual model yourself.

Even "bottom-up" methods like Normalisation require you to know enough of the semantics to identify dependencies properly.

I think the only real answer is that there are no short cuts to understanding the business problem. That doesn't mean you need to tackle the whole thing at once. Iterative, agile approaches are workable too. However, creating an entire model based on assumption and guesswork rather than analysis is almost certainly time wasted. You are bound to end up re-working much of it before it can be of any practical use.

Your conceptual diagram is perfect.

************************************************** ***
Conceptual data models are intended as a tool to begin with the design:
• And they Includes only the important entities and the relationships among them.
• Entities shouldn’t have attributes or keys specified.
In a conceptual design, the data modeler attempts to identify the highest-level relationships among the different entities.

A logical data model includes:
• 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.

Features of physical data model include:
• Specification of all tables and columns.
• Foreign keys are used to identify relationships between tables.
• De-normalization may occur based on user requirements.
• Physical considerations may cause the physical data model to be quite different from the logical data model.
At this level, the data modeler will specify how the logical data model will be realized in the database schema.
The steps for physical data model design are as follows:
1. Convert entities into tables.
2. Convert relationships into foreign keys.
3. Convert attributes into columns.
4. Modify the physical data model based on physical constraints / requirements.