CSG1207 | Database Design and Implementation Online Store | Database

Task 1 (Database Design)

This task is to design a database for the given scenario on the following page. Your final database design should include approximately eight entities.

Note:The scenario for this assignment is the same as the one from Task ¾ of Assignment 1.

State any assumptions you have made regarding your database design at the starting of the database design document. Do not make any assumptions that significantly change the structure of the scenario, as this may make Task 2 of the assignment more inappropriate. You need to make assumptions that affect your database design.

Finally, create a data dictionary with an entry for each entity in your database. The entries should list the entity name, a description of its purpose, a list of attributes, and details of any constraints applied to attributes. List the entries in your data dictionary in an applicable table creation order that can be used to create the database. It is necessary that a data dictionary should include all the information required to implement a database. Use the data dictionary in Lecture 4 and the data dictionary of the ‘company’ database (Module 5) as examples.

Some marks are also awarded for presentation and notation (2 marks).

You complete database design should include a list of assumptions, physical and logical diagrams and a data dictionary. This should be submitted as a single PDF file.

Scenario

You need to design and create a database for an online store. The database must encompass the customers, items, categories of items and the order made. You have the below-mentioned information about the way the store operates.

Customers and Addresses

1. Customer details must be recorded. This contains a customer number, first name, last name, email address, password and a column including either a ‘Y’ or an ‘N’ to indicate whether the customer desired to receive the store’s email newsletter.

a. The store wishes to ensure that each customer has a different email address.

2. The store wishes to implement a ‘referral system’ to reward customers who tell others about the store. As a result, customer details should also contain a ‘referrer column, which will include the customer number of the customer who referred them, if applicable.

3. Customers can describe addresses which are stored in the database. A customer can define multiple addresses, and each address is linked with a single customer by their customer number. Along with specifying the address, customers can specify a name for the address, e.g. ‘Home’. An address number is used to uniquely identify each address.

get more information Histogram JAVA Programming Assignment Help

Items & Categories

1. Item details must be recorded. This contains an item number, description, name, and price.

2. A list of item categories must be recorded and the database must keep track of which items are in which categories. All items are in at least one category but can be in some of them.

a. The only category details required are a category number and category name.

3. To receive more applicable newsletters, customers can stipulate which categories they are interested in. which customers are interested in which categories must be stored in a table of the database. The newsletter categories are the same as those which are associated with items.

Orders

1. Details of orders made by customers must be recorded. This contains an invoice number, the order date and customer number who made the order.

a. The order details should comprise two foreign keys referencing the address table- one for the delivery address and one of the billing address.

b. A customer then requires defining at list one address to make an order. The same address can be used for both the delivery and billing address.

2. For each order, the database must also record details of ordered items and the quantity ordered. Each order must include minimum one item, and an item can be in multiple orders.

General Information

The information above defines all of the entities, attributes, and relationships required in the database design.

It is recommended that you use auto-incrementing integers as the main key for most entities in this scenario, even though a composite primary key may be applicable in some tables such as the one keeping track of which items are in which categories.

Make sure to specify the most appropriate data type for each attribute in your data dictionary.

CSI5135 Additional Requirements

If you’re in CSI5135, the below mentioned additional requirements apply. If you’re in CSG1207, you do not need to do implement these requirements.

Make sure that your database design incorporating the following:

a. The name of each category must be unique
b. The quantity of an ordered item must be between 1 to 100 with a default of 1
c. Email addresses of customers must contain a ‘@’ symbol.
d. For security reasons, customer passwords will be encrypted using ‘bcrypt’ before storing them in a database. You must do some research to define an applicable data type and length for the password column so that it is able to comprise bcrypt hashes.

-It is up to you whether you really put bcrypt hashes into the column when writing your sample data in Task 2- it will not be used in any of the queries.

Some of these necessities will be implemented with the use of CHECK constraints when creating the database. State these CHECK constraints in your data dictionary in a way that clearly define what is being checked, or using the actual SQL code required to create the constraints.

Task 2 (implementation)

As your database has been designed, it is time to implement it in a DBMS, populate the database, and then manipulate the data via queries. The deliverables of this task are 3 files containing SQL statements.

Create your scripts as three ‘.sql’ files, with the filenames listed in the following headings. Templates for the script files are given wit this assignment brief – please use them. Format your code for readability, and use comments for heading ad to give further detail or information about your code if needed. (2 marks)

As each of the script files will include several SQL statements, it is very useful to be aware of a particular feature of SQL Server Management Studio (SSMS): if you have chosen some text in a query window, only the chosen text will be implemented when you press the Execute button.

This makes it easier to test a single statement, or even part of a statement, within a script document. You need to create all of the scripts detailed below.

Filename: create.sql

This file is a creation script, same as the ‘company.sql’ file (Module 5) used to create the company database.

Database Creation & Population Script (7 marks)

Produce a script to create the database you have designed in Task 1. Make sure to give your columns the same data types, properties, and constraints specified in your data dictionary, and ensure to name tables and columns consistently. Include any applicable default values and any CHECK or UNIQUE constraints that you feel are suitable.

Ensure this script can be run multiple times without resulting in any errors.  you can use the code at the starting of the creation scripts of the sample databases available in the unit materials to implement this. You will need to follow an applicable creation order when creating your tables.

Following the SQL statements to create your database and its tables, you must contain statements to populate the database with sufficient test data. You only need to populate the database with enough data to make sure that all views and queries return meaningful results. You can start working on your views and queries and write INSERT statements as required for testing as you go.

The final create.sql should be able to create your database and populate it with enough data to make all views and queries return meaningful results.

Ensure all referential integrity is observed – you cannot add data to a column with a foreign key constraint if you do not yet have data in the table it refers to. It is necessary to you are using an auto-incrementing integer, you cannot specify a value for that column when inserting a row of data. Simply pretend the column does not exist when inserting data – do not try to specify a value for it.

Note:the data you add is simply for testing purposes, and therefore does not require being particularly realistic, consistent and cohesive. Avoid spending unnecessary amounts of time writing sample data.

Filename: views.sql

The following page includes some general information and tips regarding views.

Customer View (2 marks)

Create a view which selects the below-mentioned details of all customers, even those without a referrer:

a. Their customer number, newsletter preference, and email address
b. Their first name and last name, concatenated into a full name
c. The customer number of their referrer, and their referrer’s full name (if they have one)

Order View (2 marks)

Create a view which the following details of all orders:

a. All of the columns in the order table
b. The customer’s first name and last name, concatenated into the full name
c. The billing address and delivery address

Ordered Item View (2 marks)

Create a view which selects the below-mentioned details of all ordered items:

a. The invoice number and customer number of the order that the ordered item is a part of
b. The item number, name and price
c. The quantity ordered and the subtotal

These views necessarily create joined or ‘flat’ versions of important tables of the database, providing you a convenient way to access and calculated information that is stored in multiple tables.

You are fortified to use the views to simplify the queries which follow – you can use a view in a SELECT statement in just the same way as you can use a table, often evading the need to write the same joins and calculations over and over.

When writing a view, it is easiest to write the SELECT statement first, and only add the CREATE VIEW statement to the starting once you have confirmed that the SELECT statement is working correctly. If you want to create extra views to use in the queries which follow, contain them in this file.

Joins are enclosed in Module 9, and views are enclosed in Module 10.

Filename: queries.sql

Write SELECT statements to complete the following queries. If you do not understand or are not sure about exactly what a query requires, contact your tutor. Contain all specified columns and orderings. Using your views on these queries can significantly reduce their complexity and the amount of code to write.

Query 1 – Item Search (2 marks)

write a query that selects all details of items that have a price of up to $30.00 and include the word ‘shirt’ in the item name. Order the results by price, in descending order.

Query 2 – Customer Interests List (3 marks)

Write a query that concatenates details about customer interests in categories into a single column (give the resulting column an alias of “interests”). Present the information in the following format:

UPPERCASE CUSTOMER NAME is interested in the UPPERCASE CATEGORY NAME category.

The query should only include the interests of customers who have a “Y” in the newsletter preference column. Order the results by the category name. Using the Customer View in this query is recommended.

Query 3 – Unpopular Items (3 marks) 

Write a query that selects the item number, item name, and price of any items that have never been purchased, i.e. Items that never appear in the ordered item table.

Hint: Select items whose item numbers do not appear in the ordered item table. Consider using NOT IN and a subquery.

Query 4 – Order Summary (3 marks)

Write a query that selects the invoice number, order date, number of items ordered and total cost of each order. Order the results by the total cost in descending order. Using the Order View and the Ordered Item View in this query is recommended.

Query 5 – Items per Customer (3 marks)

Write a query that selects the customer number, customer’s full name and a total number of items that they have ordered in their orders. Customers who have never ordered anything should appear in the results with a 0 as their total number of ordered items. Order the results by the number of ordered items in descending order. Using the Customer View and the Ordered Item View in this query is recommended.

Hint: Remember to take the quantity column of ordered items into account.

 

Query 6 – Big Spenders (3 marks)

Write a query that selects the customer number, customer’s full name, number of orders and combined total cost (of all of their orders) of the three customers who have spent the most. Using the Customer View and the Ordered Item View in this query is recommended.

Hint: You may need to use DISTINCT obtain a correct count of the customer’s number of orders (refer to Lecture 9 Slide 34).

Query 7 – Category Statistics (4 marks)

Write a query that selects the following details for each category:

a. The category number and category name
b. The number of items in the category
c. The cost of the cheapest item in the category
d. The cost of the most expensive item in the category
e. The average cost of all items in the category rounded to two decimal places
f. Make sure that the results include all categories, even those with no items in them.

Query 8 – Order Timeline (4 marks)

Write a query that selects the number of orders and total cost of those orders per year and month. Each row of the query results should include a year, the name of a month, the number of orders placed in that month, and the total cost of those orders.

The results should be in sequential order, with the earliest month of the earliest year at the top. Using the Ordered Item View in this query is recommended.

Hint: Ensure that your sample data includes orders from different years and months. The DATENAME function can be used to determine the name of a month based on a date.

Presentation, Notional, and Formatting (2 marks per task)

A small number of marks are awarded for presentation, notation, and formatting. This contains:

a. Presentation and appearance of word processed PDF file for Task 1
b. Appropriateness and consistency of notation used for diagrams/data dictionary in Task 1
c. Applicable commenting and formatting of scripts in Task 2