Novice learning database (1) Design the database with Powerdesigner
Description:
1. To learn to use development language for database programming, the key is to learn sql language, development language is only for programmers Provides an interface for operating the database.
2. I am also a beginner, and the database design software used is powerdesigner. Using this software, users can design E-R diagrams, and then the software will automatically generate and create database tables based on the E-R diagrams, that is, sql statements that are constrained between tables.
Three. Since the powerdesigner installation software is larger than 220M, it cannot be uploaded, so please download it from Baidu.
4. Learn the data of the database. I use Microsoft’s sql2000.sql statement. The reference book is “SQL Server 2005 Basic Tutorial” edited by Shan Siqing, Tsinghua University Press. The textbook for the study of database theory concepts is the database system concept “DATABASE SYSTEM CONCEPTS” Author: Abraham Silberschatz, Henry F. Korth, S. Sudarshan Higher Education Press, I use the English version. I strongly recommend this book to everyone. When I was in college, I used Wang Shan to study the database, and the one compiled by Sa Shixuan, but I was still at a loss after studying. For “DATABASE SYSTEM CONCEPTS” elementary learners only need to learn chapters 2, 3, 4, 6, 7, 14, 15, 16, 10. Undergraduates from the Computer Department of Beijing University of Posts and Telecommunications also learned these chapters.
5. The database I designed below is an implementation of the ER diagram in Chapter 3 of the “DATABASE” book (beginners can also download the space directly from my CSDN Download the database to generate the code, generate the database directly, and directly use it for various operation exercises. After all, a small-scale database is more suitable for beginners to operate)
6. If you need to learn, I suggest you go to the download address I specify Download my powerdesigner design project and use it as an example to learn, because only the key points can be explained in the blog.
Specific design:
打开POWERDESIGNER软件，文件菜单->新模型->概念数据模型，设置ER图
When designing an ER diagram, there are three points to note: 1: The attribute name cannot be repeated. For example: table 1 has an attribute called customer-id, Then there can be no attribute named customer-id in table 2, otherwise the design software will be on a business trip.
2: Let’s talk about the maping cardinality of the relationship and the cardinality constraint
Take the entity customer and loan as examples.
The mapping relationship between customer and loan should be many to many. That is, a customer can have more than one loan or no loan, and a loan can belong to one or more users. So the mapping limit of customer->loan should be 0,n. That is, a customer can have at least 0 loans and at most many (n) loans; the mapping limit of loan->customer is (1,n). That is, a loan can be owned by at least one customer, or owned by many users. (Note: If a loan has no users, then it has no meaning as an entity in the loan entity set).如下图所示
3: We want to talk about the meaning of Mandatory. Its original meaning is necessary, but it does not mean nothing here. That is NOT null. Let’s take another example to illustrate:
In a bank entity, the branch-city option of each bank entity should not be empty, and the assets option can be empty (the bank may have just been established and has no assets. But银行一旦建立，就应该有所在地，不可能在天上建！）如下图所示：
< br> After we have designed the ER diagram, select the menu tools->generate physical data model. As shown in the figure below
We can see a total of six tables. The original ER diagram has four entities (branch, customer, loan, account) and four relationships (branch-loan, branch-account, borrower, and depositer. Because the relationship between loan and branch is many to one, and the loan entity is centralized Each loan entity participates in the branch-loan relationship, so the table corresponding to this relationship is merged with the table corresponding to the entity set on the “many” side, as described in section 2.9.3.2 combination of tables in the “database” book As described in the theory. The relationship branch-account is also weakened in this way.
Careful observation shows that the attributes in the account and loan tables in the above figure have one branch-name more than the account and loan entities in the ER diagram. Attributes.
The above phenomenon can also be summarized as follows: The MANY TO MANY relationship in the ER diagram is mapped to a table in a relational database, and the MANY TO ONE relationship is mapped to one of the tables in the relational database, which represents the entity set of the MANY side. Foreign key constraints.
The last step: in the physical data design mode. That is, the current client area of powerdesign displays the .pdm file. Select the database menu -> generatedatabase option to generate the constructed data table, as well as the various data in the data table约束的sql语句。
还要补充说明一下：
我们在产生physical data model 时，会出现如下对话框,在DBMS 下拉列表中，我们要选择Mrosoft sql server2000。
Novice learning database (1) Design database with Powerdesigner
Description:
1. The key to learning to use the development language for database programming is to learn the sql language. The development language only provides programmers with an interface for operating the database.
2. I am also a beginner, and the database design software used is powerdesigner. Using this software, users can design E-R diagrams, and then the software will automatically generate and create database tables based on the E-R diagrams, that is, sql statements that are constrained between tables.
Three. Since the powerdesigner installation software is larger than 220M, it cannot be uploaded, so please download it from Baidu.
4. Learn the data of the database. I use Microsoft’s sql2000.sql statement. The reference book is “SQL Server 2005 Basic Tutorial” edited by Shan Siqing, Tsinghua University Press. The textbook for the study of database theory concepts is the database system concept “DATABASE SYSTEM CONCEPTS” Author: Abraham Silberschatz, Henry F. Korth, S. Sudarshan Higher Education Press, I use the English version. I strongly recommend this book to everyone. When I was in college, I used Wang Shan to study the database, and the one compiled by Sa Shixuan, but I was still at a loss after studying. For “DATABASE SYSTEM CONCEPTS” elementary learners only need to learn chapters 2, 3, 4, 6, 7, 14, 15, 16, 10. Undergraduates from the Computer Department of Beijing University of Posts and Telecommunications also learned these chapters.
5. The database I designed below is an implementation of the ER diagram in Chapter 3 of the “DATABASE” book (beginners can also download the space directly from my CSDN Download the database to generate the code, generate the database directly, and directly use it for various operation exercises. After all, a small-scale database is more suitable for beginners to operate)
6. If you need to learn, I suggest you go to the download address I specify Download my powerdesigner design project and use it as an example to learn, because only the key points can be explained in the blog.
Specific design:
打开POWERDESIGNER软件，文件菜单->新模型->概念数据模型，设置ER图
When designing an ER diagram, there are three points to note: 1: The attribute name cannot be repeated. For example: table 1 has an attribute called customer-id, Then there can be no attribute named customer-id in table 2, otherwise the design software will be on a business trip.
2: Let’s talk about the maping cardinality of the relationship and the cardinality constraint
Take the entity customer and loan as examples.
The mapping relationship between customer and loan should be many to many. That is, a customer can have more than one loan or no loan, and a loan can belong to one or more users. So the mapping limit of customer->loan should be 0,n. That is, a customer can have at least 0 loans and at most many (n) loans; the mapping limit of loan->customer is (1,n). That is, a loan can be owned by at least one customer, or owned by many users. (Note: If a loan has no users, then it has no meaning as an entity in the loan entity set).如下图所示
3: We want to talk about the meaning of Mandatory. Its original meaning is necessary, but it does not mean nothing here. That is NOT null. Let’s take another example to illustrate:
In a bank entity, the branch-city option of each bank entity should not be empty, and the assets option can be empty (the bank may have just been established and has no assets. But银行一旦建立，就应该有所在地，不可能在天上建！）如下图所示：
< br> After we have designed the ER diagram, select the menu tools->generate physical data model. As shown in the figure below
We can see a total of six tables. The original ER diagram has four entities (branch, customer, loan, account) and four relationships (branch-loan, branch-account, borrower, and depositer. Because the relationship between loan and branch is many to one, and the loan entity is centralized Each loan entity participates in the branch-loan relationship, so the table corresponding to this relationship is merged with the table corresponding to the entity set on the “many” side, as described in section 2.9.3.2 combination of tables in the “database” book As described in the theory. The relationship branch-account is also weakened in this way.
Careful observation shows that the attributes in the account and loan tables in the above figure have one branch-name more than the account and loan entities in the ER diagram. Attributes.
The above phenomenon can also be summarized as follows: The MANY TO MANY relationship in the ER diagram is mapped to a table in a relational database, and the MANY TO ONE relationship is mapped to one of the tables in the relational database, which represents the entity set of the MANY side. Foreign key constraints.
The last step: in the physical data design mode. That is, the current client area of powerdesign displays the .pdm file. Select the database menu -> generatedatabase option to generate the constructed data table, as well as the various data in the data table约束的sql语句。
还要补充说明一下：
我们在产生physical data model 时，会出现如下对话框,在DBMS 下拉列表中，我们要选择Mrosoft sql server2000。