-- > Write a SQL script that generates the proposed database in SQL Server/MySQL BASED ON THE FOLLOWING MUSIC library . Note that your data types and constraints may vary based on the specific DBMS, but otherwise, there should be a direct 1 to 1 relationship between the diagram and the SQL script. Include in your SQL script the following: A NOT COMMENTED OUT CREATE DATABASE statement, name your database "lname_fname_db" SQL commands for CREATE TABLE Foreign keys and referential integrity actions No other constraints are necessary in the CREATE TABLE statements Any necessary views (see below) SQL commands to enter data into the table.

1) Based on the following proposal application, you need to write a SQL script that generates the proposed database in SQL Server/MySQL

Here is Database proposal for Music library: -

The following database design proposal is to develop a music library: - Below steps are to be performed: - 1)Description: - This is to design a database for music library. The library will have information for employee, customers, products.

All these must be done in a relational database with creation of ER diagram. Proper normalization also must have done to avoid ambiguities. Relational: -Connected tables will be used to stored information about products and customers. There will be two major sets of tables - product related and customer related. The product - related tables will be used to store product information such as name, Id, rental date, return date purchased, etc. The customer-related tables will be used to store information such as customers

name, membership Id, residence information, cell phone number etc.

2)Functionalities to be implemented: -

a) Add customer, Delete customer, and update info of customer. b) Add Employees, delete employees, update info of employees. c) Add/Modify/Delete product (CDs, Videos, audios)

rental history, email address,

d) Sale/purchase of product. e) Generating reports (inventory report, salary report, sales report, payment due report,) f) Customer purchase, customer return, customer rating

Use MySQL to write them

3) Additional functionalities: -

a) Use of triggers: - Say trigger can be stored for some events like: - customer abc has returned the product within a day or two after purchase customer has rated the product poor after receiving product.

Use MySQL to write them b) GUI (Graphic User Interface):- The database application should have a user friendly gui

having descriptive buttons, tabs, frames ,links, contact us information about us information, mailing information.

.

4)Attributes of the application

Entity Employee Name Customer Sales product Purchase Rental Inventory

Attributes Id, phone number, designation, salary, address Name, Membership Id, phone number, address, email

Quantity, product id, customer, payment, payment mode payment id Product, product Id, quantity, payment, purchase id

Product, Product Id, rental date, return date product id, product name, category, in stock