Write a sql script that generates the proposed database

Assignment Help Computer Engineering

Reference no: EM132213899

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 (CD's, 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.

Reference no: EM132213899

Questions Cloud

Compute the effect of transactions on vince current ratio : Be sure to make an electronic copy of your answer before submitting it to Ashworth College for grading. Unless otherwise stated, answer in complete sentences.

Write separate functions to implement insertion sort : Write a single program to compare between insertion sort and selection sort. Write separate functions to implement insertion sort and selection sort.

Write a sort function to sort numbers smallest to largest : Write a sort function: This function should take 2 int parameters (passed by reference), and sort them from smallest to largest.

Write statements to define an input and an output stream : Write statements to define an input stream and an output stream and associate them to the following files, respectively: in.dat, out.dat.

Write a sql script that generates the proposed database : Write a SQL script that generates the proposed database in SQL Server/MySQL BASED ON THE FOLLOWING MUSIC library .

Under what conditions can larry exclude the award : Larry Bounds has won the gold bat award for hitting the longest home run in major league baseball this year. The bat is worth almost $35,000.

Assign the third through fifth character of a string s : Write a singlrepython function statement to assign the third through fifth character of a string s to an identifier called result.

Would the plan outlined in article work for premier lighting : Consider how Premier Lighting might benefit from implementing activity-based costing. Would the plan outlined in the article work for Premier Lighting?

Write a single program to perform the miles to kilometers : Your program should ask the user for input and display the output following each conversion.

User Account

All Pages