Entity relationship diagram and table definition in db2

A. DB2 Sample Database - DB2 9 for z/OS: Database Administration Certification Study Guide [Book]

entity relationship diagram and table definition in db2

Oracle, Microsoft SQL Server, MySQL and PostgreSQL are some of the An entity relationship diagram (ERD) is a visual form of relational databases. changes by looking into the database to study the table definitions. Entity Relationship Diagrams tell a story. SQL Developer can automatically add 'child tables.' . I will try importing from the Data Dictionary. Entity Relationship Diagrams in Enterprise Architect . management systems, such as Microsoft SQL Server, Oracle, PostgreSQL and MySQL. . Architect would not allow the definition of a table column without the table first.

Relationship A relationship is a connection between two entities.

How To: Generate an ERD for Selected Tables in SQL Developer | ThatJeffSmith

It connects data in tables together in meaningful ways. For instance, knowing the information of a transaction is meaningless without knowing the customer who performed the transaction. Hence, we would relate the customer and transaction tables to obtain complete information about a transaction. School An entity relationship diagram ERD is a visual form of relational databases. People use ERDs to model and design relational databases. The following is an ERD that depicts the tables for a simple school system.

School and Student are entities note: In ERD, the term "entity" is often used instead of "table". They are actually the same.

In the School table, there are two columns - id and name. A primary key is capable in uniquely defining records in a table. In other words, there must not be two or more school records that share the same id. Student, another table, has a foreign key column, namely SchoolId.

It is a reference to the primary key Id in the School table. Note that foreign keys need not be unique. Multiple student records can share the same School ID.

In a real world scenario, there can be multiple students studying at the same school and therefore have the same school id. Between the School and Student entities, there is a connector.

entity relationship diagram and table definition in db2

We call it a relationship. In this case, it is a one-to-many relationship. It means that the entity with the primary key i. School contains only one record that associates with zero, one or many records in the referenced entity i.

We can describe the relationship in everyday language by saying: A school intakes many students. Relational Database Design with ERD If the database you are going to build consists of a few tables only, you do not need to design it at all. You can simply use the management tools provided by the database management system, or run a few SQL statements to get the database built. However, this would hardly be the case in real world.

If you are planning a database with more than 10 tables, or even up to hundreds of tables, it would be better to consider designing the database prior to building it. Here are some of the benefits of database design: The type of a given relationship can vary, depending on the specific environment.

If employees of a company belong to several departments, the relationship between employees and departments is many-to-many. You need to define separate entities for different types of relationships. When modeling relationships, you can use diagram conventions to depict relationships by using different styles of lines to connect the entities. One-to-one relationships When you are doing logical database design, one-to-one relationships are bidirectional relationships, which means that they are single-valued in both directions.

For example, an employee has a single resume; each resume belongs to only one person. In this case, the relationship reflects the rules that an employee can have only one resume and that a resume can belong to only one employee. This figure reinforces the business rules that a department can have many employees, but that each individual employee can work for only one department.

An employee can work on more than one project, and a project can have more than one employee assigned. If you look at this book's example tables in "Appendix A. Example tables in this book"you can find answers for the following questions: What does Wing Lee work on?

Who works on project number OP? Applying business rules to relationships Whether a given relationship is one-to-one, one-to-many, many-to-one, or many-to-many, your relationships need to make good business sense. Therefore, database designers and data analysts can be more effective when they have a good understanding of the business. If they understand the data, the applications, and the business rules, they can succeed in building a sound database design.

When you define relationships, you have a big influence on how smoothly your business runs. If you don't do a good job at this task, your database and associated applications are likely to have many problems, some of which may not manifest themselves for years.

Defining attributes for the entities When you define attributes for the entities, you generally work with the data administrator DA to decide on names, data types, and appropriate values for the attributes.

DB2 9 for z/OS: Database Administration Certification Study Guide by Daniel Luksetich, Susan Lawson

Naming attributes Most organizations have naming conventions. In addition to following these conventions, DAs also base attribute definitions on class words.

A class word is a single word that indicates the nature of the data that the attribute represents. When an organization does not have well-defined guidelines for attribute names, the DAs try to determine how the database designers have historically named attributes.

Problems occur when multiple individuals are inventing their own naming schemes without consulting one another. Choosing data types for attributes In addition to choosing a name for each attribute, you must specify a data type. Most organizations have well-defined guidelines for using the different data types. Here is an overview of the main data types that you can use for the attributes of your entities. String Data that contains a combination of letters, numbers, and special characters.

Some of the string data types are listed below: The common short name for this data type is CHAR. Fixed-length graphic strings that contain double-byte characters.

Varying-length graphic strings that contain double-byte characters.

entity relationship diagram and table definition in db2

Varying-length strings of double-byte characters. Data that contains digits. The numeric data types are listed below: Precision is the total number of digits and scale is the number of digits to the right of the decimal point. REAL, for single-precision floating-point numbers. Datetime Data values that represent dates, times, or timestamps. The datetime data types are listed below: Dates with a three-part value that represents a year, month, and day.

Times with a three-part value that represents a time of day in hours, minutes, and seconds.

entity relationship diagram and table definition in db2

Timestamps with a seven-part value that represents a date and time by year, month, day, hour, minute, second, and microsecond. During physical database design you might need to change data type definitions or use a subset of these data types. The database or the host language might not support all of these definitions, or you might make a different choice for performance reasons.

Deciding what values are appropriate for attributes When you design a database, you need to decide what values are acceptable for the various attributes of an entity. For example, you would not want to allow numeric data in an attribute for a person's name.

The data types that you choose limit the values that apply to a given attribute, but you can also use other mechanisms. These other mechanisms are domains, null values, and default values.

Domain A domain describes the conditions that an attribute value must meet to be a valid value.

entity relationship diagram and table definition in db2

Sometimes the domain identifies a range of valid values. By defining the domain for a particular attribute, you apply business rules to ensure that the data will make sense. A domain might state that a phone number attribute must be a digit value that contains only numbers. You would not want the phone number to be incomplete, nor would you want it to contain alphabetic or special characters and thereby be invalid.

You could choose to use either a numeric data type or a character data type. However, the domain states the business rule that the value must be a digit value that consists of numbers. A domain might state that a month attribute must be a 2-digit value from 01 to Again, you could choose to use datetime, character, or numeric data types for this value, but the domain demands that the value must be in the range of 01 through In this case, incorporating the month into a datetime data type is probably the best choice.