Database from Queries
Designing Database Tables – Part 13
Division 2
Forward: From this part to the end of this division, we shall learn how to produce normalized tables from database 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.
Example
We shall produce a set of normalized tables for the database of a whole seller who orders items from manufacturers and sells to shops. There are 6 steps in the process of designing a database (normalized tables) from queries.
Step 1: Identity the Application
Step 1 in this design is to identify the application. This means you the designer should identify what the database is to track. The database will track the sales and ordering of the products (shop items) for the whole seller. With that, we have identified the application.
Step 2: Determine the Queries
The people who need the database need to tell you the questions (queries) they will be asking from the database. You will never be able to get an exhaustive set of queries (from them); however get as many as you can. One way to do this is to see the CEO, managers and subheads and let them tell you what questions they will be asking the database; if the workers do not know anything about database, then tell them that they should tell you the questions they are already asking from themselves; guide them through. Clerks always do what the managers or subheads tell them to do; so you may not have to see the clerks.
- 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
So, these are the queries you got from the higher-ups (heads and managers). The same questions above can be asked in different ways.
Now that you have the queries, you have to go to step 3, which is a crucial step. Let us do that in the next part of the series,
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT