Experiment 5-Database Design Experiment

Experiment 5-Database Design Experiment

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:

Insert picture description here

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.

Insert picture description here

3.2. Region table region
Region: region number regionkey, region name name, remarks comment. Main code: region code regionkey.

Insert picture description here

3.3. Country
Nation: country number nationkey, country name name, region to which it belongs, remarks and comments. Main code: country code nationkey.

Insert picture description here

3.4. Supplier
Supplier: supplier number suppkey, customer name, address, nationality, telephone, remarks, etc. Main code: supplier number suppkey.

Insert picture description here

3.5. Customer Customer table
: customer number custkey, customer name name, address address, telephone phone, nationality, remarks comment. Main code: customer number custkey.

Insert picture description here

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.

Insert picture description here

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.

Insert picture description here

Finally got 7 complete tables:

Insert picture description here

4. Extension
Use the tool PowerDesigner tool to draw the ER diagram as follows:

Insert picture description here

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.