Novice Learning Database (i) Design database with PowerDesigner

Description

I. Learning to use the development language for database programming, the key is to learn the SQL language, the development of language is only to provide programmers with an interface to operate the database.

Two. I am also a beginner, using the database design software is powerdesigner. The software allows users to design e-r diagrams, and the software automatically generates SQL statements that create database tables, which are inter-table constraints, based on the e-r graph.

Three. Since PowerDesigner installation software is more than 220M, can not upload, so please users themselves Baidu download.

Four. Learn the database data. I use Microsoft's Sql2000.sql Statement reference book is "SQL Server 2005 Basic Tutorial" Flash siqing Authoring, Tsinghua University Press. The textbook of database theory concept learning is the database system concept Concepts "Abraham Silberschatz, Henry F.korth, S.sudarshan higher Education Press, I use the English version." I strongly recommend this book to you. Before the university when the database was Wang Shan, Moronobu authored the book, the results of learning or confused. For the DATABASE SYSTEM concepts beginner learners only need to learn 2,3,4,6,7,14,15,16,10 chapter. The undergraduate students of the computer Department of the College of Posts and telecommunications are also the chapters.

Five. I design the database, is the third chapter of "Database" E-r Diagram implementation (beginners can also directly from my csdn download space download database generated code, directly generated the database, directly for a variety of operation exercises, after all, a small database, More suitable for beginners to operate)
Six. If you have a learning need to suggest to my designated to download my PowerDesigner design project, as an example of learning, because in the blog can only be slightly explained.

Specific design:

Open PowerDesigner Software, File menu, new model--concept data model, setting E-R diagram

When designing e-r charts, note three: 1: Property names cannot be duplicated for example: Table 1 has a property called Customer-id, then table 2 can not have the name Customeer-id this attribute, otherwise the design software will travel.
2: Let's talk about the relationship maping cardinality, and cardinality constraint
Take entity customer and loan as an example.
The mapping between customer and loan should be many to many. That is, a customer can have more than 1 or no loans, a loan can belong to one or more users. So the mapping limit for Customer->loan should be 0,n. That is, a customer can have a minimum of 0 loans, up to a maximum of (n) loans; the mapping limit for Loan->customer is (1,n). That is, a loan can be owned by at least one customer, or owned by a number of users. (Note: If a loan does not have a user, it is not in the sense of being an entity in the loan entity set). As shown


3: Let's talk about the meaning of mandatory. It was meant to be, and it was not meant to be empty here. That is, not null. Let us give an example to illustrate:
In a bank entity, the branch-city option for each bank entity should not be empty, and the assets option can be empty (the bank may have just established, no assets.) But once the bank is established, there should be a location, impossible to build in the sky! ) as shown in the following:



After we have designed the E-r diagram, select the menu tools->generate physical data Model. As shown

We can see a total of six tables. The original E-r diagram has four entities (branch, Customer,loan,account) with four relationships (branch-loan,branch-account,borrower,depositer. Since the relationship between loan and branch is many to one, and each loan entity in the loan entity is involved in the Branch-loan relationship, the table that corresponds to the relationship is merged with the tables corresponding to the entity set of the "many" party, as in the Database, as described in the 2.9.3.2 combination of tables section of the book. The relationship Branch-account is also weakened.
Careful observation can be found: the properties in the Account,loan table, more than the Account,loan entity in the E-R chart a branch-name attribute.
The above phenomenon can also be summarized as follows: The many to many relationship in the E-r diagram maps to a table in a relational database, and the many to one relationship is mapped into a relational database, representing a FOREIGN key constraint in the table of the many entity set.
Last step: In physical data design mode. That is, the Powerdesign current customer area shows the. pdm file. Select the Database menu->generatedatabase option to generate the build data table and the SQL statements for the various constraints in the data table.
Also add a note:
When we generate the physical data model, the following dialog box appears, in the DBMS drop-down list, we will select Mrosoft SQL server2000.

Novice Learning Database (i) Design database with PowerDesigner