Information must be maintained about customer’s invoice details, email addresses and credit cards. In one sale customers are allowed to purchase any number of DVDs. The items in the sale can only be delivered to one address and the cost of the DVDs are only made to one credit card.

A customer is provided with an online shopping basket where the articles to be purchased are placed. As each item is selected the item is placed in the shopping cart. When the customer has completed their shopping the customer is invited to checkout and pay for their items in the shopping basket.

At the checkout the credit card details are verified together with the address the items are to be shipped to. At the end of this process an email is sent to the customer with details of the sale, the shopping cart details are removed.

You must design a database with the above-mentioned specification indicating the necessary keys and relationships. Draw E/R diagrams to arrive at the tables along with their attributes such that the database is in the third normal form.

Summary: Design a relational database that capable of maintaining Customer Details, DVD Details, and Order Details.

Notice: Assignment must satisfy all requirements/learning outcomes specified on page 2 in order to Pass.
Marking Scheme

PASS – meet the following:
Task 1
1.Compare and contrast the integrated database environment with ?the traditional file processing environment. Analyze the different approaches to database design [Assessment Criteria: 1. a, c]

2.
Illustrated with suitable examples from the given case study the meaning of data mining and data warehousing[Assessment Criteria: 1. b]

Task 2

3.
Create a schema listing the attributes for each entity identified and show that your schema is normalised (at least in 3NF). Identify primary, foreign and composite keys if there are any. [Assessment Criteria: 2. c]

4.
Draw an entity relation diagram that will enable you to create a database for Online DVD Sales. Resolve “many to many”relationships if there are any. State any necessary assumptions. [Assessment Criteria: 2. c]

5.
Provide the incorporate of query languages to create the database with minimum of six tables with necessary attributed. [Assessment Criteria: 2. a, b]

Task 3

6.
Design and create at least 10 records. Also provide list of the records in each table. There should be at least 10 records in each table. [Assessment Criteria: 3. a]

7.
Using MySQL as visual tools demonstrate the extraction of meaningful data through the use of query tools. (Query below)[Assessment Criteria: 3.b & 3. c]

Query transactions

a.List all the orders for customer Jane Swinton.
b.List all the customer IDs and names who purchased DVDs in March 2011 in DVD name order.
c.Antoine Enriques has made several purchases in the months December 2011 to April 2012. List all the DVDs he has purchased in this time.
d.List the customer names whose purchase is more than £50
e.List all the available DVD’s in the store.
f.List the count of customers who purchased common DVD.
g.Display the total amount of a particular customer’s purchase.
h.List all the customers in descending orderby customer’s name.

Task 4
8.
Using query tools, Implement and test the verification and validation processing withabove 8 different SQL types of queries from the database illustrating theunderstanding of the various features of SQL (update, sorting, joining tables, conditions using the where clause, grouping, set functions, sub-queries etc.). [Assessment Criteria: 4.b, & 4.c]
9.
For each query, state the English equivalent documentation, the SQL statement and show the output from the query. Queries must be based on business logic. [Assessment Criteria: 4.a]

State any assumptions you make but they should not contradict any of the given specifications of the system. Evaluation will be based on problem solving approach, effective design and implementation, submitted documentation. See grading criteria below.