Today, I will introduce the physical data model. The physical data model will be used frequently in the future. The physical model corresponds to a specific database type.
1. Open PowerDesigner, then click File --> New Model and select the physical data model as shown in the figure below
When the physical data model is established, there are many types of databases to choose from:
2. Create the main page as shown in the figure, but the button on the right is slightly different from the conceptual model. The three most commonly used physical models are table (table), view (view), reference (relationship), as shown in the figure below:
3. Create a student information table, and then click Columns to set as shown in the figure below. It is very simple. The ones that need to be noted are P (primary primary key), F (foreign key foreign key), M (mandatory, which means it cannot be empty):
Set the auto-increment of the student number (the auto-increment in MYSQL is this AUTO_INCREMENT), double-click the primary key column to set:
Right-click and select "SQL PREVIEW" to preview sql:
4. Let's create a new class information table:
5. Click the Reference button on the right. Because the class is one-to-many for students, the mouse is pulled from the student to the class as shown in the figure below. The student information table will change, and a row will be added to the student information table. This row is The primary key of the class table is used as the foreign key of the student table, linking the class table with the student table.
6. Double-click a line in the middle to display the following picture, just modify the name and code
7. In order to establish a many-to-many relationship, a teacher information table needs to be established. Because the many-to-many relationship of the physical model requires an intermediate table to connect, as shown in the figure below, only one field, primary key, and self-increment are set:
It should be noted that the side with arrows is one, and the side without arrows is many, that is, the one-to-many many-to-one relationship needs to be clarified. Students can also have many teachers, and teachers can also have many students, so students and teachers Both can be the subject.
8. To export the sql statement, click Generate Database of the Database button or press ctrl+G
In the Options tab, you can personalize the selection and configuration of sql scripts, such as canceling foreign keys, removing drop statements, etc.
You can preview the sql script to be generated in the Preview tab. Select the table to be exported in Selection, then click Apply and Confirm:
9. Click OK, you can generate the sql script in the location you specify