MYSQL: Setting up a relational database to create tables.

Create database and table objects, apply data integrity using constraints, fill of all the tables with data, and answer questions about data.

Youll create a relational database for a small company that rents furniture to college students. First, youll create four tables. Next, youll set the relationship between these tables. Apply data integrity using constraints: Primary Key, Foreign Key, Unique, Default, Check. Choose a column in each table to apply the constraint.

Students at universities all over the country move into accommodations and then need certain items to help them live as comfortably as possible. Furniture Rental, Inc. (FRI) steps in where apartments leave off and rents sturdy furniture to college students for the academic year. This allows students to have good desks, chairs, lamps, sofas, and other furniture without buying the items. FRI has been able to strike deals with universities that allows the company to put the rental costs on the students bursar bill. This method avoids any direct billing from FRI to the student and always results in full collection of the rental fees.

The company is growing rapidly and needs your help. An owner of FRI heard that you are proficient in database design. You have been hired to finish the database project and you begin the job with the tables already designed for you. The database has four tables:

1. The Customers table keeps track of customer information such as ID, name, address, telephone number, and email address.

2. The Inventory table keeps track of all the items available to rental in particular, the item ID, its description, and price per month. Assume that an unlimited supply of furniture is available for rental. This is a reasonable assumption because FRI has10 large warehouses across the country and items are always available from at least nearby warehouse.

3. The Rentals table records an identification number for each rental, the customer who rented the item(s), the date they were rented, and the date they were returned.

4. The Rental Line Item table records the individual item IDs rented within each rental ID, along with the quantity item rented.

The owners of FRI have a few requirements for information output that they would like to see in the database beyond simple data recording. First, they want to be able to record a rental order complete with details of what customers rented.

In addition, the owners want the database to answer some questions. First, they want to see which customers rented specific items. For example, the kitchen chairs the FRI has been renting are of very poor quality and often break. After complaints to the manufacturer, FRI has been able to replace all defective chairs.

The company needs a listing of renters who have defective chairs so it can contact the renters and replace the chairs quickly. Next, the company wants to be able to send reminders to students who have not returned any rented items by December 15. This reminder is sent to make sure that students remember to return all rental items, even if they are graduating mid-year or simply moving on at the end of the fall semester.

Of course, the company needs to keep track of the most popular furniture for rental so it can plan future marketing promotion and warehouse stocking. Also, the company would like a report that calculates and then shows all the rental money billed to the student for the fall semester.

For now, you will answer just the following questions:

1. List customers from Portland city.

2. List items that are rent for less than $10/month.

Use MySQL to create the tables with the fields shown in below Figures. These tables were discussed during the analysis phase. DO NOT forget to set the data integrity constraints when required. This is sample data; you can add as much as you want. Create scripts to manipulate and meet the requirements of IRF owners for the output of information they would like to see.

rentals INT DOT | LAITIME | INT VAROR VARDAS de VW ON 10 195 200RS Tiphone VIDELSE VARCHARDS Indon wory De VARCHARDS Pich DEMA retten TALLIN Qui SMALL Customer ID - Last Name - First Name 101 Smith John 102 Chavez Mary 0 Address City 34 Redback Road Austin 9 Elm rch Road Woods Hole State TX MA Zip 78701 02543 Telephone Email (512) 998-0675 jsmith@pep.net (508) 443-3212 Figure 1. The Customers table Item ID C-101 C-102 D-101 D-102 Description Desk Chair Kitchen Chair Single Dresser Double Dresser Price Per Month $15.00 $12.00 $30.00 $35.00 $0.00 Figure 2. The Inventory table Rentals Rental Number - Customer ID - Date Rental - Date Returned 1 101 8/30/2020 12/5/2020 2 102 8/15/2020 Quantity RentalLineltem Rental ID Item ID 1 D-101 1 D-102 2 C-101 2 C-102 1 1 1 2