The intention of this lab is the understanding the process and tools of database design. A formatted Microsoft Word document shall explain the process of designing a database: the purpose of a database, tables in the database, and further to the level of each of the fields in table.
For instance, the purpose of the “grocery store database” would be on the lines of maintenance of inventory, sales, purchases, customers, and suppliers – each of which could become a distinct table. Each of these tables may in turn have different attributes or field (columns). These tables may be related to each other, so as to avoid redundancy of information. For instance, if selling price is stored in master inventory table, you don’t need it at the sales table again – one can always look that up from there!
Various vendors offer their database systems to define the above and write programs to further automate the entire system. One such tool from Microsoft Office is Access. We shall be using this tool for the purpose of definition. We shall not cover the details of further automation of this database where bill could be printed or reports could be generated.
Access is a database system allowing the database designer (you), to create tables, define the relationships amongst those tables, apart from various other advanced features like report generation, creation of forms, etc. The “create table” and “relationship” is the focus of our study as database designer.
After creating all the necessary tables, you can double click on each of them one after the other (usually in some given order) and enter some “dummy” input. If it accepts expected input values your database has a good-design.
You can find an example database HERE.
Three tables: Customers, Orders, Order_detail
Table contents:
Customers: CustomerID *, Name, Address, Phone number
Orders: CustomerID, Order# *, OrderDate
Order_detail: Order# *, Price, Delivered
(* are primary keys)
One Query which shows all related information in a single table.