Lab 5: Design of a Database

1.1        Introduction

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.

1.2        Example

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!

1.3        Tools Used

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.

1.4        Access

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.

1.5        How do I know “design is working?”

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.

 

1.6        Examples

You can find an example database HERE.

1.7        Submission

  1. The submission of the design document (formatted Microsoft Word or Frontpage) with the link to the Access file must be uploaded to your personal website at http://www.personal.kent.edu by Mon, Mar 19, 2007.  Email me the link.
  2. The design should have at least 3 related tables and one query along with explanation of their purposes.
  3. Also, read the "Cooperation on Homework Assignments" of the syllabus.
  4. Contact me if you have any questions during my office hours or by email appointment.