DigitalX has been operating a chain of retail stores selling CD's, DVD's and Games for a number of years. Recently they have been operating an online store in an attempt to expand their business.
Due to the success of the new online operation DigitalX have decided to redevelop and expand their online business. You have been hired as part of the development team and tasked with the development of the new database system.
Much of the initial research and requirements analysis has already been completed. DigitalX has provided you with the following case study and documentation to assist you in developing the database. The case study details how DigitalX envision the new online store will operate.
Because the database must support an online store security is a concern. The initial requirements analysis has identified some security concerns and they have been included in the case study.
CASE STUDY
Products
DigitalX sells three broad categories of products, music (CD's), DVD's and games. Each of these categories can be further broken down into subcategories, for example: music and DVD's can be broken down into genres and games can be divided by gaming platform e.g. XBOX, Playstation and PC. The database must support these product categories and subcategories. It is also essential that DigitalX be able to add additional categories and subcategories in the future. They must be able to make these additions without having to make any structural changes to the database.
When it comes to storing product information DigitalX requires at minimum the following information: a name and description for each product, the subcategory to which product belongs to, cost of product, RRP of product and the number of units currently in stock. The margin of every product (RRP) is 20% of the cost.
You should also consider adding additional columns according to the other requirements of this project. (A STATUS column to show if this product is available)
Customers
Customers will register with DigitalX via the DigitalX website. Upon registration customers supply their email address and a password. Customers will use their email addresses and passwords to login to the website. For this reason customers may only register their email address once. You must ensure that your database enforces this restriction.
During the registration process customers will be requested to supply some personal information includes but not limited to date of birth, home address, telephone number and mobile phone number. This information must be recorded in the database.
For more information on the registration process view appendix 3: 'User registration use-case'.
Passwords
DigitalX want to ensure that customer password are stored securely in the database and cannot be viewed simply by looking at the data in the database. It has been decided that the passwords must be stored in an encrypted format. It has been suggested that you create stored procedures for saving and retrieving passwords and make use of SQL Server's cryptography subsystem to implement the encryption.
Order Process
Customers will browse the DigitalX online store and add products to their shopping cart. Customers will have the opportunity to increase and decrease the quantity of an item in their shopping cart and also remove unwanted items from their shopping cart. Once a customer is satisfied with the contents of his/her shopping cart he/she will continue to the checkout process.
The checkout process will involve creating an order in the database, prompting the customer for a credit card number and processing the payment. The order will include the date the order was placed, information about each product being ordered and the quantity of each, the details of the customer placing the order, as well as the billing address and shipping address for the order.
Each order will also have a status indicator, indicating the progress of the order. Possible status values include: PROCESSING, BACKORDER, SHIPPING, DELIVERED and CANCELLED. DigitalX will use these values to determine which orders have been completed and which orders are still being processed. Customer will also be able to view these status values via the DigitalX website.
You should also store the customer's credit card number and expiry date used to place the order. Note, it is only necessary to store the last credit card number used by each customer. The credit card number must be stored in an encrypted format.
Once an order is ready to be shipped an invoice is created. A sample invoice is included as appendix 7. The invoice is included with the products when they are shipped.
For more information on the order process view appendix 4: 'Place order use-case'.
Viewing order status
The DigitalX website allows customers to view their past and current orders. This allows customers to view the status of the current orders as well as review orders they have placed previously. Your database must support these pages.
Appendix 6 includes a sample of the Order status web page.
Customer / usage predictions:
DigitalX currently has 150 000 registered customers for their online store, and process 1500 orders per day. However, with the coming expansion DigitalX expect to increase their customer base to 250 000 within the next six months and reach 350 000 customers within the next twelve months.
It is also predicted that the number of orders processed per day will increase from the current 1500 a day to 2500 per day over the next six months and double again within twelve months.
Availability and backup requirements:
Because customers place orders with DigitalX via their online store, DigitalX cannot recreate orders if any information is lost. For that reason it is essential that no data is lost in the event of a system failure for whatever reason.
The online store is open to customers twenty four hours a day seven days a week. DigitalX cannot afford for the database to be unavailable for any reason and have therefore set a 99.7% availability target. As DigitalX is still in the early stages of developing its online business it doesn't yet have the revenue to invest heavily in its hardware infrastructure. For that reason careful consideration should be given to the budget when designing an availability and backup solution.
Usage data for the past several months has been averaged and a graph compiled, providing an indication of the expected workload. Review this information carefully when planning your backup policy.
The activity graph is included as appendix 1.
Data
Suppliers upload the latest price list at 10pm every Sunday in CSV format. You need to use appropriate technology to ensure that the products in DigitalX database can be updated timely. Assume that DigitalX has three suppliers; the format of all files are the same; the location of all files is C:\Data.
You database shall automatically create/update/delete(logical) SubCategory/Product according to the price list. The MAIN category remains the same.
Sample price-list file is provided. You can also populate you own data for testing.
Project Guidelines:
Your project shall have the following features but not limited to:
1. Create the DigitalX database. Design tables and relationships.
2. Ensure that email addresses may only be used once in the database.
3. Create a stored procedure to add a new customer to the database. The stored procedure must accept all customers' information as parameters and insert them into the database. The stored procedure must encrypt the customer's password and store the encrypted password.
4. Create stored procedures to encrypt and decrypt a customer's credit card number.
(Hint: you will need to use the EncryptByxxx and DecryptByxxx system functions for your stored procedures.)
5. Create a table-valued user defined function to support the order history page of the website as shown in appendix 6.
6. Design a high availability plan and backup schedule for the DigitalX database. A graph for system activity is provided in appendix 1. Use this graph as a guide when planning your backup schedule.
Implement your backup plan in the database you have developed.
You DO NOT need to implement your availability solution. Instead of, state the reason and plans of you high availability solution by words. (Not less than 200 words)
7. Create a SSIS package to implement incremental processing of products information.
8. A view must be created to allow DigitalX employees to view all orders on backorder. The view must include the customer's email, contact details, the date the order was placed and the order number.
9. A view must be created to allow DigitalX employees to view all the products on backorder and the quantity of each on order. Your view must aggregate the data from the backorder products so that each product appears only once in the list.
10. Identify areas in your database where indexes would help improve query performance and create indexes on those columns.