Experiment 5-Database Design Experiment
- 1. Experiment topic
- 2. Experimental content and requirements
- Three, experimental steps
- 1. Analyze the problem
- 2. Conceptual structure design
- 3. Logical structure design
- Fourth, the experimental experience
1. Experiment topic
Database design experiment
2. Experimental content and requirements
Master the basic methods of database design and database design tools.
Master the basic steps of database design, including database conceptual structure design, logical structure design, physical structure design, and database model SQL statement generation. Ability to use database design tools for database design.
Three, experimental steps
One supplier can supply multiple parts, and one part can also have multiple suppliers. One customer order can order parts from multiple suppliers. Customers and suppliers belong to different countries, and countries are divided into regions based on five continents and eight oceans.
1. Analyze the problem
According to the actual semantics, the relationship between entities is analyzed, and the one-to-one, one-to-many and many-to-many relationships between the entities are determined.
2. Conceptual structure design
Draw ER diagram:
3. Logical structure design
According to the rules of transforming the conceptual structure into the logical structure in the database design principle, each entity is transformed into a relationship, and the many-to-many relationship is also transformed into a relationship. Therefore, the logical structure of the database is designed according to the above ER diagram, and the Mysql database is used, abstracted in the form of table:
3.1. Parts list part
Part: part number partkey, part name name, part manufacturer mfgr, brand brand, type, size, retail price, retail price, packaging container, remarks comment. Master code: part number partkey.
3.2. Region table region
Region: region number regionkey, region name name, remarks comment. Main code: region code regionkey.
Nation: country number nationkey, country name name, region to which it belongs, remarks and comments. Main code: country code nationkey.
Supplier: supplier number suppkey, customer name, address, nationality, telephone, remarks, etc. Main code: supplier number suppkey.
3.5. Customer Customer table
: customer number custkey, customer name name, address address, telephone phone, nationality, remarks comment. Main code: customer number custkey.
3.6. Order table
Order: order number orderkey, order status status, total order price totalprice, order date order-date, order priority orderpriority, bookkeeper clerk, shipping priority video priority, remark comment. Main code: order number orderkey.
3.7. Order item table
Lineitem: order item number linenumber, ordered part number partkey, ordered parts supplier number suppkey, number of parts quantity, total price of parts extendedprice, discount discount, tax rate tax, return flag returnflag, etc. Main code: line item number linenumber.
Finally got 7 complete tables:
Use the tool PowerDesigner tool to draw the ER diagram as follows:
Fourth, the experimental experience
The focus of this experiment lies in the design of the conceptual structure and the design of the logical structure; although the logical structure design can be transformed from the conceptual structure according to certain rules, because the conceptual structure is usually more abstract, less consideration is given to more details and less consideration. Many details, so the converted logical structure needs further adjustment and optimization.
This time the conceptual structure selected the purchasing, sales and customer management application database that has been used in the experimental guide. It is more complicated, but it is better to learn the conceptual structure design and the logical structure design.