Vinyl Vortex has already migrated their inventory data to the new system; therefore, you will find the Album and AlbumItem tables are already created and populated from the previous assignment. The next data needed to migrate into the new system is customer account information, the data and format which has been provided in the Customer However, Vinyl Vortex was unable to transfer the records for orders and invoices to the new system; therefore, you will have to create the new Order and OrderItem tables from scratch.
Customers may make multiple order purchases. For each order, we must store the order number, the date of the order, along with a reference to the customer that placed the order. Each order consists of multiple album items, which need to be stored in the OrderItem table, that corresponds to each item in an order. Thus, each record in the OrderItem table will reference the OrderID of the associated Order, and the AlbumItemID of the associated AlbumItem. Then, each OrderItem record also needs to track the quantity purchased of each AlbumItem in that Order.
Create three CREATE TABLE statements that add the tables described above and provided in the Customer spreadsheet. You may download the working file for your convenience: SQL-DD-ASN1.sql. To create each table, performing the following steps:
Identify attributes and fields for the Customer, Order and OrderItem tables. Attributes for the Customer table may be drawn from the Customer spreadsheet, while Order and OrderItem are drawn from the description above.
Choose field names and data types for each attribute of the Customer, Order and OrderItem table.
Choose the primary key fields for each table and define primary key constraints on the field.
Choose the foreign key fields in each table that match the values of the primary key fields in related tables. Define foreign key constraints on the foreign key fields.