Categories
Computer Science

Must be able to provide runable code and also provide queries Assignment: Consid

Place your order now for a similar assignment and have exceptional work written by our team of experts, At affordable rates

For This or a Similar Paper Click To Order Now

Must be able to provide runable code and also provide queries
Assignment: Consider the below schema (primary keys are in bold and underline):  Customers(custNo, custName, email, phone, login, password) o A customer has a unique customer identifier, name, email, phone, unique login, and password.  Plans(planID, progName, startedDate, maxNoRentals, weeklyFee) o Each plan has a unique plan identifier, a plan name (e.g., “Basic”, “Rental Plus”, “Supper Access”, etc.), started date, the maximum number of rentals allowed (e.g., “Basic” allows 3 equipment per week; “Rental Plus” allows 10.) and a weekly fee.  Equipment(equipID, equipName, category, model, yearMade, maintDate, status ) o Each piece of equipment has a unique identifier (equipID), name, category, model, year make, maintenance date (maintDate), and status. Only the most recent maintenance date is recorded. The status is used to document its availability and its possible values are available and rented.  Rentals(equipID, custID, rentDateTime, status) o The rentals table represents the fact that a piece of equipment was rented by a customer with a specific date and time. The status is used to specify any damages. To distinguish multiple rentals of the same equipment by the same customer, its primary key is a composite key (equipID, custID, rentDateTime). When a customer rents a piece of equipment, an entry in Rentals should be added, and its status should be recorded, such as “Good condition.” Keeping a rental history helps to improve the rental business by doing data mining.  Contracts (custID, planID, since, startedDate, endedDate, contractLength) o The Contracts table represents the fact that a customer signed a specific rental plan. Each contract has the initial signed up date (sine), program started date, ended date, and the contract length. Each contract can last from anywhere from 4 weeks to 54 weeks. Specifically, the foreign keys for this database are as follows: o the attribute equipID of relation Rental that references relation Equipment, o the attribute custID of relation Rental that references relation Customers, o the attribute custID of relation Contracts that references relation Customers, and o the attribute planID of relation Contracts that references relation Plans. Project Tasks: Task 1: Relational Database (30 %) a) Create a SQL script file with CREATE TABLE statements and INSERT statements that populate each table with at least 20 records. This file should be runnable on MySQL. Add DROP TABLE statements or a DROP DATABASE statement to the beginning of the script file so it can be run whenever you need to debug your schema or data. b) To support the store’s daily operations, you need to identify at least ten business functions. Elaborate on each function by discussing its required input data, possible output information, anticipated frequency, and anticipated performance goal. In addition, you should write SQL statement(s) for each of the business functions. There should be  three queries involving GROUP BY, HAVING, or aggregate operators;  at least one query involving GROUP BY, HAVING, and aggregate operators;  at least five queries with at least two selection conditions;  at least four queries involving two tables;  at least two queries involving at least three tables; and  at least four queries involving sorting results.

For This or a Similar Paper Click To Order Now

Leave a Reply