为了设计一个体面的数据库(当然,你需要首先了解数据库设计的基本内容),我重新阅读了这本书和一些在线文章,并编制了一个简短的步骤清单,下文将更详细地说明这些步骤:
(书中描述了很多步骤: Database Systems - 设计、执行和管理(第九版) , 并且这些页码也指着这些页码,但我将尽量在这里描述,并在以后几天内编辑这个答案,使其更加完整)
- Create a detailed narrative of the organization’s description of operations.
- Identify the business rules based from the description of operations.
- Identify the main entities and relationships from the business rules.
- Translate entities/relationships to EER model
- Check naming conventions
- Map ERR model to logical model (pg 400)*
- Normalize logical model (pg 179)
- Improve DB design (pg 187)
- Validate Logical Model Integrity Constraints (pg 402) (like length etc.)
- Validate the Logical Model against User Requirements
- Translate tables to mySQL code (in workbench translate EER to SQL file using export function then to mySQL)
*你可以跳过这一步 如果你使用工作台 和工作 和工作 ER模型的工作 你设计在那里。
1. Describe the workings company in great detail. If you are creating personal project describe it in detail if you are working with a company ask for documents describing their company as well as interviewing the employees for information (interviews might generate inconsistent information make sure to check with supervisers which information is more important for design)
2. Look at the gathered information and start generating rules from them make sure to fill in any information gaps in your knowledge. Confirm with supervisers in the company before moving on.
3. Identify the main entities and relationships from the business rules. Keep in mind that during the design process, the database designer does not depend simply on interviews to help define entities, attributes, and relationships. A surprising amount of information can be gathered by examining the business forms and reports that an organization uses in its daily operations. (pg 123)
4. If the database is complex you can break down the ERD design into followig substeps
i) Create External Models (pg 46)
ii) Combine External Models to form Conceptual Model (pg 48)
Follow the following recursive steps for the design (or for each substep)
I. Develop the initial ERD.
II. Identify the attributes and primary keys that adequately describe the entities.
III. Revise and review the ERD.
IV. Repeat steps until satisfactory output
You may also use entity clustering to further simplify your design process.
Describing database through ERD:
Use solid lines to connect Weak Entities (Weak entities are those which cannot exist without parent entity and contain parents PK in their PK).
Use dashed lines to connect Strong Entities (Strong entities are those which can exist independently of any other entity)
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\"\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\你的姓名是否遵循你的命名公约。我曾经有建议命名公约 在这里命名公约的建议 但人们并不真正喜欢它们。 我建议遵循你自己的标准 或者在网上寻找一些命名公约。 请发表评论, 如果你发现一些命名公约非常有用的话。 \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
6.
Logical design generally involves translating the ER model into a set of relations (tables), columns, and constraints definitions.
使用这些步骤将ER转换为逻辑模型:
- Map strong entities (entities that dont need other entities to exist)
- Map supertype/subtype relationships
- Map weak entities
- Map binary relationships
- Map higher degree relationships
7. 使逻辑模型正常化。您还可以使逻辑模型失去正常化,以便获得某些理想的特性。 (如改进性能)
<强>8.
Refine Attribute Atomicity -
It is generally good practice to pay attention to the atomicity requirement. An atomic attribute is one that cannot
be further subdivided. Such an attribute is said to display atomicity. By improving the degree of atomicity, you also gain querying flexibility.
Refine Primary Keys as Required for Data Granularity - Granularity refers to the level of detail represented by the values stored in a table’s row. Data stored at their lowest
level of granularity are said to be atomic data, as explained earlier. For example imagine ASSIGN_HOURS attribute to represent the hours worked by a given employee on a given project. However, are
those values recorded at their lowest level of granularity? In other words, does ASSIGN_HOURS represent the hourly
total, daily total, weekly total, monthly total, or yearly total? Clearly, ASSIGN_HOURS requires more careful definition. In this case, the relevant question would be as follows: For what time frame—hour, day, week, month, and
so on—do you want to record the ASSIGN_HOURS data?
For example, assume that the combination of EMP_NUM and PROJ_NUM is an acceptable (composite) primary key
in the ASSIGNMENT table. That primary key is useful in representing only the total number of hours an employee
worked on a project since its start. Using a surrogate primary key such as ASSIGN_NUM provides lower granularity
and yields greater flexibility. For example, assume that the EMP_NUM and PROJ_NUM combination is used as the
primary key, and then an employee makes two “hours worked” entries in the ASSIGNMENT table. That action violates
the entity integrity requirement. Even if you add the ASSIGN_DATE as part of a composite PK, an entity integrity
violation is still generated if any employee makes two or more entries for the same project on the same day. (The
employee might have worked on the project a few hours in the morning and then worked on it again later in the day.)
The same data entry yields no problems when ASSIGN_NUM is used as the primary key.
试着回答问题:“谁可以使用表格,表格的哪些部分可以使用?” ETC。
请留下建议或链接,在下面的评论中作更清楚的描述。
