DATA MANAGEMENT SYSTEMS DESIGN PROJECT
Purpose of this project
Analyze, design, implement, and document a database system application. You will use the methodology for database development learned in class. The system must be implemented on a DBMS with any language as a host–language for the application. The system must be menu–driven and include the basic functionality described below.
The Online Computer Store
The following specifications are intended as a guide; they are not the complete specifications. These are intended to be a basis for you to get started in the right direction in designing your system. You as the designer must analyze and decide what other details or features should be specified for your system. Thus, individual group implementations will differ in terms of design and implementation styles. Every group has to mention clearly in its report what other specifications are assumed. However: (a) they should not contradict the assumptions described below, and (b) they have to be clearly stated in your report.
DATABASE REQUIREMENTS
(Specifications for the design of the database)
Newark–IT is a company that sells computers all over the world. It is a web–based company. All sales are managed via a website. The company wants to build a database system for customers, products, and sales transactions. You have been approached to help Newark–IT with the database design and application development.
1. A customer has an ID, a first name and a surname.
2. Each customer has a home address, a telephone number, and an email address.
3. A customer can have one or multiple credit cards. Each credit card has a number, a security number, the name of its owner, the billing address, the type of credit card, and an expiry date.
4. A customer can specify multiple shipping addresses. A shipping address is identified by the name the customer choses for this address (which is unique among the shipping addresses of this customer) and the ID of the customer. For a shipping address the zip code, street name, street number, city, state and country are provided. If a customer is deleted, we need not keep track of her shipping addresses any longer.
5. Customers have a status (regular, silver, gold and platinum). A status level corresponds to different advantages. In particular, a credit amount is offered to the customers of silver status and above which might be different for each of these customers. In addition, the company proposes each month special offers (reduced prices) for some products exclusively for gold and platinum status customers.
6. An offer is the combination of a particular product and a price.
7. Each product has its own product ID, a name, a recommended price, and a brief textual description.
8. Each product has also a unique product type. The quantity of each product in stock is recorded in the database.
9. A customer can collect several products in a shopping basket each at a different quantity. If the product is on offer and the customer is of gold or platinum status, she is charged the reduced price for this product.
10. A shopping basket is always non–empty. It is created with the first item added.
11. A successful sales transaction is recorded in the database, when a customer buys a shopping basket (with products), choses one and only one shipping addresses and the payment is confirmed. The price and the quantity of every product in the transaction should be available as well as the total amount of the transaction.
12. The payment of a sales transaction is done with a credit card. It can be one of the credit cards stored by the customer, or a credit card whose information is recorded for this particular transaction.
13. As soon as a sales transaction is confirmed, the shipment is started. If a product is not delivered successfully, for instance because the cargo got lost, the transaction gets tagged with not–delivered tag.
14. There are three main categories of product types: Desktop computers, laptops, and printers.
15. There are also product types that do not belong to any of these main categories (e.g., different kinds of accessories).
16. Product types of main categories have specific attributes: Desktop computers and laptops have both a CPU type, and laptops have also a weight and a battery running time. Printers have a resolutions and a printer–type.