PLEASE ONLY ANSWER IF YOU WILL ANSWER ALL QUESTIONS. ALL QUESTIONS ARE TOGETHER

AND CANNOT BE SPLIT.

[Q.3] Design and create a MusicStore" database for developing application for selling music files to the customers in the Internet environment. Data Requirements: Note: for this question, we do not need to have actual music files, but a music file name must be unique. You can make reasonable assumptions about the data requirements described below. Each customer must have a customerId (we assume that the customer already registered for the web music delivery service), an email address, first name, last name, street address, city, state, zip code, and a phone number). Each product (i.e., music file) must have a productId, unique name, artist name (who played the music), and unit price. Each customer can have one or more music file downloads. Same music file can be downloaded one or more times for the customer and for her/his friends. After music files are downloaded by a customer, the customer must pay the total amount of the downloaded (i.e., purchased) music files by a credit card. Valid card types are VI (visa), AX (American Express), and CA (Master Card). Each music file download must be identified by a filename (or fileId if you use unique fileld as a primary key), download date and time. The music store needs to keep each customer's purchase history. 1. Create a conceptual MusicStore database schema in ER diagram. 2. Map the conceptual MusicStore database schema to logical database schema. The logical database schema must show referential constraints by drawing an arrow that connects a foreign key column of a referencing table to a primary key column of the referenced table. 3. Use MySQL Workbench to create an ER diagram for a MySQL MusicStore database that stores information about customers, music files, the details of the music file purchase history of each customer. The purchase history needs to store valid credit card type and number. Total amount of the purchased music files must be derived from the database. Include the MySQL ER diagram in your assignment 1 report (Use any screen capturing/editing tool for this). Refer to an example MySQL mwb ER diagram shown below. customers customerid INT(11) firstName VARCHAR(50) lastName VARCHAR(50) address VARCHAR(255) city VARCHAR(50) State CHAR (2) zip VARCHAR(20) phoneNo VARCHAR(15) Indexes I orders products productId INT(11) title VARCHAR(50) + artist VARCHAR(50) unitPrice DECIMAL (9. Indexes orderId INT(11) customerId INT(11) orderDateAndTime DATETIME Indexes describeOrder Details orderId INT(11) productId INT(11) orderQty INT(11) downloadDateAndTime VARCHAR(45) creditCard Type VARCHAR(3) creditcard Number VARCHAR(25) Indexes 4. Use MySQL Workbench forward engineer tool (the tool is available in database pull down tab as shown below): O O Save the SQL generated by the Workbench as createMusicStore.sql (SQL DDL). Include a statement to drop the database if it already exists. Include statements to create and use the database. o Include statements to drop and create tables of the database. Include any foreign keys and indexes for each table. o Specify the utf8 character set for all tables. Include a part of the script (i.e., createMusicStore.sql) in your report. O MySQL Workbench Local instance MySQL56 x Edit View Arrange Model File MySQL Model (web Music Store.m..X EER Diagram Database Tools Scripting Help Connect to Database... Ctrl+U Manage Connections... Bird's Eye www Ctrl+R Zoom: 100% - Reverse Engineer... Forward Engineer... Ctrl+G Schema Transfer Wizard... Migration Wizard... Edit Type Mappings for Generic Migration... Synchronize Model... Ctrl+Shift+Z Synchronize with Any Source... Ctrl+Shift+Y Compare Schemas... 5. Write SQL statements (i.e., insert statements) to populate database tables. Each table must have at least 3 tuples. 6. Write SQL scripts to retrieve tuples of each table populated. 7. Write a SQL script to retrieve music file names purchased by each customer