All questions in this assignment refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script.

Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. You are encouraged to test your SQL statements in Workbench, and please use the ‘tidy’ tool to properly format your SQL before you save it as a Script from Workbench or Notepad++. Use comments to indicate the question number for each answer. Please give all code. Don't give same like other code. Please do with error free.

1. Write a SELECT statement that returns the title, artist and unit_price columns from the items table. Return only those items that have a unit_price of at least $16, but less than $17.50

2. Write a SELECT statement to return all columns from the customers table where the customer’s state is Ohio (OH). Sort the results by last name in descending order.

3. Write a SELECT statement to return all columns from the customers table whose zip code begins with a “9”

4. Write a SELECT statement to return the order_id from the orders table where the number of days between the order_date and the shipped_date is less than 6

5. Expand the statement in question 4 to include the number of days between order and ship date as a column alias called ‘processing_days’ and sort the results by that column in ascending order

A co-worker reviews a database design you’ve been working on . In it, they notice that you include the following in your customer entity:

Customer Name (Frist, Middle Initial Last)

Customer Address

i.Street address

ii.City

iii.State

iv.Zip

….

Your co-worker argues that having the City is unnecessary duplication of data, as if you know the zip code, the city can be determined. They also argue that your design violates 3NF (because of the inclusion of both City and Zip).    In addition to your desire to tell him to …, you also want to answer the specific questions. Namely, does this design violate 3NF, and if so why?   If it does violate 3NF, what would it look like (in terms of Entities, Attributions, and Relationships) of the model that doesn’t violate 3NF.   Finally, if it does violate 3NF, what would your rationale be to keep it as is?

Draw an ER diagram with these attributes ( ER diagram for SQL for a library database)

DRAW ER DIAGRAM AS ONE FULL LIBRARY SYSTEM, NOT DIFFERENT FOR EACH ATTRIBUTE.

Attributes :

          Customer

• Cust_ID: key identifier, required, simple, single valued

• Cust_Name{ first name, last name}: Key Identifier, simple;composite, multivaried

•   Address{street, city,zip,state}: Customer address, required, composite, single can be derived from zip

• (placeholder, there should be another attribute here to represent the books taken out by the customer. Not sure.)

          Inventory

• Book_ID: Key identifier, required, simple single valued

• Book_Name: Key identifier, required, simple, single-valued

• Genre: required: simple; single valued

• Publication_Date: required; simple; single valued

         Transaction

•    Book_ID: Key identifier, required, simple, single valued

• Rental_Date: required, simple, single valued

• Rental cost: required, simple, single valued

• Rental_Date: required, simple, single valued

JAVA PLEASE

In this project, we are going to build a tiny database organized as a singly linked list for storing and retrieving the information of a group of products on sale. The information of each product includes the following items: Product ID, Name, Seller, Quantity In Stock, Average Shipping Time, Original Price, Current Price. We assume that a node in the linked list should be defined by the following class.

class Product

{

     long ID;

     String name;

     String seller;

     long quantityInStock;

     int averageShippingTime; // number of days

     double originalPrice;

     double currentPrice;

     // implement the constructors and methods if necessary

}

Then please implement a class ProductList for the linked list according to the following requirements. Also, you need to appropriately define the constructors and methods in the class Product.

1. (5 points) Implementing a constructor for ProductList which creates a product list using the information provided in a file. An example is given in the attachment by the file products.txt.
2. (5 points) Implementing a constructor for ProductList which creates a new but identical product list based on the given list.
3. (5 points) Implementing a method in ProductList which shows (displays) the products in the list in the ascending order of the current price.
4. (5 points) Implementing a method in ProductList which shows (displays) the products in the list in the ascending order of the discount which is calculated as

(original price - current price)/original price

You need to decide the implementation and usage of any auxiliary methods. You may decide which sorting algorithm is used in sorting a product list. Please provide a main method to show the test cases for all above items and make sure that your source code can be successfully compiled and there is no exception in the run your program.

(20 points) In this project, we are going to build a tiny database organized as a singly linked list for storing and retrieving the information of a group of products on sale. The information of each product includes the following items: Product ID, Name, Seller, Quantity In Stock, Average Shipping Time, Original Price, Current Price. We assume that a node in the linked list should be defined by the following class.

class Product

{

     long ID;

     String name;

     String seller;

     long quantityInStock;

     int averageShippingTime; // number of days

     double originalPrice;

     double currentPrice;

     // implement the constructors and methods if necessary

}

Then please implement a class ProductList for the linked list according to the following requirements. Also, you need to appropriately define the constructors and methods in the class Product.

1. (5 points) Implementing a constructor for ProductList which creates a product list using the information provided in a file. An example is given in the attachment by the file products.txt.
2. (5 points) Implementing a constructor for ProductList which creates a new but identical product list based on the given list.
3. (5 points) Implementing a method in ProductList which shows (displays) the products in the list in the ascending order of the current price.
4. (5 points) Implementing a method in ProductList which shows (displays) the products in the list in the ascending order of the discount which is calculated as

(original price - current price)/original price

You need to decide the implementation and usage of any auxiliary methods. You may decide which sorting algorithm is used in sorting a product list. Please provide a main method to show the test cases for all above items and make sure that your source code can be successfully compiled and there is no exception in the run your program.

Draw an ER diagram with these attributes ( ER diagram for SQL for a library database)

Attributes :

          Customer

• Cust_ID: key identifier, required, simple, single valued

• Cust_Name{ first name, last name}: Key Identifier, simple;composite, multivaried

•   Address{street, city,zip,state}: Customer address, required, composite, single can be derived from zip

• (placeholder, there should be another attribute here to represent the books taken out by the customer. Not sure.)

          Inventory

• Book_ID: Key identifier, required, simple single valued

• Book_Name: Key identifier, required, simple, single-valued

• Genre: required: simple; single valued

• Publication_Date: required; simple; single valued

         Transaction

•    Book_ID: Key identifier, required, simple, single valued

• Rental_Date: required, simple, single valued

• Rental cost: required, simple, single valued

• Rental_Date: required, simple, single valued

in java

Write a contacts database program that presents the user with a menu that allows the user to select between the following options:

• Save a contact.
• Search for a contact.
• Print all contacts out to the screen.
• Quit

If the user selects the first option, the user is prompted to enter a person's name and phone number which will get saved at the end of a file named contacts.txt.
If the user selects the second option, the program prompts the user asking for the name of the contact. It then searches the contacts.txt for a matching name. If found, it displays the phone number on the screen. If not found, it will display an appropriate error message.
If the user selects the third option, the program displays all contacts stored in contacts.txt in a neat table.
The program is menu driven and will repeat presenting the menu and processing choices until the user selects the fourth option to quit.
If the user selects an invalid option, an appropriate error message should be displayed.
If the user selects to print all contacts to the screen with no stored contacts, an appropriate error message should be displayed.

You may use message dialogs or make it a purely console-based application

Draw the ER diagram for the following:

1. Emerging Electric wishes to create a database with the following entities and attributes: (10)

• Customer, with attributes Customer ID, Name, Address (Street, City, State, Zip Code), and Telephone

• Location, with attributes Location ID, Address (Street, City, State, Zip Code), and Type (values of Business or Residential)

• Rate, with attributes Rate Class and RatePerKWH After interviews with the owners, you have come up with the following business rules:

• Customers can have one or more locations.

• Each location can have one or more rates, depending on the time of day.

Draw an ERD for this situation and place minimum and maximum cardinalities on the diagram. State any assumptions that you have made.

The Scenario:

A company is transitioning from a spreadsheet model to a database to track customers and purchases.

The Request:

• You need to normalize the data up to N3.

Requirements and Information:

• Normalize the below spreadsheet:
  • Normalize_Assignment2.csv

Need help as the following:

Upload an excel spreadsheet wit the normalized data

Expert Answer

An expert answer will be posted here

When customers place an order, the order details will need to be stored into the database, as well as details of the customer. Naturally this will include details such as name, address and phone number. For each order they can order multiple t-shirts. For each t-shirt, they will be allowed to choose:

- The style of the t-shirt

- The sleeve configuration

- The colour of the t-shirt

- The size of the t-shirt

- The text/image/logo to be printed

- The material used for t-shjirt

- The material used for the text/image/logo

It is extremely important to store the data in a structured format, because it will be used to send directly to the automated T-Shirt create equipment to be instantly created and shipped. Customers will be required to pay before the products are sent so details will need to be kept of the payment. Your friends are planning to accept payment by credit card, direct bank deposit and paypal. For credit cards they need to store the credit card number and expiry date, for direct deposit they need a field to tick off that the payment has appeared in their bank account and for paypal they again need a field to tick off plus the paypal user id of the payer.

REQUIREMENTS – PASS/CREDIT – DATA MDOEL

Create an ER diagram, relational model and any business rules or assumptions made.

REQUIREMENTS – HIGHER LEVEL – SQL IMPLEMENTATION

For additional marks, provide the SQL commands to create the tables and insert a few rows into each table. Also provide several business question and SQL queries to test out the tables, include at least a:

• SELECTION condition query

• GROUP BY query

• JOIN query

• NESTED query

Also create a least one visualisation of the data using Orange/Tableau/Excel.

Overview

The purpose of this assignment is to give you experience designing a database and building an Entity Relationship Diagram in a business scenario. Submit all tasks in one document. Include headings in your document.

The organization is a dentist's office. When new patients are seen for the first time, they complete a patient information form that asks for their name, address, phone number, and brief medical history, which are stored in the patient information file. When a patient calls to schedule a new appointment or change an existing appointment, the receptionist checks the appointment file for an available time. Once a good time is found for the patient, the appointment is scheduled. If the patient is a new patient, an incomplete entry is made in the patient file; the full information will be collected when the patient arrives for the appointment. Because appointments are often made far in advance, the receptionist usually mails a reminder postcard to each patient 2 weeks before the appointment.

Consider the information needed for the patient billing system:

• Patient information - The system should be able to retrieve their contact information as well as their age, blood type, occupation, employer information, and insurance carrier information.

• Insurance carrier information -The system should be able to retrieve the number of patients on plan and the carrier's contact information.

• Dentist information - The system should be able to retrieve information about the dentist.

Instructions

After completing the learning activities for this module, use what you have learned to complete this assignment:

1. Create a Class diagram. Draw a conceptual class diagram with a minimum of 7 classes.

2. Create an Entity Relationship Diagram (ERD). Include a minimum of 7 entities and 50 attributes.

3. Create a CRUD Matrix.