Identifying Data Items and Entities
Designing Database Tables – Part 14
Division 2
Forward: In this part of the series, we see how to create the database tables in at least the 3NF, from queries.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Data Item
A data item is a cell of a table.
Data Element
A data element or property or attribute is a column of a table.
Entity
An Entity is a table that is in at least the third normal form (3NF).
Example Queries
Remember, our example is dealing with a whole Seller that orders products from manufacturers and sells them to big shops. We saw the queries in the previous part of the series. I present them here again for easy reference:
- List all products that the company sells.
- Produce current product list (do not include products that we are no longer selling).
- List sales by year.
- List customers and suppliers by city.
- Give employee sales by country.
- List the invoices between two dates.
- Give products for certain orders.
- What is the total for a particular order?
- List the categories of products that were sold in a particular year.
- List products that are above the average price.
- List products by category.
- Give quarterly orders.
- Give quarterly orders by product.
- Give category sales for a particular year.
- List Sales by category.
- List the ten most expensive products and their prices
We have to use the queries to create the tables. Before we do that, there are certain things you should watch out for: You have to watch out for synonyms. For example, in one query the word, “product” may be used but in another query, the word, “Item” would be used to mean the same thing. One of the above queries has the phrase, “products for certain orders”. You may see “certain orders” in the query as “order details” or “products of an order”. For the purpose of creating tables, these phrases mean the same thing. With experience you get to know these types of things.
Step 3: Creating the Normalized Tables
The procedure to create the normalized tables is not very scientific. So the table you get may be different from what another designer gets. The only way to be sure that you have the correct tables is to use the Form and Report Approach on the table notations to check and finalize the tables.
The procedure begins by identifying nouns in all the queries that are of the company’s work (activities, organigram and asserts). These nouns identified are data elements and will become column headings for the different tables. So, in this way, we have got the columns; we are still to get the entities (tables). If your queries were reasonable in content and number, then you should have all the important columns.
You now have to group the columns. Each group consists of columns that express information about the same subject. That subject is the name you give to the table. This table name may or may not be a word in the queries. If this procedure is done perfectly then all your tables should be in at least the 3NF. The problem is that nobody is perfect to always be sure that he has done the work without forgetting anything or without any error. So, you will need to check and finalize your tables with the Form and Report Approach at the end.
Using that procedure, you should have the following data elements (columns):
Products, Sales, Suppliers, Customers, City, Employee, Country, Invoices, Dates, Orders, Certain Orders, Categories, price.
If you really think that a phrase e.g. “Certain Orders”, acts like a noun for the business, put it in this list; but you have to be very careful not to do something wrong here.
The Tables for the Example
To have the tables, you form groups from the above columns. Remember, the name of a group is the subject that bonds the columns together. Nothing stops the name of the table from being the name of one of the columns read from the query. You need to know how the company operates (business rules) in order to know how to group the columns together; you may need to see the customer (whole seller) to know the business rules. The tables are:
Products(Products, Categories, Price)
Sales(Sales, Date, Employee, Customers)
Suppliers(Suppliers, City, Country)
Customers(Customers, City, country)
SaleDetails(Sales, Products)
Orders(Orders, Date, Employee, Suppliers)
OrderDetails(Orders, Products)
Employee(Employee)
Effective Design of database from queries comes with experience. So, do not worry if the design process is looking difficult. Let us see how the tables came about:
The Products table has the columns Products, Categories and Price. In a Products row in the Products table, the particular product in stock has to be indicated; that is why you have the products column. Products exist in categories. For example, Soft drinks, coffees, teas, beers, and ales fall under the Beverages category. So, in a products row the category of products has to be indicated; that gives you the Categories column. All products have prices; so the price of a product has to be indicated in a products row; and so the price column.
The Sales table has the columns Sales, Date, Employee and Customers. Sale means an act of selling. So, in a sales row that act of selling has to be indicated; it is indicated above by, Sale. A sale takes place in a particular date; so the date has to be indicated. An employee is the one who carries out the sale, so the particular employee has to be indicated. You sell to a customer, so customer is indicated.
The Supplier table has the columns, Suppliers, City and Country. In the suppliers’ table, the supplier himself has to be indicated; this is indicated as Suppliers above. The supplier is living in a particular city and in a particular country; these are indicated.
“Invoices” among the data elements means Sales Details in our context. So you have the sales details table. In the rows of the SaleDetails table, you have to indicate the sale that was made (act of selling) and the particular product that was sold. Assume that in one sale, four products are sold. In a Sale table row you will have something indicating the act of sale; you will have the date the sale was made and you will have the employee who made the sale; you do not need to have the four products indicated. Now, in the SaleDetails table, you will have four rows for the four products. In the first of these rows, you will have the sale identifier (indicator) and the identifier (indicator) of the first product. In the second of the rows, you will have the same sale identifier and the identifier of the second product. In the third, row you will have the same sale identifier and the identifier of the third product. In the fourth row, you will have the same sale identifier and the identifier of the fourth product. Other rows in the SaleDetails table will be similar. The OrderDeatails table is explained in a similar way as the SalesDetails table, but you are dealing with ordering.
The Employee table has the column, Employee. This table has all the employees in the company. At this point all these tables are at least in the third normal form.
Note: a column from the data elements can appear in more than one table. As you group columns into a table, the group should be as small as possible; otherwise the resulting table will not be normalized.
Note that the Business noun, Total, found in the queries does not appear among the data elements and does not appear in the tables. This is because, total is a computed value and should normally be calculated when the user wants it. Computed values such as Total do not affect the derivation of the table in normalized form. You can bring in computed values like this in a table, after you have had the table in the normalized form.
Another important noun that does not appear among the data elements or in the table is year. Year and Date are normally used together in life. A date e.g. 9/8/10 has the month (9), the day of the month (8) and the year (10). The year can always be determined form the date and the difference in years can always be determined from the difference in dates. So Year does not have to feature (appear) among the data elements and in the tables. You have to watch out for things like this.
Well, we have seen much in this part of the series. Let us stop here and continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT