logical database

Logical and Physical Database comparison {1}

by Garcello D
This week’s article I decided to blog about is called “Logical Versus Physical Database Modeling,” It was written by the staff of developer.com but the information was originally derived from a book called “Database Design” which was written by Ryan K. Stephens and Ronald R. Plew. The article basically explains the logical and physical data modeling and then compares them in a very simple and easy to learn method. The article starts off explaining how models are required and visually represent a proposed database, and then it talks about the different types of diagrams that are used which are the entity relationship diagram, the process flow diagram and the server model diagrams. Then the article introduces the logical and physical data modeling and explains the importance of knowing the difference between the two when it comes to databases. The logical Modeling section then explains that it uses the business requirements and converts those requirements into a model. Creating this model requires the gathering of business entities, business processes, and organizational units. After this information is gathered diagrams and reports are produced that show the processes, that data that exists and the relationships between the data. It should show a physical representation of the activities and data relevant to a particular business. The physical database is the actual design of the database based on what was acquired from the logical database. It deals with the conversion of the logical model into a relational database model. But this is just a small summary which is covered more in depth inside the article. read more...

Steps to Create a Good Self-tuning Logical Database Design {3}

by Andrew S
The journal talks about logical database design and how database administrators have to maintain efficient databases to keep up with the current trends.  There is a lot of talk about self-tuning physical database design, but the self-tuning logical database design aspect is understudied.  The author of the article explores a framework in which a database should be able to self-tune its logical database schema that have SQL workloads.  Self-tuning of a logical database is necessary if it has to adapt and evolve to better match its user’s requirements. read more...

Logical vs. Physical Modeling {2}

by Kevin Q
My article was on the differences of logical database modeling versus physical database modeling. It begins by briefly describing the two before jumping into full detail of each individually. For logical modeling it’s mostly what we have learned in class already. It’s gathering the requirements, entities, relationships and conveying them into a model. The deliverables that come from Logical Modeling are Entity relationship diagrams, Business process diagrams, and User feedback documentation. We are already familiar with ERDs through class, but the business process diagrams are models that show how parent and child processes are handled in a company or organization. To see how the company and its employees move data will help in the design of the database application interface. Physical Modeling is taking the logical modeling that has been developed and implementing it into an actual database. In the pyhsical model, tables and columns are created based on the logical model(ERD), which means entities, their attributes, relationships, primary keys, foreign keys and constraints. Everything carries over to create the physical model which can be used to view data in many ways that would be useful to employees and the company. Physical modeling is specific to the database software that is used. The deliverables in physical modeling are Server Model Diagrams and User feedback. Server model diagrams are tables, columns, and relationships within a database. read more...

Important Steps to Convert The Data Sucessfully {1}

by Jamal A
The article I read talks about the procedure that can successfully convert a logical data model into a physical  data model, particularly in a warehouse setting. The article describes some of the necessary steps that can   help convert the data successfully. The article talks about the rules that are necessary to follow when creating logical and physical data models. For Instance: “The business authorization to proceed is received, business requirements are gathered and represented in a logical data model, which will completely represent the business data requirements and will be non-redundant, the logical model is transformed into a first-cut physical model by applying several simple modifications, such as splitting a large table or combining entities in a 1:1 relationship, the logical model is then transformed into a second-cut physical model by iteratively  applying three levels of optimizations or compromises. The outcome from this is a physical database design, apply safe compromises to the model, such as splitting a table or combining two tables, apply aggressive  compromises to the model, such as adding redundant data, the physical database design is then converted to  a physical structure by generating or writing the DDL and installing the database”. In This article, The Information  Gathering Task is also described. I think gathering information is the most critical task when developing a data model. The article described three ways to approach data model development that is, “Top down, inside out, and bottom up”. read more...

4th Step: Implementation Design {4}

by Penny P
In database analysis and design, there are three major steps that are followed: conceptual design, logical design, and physical design. The author of the article explains that there is a need to extend and include a fourth step: implementation design. This step would come in after the logical design and before the physical design. The current view of the implementation step is seen as a substep of either the logical or physical design. In other words, it’s lumped in with the design of the logical or the physical. By making the implementation design a separate step, designers can fulfill several important actions before the physical design is created. Doing so will allow the logical design to stay focused on tasks that need not consider them, such as how to implement the database when they move on to the next step. And for the physical design stage, the designer can better focus on the best way they could access the database with the DBMS. read more...

Normalizing a database {1}

by Polun L
The article, “Relational Database Normalization Process”, talks about normalization standing an important role in the relational database because it ensures data are logically stored, eliminates duplicated data, and increases the speed of processing data, so that database system can be accurate and efficient. When a database is normalized, the article suggests users to start from the general and work towards the specific because normalization is formed from several rules called “normal forms”. In each level of normal form, the articles shows the table of how each level of form should be and provides the explanation and summary of each form. At the end, it gives readers a review of all the important terms with full explanation such as three types of relationships between entities, primary key and foreign key. read more...

How to Design a Better Database {4}

by Penny P
The authors of this article discussed how physical database design is under-studied because database administrators have to maintain databases on a daily basis. The idea is to design databases that could adjust themselves to the characteristics of applications, such as indexing definitions or automatically gathering information from SQL workloads. The authors explain that self-tuning logical database design is needed. Most databases contains information that causes data redundancies and null values. Two main ways of maintaining efficient databases is to: 1) reduce the length of the join paths “without sacrificing the normal form based on functional dependencies” and 2) reduce the length join paths “by introducing data redundancy” (Marchi, Hacid & Petit). Null values may not be of much importance for the database designers but it matters greatly for the database programmers who performs the SQL queries. The authors come to the conclusion that good designs can’t be obtained when the database is designed, however, a better design could be made afterward. With the use of SQL workloads, they could tune the database and filter out the information that is needed or not needed. The SQL statements should be used to do three main things: minimize null values, maximize the efficiency of queries performed, and data integrity. read more...

Reference Data {Comments Off on Reference Data}

by Alexander V
Summary:

The article talks about the advances of database design and modeling and how it is a necessary skill. Then it goes on to question whether different data models have different rates of accuracy and whether the data model can have all the design information for a database. The author states that there are limits on what models can do and failure to understand the limits can lead to data management problems. He says data modeling in general is focused on the logical level, which is a good thing. The problem that the author brings to light is when there is the divide between the logical and physical database design and the data entered into the database acted to “specify a layer of design.” This type of data is called referential data and is commonly referred to as “code tables, lookup tables or domain values.” Referential data typically contains a “code” which is the  primary key and a description. Reference data has many important properties that other types of data do not have. One property would be that a “code” value usually has a definition.  The author defines reference data as “any kind of data that is used solely to categorize other data found in a database or solely for relating data in a database to information beyond the boundaries of the enterprise.” One way that referential data specifies database design is by “effectively replacing attributes in entities.” He says one of the biggest problems with referential data design is failure to assign definitions to data values. This leads to the problem of the divide between logical and physical models. Data models and databases are full of reference data tables and business users are usually left to deal with data values which are not found in the data model and are needed to understand the database. The author concludes that there needs to be better tools and techniques to deal with this problem. read more...