Can I get the answer for the second part in the task
checklist?
- Can I Get The Answer For The Second Part In The Task Checklist 1 (124.6 KiB) Viewed 13 times
KMK2263 May 10, 2022 KMK2263 DATABASE MANAGEMENT SYSTEMS ASSIGNMENT 2-STRUCTURED QUERY LANGUAGE (SQL) 15% 1. Focus • In this assignment, you will design and implement a database physical structure for keeping track of information about a CAR RENTAL
COMPANY. ● Reference:
http://www.crystal.uta.edu/~elmasri/db1/Project2_Spring 2012.doc 2. Task Requirements: Assume that the following requirements were collected for this application: • The database keeps track of CUSTOMERS. Each CUSTOMER has a unique IdNo. (assume this is a unique integer generated by the system for each new CUSTOMER, such as 1, 2, 3, ...), a Name (assume this is string consisting of an single initial and last name only for simplicity, such as "J. Julia" or "R. Tiger Wong"), and a Phone (a string of 12 characters such as "082-584-3000"). ● The database keeps track of CARs available for rental, which are categorized based on their type. There are six main types: COMPACT, medium, LARGE, SUV (Sports Utility Vehice), truck, and VAN. Each type of car has its own DailyRate and Weekly Rate (assume all cars of the same type have the same rental rates). For simplicity, we will assume that there is only one rental location. . Each CAR has a VehicleID (a unique number for each car - assume it is a number 1001, 1002, 1003, ...), Model (Mitsubishi, Isuzu, Toyota, Ford, Honda and ...), and Year (2015, 2014, ...). ● The database will keep track of the current (active) rentals as well as scheduled rentals of each car. There are two types of rental: DAILY and weekly. For each daily rental, the information kept will include the specific CAR and CUSTOMER as well as the NoOfDays, StartDate, and ReturnDate (the ReturnDate can be calculated from the StartDate and NoOfDays). For each weekly rental, the information kept will include the specific CAR and CUSTOMER as well as the NoOfWeeks, StartDate, and ReturnDate (the ReturnDate can be calculated from the StartDate and NoOfWeeks). Each rental will also have the Amount Due for the rental, which is a derived value that can be calculated from the other information. . The database will also keep track of which cars are available for rental during which periods. 3. Task Checklists Based on the case study above, you are required to proposed
solutions based on the following criteria: a) 1 You will first design an ERD diagram based upon the RENTALS database requirements specified above, and documentation report describing your design choices. As part of this assignment, you should identify any missing or incomplete requirements, and explicitly state them in your documentation. You should also explicitly state any assumptions you made that were not part of the requirements listed above. KMK2263 Database Management Systems Assignment 2
KMK2263 May 10, 2022 b) 2 The second part of the assignment will be to map the ERD design to a real relational database schema, and create the tables corresponding to the relational schema using MySQL, DBMS software. You will add to your report a listing of the CREATE TABLE statements. Specify as many constraints (key, referential integrity) as you can in the relational schema. c) 3-The third part of the assignment is to load some data into the database (Tables), and apply certain retrieval queries. You will create your own data. Include at least 10 customers, and at least 20 cars of different types. Write at least 5 queries to retrieve and print all the data you entered. Try to display/print the data so that it is easy to understand Writing Format Suggestion & Evaluation ● Write a brief report including your findings and enclose your Structured Query Language (SQL) code (print screen) from MySQL DBMS (command line). . Softcopy (PDF Format Only) . Corporate Binding, Front Cover (Check format cover on eleap) · The assignment must be typed using A4 paper with single spacing. Use the font Arial or Calibri, sized 11 points. Assignment presented must be clear and easily read. 5. Dateline Assignments must be handed over in 'soft copy' and 'uploaded in 'ELEAP e learning (PDF Format Only)' before 3 June 2022. 3 KMK2263 Database Management Systems Assignment 2 4.