Database Design Step 5

1. Determine entities and relationships

A) design macro behaviors. What do you use this database? For example, you want to manage employee information.

B) determine the entities. Determine the subject scope of the information to be managed for a series of behaviors. This will change to table. For example, hire an employee, specify a specific department, and determine the skill level.

C) determine relationships. Look at the behavior and determine the relationship between tables. For example, there is a relationship between a department and an employee. Name the link.

D) refine the behavior. You start from macro behaviors and now carefully check these behaviors to see which behaviors can be converted into micro-behaviors. For example, the information for managing employees can be subdivided:
● Add new employees
● Modify existing employee information
● Delete the transferred employee

E) Determine business rules. Look at your business rules and determine which one you want to adopt. For example, a department may have only one department leader. These rules will be designed into the database structure.

Example:

Acme is a small company with offices in five places. Currently, there are 75 employees. The company is ready to quickly expand its scale by dividing nine departments, with each department under its leadership.
To help search for new employees, the Personnel Department has planned 68 skills to prepare for future personnel management. When an employee is recruited, the professional level of each skill is determined.

Define macro behavior
Some Acme companies' macro behaviors include:
● Recruiting employees
● Dismissed employees
● Manage employee personal information
● Manage skills required by the Company
● Manage the skills of employees
● Manage Department Information
● Manage Office Information

Determine entities and relationships
We can determine the topic domains (tables) and their relationships to store information, and create a chart based on macro behavior and description.
We use boxes to represent tables, and diamond to represent relationship. We can determine which relationship is one-to-many, one-to-one, and many-to-many.
This is a E-R sketch that will be refined later.

Refined macro behavior
The following micro behaviors are based on the above macro behaviors:
● Add or delete an employee
● Add or delete an office
● List all employees in a department
● Add a skill
● Add an employee's skill
● Determine the skills of an employee
● Determine the skill level of an employee
● Determine all employees with a certain skill level
● Modify employee skill levels

These micro-behaviors can be used to determine which tables or relationship are needed.

Determine Business Rules
Business Rules are often used to determine one-to-many, one-to-one, and many-to-many relationships.
Related Business Rules may include:
● There are currently five offices; up to 10 can be expanded.
● Employees can change departments or offices
● Each department has a department leader
● Each office can have up to three phone numbers
● Each phone number has one or more extensions.
● When an employee is recruited, the professional level of each skill is determined.
● Each employee has 3 to 20 skills
● An employee may be arranged in or out of the office.

2. determine the required data

Determine the required data:
1. Confirm the supported data
2. List all data to be tracked. Describes table (topic) data to answer these questions: who, what, where, when, and why
3. Create data for each table
4. List the available data that each table currently looks suitable
5. Set data for each relationship
6. If so, list applicable data for each relationship

Confirm supported data

The supported data will be the field name in the table. For example, the following data applies to the tables "employee", "Table skill", and "table expert in.

If you plot the data into a chart, like:

Note:
● When determining the support data, please be sure to refer to the macro behavior you have previously identified to clearly understand how to use the data.
● For example, if you know that you need a list of all employees sorted by their surnames, make sure that you split the supported data into names and surnames, which is better than simply providing a name.
● The name you selected should be consistent. This makes it easier to maintain the database and read the output reports.
● For example, if you use an abbreviation emp_status in some places, you should not use the full name (empolyee_id) in another place ). Instead, these names should be emp_status and emp_id.
● Whether the data is relative to the correct table does not matter. You can decide based on your preferences. In the next section, you will determine this by testing.

3. Standardized data

Standardization is a series of tests you use to eliminate data redundancy and ensure that data is associated with the correct table or relationship. There are 5 tests in total. In this section, we will discuss three frequently used items.
For more information about standardized testing, see the relevant database design books.

Standard Format
Standardized format is a common test method for standardized data. After your data passes the first test, it is considered to have reached the first standard format; pass the second test to reach the second standard format; pass the third test to reach the third standard format.

Standard Format:
1. List Data
2. Determine at least one key for each table. Each table must have a primary key.
3. Confirm the key of relationships. The key of relationships is the key connecting two tables.
4. Check the computed data in the supported data list. Computing data is usually not stored in the database.
5. Place the data in the first standardized format:
6. Remove duplicate data from tables and relationships.
7. Create one or more tables and relationships based on the excluded data.
8. Put the data in the standard format for the second time:
9. use more than one key to determine tables and relationships.
10. Remove Data that depends only on a portion of the key.
11. Create one or more tables and relationships based on the excluded data.
12. Put the data in the standard format for the third time:
13. Remove Data that depends on tables or relationships and is not a key.
14. Create one or more tables and relationships based on the excluded data.

Data and keys
Before you start standardization (test data), you can simply list the data and determine a unique primary key for each table. This key can be composed of one field or several fields (chained keys.

A primary key is a set of fields that uniquely distinguish rows in a table. The primary key of the employee table is the employee ID field. The primary keys in works in relationship include the Office Code and employee ID fields. Each relationship in the database is given a key, which is extracted from each table connected to it.

Place the data in the first standardized format
● Remove duplicate groups
● To test the standardized format for the first time, remove duplicate groups and put them in their respective tables.
● In the following example, the phone number can be repeated. (A staff member can have more than one phone number .) Remove duplicate groups and create a new table named telephone. Create a relationship named associated with in telephone and office.

Put the data in the standard format for the second time
● Remove data that does not depend on the entire key.
● Only those tables and relationships with more than one key can be viewed. To test the second standardized format, remove any data that does not depend on the entire key (all fields that form the key ).
● In this example, the original employee table has a key consisting of two fields. Some data does not depend on the entire key. For example, the Department name only depends on one of the keys (Department ID ). Therefore, the Department ID. Other Employee data does not depend on it. It should be moved to a new table Named Department and a relationship named assigned to should be created for the employee and department.

Put the data in the standard format for the third time
● Remove data that is not directly dependent on keys.
● To test the third-time standard format, except for data that is not directly dependent on keys but other data.
● In this example, the original employee table has data dependent on its key (employee ID. However, office location and office phone depend on other fields, namely office code. They are not directly dependent on the employee ID key. Move this set of data, including office code, to a new table named office, and create a relationship named works in for the employee and office.

4. Relationship considerations

After you complete the standardization process, your design is almost complete. All you need to do is consider the relationship.

Consider the relationship with data
Some of your relationship may contain data. This often happens in many-to-many relationships.

In this case, the relationship is converted into a table. The relationship key is still the table key.

Consider the relationship without data
To implement a non-data relationship, you need to define an external key. An external key is one or more fields that contain the primary key of another table. The external key enables you to connect multiple tables at the same time.

There are some basic principles that can help you decide where to put these keys:

One-to-multipleIn a one-to-multiple relationship, the primary key in "one" is placed in "multiple. In this example, the external key is placed in the employee table.

One-to-oneIn a one-to-one relationship, the external key can be placed in any table. If you want to put it on one side rather than the other side, you should put it on the required side. In this example, the external key (Head ID) is in the department table because this is required.

Many-to-manyIn the multi-to-Multi-relationship, use two external keys to create a new table. The existing old table is associated with the new table.

5. Inspection Design

Before you complete the design, make sure it meets your needs. Check the behavior you defined at the beginning and confirm that you can obtain all the data required for the behavior:
● Can you find a path to wait for all the information you need?
● Does the design meet your needs?
● Is all required data available?
If you answer all of the above questions, you have almost completed the design.

Final design
The final design looks like this:

Design database table attributes
The database design needs to determine what table and what fields each table has. This section describes how to specify the attributes of each field.

For each field, you must determine the field name, data type and size, whether to allow null values, and whether you want the database to limit the values allowed in fields.

Select field name
The field name can be any combination of letters, numbers, or symbols. However, if a field name contains letters, numbers, or underscores, or does not start with a letter, or is a keyword (For details, refer to the keyword table), when a field name is used, it must be enclosed in double quotation marks.

Select a data type for a field
SQL Anywhere supports the following data types:
INTEGER (INT, integer, smallint)
Decimal, numeric)
Floating Point Number (float, double)
Character type (char, varchar, long varchar)
Binary data type (binary, long binary)
Date/time type (date, time, timestamp)
User-Defined type

For more information about data types, see "SQL Anywhere data types. The data type of the field affects the maximum size of the field. For example, if you specify smallint, this field can contain an integer of 32,767. An integer can contain 2,147,483,647 integers. For char, the maximum value of a field must be specified.

The long binary data type can be used to save the example image (such as Bitmap) or text editing document in the database. These types of information are usually called 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, you can define this field as not null. Otherwise, the field value can be null. The default value in SQL is to allow null values. You should explicitly define the field as not null unless you have good reasons to set it to allow null values.

For a complete description of the null value, see "null value ". For more information about its usage, see "search conditions ".

Select Constraints

Although the data type of a field limits the data that can exist in the field (for example, only numbers or dates can be saved), you may want to further constrain its allowed values.

You can limit the value of any field by specifying a "check" constraint. You can use any valid condition that can appear in the WHERE clause to constrain the allowed value, although most check constraints use the between or in condition.

More information

For more information about the valid conditions, see "search conditions ". For how to specify constraints for tables and fields, see "ensuring data integrity ".

Example
In the example database, there is a table Named Department. The fields are dept_id, dept_name, and dept_head_id. It is defined as follows:

Note that each field is specified as "not null ". In this case, the data of all fields in each record in the table is required.

Select primary key and external key
A primary key is a field that uniquely identifies each record in a table. The primary key should be part of the database design.
An external key is one or a group of fields that contain the primary key value in another table. The external key relationship establishes one-to-one and one-to-many relationships in the database. If your design has been properly standardized, the external key should be part of the database design.