Online DVD Sales wants to create an information system for online sales of DVDs. People are allowed to register as customers of the online site and to update their stored information.
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.
Our Service Charter
-
Excellent Quality / 100% Plagiarism-Free
We employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers. -
Free Revisions
We keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed. -
Confidentiality / 100% No Disclosure
We make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service. -
Money Back Guarantee
If the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit. -
24/7 Customer Support
We have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.