[MUSIC] In this session we will learn the steps of relational database modeling. In this example, we use PowerDesigner Data Architect because it's one of the few that allow us to perform the three steps, the Conceptual Design, the Logical Design, and the Physical Design. You only have to make the Conceptual design diagram using the Entity-Relationship Model. And the tool performs a logical design using the relational model and with the Logical design. The Physical design is generated by the tool, where you can create a DDL file with the sentences to create a database object corresponding to physical modeling. Our conceptual data model, CDM, helps you analyze the conceptual structure of an information system to identify the principle entities to be represented. Their attributes and the relationship between them. Our conceptual data model is more abstract than a logical data model or a physical data model. Once he signed the modeling tool, what we must do on the first place is to create a concept model with entities, attributes and relationships. You create a new data model by selecting find new model. [MUSIC] Choose conceptual data model, as a model type and conceptual diagram, as a first diagram and then click OK. On the far right is a toolbox where these icons are located. The disposition in the icon of the entity and drag it to the work space. Then we click on the arrow. We start adding th object that we have previously selected, and we can locate them in the workspace. By double clicking on each entity, we can capture the name of each entity and its attributes, including the primary key. In our example, we need to create two entities, patients and decease. Click on the arrow icon on the toolbox to start adding entities, and double click on each entity to add columns. In case of patients, we have ID patient by batch four. [MUSIC] Name patient of variable char of 20. Other Patients variable char of 40 and don't forget to specify that ID patient is identifier of primary key. In the case of the Diseases, we have disease as variable char of four as an identifier. [MUSIC] Our indices is variable char of of 20, and boolean attribute contagious. To mark if a disease is contagious or not, since a patient has family members who are also patients we can translate these into the conceptual model. I said reflexive relationship of patients, as a patient can suffer from several diseases. And a disease can be suffered by several patients. We have the relationship suffer from [INAUDIBLE] of many to many. The name of the relationship will be is familiar with, and commonality is many to many. Until now, we have created our first conceptual data model. The second step during analysis, and design of relational databases is a logical model. A logical data model helps you analyze the structure of an information system independent of any specific physical database implementation. A logical data model has migrated entity identifiers and is less abstract than a conceptual data model. But does not allow you to model views, index access, and other elements that are available in the more concrete physical data model. Then once we have our concept or data model, we can generate a logical model. For this, select Tools on the main menu to generate a logical model, and then click OK. You can rename the logical diagram to something meaningful, like Logical_Model_Patients. Now, as you can see the logical model, it provides a graphical view of the structure of the information system. Where primary identifiers migrate alone, one too many relationships to become foreign keys, and many too many relationships we'll replace with an intermediate relation. It was linked with one to many relationships to the extremities which is a case of relation it's familiar with and suffer from. Now, we have our logical model. The first step on analysis and design of relational model is a physical diagram. A physical diagram provides a graphical view of your database's structure and helps to analyze its tables, including the columns, indexes and triggers, views and procedures, and the references between them. A physical data model helps you to analyze the tables, views, and other objects in a database. This model is more concrete than a conceptual or a logical models. You can model, reverse engineer, and generate for all the most popular database management systems. To generate a physical diagram within a logical model, select Tools. Then, Generate Physical Model. You can rename the model as Physical_Model_Patients, for example, and then click okay. Now, we can see our physical model of the patient system. Having the physical design, we can generate a data definition language. In other words, the secret script to create tables from our model. On the main menu, select the database option. Then generate database, then select the database management software where you are going to execute the SQL script, such as MySQL version 5. And you can choose an output path and the name of the SQL script. In our example it will be create pages dot SQL. As you can see, the SQL generated. Now, you're ready to create a database tables of the patient system. Once we have the SQL screen to create DV objects, we should save the worst base on every data model. [MUSIC] And finally, click on Exit. This SQL script was a result of the modeling process. We can use it to connect the database and create the objects, congratulations. [MUSIC]
