AW Bikes is a bike retailer. They sell bikes and related products direct to consumers.Prior to...

Question

AW Bikes is a bike retailer. They sell bikes and related products direct to consumers.

Prior to completing any of the below tasks, you will need tosetup the database.

download

Database Updates:

Management would like to track additional information about a product category – the product line it belongs to. See the attachedCategory Information spreadsheet

download. Add the product line information to the database and normalize the data as necessary. You may create as many additional tables as you deem necessary.

Management would also like one table that stores address information, instead of having the customer’s home address in the Customer table, and their shipping address on the OrderHeader table. The table should hold a minimum of two rows per customer – one row for the home address, and one row for each unique shipping address. Populate the addresses into the new address table. When finished moving the data, remove the old address fields from Customer and OrderHeader. Make sure you have a way to link the home and shipping addresses back to the correct customers and orders before you remove the address columns!

Management would also like you to add foreign keys to each table in the database where they make sense. TheER diagram

downloadshows the relationships, but the keys themselves do not exist in the database - add all the foreign keys the diagrams shows.

After making these changes to the database, create an updated ER diagram to reflect the new database structure. The ER diagram should be created in some sort of software for this purpose (LucidChart, Visio, Draw.io, etc). All tables and columns should be included. The relationships between the entities should be visible, and should denote the type of relationship (1:1, 1:M, M:1). Primary and foreign keys should be noted on the diagram.

Database Objects- to be created AFTER database changes

Create a stored procedure that takes an CustomerID and returns a result set with the customer's name and home address fields.

Create a function that calculates sales tax given a state and an amount to be taxed. If the state does not exist in the SalesTax table, the function should return 0.

Create a view that displays the customer's name, OrderID, order subtotal, tax, freight, Order total (Subtotal + Tax + Freight), and shipping address fields.

Extra Credit

(+5 EC if correct) For the address table change, store only the unique addresses per customer. For example, if a customer has the same address in the customer table and for all of their orders, they should only have one row in the address table. If they have different addresses on the customer table and any of the orders, they should have one row for every address. The IDs of the addresses in each table must match the address originally used if the customer has different home and shipping addresses.Do either the regular credit version of this or the extra credit version - not both.

(+2.5 EC if correct) For the view created in database objects #3, also include the customer's home address fieldson the same row
as the rest of the data

Submitting the Final Project:

The ER diagram should be submitted as a PDF. No other formats will be accepted. For the table changes & database objects, submit either a document or SQL file containing all the SQL statements used to accomplish each item.

awbikes-dbcreate-1-ksybw14l.sql

Neha · Accepted Answer

88519 - db/basic questions.sql
Create table Address(
AddressId int,
Address varchar(50) not null,
City varchar(30) not null,
State char(2) not null,
ZipCode varchar(10) not null,
constraint pk_Address Primary Key (AddressId));
Create table Customer(
CustomerID int,
Title varchar(8),
FirstName varchar(50) not null,
MiddleName varchar(50),
LastName varchar(50) not null,
Suffix varchar(10),
AddressId int,
ShippingAddressId int,
constraint fk_Address foreign key (AddressId) REFERENCES Address(AddressId),
constraint fk_ShippingAddress foreign key (ShippingAddressId) REFERENCES Address(AddressId),
constraint pk_Customer Primary Key (CustomerID));
Create table Vendor(
VendorID int,
Name varchar(50) not null,
constraint pk_Vendor primary key (VendorID));
Create table Category(
CategoryId int,
Name varchar(50) not null,
constraint pk_Vendor primary key (CategoryId));
Create table Product(
ProductID int,
Model varchar(50) not null,
Name varchar(50) not null,
ProductNumber varchar(25),
Color varchar(15),
ListPrice numeric(9,2),
Cost numeric(9,2),
Category varchar(50) not null,
VendorID int,
CategoryId int,
constraint pk_Product Primary key (ProductID),

AW Bikes is a bike retailer. They sell bikes and related products direct to consumers. Prior to completing any of the below tasks, you will need tosetup the database. download Database Updates:...

Database Updates:

Database Objects- to be created AFTER database changes

Extra Credit

Submitting the Final Project:

Answer To: AW Bikes is a bike retailer. They sell bikes and related products direct to consumers. Prior to...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment