Your job is to develop a database management system for a bike shop who ‘buys' and ‘sells' new and second-hand bikes, and also parts of bike. A bike is constructed with parts while a part can be fitted to different bikes. The shop sales a list of selected brands of bikes, and each brand has a few models. There are a few staff members working in the shop. Some staff members are in charge of pricing, while some are to sell the products (including bike and parts). Both new and used bikes are bought with a price (known as ‘buying price'), and the shop sells it with an appropriate price (known as ‘selling price'). The prices (buying price, selling price) of a second-hand bike must be approved individually by a qualified member of staff who is in charge of pricing.
Each product (bike or part) is uniquely identified by an item code. A description about the bike such the brand, model, and condition needs to be provided. In addition it is necessary to take a recorder who approved the prices. For parts, the item code, the name of part, the mode and brand it fits and so on need to be recorded.
The database system to be developed must be able to facilitate the member staff who sales the products quickly search the parts that fit a specific model of bike, and give the correct price. One the other hand, the database system is hoped to assist the pricing staff to give appropriate price to a bike according to the model and the price of parts and so on.
Requirements to the report:
Write a report (no more than 5 pages) to present your work including the following sections:
(1) Introduction: including a review of the aims, approaches, procedures of database design, and so on.
(2) Presenting an Entity Relationship (ER) diagram, which must include all the necessary entities and their relationships.
(3) Based the ER diagram produced, producing a list of tables (the resulting tables should have been normalized up to 3NF) and the associated attributes. The primary keys and foreign keys (if any) of the tables must be stated clearly.
(4) Evaluation of your design - check if the tables are on 3NF and explain why you think they are on 3NF.
(5) The conclusion and discussion: what knowledge you have gained, the possible further improvement of the design, and any other reflection.
(6) References (any reference used in your course work need to be listed and cited appropriately