Logical and Physical Data Models

The strong-arm entropy exemplification (PDM) describes how the in varianceation represented in the formal selective in contouration molding is actually implemented, how the entropy- supervene upon requirements argon implemented, and how the selective information entities and their kinships be maintained. in that respect should be a mapping from a give Logical entropy baffle to the Physical entropy Model if both systemls argon mathematical functiond. The form of the Physical Data Model thrun-in out vary greatly, as shown in externalise 31. For much(prenominal) or slight purposes, an additional entity-relationship style plat testament be sufficient.The Data description Language (DDL) whitethorn withal be employ. References to message format standards (which aim message subjects and options to be handlingd) may suffice for message-oriented carrying outs. (Getting information from the LDM in form of file) Descriptions of file formats may be handlingd when file passing is the mode employ to exchange information. Inter direct systems may use a variety of techniques to exchange selective information, and thus apply several distinct set offitions in their Physical Data Model with each partition using a opposite form.The puzzle out illustrates some options for expressing the Physical Data Model and an other remand (in the real document) provides a add uping of the types of information to be captured. A soulfulnessal info ensample (or entropybase stick out) is a representation of a entropy design which takes into count the facilities and constraints of a effrontery infobase management system. In the lifecycle of a project it typically derives from a synthetical selective information standard, though it may be reverse-engineered from a attached infobase implementation.A smash physio commonssense information exercise will imply all the informationbase artifacts required to create relationships among put backs or to achieve action goals, such as indexes, constraint definitions, linking sidesteps, partiti championd panels or clusters. Analysts tail assembly usually use a soulal information regulate to calculate storage estimates it may intromit specific storage allocation dilate for a given selective informationbase system. As of 2012 seven main infobases dominate the commercial marketplace Informix, Oracle, Postgres, SQL Server, Sybase, DB2 and MySQL.Other RDBMS systems execute either to be legacy entropybases or used inside academia such as universities or further education colleges. Physical data forms for each implementation would differ signifi supporttly, non least due to vestigial operating-system requirements that may sit underneath them. For example SQL Server runs save on Microsoft Windows operating-systems, while Oracle and MySQL good deal run on Solaris, Linux and other UNIX- base operating-systems as closely as on Windows.This means that the disk requirements, security requirements and m some(prenominal) other aspects of a sensible data position will be influenced by the RDBMS that a database administrator (or an organization) chooses to use. Overview Logical data fashion sit arounds represent the gip structure of a domain of information. They be often diagrammatic in burnacter and atomic publication 18 most typically used in lineage workes that seek to capture amours of importance to an organization and how they name to cardinal a nonher. formerly validated and approved, the analytic data model can become the undercoat of a corporal data model and inform the design of a database.Logical data models should be base on the structures identified in a preceding abstract data model, since this describes the semantics of the information context, which the arranged model should excessively reflect. Even so, since the pellucid data model anticipates implementation on a specific computing system, the content of the limpid data model is adjusted to achieve indisputable efficiencies. The term Logical Data Model is some whiles used as a synonym of Domain Model or as an utility(a) to the domain model.While the 2 concepts are closely related, and shake up overlapping goals, a domain model is more focused on capturing the concepts in the fuss domain rather than the structure of the data associated with that domain. History The ANSI/SPARC three aim architecture, which shows that a data model can be an external model (or view), a conceptual model, or a physical model. This is not the only way to look at data models, scarcely it is a utilitarian way, particularly when comparing models. 1 When ANSI freshman laid out the idea of a tenacious schema in 1975,2 the choices were hierarchical and network.The relative model w here(predicate) data is described in wrong of tables and towboats had just been recognized as a data organization theory but no computer packet product existed to aver that commence. Since that succession, an object-oriented approach to data modelling where data is described in terms of forkes, designates, and associations has also been introduced. Logical data model topics Reasons for building a logical data model * Helps commonplace deriveing of backing data elements and requirements * Provides existence for designing a database Facilitates avoidance of data redundancy and thus prevent data & pipeline transaction inconsistency * Facilitates data re-use and sharing * Decreases ontogeny and maintenance time and cost * Confirms a logical influence model and helps impact analysis. mannikin benefits * Facilitates business process improvement * Focuses on requirements unconditional of technology * Facilitates data re-use and sharing * Increases return on investment * Centralizes metadata * Fosters unseamed communication amongst applications * Focuses communication for data analysis and project chemical group members * Establi shes a consistent naming schemeLogical & Physical Data Model A logical data model is sometimes incorrectly called a physical data model, which is not what the ANSI people had in mind. The physical design of a database involves deep use of particular database management technology. For example, a table/ chromatography column design could be implemented on a collection of computers, hardened in dis corresponding parts of the world. That is the domain of the physical model. Logical and physical data models are very disparate in their objectives, goals and content. Key differences illustrious below. Logical Data Model Physical Data ModelIncludes entities (tables), attributes (columns/fields) and relationships ( discovers) Includes tables, columns, keys, data types, validation rules, database triggers, stored procedures, domains, and access constraints Uses business names for entities & attributes Uses more delineate and less generic specific names for tables and columns, such as ab breviated column names, limited by the database management system (DBMS) and any company delineate standards Is independent of technology (platform, DBMS) Includes primeval keys and indices for fast data access. Is normalized to fourth normal form(4NF) May be de-normalized to meet process requirements based on the nature of the database. If the nature of the database is Online Transaction subroutineing(OLTP) or Operational Data Store (ODS) it is usually not de-normalized. De-normalization is common in Datawarehouses. A logical data model describes the data in as much detail as possible, without regard to how they will be physical implemented in the database. Features of a logical data model include * Includes all entities and relationships among them. All attributes for each entity are specified. * The primary key for each entity is specified. * Foreign keys (keys identifying the relationship between different entities) are specified. * Normalization occurs at this level. The steps for designing the logical data model are as go withs 1. Specify primary keys for all entities. 2. describe the relationships between different entities. 3. Find all attributes for each entity. 4. Resolve many-to-many relationships. 5. Normalization. The conformation below is an example of a logical data model.Logical Data Model Comparing the logical data model shown above with the conceptual data model diagram, we see the main differences between the two * In a logical data model, primary keys are present, whereas in a conceptual data model, no primary key is present. * In a logical data model, all attributes are specified within an entity. No attributes are specified in a conceptual data model. * Relationships between entities are specified using primary keys and hostile keys in a logical data model.In a conceptual data model, the relationships are simply stated, not specified, so we simply k straightaway that two entities are related, but we do not specify what attribut es are used for this relationship. Logical Model pattern Physical Model Design Figure 5. A logical data model (Information Engineering notation). You also choose to identify the cardinality and optionality of a relationship (the UML combines the concepts of optionality and cardinality into the single concept of multiplicity). Cardinality represents the concept of how many whereas optionality represents the concept of whether you must deal something. For example, it is not enough to know that customers place orders. How many orders can a customer place? N atomic number 53, angiotensin-converting enzyme, or several? Furthermore, relationships are two-way streets not only do customers place orders, but orders are placed by customers. This leads to questions like how many customers can be enrolled in any given order and is it possible to have an order with no customer mired? Figure 5 shows that customers place zero or more orders and that any given order is placed by one customer a nd one customer only.It also shows that a customer lives at one or more addresses and that any given address has zero or more customers living at it. Although the UML distinguishes between different types of relationships associations, inheritance, aggregation, composition, and dependency data modelers often arent as concerned with this issue as much as object modelers are. Subtyping, one application of inheritance, is often found in data models, an example of which is the is a relationship between Item and its two sub entities Service and Product.Aggregation and composition are much less common and typically must be implied from the data model, as you see with the part of role that Line Item takes with Order. UML dependencies are typically a software construct and therefore wouldnt appear on a data model, unless of occupation it was a very highly detailed physical model that showed how views, triggers, or stored procedures depended on Logical Data Models (LDMs) represent data ta ble (Entity Type) relationships. Logical Data Model Notations Entity Type Entity Type refers to a group of related data placed in an RDBMS (Relational Database Management Systems) table.An entity is an instance of an entity type represented as a single row in a data table. Relationships and Multiplicity Relationships illustrate how two entity types are related. Cardinality specifies how many instances of an entity relate to one instance of another entity. Physical data model represents how the model will be built in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Features of a physical data model include * Specification all tables and columns. Foreign keys are used to identify relationships between tables. * Denormalization may occur based on user requirements. * Physical considerations may cause the physical data model to be quite a different from the logical data model. * Physical data model will be different for different RDBMS. For example, data type for a column may be different between MySQL and SQL Server. Steps For Physical Data Model * Convert entities into tables. * Convert relationships into foreign keys. * Convert attributes into columns. * Modify the physical data model based on physical constraints / requirements. Physical v/s logical Entity names are now table names. * Attributes are now column names. * Data type for each column is specified. Data types can be different depending on the actual database existence used. Data theoretical account is the act of exploring data-oriented structures. Like other modeling artifacts data models can be used for a variety of purposes, from high-level conceptual models to physical data models (PDMs). Physical data modeling is conceptually similar to design tell modeling, the goal beingness to design the internal schema of a database, personation the data tables, the data columns of those tables, and the relationships between the tables. presents a partial PDM for the university you know that it isnt complete by the fact that the Seminar table includes foreign keys to tables that arent shown, and quite frankly its obvious that many domain concepts such as course and professor are clear not simulate. All but one of the boxes represent tables, the one exception is UniversityDB which lists the stored procedures implemented within the database. Because the diagram is given the emboss Physical Data Model you know that the crystallise boxes represent tables, without the diagram stereotype I would have needed to use the stereotype Table on each table.Relationships between tables are pattern using standard UML notation, although not shown in the example it would be reasonable to model composition and inheritance relationships between tables. Relationships are implemented via the use of keys (more on this below). Figure 1. A partial PDM for the university. When you are physical data modeling the following tasks are performed in an iterative manner * signalize tables. Tables are the database equivalent of straines data is stored in physical tables. As you can see in Figure 1 the university has a assimilator table to store pupil data, a Course table to store course data, and so on.Figure 1 uses a UML-based notation (this is a publicly specify profile which anyone can provide input into). If you have a physical body model in place a good start is to do a one-to-one mapping of your classes to data tables, an approach that works well in greenfield environments where you have the luxury of designing your database schema from scratch. Because this rarely happens in practice you need to be prepared to be constrained by one or more legacy database schemas which you will then need to map your classes to.In these situations it is unlikely that you will need to do much data modeling, you will simply need to learn to live with th e existing data sources, but you will need to be able to read and experience existing models. In some cases you may need to perform legacy data analysis and model the existing schema before you can start functional with it. * Normalize tables. Data normalization is a process in which data attributes within a data model are organized to increase the cohesion of tables and to reduce the coupling between tables. The first harmonic goal is to ensure that data is stored in one and only one place.This is an authorised consideration for application developers because it is incredibly difficult to stores objects in a relative database if a data attribute is stored in several places. The tables in Figure 1 are in third normal form (3NF). * Identify columns. A column is the database equivalent of an attribute, and each table will have one or more columns. For example, the Student table has attributes such as FirstName and StudentNumber. Unlike attributes in classes, which can either be uninitiate types or other objects, a column may only be a primitive type such as a char (a string), an int (integer), or a float. Identify stored procedures. A stored procedure is conceptually similar to a global method implemented by the database. In Figure 1 you see that stored procedures such as averageMark() and studentsEnrolled() are modeled as operations of the class UniversityDB. These stored procedures implement code that work with data stored in the database, in this case they calculate the average mark of a student and count the number of students enrolled in a given seminar respectively.Although some of these stored procedures clearly act on data contained in a single table they are not modeled as part of the table (along the lines of methods being part of classes). Instead, because stored procedures are a part of the overall database and not a single table, they are modeled as part of a class with the name of the database. * Apply naming conventions. Your organization sh ould have standards and guidelines applicable to data modeling, and if not you should lobby to have some put in place.As always, you should follow AMs practice of Apply Modeling Standards. * Identify relationships. There are relationships between tables just like there are relationships between classes. The advice presented relationships in UML class diagrams applies. * Apply data model patterns. Some data modelers will apply common data model patterns, David Hays (1996) book Data Model Patterns is the best file name extension on the subject. Data model patterns are conceptually closest to analysis patterns because they describe solutions to common domain issues.Hays book is a very good reference for anyone involved in analysis-level modeling, even when youre taking an object approach instead of a data approach because his patterns model business structures from a wide variety of business domains. * Assign keys. A key is one or more data attributes that uniquely identify a row in a table. A key that is two or more attributes is called a composite key. A primary key is the preferred key for an entity type whereas an alternate key (also known as a secondary winding key) is an alternative way to access rows within a table.In a physical database a key would be formed of one or more table columns whose value(s) uniquely identifies a row within a relational table. Primary keys are indicated using the stereotype and foreign keys via . Read here for more well-nigh keys. Although similar notation is used it is interesting to step the differences between the PDM of Figure 21 and the UML class diagram from which is ti based 1. Keys. Where it is common practice to not model scaffolding properties on class models it is common to model keys (the data equivalent of scaffolding). 2. Visibility. Visibility isnt modeled for columns because theyre all public.However, because most databases support access control rights you may want to model them using UML constraints, UML note s, or as business rules. withal stored procedures are also public so they arent modeled either. 3. No many-to-many associations. Relational databases are unable to natively support many-to-many associations, unlike objects, and as a result you need to resolve them via the addition of an associatory table. The closest thing to an associative table in is WaitList which was introduced to resolve the on waiting list many-to-many association depicted in the class diagram.A pure associative table is comprised of the primary key columns of the two tables which it maintains the relationship between, in this case StudentNumber from Student and SeminarOID from Seminar. Notice how in WaitList these columns have both a PK and an FK stereotype because they urinate up the primary key of WaitList while at the same time are foreign keys to the other two tables. WaitList isnt truly an associative table because it contains non-key columns, in this case the Added column which is used to ensure that the first people on the waiting list are the ones that are given the opportunity to enroll if a seat becomes available.Had WaitList been a pure associative table I would have applied the associative table stereotype to it. Logical Versus Physical Database Modeling * March 14, 2001 * By Developer. com Staff * Bio * take Email * More Articles After all business requirements have been garner for a proposed database, they must be modeled. Models are created to visually represent the proposed database so that business requirements can easily be associated with database objects to ensure that all requirements have been completely and accurately gathered.Different types of diagrams are typically produced to illustrate the business processes, rules, entities, and organisational units that have been identified. These diagrams often include entity relationship diagrams, process flow diagrams, and legion model diagrams. An entity relationship diagram (ERD) represents the entities, or gr oups of information, and their relationships maintained for a business. Process flow diagrams represent business processes and the flow of data between different processes and entities that have been defined.Server model diagrams represent a detailed picture of the database as being transformed from the business model into a relational database with tables, columns, and constraints. Basically, data modeling serves as a link between business necessarily and system requirements. Two types of data modeling are as follows * Logical modeling * Physical modeling If you are going to be working with databases, then it is important to understand the difference between logical and physical modeling, and how they relate to one another.Logical and physical modeling are described in more detail in the following subsections. * Post a exposition * Email Article * Print Article * Share Articles Logical Modeling Logical modeling deals with gathering business requirements and converting those requi rements into a model. The logical model revolves around the needs of the business, not the database, although the needs of the business are used to establish the needs of the database. Logical modeling involves gathering information about business processes, business entities (categories of data), and organizational units.After this information is gathered, diagrams and reports are produced including entity relationship diagrams, business process diagrams, and eventually process flow diagrams. The diagrams produced should show the processes and data that exists, as well as the relationships between business processes and data. Logical modeling should accurately render a visual representation of the activities and data relevant to a particular business. Note Logical modeling affects not only the way of database design, but also indirectly affects the performance and administration of an implemented database.When time is invested performing logical modeling, more options become avail able for planning the design of the physical database. The diagrams and documentation generated during logical modeling is used to line up whether the requirements of the business have been completely gathered. Management, developers, and end users alike review these diagrams and documentation to determine if more work is required before physical modeling commences. ordinary deliverables of logical modeling include * Entity relationship diagrams An Entity Relationship Diagram is also referred to as an analysis ERD.The point of the initial ERD is to provide the development team up with a picture of the different categories of data for the business, as well as how these categories of data are related to one another. * Business process diagrams The process model illustrates all the parent and child processes that are performed by individuals within a company. The process model gives the development team an idea of how data moves within the organization. Because process models illus trate the activities of individuals in the company, the process model can be used to determine how a database application interface is design. * exploiter feedback documentationPhysical Modeling Physical modeling involves the actual design of a database harmonise to the requirements that were established during logical modeling. Logical modeling mainly involves gathering the requirements of the business, with the last mentioned part of logical modeling directed toward the goals and requirements of the database. Physical modeling deals with the vicissitude of the logical, or business model, into a relational database model. When physical modeling occurs, objects are being defined at the schema level. A schema is a group of related objects in a database. A database design motility is normally associated with one schema.During physical modeling, objects such as tables and columns are created based on entities and attributes that were defined during logical modeling. Constraints ar e also defined, including primary keys, foreign keys, other unique keys, and check constraints. Views can be created from database tables to summarize data or to simply provide the user with another perspective of certain data. Other objects such as indexes and snapshots can also be defined during physical modeling. Physical modeling is when all the pieces come together to complete the process of defining a database for a business.Physical modeling is database software specific, essence that the objects defined during physical modeling can vary depending on the relational database software being used. For example, most relational database systems have variations with the way data types are represented and the way data is stored, although basic data types are conceptually the same among different implementations. Additionally, some database systems have objects that are not available in other database systems. Implementation of the Physical Model The implementation of the physical m odel is dependent on the hardware and software being used by the company.The hardware can determine what type of software can be used because software is normally developed match to common hardware and operating system platforms. Some database software capacity only be available for Windows NT systems, whereas other software products such as Oracle are available on a wider range of operating system platforms, such as UNIX. The available hardware is also important during the implementation of the physical model because data is physically distributed onto one or more physical disk drives. Normally, the more physical drives available, the better the performance of the database after the implementation.Some software products now are Java-based and can run on virtually any platform. Typically, the decisions to use particular hardware, operating system platforms, and database software are made in conjunction with one another. A logical data model describes your model entities and how t hey relate to each other. A physical data model describes each entity in detail, including information about how you would implement the model using a particular (database) product. In a logical model describing a person in a family tree, each person node would have attributes such as name(s), date of birth, place of birth, and so forthThe logical diagram would also show some kind of unique attribute or combination of attributes called a primary key that describes exactly one entry (a row in SQL) within this entity. The physical model for the person would contain implementation details. These details are things like data types, indexes, constraints, etc. The logical and physical model serve two different, but related purposes. A logical model is a way to draw your moral roadmap from a problem specification to an entity-based storage system.The user (problem owner) must understand and approve the logical model. A physical model is the roadmap from the logical model to the hardware. The developer (software owner) must understand and use the physical model. ERD Consider a hospital Patients are treated in a single ward by the doctors depute to them. Usually each long-suffering will be assigned a single doctor, but in rare cases they will have two. Heathcare assistants also attend to the patients, a number of these are associated with each ward. ab initio the system will be concerned solely with drug sermon.Each patient is required to take a variety of drugs a certain number of times per day and for varying lengths of time. The system must record details concerning patient treatment and staff payment. Some staff are paying part time and doctors and care assistants work varying amounts of overtime at varying rates (subject to grade). The system will also need to crossbreed what treatments are required for which patients and when and it should be capable of calculating the cost of treatment per week for each patient (though it is currently unclear to what use this information will be put).