Database Development

Style-Locale is a new Melbourne based company that intends to market their newly designed furniture. Each furniture item comes as a kitset, similar to Ikea. However, what makes their furniture easy to assemble is their patented locking systems that do not require Allen keys, Hex keys, bolts, screws, or nails. Furniture frames, bases, shelves, desks are made out of a specially created material similar to wood-plastic composites but are recyclable. They are also light and come in various different colours. Style-Locale has 5 branches within Victoria and if its sales prove successful in the next year, is looking to increase their presence in the other states around Australia. There has even been talk about off-shore markets through the use of the Internet.

Style-Locale has now decided to manufacture its full line of products including their computer desks. They have dropped their sofa-bed and extendable dining tables for the time being. Raw material is kept in a different location than that of their finished products. Raw materials consists of material bought through other suppliers as well as core components made by Style-Locale. Raw materials are sourced mostly from suppliers in Australia but also include a few offshore trading partners, such as New Zealand, China, and Canada.

Style-Locale’s immediate requirement is to improve their tracking of raw materials and assembled/manufactured products. Currently, their records are stored on an Excel spread-sheeting system. They are keen to have a database that will not only keep accurate records, but also to print out a list of items that need replenishing on a regular basis. Other required reports include product catalogues, suppliers and their products, and a list of products and their assembled components. Other information required includes keeping track of authorisation of purchase orders and breaking down the products into broad categories.

Having just started with their new line of products, Style-Locale is also interested in analysing more accurately just how well these products are going in terms of sales and whether there is justification for continuing or discontinuing the new lines. Therefore, as a future feature of their database Style-Locale are very keen to keep track of both customer preferences for all their products

Information Given

• You are given an initial prototype of a Database, which contains the structure of tables for Suppliers, Assembly, Raw Materials, Order, Order Lines, and Product.
• You are already given data for the Suppliers, Raw Materials, Order, and Product tables.
• You are given an Excel file that contains data for the Assembly and Order Lines tables.
• The initial table structure already shows that entities are linked by 1 to many relationships. Refer to Relationship tool under Database Tools and the Entity Relationship Diagram (in the Appendix).
  • A product is made up of many raw material components.
  • The Assembly table shows the link between specific raw materials and specific products. Some raw materials are used by more than one product.
  • An Order is broken down into two tables. The Order table shows the overall order to each supplier and the Order Lines table specifically lists the specific items ordered.

Tasks Required:

• Familiarise yourself with the database structure and the spread-sheets provided.
• You will need to populate the database with the spread-sheet data for the Assembly and Order Lines tables. You should end up with the entity relationship diagram shown in the appendix.
• Think of one extra table that would be useful to have in this database. Create this table and then link it up to the other tables where appropriate. In addition, add at least 10 records in this table if possible.
• You need to redesign all the tables with data validation where appropriate. For example, as in your workshop exercises put in auto-numbering, captions, formats, field sizes, input masks, validation rules, and other property values where appropriate. The idea is to make the tables easy to maintain.
• You will need to build at least one form that has a sub-form in it. This means you want a form representing a one-to-many relationship. E.g. a form showing suppliers and the raw materials they supply. Make sure this form has appropriate data validation. Be aware not all data validation created for the tables will transfer to the forms. Therefore, you may have to create data validation specifically for your forms.
• You will need to build 7 queries contributing to the purpose of the database, incorporating the following requirements:
  • Style-Locale would like a query to show which raw materials need to be replenished according to their restock level.
  • Style-Locale would like a list to show which products are no longer active.
  • Style-Locale would like a list to show all the raw material components required for each product recorded in their Assembly table.
  • At least 2 queries should be based on more than two tables.
  • There should be at least 1 dynamic query.
  • There should be a least 1 group by query.
  • There should be a least 2 queries showing a calculation.
  • September and October orders for raw materials from Technic Glass Ltd have the incorrect discount recorded. Therefore, create a query to update the discount for those orders. Therefore, the discount from Technic Glass needs to be changed from 0.025 to 0.05.
• Create a report for each query. That means there will be 7 reports.
• You will create one menu form that will have buttons to display all your forms and reports that you have created.