1 to determine the entities and relationships
a) the design of macroeconomic behavior. What do you use this database? For example, want to manage employee information.
b) Determine the entities. For a series of actions, identify the management information related to the subject areas. This will become a table. For example, hiring employees, assign specific departments to identify skill levels.
c) identify relationships. Looking at behavior to determine what kind of relationship between tables. For example, in the department and there is a relationship between employees. Name to this relationship.
d) refinement of behavior. You start from the macro-behaviors, these behaviors are now double-check to see what actions can be converted to micro behavior. For example, the management of employee information can be broken down as follows:
● increase the presence of new employees to modify employee information ● ● delete transferred employees
e) Identify business rules. Look at your business rules to determine what you want to take. For example, there may be such a rule, a department has one and only one department heads. These rules are designed to the database structure.
ACME is a small company, with offices in five locations. Currently, there are 75 employees. Company plans to rapidly expand the scale division of the nine departments, each department has its own leadership.
To help search for new employees, the personnel department planned 68 kinds of skills, personnel management to prepare for the future. Staff have been recruited into, the level of expertise of each skill are identified.
ACME's definition of the macro-behavior of some macroscopic behavior include:
Dismissal of staff recruitment ● ● ● ● manage employee personal information management company ● management skills required information which employees have which skills ● ● Management Information Management Office Information
Determine the entities and relationships
We can determine the subject area to store the information (tables) and their relationships, and create a description based on macroscopic behavior and the chart.
We use boxes to represent the table, with the diamonds represent relationship. We can determine what is one to many relationship, one to one, and many to many.
This is a draft ER, will be detailed later.
Detailed behavior of the macro-behaviors based on the following micro-macro behavior above form:
● ● add or delete a staff office ● add or remove a listing of all employees in a department to add a skill ● ● increase the skills of an employee to determine an employee's skills ● ● determine the skill level of each employee ● Make sure all have the same level of skills of employees of an employee's skill level changes ●
These micro-behavior can be used to determine which table or relationship.
Business rules to determine the business rules used to determine the many, one, and many to many relationships.
Related business rules may include:
● There are five offices; extended to allow up to 10.
● Employees can change departments or offices ● Each department has a department heads ● Each office has at most three phone numbers ● Each telephone number has one or more extensions ● staff have been recruited into, the skills of each professional level have been identified.
● Each employee has the skills ● 3-20 an employee may be arranged in a single office, or may not arrange office.
2 Identify the required data
To determine the required data:
1 Identify supporting data
2 lists all the data to be tracked. Description table (theme) of the data to answer these questions: who, what, where, when, and why
3 up data for each table
4 lists each table now, it seems appropriate data available
5 set of data for each relationship
6. If there is, for each relationship listed in the applicable data
Identify supporting data
You sure will be a table of supporting data in the field name. For example, the following data for the table Employee, table Skill, Table Expert In.
If these data into a chart drawn, like:
● In determining the supporting data, please be sure to refer you to determine the macroscopic behavior before, to know how to use the data.
● For example, if you know you need all employees sorted by last name of the list, make sure that you will support the data broken down into first and last name, more than simply providing a name will be better.
● The name you choose the best consistency. This will be easier to maintain database and easier to read the output of the report.
● For example, if you are in some places with an abbreviated name Emp_status, you should not be in another place using the full name (Empolyee_ID). Instead, these names should be Emp_status and Emp_id.
● whether the data correspond with the correct table does not matter, you can set according to their preferences. The next section, you will pass the test to determine this.
3 standardized data
Standardization is to eliminate data redundancy and your data and ensure the correct table or relationship associated with a series of tests. There are five tests. In this section, we will discuss the frequently used three.
For more information on standardized tests, refer to books on database design.
Standardized format for standardized data format is commonly used standardized test methods. Your data through the first pass after the test, it is considered to be the first to achieve a standardized format; second time through the test, a standardized format to the second; test by the third time, to the third standardized format.
How standard format:
1. Lists data
2. Identify at least one table for each key. Each table must have a primary key.
3. Determine the relationships of the key. relationships of the key is the key to connecting the two tables.
4. Check the list of supporting data calculated data. Calculated data is usually not stored in the database.
5. On the first pass of the data in a standardized format:
6. From the tables and relationships to remove duplicate data.
7. To remove the data you create one or more tables and relationships.
8. On the second pass of the data in a standardized format:
9. With more than one key to determine the tables and relationships.
10. Remove only part of the data depends on the key.
11. To remove the data you create one or more tables and relationships.
12. Data on the third time in a standardized format:
13. Remove tables or relationships are dependent on other data, and not the key.
14. To remove the data you create one or more tables and relationships.
You start with the key data in standardized (test data) first, simply list the data for each table to determine a unique primary key. This key can be a field or several fields (key chain) components.
A primary key is the only distinction between a table of a group of field lines. Employee table's primary key is the Employee ID field. Works In relationship to the primary key, including Office Code and the Employee ID field. To the database is given a key for each relationship, each of which it is connected from the table to extract the key generation.
On the first pass of the data in a standardized format ● ● remove duplicate set of standardized format to test the first pass, remove the duplicate group them into their respective a table.
● In the following example, Phone Number can be repeated. (A staff member can have more than one phone number.) Remove the duplicate group, create a new table called Telephone. Telephone and Office created in a file called Associated With the relationship.
On the second pass of the data in a standardized format ● remove those who do not depend on the key data.
● Only those who have more than one key of the tables and relationships. To test the second time a standardized format, except for those who do not rely on any of the key data (composed of all key fields).
● In this case, the original Employee table has a key composed of two fields. Some data does not depend on the key; for example, department name only rely on one key (Department ID). Therefore, Department ID, Employee data does not depend on the other it should be moved to a new table named Department and the Employee and Department Assigned To create a file called the relationship.
Data on the third time in a standardized format ● remove those who do not directly depend on the key.
● To test the third time a standardized format, except for those not directly dependent on the key, but depends on other data.
● In this case, the original Employee table are dependent on its key (Employee ID) data. However, office location and office phone depends on other fields, that Office Code. They are not directly dependent on the Employee ID key. This set of data, including the Office Code, moved to a new table named Office and Office for Employee and create a file called Works In a relationship.
4. Consider the relationship
When you have completed the standardization process, your design is almost complete. You need to do is to consider the relationship.
Consider the relationship with the data set some of your relationship may contain data. This often occurs in many to many relationships.
In such cases, the relationship into a table. The key relationship is still a table in the key.
Considerations no data no data to realize the relationship relationship, you need to define a foreign key. External key is another table containing the primary key of one or more fields. Foreign key allows you to connect multiple tables of data.
There are some basic principles can help you decide where to put these keys:
In the many-many relationship, "one" in the primary key on the "more" in the. In this case, the external button on the Employee table.
One in one relationship, the foreign key can be put into any table. If you have to put one side, and not on the other side, should be placed to the side. In this case, the external key (Head ID) in the Department table, because this is necessary.
Many-in-many relationship, with two external buttons to create a new table. The old table has been saved by this new table into contact.
5 test design
Before you complete the design, you need to make sure it meets your needs. Check your defined at the outset, recognizing that you can get all the data needed to conduct:
● You can find a path to wait until all the information you need to do?
● Does the design meet your needs?
● All required data are available?
If you have answered the above questions, you have almost completed design.
Final design of the final design looks like this:
Design a database table attribute database design needs to determine what tables, each table has any field. This section discusses how to specify the properties of each field.
For each field, you must determine where the field name, data type and size, whether to allow NULL values, and whether you want the database to restrict the allowed field values.
Select the field name field name can be letters, numbers, or any combination of symbols. However, if the field name includes letters, numbers or underscores, beginning with the letter or not, or if it is a key (see key table), when using the field name must be enclosed in double quotes .
Select the data type for the field
SQL Anywhere supports the data types include:
Integer (int, integer, smallint)
Decimal (decimal, numeric)
Floating point (float, double)
Character (char, varchar, long varchar)
Binary data type (binary, long binary)
Date / time types (date, time, timestamp)
On data types, please see "SQL Anywhere data types" section. Field's data type affect the maximum field size. For example, if you specify SMALLINT, this field is an integer can hold 32,767. INTEGER integer can hold 2,147,483,647. In terms of CHAR, the field must specify the maximum value.
Long binary data type can be used to save such images in the database (such as bitmaps) or editing text documents. These types of information are often referred to as binary large objects, or BLOBS.
For a complete description of each data type, see "SQL Anywhere data type."
NULL and NOT NULL
If a field value is required and you leave the field defined as NOT NULL. Otherwise, the field value can be NULL values, which can have a null value. SQL the default is to allow null values; you should explicitly define the field as NOT NULL, unless you have good reason to be set to allow null values.
NULL value for a complete description, see "NULL value". For the contrast usage, see "Search conditions".
Despite the field's data type limits the data in the field can exist (for example, can store numbers or dates), you may want to go further to restrict the allowed values.
You can specify a "CHECK" constraint to limit the value of any field. You can use the WHERE clause can appear in any valid conditions to constrain the allowed value, although most of the CHECK constraint using the BETWEEN or IN condition.
For more information about the conditions of validity, see "Search conditions". About how to specify the table and field constraints, see "Ensuring Data Integrity".
Case examples in a database table named department, the field is dept_id, dept_name, dept_head_id. Defined as follows:
Note that each field is designated as "not null". In this case, each record in the table all the fields of data are required.
Select the primary key and foreign key primary key uniquely identifies each record in the table fields. How to correct your form has been standardized, the database primary key should be part of the design.
External key is another table that contains a primary key or a set of fields. Foreign key relationship in the database and set up one-many relationship. If you have the correct design standards, foreign keys should be part of the design database.
From Baidu library.