Data Modeling is analogous to an architect's initial developing of a blueprint for a building. Before starting actual construction, the architect must first develop a design based upon the owner's requirements-how many rooms, the number of facilities, parking spaces, etc. The architect must also keep in mind various legal requirements and building codes. Data Modeling involves a similar sort of process, of integrating input from end users, determining and documenting their requirements. Based upon all your gathered information, and incorporating business rules and practices, you construct the data model, or prototype.
The application development process should have a data model. A database inadequately designed can prove to be a disaster, both to the business and to the application itself. The time you spend in analyzing user requirements to develop a solid data model will prove well worth the effort. A data model lets you present user requirements in their proper perspective. In the database design process, you design customized tables to accurately reflect elements that the users wish to maintain and which accurately identifies their characteristics. You also define relationships between the tables, and a structure to integrate all the information in the most efficient manner. Following are the processes to analyze user-requirements, design tables, identify attributes, and establish relationships between tables.
Several software tools are available for modeling a database. Some of these tools, called CASE tools (computer-aided software engineering tools), can be used not only to draw diagrams but also to create documentation of the design. The three main components of these diagrams, called Entity/Relationship diagrams, or E/R Diagrams, are:
- Entities
- Attributes
- Relationships
While designing a database, you need to follow several steps, specifically:
- Identify the need for the database,
- Analyze existing information,
- Identify a list of Entities (Tables),
- Identify a list of Attributes (Columns),
- Identify relationships between Entities (Tables),
- Normalize the design.
Let's consider the database requirements of a certain Mrs. Cruz, who runs a Book Store. Currently she keeps track of all her books in an Excel spreadsheet. Her business is growing and she finds she can no longer keep up with the demand, especially because of increasing difficulties she is having maintaining her Excel spreadsheet. The spreadsheet has no capability of searching by title, author, or ISBN number. Currently, customer orders are manually logged into a registry as they are received. In order to automate all of these features, Mrs. Cruz should design a database for her book store. To help her design such a database, let us walk through the step-by-step process of database design, as outlined above. We will need to help in two ways:
- Identify the need for a database. The need for the database is usually defined by the client's needs. For example, Mrs. Cruz' needs will include:
- Keeping track of a list of books
- Keeping track of customers buying the books
- Producing a monthly sales report
- Keeping track of all book orders
- Analyze existing information. This step will involve asking such questions as: Is there an already existing database? If not, how is current data being stored? And so on. This will help in designing tables and columns.
It is important to understand the client's requirements and to prepare specifications, or requirements, document. This document can be submitted to the client so that the client and you agree with the stated requirements. While preparing this document, it is very important to communicate with the end-users of the database and also to understand, as fully as possible, business rules and practices.
The next step is to identify entities and attributes based on the requirements gathered in the above steps. You will identify tables and columns for this database in further topics.
Entities
Entity refers to information of significance in the end-user's working environment. An entity can be considered any substantive object of significance to the business, about which you maintain information. An entity may or may not be a tangible or actual object, such as a person, place, or thing; it may be an abstract activity, like an appointment or an operation. It must, however, be uniquely identifiable. Moreover, every instance or occurrence of the entity must be separate and distinctly identifiable from all other instances of that type of entity. Tables are used to represent entities in a database.
Following are the guidelines to represent an entity:
- Entities are represented in round-cornered boxes, called softboxes:
- Entity names appear in uppercase letters.
- Entity names are nouns, for e.g., CUSTOMER, EMPLOYEE, DEPARTMENT, etc.
- Entity names are singular.
Consider the bookstore example we discussed in the previous topic and identify the entities. Since the bookstore needs to keep track of all the books, you can identify BOOKS as an entity. The bookstore also keeps track of customers and the orders placed by them, so that we will need to establish two more entities called CUSTOMER and ORDERS. You can represent these entities in the E/R Diagram.
Consider another example. You need to design a database for a bank. In this case, you need to keep track of all the accounts for each customer. You will need two entities called ACCOUNT and CUSTOMER, assuming that a customer can have more than one account. Let's say the bank may have several branches and that you need to keep track of all customers individually and their account information by branch. Thus, you would need an entity called BRANCH.
Attributes
Attributes are characteristics or properties of an entity. In other words, any property that helps to describe an entity is called an attribute. An attribute might be either required or optional. When it's required, it must have a value; when it's optional, it may or may not have an assigned value. When an attribute has no value, it is said to be null. Zero and blank spaces are by definition notnull. Examples of typical attributes include name, gender, order date, and quantity. Attributes are analogous to columns in databases.
Following, are the conventional guidelines for accurately representing the attributes of an entity:
- Attributes should help to describe the entity.
- Attributes should appear inside softboxes, located below the name of the appropriate entity.
- Attributes appear in lowercase letters (for example: name, address, phone_number, gender, etc.).
- Attributes are written in the singular.
Consider the book store example, where you defined three entities CUSTOMER, ORDERS, and BOOKS. Let us identify the attributes for each of these entities. For the CUSTOMER entity, you need to record first name, last name, middle initial, address, city, state, zip, and phone number. For the BOOKS entity, you need to record ISBN, title, description, author, year published, and publisher. For the ORDERS entity, you need to keep track of the order number, order date, and shipment type. You can represent these attributes for each of the entities as shown below:
Let's consider another example-a bank database. We will identify the attributes for the entities CUSTOMER, BRANCH, and ACCOUNT. For the BRANCH entity, you can keep track of branch number, address, city, state, and zipcode. For the CUSTOMER entity, you can keep track of first name, last name, middle initial, address, city, state, and phone number. For the ACCOUNT entity you will need the account number, account type, account balance, and date opened.
Relationships
In Relational databases, relationships can exist between data held in different tables. The relationship between tables can be defined according to business requirements and rules. Relationships can be technically defined as bi-directional associations between two or more entities (tables). The four types of relationship are:
- One-to-One
- One-to-Many
- Many-to-Many
- Recursive
Relationships are represented by lines connecting two entities. The following figure illustrates the usual relationship-diagramming conventions:
Representing the relationship in the E/R Diagram depends on carefully considering how the two entities actually relate to each other. For example, consider the relation between the
MOVIE and
VIDEO_RENTAL entities. The video store may have tapes that have never been rented. In this instance the relation from
MOVIE to
VIDEO_RENTAL would be optional, which means you would use the dashed line near the
VIDEO_RENTAL 's end. On the other hand, anything rented from the store must be a video. This cannot be optional, so we use the solid line near the
MOVIE end.
One-to-One RelationshipsIn One-to-One relationships, the two entities (or tables) are related to each other such that for every row in table X, there exists only one row in the table Y and for every row in table Y, there exists only one row in the table X.
For example, observe the relationship between two tables - EMPLOYEE and EMPLOYEE_CONFIDENTIAL
Each EMPLOYEE can have only one confidential record stored in EMPLOYEE_CONFIDENTIAL table. Each confidential record in EMPLOYEE_CONFIDENTIAL table relates to only one employee in EMPLOYEE table. Hence, there is a One-to-One relationship between these two tables.
Consider the Book Store example discussed earlier. Let's say you want to keep track of certain confidential information relating to customers' credit card accounts, information you will maintain in a different entity called CUSTOMER_CREDITCARD. You need to keep track of only one record for each customer, and let's say it is not mandatory to have such confidential information for each and every customer. You can accomplish this simply by introducing a new, additional entity called CUSOTMER_CREDIT, and by relating it to each CUSTOMER entity as a One-to-One relationship, with an optional relationship for confidential information. This relationship can be represented by dashed line pointing towards the entity called CUSTOMER_CREDIT.
Now consider the Bank database discussed previously. Let's say each branch of the bank also has some confidential information such as a branch security code, locker code, and that every branch must have one and only one confidential record stored separately. In this case, you would need an entity called BRANCH_SECURITY related to the BRANCH entity on a One-to-One basis, with a mandatory relationship on both sides.
One-to-Many Relationships
In One-to-Many relationships, the two entities (or tables) are related to each other such that for every row in table X, there exists one or more rows in table Y, and for every row in table Y, there exists only one row in table X.
For example, observe the relationship between two tables--EMPLOYEE and DEPARTMENT.
Each department can have many employees while each employee can only work in one department. In other words, for each row in the
DEPARTMENT table, there could be many corresponding rows in the
EMPLOYEE table.
Consider again our Book Store example. A customer can place an order for books any number of times. Furthermore, let's say a customer is entered in the database only if he/she has ordered at least one book. Finally, an order will always relate to only one customer. Based on such business rules and practices, you will have a mandatory One-to-Many relationship between the two entities, CUSTOMER and ORDERS.
Now consider again the example of the Bank database. Let's say each branch of the bank will have many accounts, and that you could have a branch without any accounts. Based on such a business rule or practice, you will have a One-to-Many relationship between BRANCH and ACCOUNT, with an optional relation on the ACCOUNT side, since a branch can exist without any accounts having been opened. Also, let's say a customer can have one or many accounts and that each customer must have at least one account. It follows that you will have a mandatory One-to-Many relationship between CUSTOMER and ACCOUNTS.
Many-to-Many Relationships
In Many-to-Many relationships, the two entities (or tables) are related to each other such that and for every row in table X, there exists one or more rows in table Y, and vice-versa.
When two entities have a Many-to-Many relationship, the storage of repetitive data results, eventually requiring enormous space and becoming increasingly difficult to maintain.You can avoid the Many-to-Many relationship by breaking it into two One-to-Many relationships.
Observe below, for example, the relationship between two tables - ORDERS and PRODUCTS.
In this example, we have a table that stores order information and another table that stores product information. Each order could involve many products and each product could be involved in many orders, leading to a Many-to-Many relationship.
In the Book Store example, where a book can be in one or many orders, and an order can include one or many books, every order should show at least one book ordered. A book in the book's entity may or may not have any orders, meaning it may not have been bought by any customer. In this case the relationship is optional. Therefore, it follows that you have a Many-to-Many relationship between ORDERS and BOOKS, with an optional relation.
Recursive Relationships
In Recursive relationships, an entity has two columns that are related to each other with a hierarchical relationship between the columns. Recursive relationships can, in turn, be One-to-One, One-to-Many or Many-to-Many.
For example, in the employee table, the managers are also considered employees of the company. One manager could be managing several other employees. The
Manager column in the
EMPLOYEE table thus refers back to the
EmployeeID column of the
EMPLOYEE table. Such a relationship within a table is called a Recursive relationship.
Identifiers
Identifiers enforce business rules in a database. Identifiers enable you to maintain the integrity of the database. The two identifiers are:
A Primary key is a column or combination of columns whose values uniquely identify a row in a table. Each and every table in the database must have a primary key. The two properties of the primary key are that:
- its value is unique
- its value is not null
Therefore it is said that a primary key's value is definitely known and is unique in the entire table. For example, the VIDEO table can have a unique identifier called video_id. Attributes are identified by their role in the table or entity as follows:
- Unique attributes that are a part of the primary key are represented with the pound symbol, #.
- Mandatory attributes are represented by the * symbol.
- Optional attributes are represented by the letter, o.
A Foreign Key is a column in a table that refers to a primary key of another table. For example, movie_id (foreign key) in the VIDEO_RENTAL table refers to movie_id (primary key) in the MOVIE table. Foreign keys are generally represented as (fk) in the E/R Diagram.
Various programs might share subschemas, or each may have its own.