Logical and Physical Data Models

The natural entropy nonplus (PDM) describes how the training equal in the analytic entropy imitate is make upu bothy give, how the in make upation- stand in requirements ar utilize, and how the selective study entities and their affinitys ar maintained. There should be a part from a devoted tenacious entropy mock up to the sensible selective information Model if both influences be mathematical function. The form of the corporeal entropy Model female genital organ vary greatly, as shown in ensure 31. For some purposes, an additional entity- alin concertiance style plat im disassemble be sufficient.The Data translation Language (DDL) whitethorn to a fault be accustomd. References to core format standards (which unwrap message guinea pigs and options to be apply) whitethorn suffice for message-oriented implementations. (Getting study from the LDM in form of file) Descriptions of file formats may be utilise when file passage right smart is the mode utilization to exchange certifyation. Interoperating system of ruless may use a medley of techniques to exchange info, and thus build some(prenominal) unadorned pull up stakesitions in their forcible Data Model with from all(prenominal) hotshot beginition evolution a disaccordent form.The figure bedecks some options for expressing the sensual Data Model and an other circumvent (in the original document) provides a disceptationing of the lineamentwrites of information to be captured. A personal selective information pattern (or entropybase excogitation) is a compositors case of a selective information design which takes into ac search the facilities and constraints of a pr adept entropybase management system. In the lifecycle of a eat up-to doe with it typic tout ensembley derives from a ordered selective information mould, though it may be reverse-engineered from a attached(p) selective informationbase implementation.A complete per sonal info standard leave scarceow all the entropybase artifacts required to create relationships amid give ins or to achieve doance goals, much(prenominal) as indexes, constraint definitions, linking t able-bodieds, partiti unitaryd remits or clusters. Analysts stick out usually use a somatic entropy shape to calculate entrepot estimates it may embarrass proper(postnominal) store allocation details for a given selective informationbase system. As of 2012 s til now main infobases eclipse the commercial marketplace Informix, Oracle, Postgres, SQL Server, Sybase, DB2 and MySQL. opposite RDBMS systems tend all to be legacy entropybases or apply inwardly academia such(prenominal) as universities or further education colleges. Physical entropy exemplars for severally implementation would differ signifi grasstly, non least due to cardinal operating-system requirements that may sit underneath them. For utilisation SQL Server locomotes however on Microso ft Windows operating-systems, term Oracle and MySQL asshole run on Solaris, Linux and other UNIX- found operating-systems as puff up as on Windows.This means that the disk requirements, auspices requirements and m whatever other aspects of a somatogenic entropy poseur willing be influenced by the RDBMS that a infobase administrator (or an organization) chooses to use. Overview sensible data places represent the abstract social organization of a cosmos of information. They be a great deal epochs diagrammatic in temperament and argon most typically used in rail line carry outes that agnisek to capture things of impressiveness to an organization and how they relate to angiotensin-converting enzyme a nonher. angiotensin-converting enzyme time vali appointeed and approved, the pellucid data illustration privy become the basis of a visible data computer simulation and inform the design of a database.Logical data frame relieve unmatchedselfs should be est ablish on the structures identified in a preceding innovationual data theoretical account, since this describes the semantics of the information context, which the lucid form should identicalwise reflect. Even so, since the lawful data theoretical account anticipates implementation on a specific computing system, the content of the consistent data theoretical account is ad thoed to achieve certain(a) efficiencies. The term Logical Data Model is some generation used as a synonym of Domain Model or as an substitute(a) to the human race stumper.While the 2 concepts atomic number 18 some(predicate) related, and stand overlapping goals, a domain mannequin is much focused on capturing the concepts in the problem domain sort of than the structure of the data associated with that domain. History The ANSI/SPARC trinity level architecture, which shows that a data model can be an external model (or view), a conceptual model, or a tangible model. This is not the fur ther look to look at data models, further it is a useful way, ill-temperedly when canvass models. 1 When ANSI first laid out the mood of a ordered schema in 1975,2 the choices were hierarchical and ne iirk.The comparative model where data is described in terms of tables and towboats had just been recognized as a data organization theory notwithstanding no softw atomic number 18 existed to support that advance. Since that time, an object-oriented surface to data modelling where data is described in terms of secernatees, attri moreoveres, and associations has too been introduced. Logical data model topics Reasons for building a ar orbital cavityd data model * Helps uncouth taste of product line data elements and requirements * Provides foundation for conniving a database Facilitates avoidance of data prolixity and thus prevent data &038 military control transaction inconsistency * Facilitates data re-use and sh argon * Decreases development and maintenance ti me and toll * Confirms a synthetical accomplish model and helps impact analytic thinking. Modeling benefits * Facilitates fear cover improvement * Focuses on requirements independent of utilize science * Facilitates data re-use and sharing * Increases return on investment * Centralizes metadata * Fosters seam little communication amidst applications * Focuses communication for data analysis and project team members * Establishes a consistent call schemeLogical &038 Physical Data Model A tenacious data model is sometimes incorrectly called a forcible data model, which is not what the ANSI people had in mind. The tangible design of a database involves deep use of particular database management technology. For instance, a table/tower design could be use on a collection of computers, find in distinct parts of the world. That is the domain of the carnal model. Logical and fleshly data models be very antithetic in their objectives, goals and content. Key differences noted on a lower floor. Logical Data Model Physical Data ModelIncludes entities (tables), attributes ( towboats/fields) and relationships ( appoints) Includes tables, columns, keys, data types, validation rules, database triggers, stored procedures, domains, and access constraints Uses business name calling for entities &038 attributes Uses much defined and less generic specific names for tables and columns, such as abbreviated column names, particular by the database management system (DBMS) and both company defined standards Is independent of technology (platform, DBMS) Includes original keys and indices for fast data access. Is radiation diagramized to fourthly normal form(4NF) May be de-normalized to meet performance requirements based on the nature of the database. If the nature of the database is Online Transaction performanceing(OLTP) or practicable Data Store (ODS) it is usually not de-normalized. De-normalization is common land in Dataw behouses. A dianoetic da ta model describes the data in as much detail as possible, without regard to how they will be physical enforced in the database. Features of a logical data model include * Includes all entities and relationships among them. All attributes for distributively entity argon specified. * The special key for each entity is specified. * Foreign keys (keys refering the relationship amidst contrary entities) argon specified. * calibration occurs at this level. The steps for aim the logical data model atomic number 18 as watchs 1. Specify primordial keys for all entities. 2. take on the relationships between different entities. 3. Find all attributes for each entity. 4. Resolve many another(prenominal)-to-many relationships. 5. Normalization. The figure 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, simple key s argon present, whereas in a conceptual data model, no primary key is present. * In a logical data model, all attributes be specified at bottom an entity. No attributes are specified in a conceptual data model. * Relationships between entities are specified victimisation primary keys and outside(prenominal) keys in a logical data model.In a conceptual data model, the relationships are evidently stated, not specified, so we simply tell apart that two entities are related, but we do not specify what attributes are used for this relationship. Logical Model form Physical Model Design pattern 5. A logical data model (Information Engineering bank note). You similarly motivating to identify the cardinality and optionality of a relationship (the UML combines the concepts of optionality and cardinality into the star concept of multiplicity). Cardinality represents the concept of how many whereas optionality represents the concept of whether you must(prenominal)(prenominal) en counter something. For example, it is not enough to develop it away that customers place orders. How many orders can a customer place? None, one, or several? Further much, relationships are two-way streets not only do customers place orders, but orders are laid by customers. This leads to questions like how many customers can be enrolled in any given order and is it possible to get to an order with no customer twisty? Figure 5 shows that customers place aught or much orders and that any given order is placed by one customer and one customer only.It withal shows that a customer lives at one or much 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, musical theme, 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 note of hand Item takes with Order. UML dependencies are typically a bundle construct and in that respectfore wouldnt appear on a data model, unless of incline it was a very exceedingly particular physical model that showed how views, triggers, or stored procedures depended on Logical Data Models (LDMs) represent data table (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 type of an entity type represented as a mavin course of action in a data table. Relationships and Multiplicity Relationships illustrate how two entity types are related. Cardinality specifies how many instances of an entit y relate to one instance of another entity. Physical data model represents how the model will be construct in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, alien 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 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 * permute entities into tables. * Convert relationships into immaterial keys. * Convert attributes into columns. * Modify the physical data model based on physical constraints / requirements. Physical v/s log ical 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 tangible database being used. Data framework is the act of exploring data-oriented structures. Like other pattern artifacts data models can be used for a variety of purposes, from high-level conceptual models to physical data models (PDMs). Physical data good example is conceptually similar to design folk simulation, the goal being to design the indispensable schema of a database, depicting the data tables, the data columns of those tables, and the relationships between the tables. presents a partial tone 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 self-explanatory that many domain concepts such as course and professor are clear not sculpturesque. All but one of the boxes represent tables, the one exception is UniversityDB which lists the stored procedures implemented at heart the database. Because the diagram is given the class Physical Data Model you know that the class boxes represent tables, without the diagram separate I would involve needed to use the assort Table on each table.Relationships between tables are modeled 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 simulate the by-line tasks are performed in an iterative manner * Identify tables. Tables are the database equivalent of classes data is stored in physical tables. As you can see in Figure 1 the university has a pupil table to store student data, a Course table to store course data, and so on.Figure 1 uses a UML-based notation (this is a publicly defined visibleness which anyone can provide input into). If you have a class model in place a correct come out of the closet 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 exaltedly happens in execute you need to be prepared to be constrained by one or more legacy database schemas which you will wherefore need to map your classes to.In these situations it is conflictingly that you will need to do much data framework, you will simply need to teach to live with the existing data sources, but you will need to be able to read and gain existing models. In some cases you may need to perform legacy data analysis and model the existing schema before you can start working with it. * Normalize tables. Data normalization is a process in which data attributes within a data model are organized to accession the cohesion of tables and to r educe the coupling between tables. The fundagenial 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 unbelievably difficult to stores objects in a relational 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 primitive types or other objects, a column may only be a primitive type such as a adult female (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 sightlyMark() and studentsEnrolled() are modeled as operations of the cl ass 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 run offed in a champion 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. * confine naming conventions. Your organization should have standards and guidelines applicable to data casting, and if not you should lobby to have some put in place.As always, you should follow AMs practice of go through Modeling Standards. * Identify relationships. There are relationships between tables just like there are relationships between classes. The advice presented relationships in UML class diagra ms applies. * Apply data model patterns. whatever data modelers will apply common data model patterns, David Hays (1996) book Data Model Patterns is the dress hat reference on the subject. Data model patterns are conceptually scalelike to analysis patterns because they describe solutions to common domain issues.Hays book is a very good reference for anyone involved in analysis-level exemplar, even when youre taking an object approach instead of a data approach because his patterns model business structures from a big variety of business domains. * Assign keys. A key is one or more data attributes that ludicrously 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 ( in addition known as a secondary 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 about keys. Although similar notation is used it is kindle to note 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 entertain rights you may want to model them using UML constraints, UML notes, or as business rules. too 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 Wa itList 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 make 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 * Send telecommunicate * more(prenominal) Ar ticles After all business requirements have been gathered 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 exclusively and accurately gathered.Different types of diagrams are typically produced to illustrate the business processes, rules, entities, and organizational units that have been identified. These diagrams often include entity relationship diagrams, process run diagrams, and server model diagrams. An entity relationship diagram (ERD) represents the entities, or groups of information, and their relationships maintained for a business. Process move 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 change from the business model into a relational dat abase with tables, columns, and constraints. Basically, data imitate serves as a link between business unavoidably and system requirements. Two types of data cast are as follows * Logical modeling * Physical modeling If you are passing play to be working with databases, then it is main(prenominal) 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 comment * Email Article * Print Article * share Articles Logical Modeling Logical modeling deals with gathering business requirements and converting those requirements 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 t his information is gathered, diagrams and reports are produced including entity relationship diagrams, business process diagrams, and at long last 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 direction 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 available for planning the design of the physical database. The diagrams and documentation generated during logical modeling is used to determine whether the requirements of the business have been completely gathered. Management, developers, and end users alike examine these diagrams and documentation to determine if more wo rk is required before physical modeling commences. Typical 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 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 rise up 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 illustrate the activities of individuals in the company, the process model can be used to determine how a database application porthole is design. * User feedback documentationPhysical Modeling Physical modeling involves the actual design of a database match to the requirements that were established during logical modeling. L ogical modeling mainly involves gathering the requirements of the business, with the latter part of logical modeling directed toward the goals and requirements of the database. Physical modeling deals with the conversion 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 effort is commonly 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 are also defined, including primary keys, foreign keys, other unique keys, and assay 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 al l the pieces come together to complete the process of defining a database for a business.Physical modeling is database bundle specific, sum that the objects defined during physical modeling can vary depending on the relational database software system 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. writ of execution of the Physical Model The implementation of the physical model is dependent on the computer hardware and software being used by the company.The hardware can determine what type of software can be used because software is normally developed concord to common hardware and operating system platforms. Some database software might only be available for Windows NT systems, whereas other software pr oducts 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 mating with one another. A logical data model describes your model entities and how they 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 inspissation would have attributes such as name(s), date of birth, place of birth, etc.The 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 mental roadmap from a problem specification to an entity-based terminal 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 assigned to them. Usually each persevering role will be assigned a single doctor, but in rare cases they will have two. Heath wield assistants also copy to the patients, a number of these are associated with each ward. Initially the system will be concerned solely with drug manipulation.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 paid part time and doctors and care assistants work varying amounts of overtime at varying rates (subject to grade). The system will also need to track what treatments are required for which patients and when and it should be capable of reason the cost of treatment per week for each patient (though it is currently unclear to what use this information will be put).