database design for tina s teas

Database Design for Tina’s TeasBus241

Tina loves tea. She is passionate about brewing and enjoying the perfect cup of tea. Her passion for tea is so great that she has recently decided to quit her government job and pursue her dream of owning her very own tea shop.

Tina knows that she will need some way of tracking her inventory and suppliers as well as other important business information. She is not experienced, however, with database design so she has contacted you for advice.

As Tina’s database consultant, your first job is to put together a conceptual design for a store database. During your meetings with Tina, you have gathered the following information:

  • The database must keep track of her product inventory, including information such as product name, product type, description, cost, selling price and supplier.
  • She also wishes to track the following customer information: name, contact information, birthday, and purchase history.
  • For suppliers, she would like to keep their contact information including the company name, sales representative name, telephone, email, address and notes.
  • Once her database is operational, Tina would like to use it to keep track of sales and manage her inventory.

Some of the reports that Tina would like to produce include:

  1. A customer contact list.
  2. A list of suppliers.
  3. An inventory report showing all inventory along with the supplier.
  4. A sales report showing who bought what for a given period of time.
  5. Other sales reports such as total sales by product, total sales for a date range, totalsales by product type, etc.

Based on these requirements, put together a conceptual database design for Tina. Your document should include:

  1. A brief introduction describing the purpose of the database (approximately half a page).
  2. A section discussing the questions that the finished database should help answer (i.e. list of products by type).
  3. A list of the entities in your database and a model of your database in the form of an entity-relationship diagram (ERD) showing entities and the relationship between them.
  4. A table map showing the attributes for each table and how the relationships are connected. The model should be normalized (no duplication of data, no many-to-manyrelationships, no storing of derived data, all fields in the table depend upon the primary key only.

5. A brief data dictionary for each table. Use a format similar to the following:

I recommend using Microsoft Word for your database design document. You may want to use PowerPoint to draw your ERD and table map and then copy and paste them into your Word document.