Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader

Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

ICT320 Database Programming

tag 0 Download 23 Pages / 5,724 Words tag 28-09-2020

Question:

Your company, Complete Database Services (CDS) has been approached by the owner of CurRENT CarRENTals to create a database based on a design created by a consulting firm (refer to the ERD). In addition to the creation of the database, CurRENT CarRENTals would like the database to have some additional features, which are detailed below. Assignment Deliverables 3 SQL Scripts: Script 1: Create one SQL script that is used to create the database and insert the data in the appropriate tables. Name this script as CreateFirstname_idnumber.sql (where Firstname is your first name and idnumber is your student ID number).
 
Script 2: This script should incorporate the extra features of the database. Save this as Extraidnumber.sql Script 3: The second script is to include SQL statements that will clear the whole database (ie remove all data and tables etc). Save this as DeleteFirstname_ idnumber.sql. SQL Scripts The scripts will be used to recreate your database. When the scripts are run, they should not return any errors. The names of tables and attributes are to be exactly as is given to you. You may NOT provide your own naming or spelling. All scripts must include comments of what each statement is doing.
 
The following are the details of each script: Script 1 This script will include the SQL statements that are used to create the tables and the other aspects of the database. Refer to the ERD of CurRENT CarRENTals, you will see that there are 13 entities, each with a number of attributes. The tables that you create should include the attributes as they are stated below. You cannot modify the table or attribute names. a) Create the tables as shown in the ERD. Page 4 of 11 b) The attributes for each of the tables are listed. c) The attributes that are in bold are required, and cannot have Null value d) You are required to come up with your own datatypes. The datatypes must be appropriate and suit the required constraints.
 
You will have to provide justification for your choice of datatype in a report. Therefore do not assume you can enter any datatype. e) The foreign and primary keys are indicated for you. f) Create a sequence for the following Primary Keys RentalID: the first rental number to be created would be 1, other rental numbers that follow are to be incremented by 1. 999 is the limit of numbers for the rental number. Numbers can cycle. CustomerID: the first customer number to be created would be 1000, other rental numbers that follow are to be incremented by 1. 9999 is the limit of numbers for the customer number. Numbers cannot cycle. g) Insert the data into the appropriate tables.
 
The tables with the correct constraints should be created before you enter the data. For Primary Key data based on a sequence created, you must enter that data using the correct function. Refer to the data in the Excel spreadsheet. Script 2: This script should incorporate the following extra features of the database. Save this as Extraidnumber.sql 1) When new rows in the corresponding tables are entered the following data needs to be checked to make sure that the date is correct (ie either before or after the current date) Customer’s MemberDate: must be before or equal to the current date Vehicle’s PurchaseDate: must be before or equal to the current date Rental’s CheckoutDate: must be after or equal to the current date 2) .
 
When a new car is entered into the database, the value of the NextUpdate column should automatically be set to one month from the date the vehicle was added to the table. Page 5 of 11 3) The value of a vehicle will depreciate monthly. When the date of the NextUpdate for each vehicle arrives, the value of the vehicle should depreciate by 2.5% monthly. When the value has been updated, the date of the NextUpdate column of the Vehicle table should be set to one month from the date it was updated. (Don’t worry about situations where the date has been missed – assume that the system will be turned on every day, without fail) 4) When a vehicle is rented (ie when a row is added to the rental table), the database should do the following: a) Automatically calculate the duedate depending on how many days the vehicle is rented out for. b) Automatically calculate the rent to be charged and input it into the rentalcharge table.
 
The rental of a car is calculated as follows: (car type charge + 0.1% of the value of the car) per day + any insurance A discount will apply depending on how many days the rental is for. The discount does not apply to the insurance. c) If any accessories are rented (ie new rows added to the rentaccessory table), the quantity of that accessory (in the accessory table) should be reduced by the quantity taken. Also, the total charge of the accessories rented should be added to the rental charge previously calculated in (b) above. 5) When the vehicle is returned (ie when the checkin date - which was originally NULL - is updated for that rental), the following should be done:
 
 
a) Values for the fields OdometerIn, FuelLevel, Damage must be entered and therefore cannot be NULL when the car is returned.
b) The number entered for the OdometerIn in the rental table should automatically be updated for that vehicle in the vehicle table.
c) If the fuel tank is not full (ie, the level is not 1 but: 0.75, 0.5, 0.25, 0) the customer is charged for a tank fill up. (The cost that is charged is the (1- fuellevel that is entered) * fueltank (available in the vehicle table) * the Page 6 of 11 currentcost (found in the fueltype table)). Once calculated this charge should be entered in the rentalcharge table.
d) If the vehicle is not returned by the duedate a penalty is incurred (which is calculated as the rent charge per day plus a penalty. The penalty will differ depending on the number of days overdue).
e) If there are damages to the car, and the customer didn't take the full insurance option, the damages are paid by the customer. If Partial Insurance is taken, the customer pays for half of the damages. The damages is calculated when a value is entered in the damage field of the rentalcharge table.
 
Note: This is different to the other charges where the charges are calculated automatically and entered into the rentalcharge. However, for damages, the value has to be ascertained by the user physically, and so once the damage value is identified, it is entered manually into the rentalcharge table. What is required then is for the database to identify if it should make a change to that value by checking the type of insurance that the customer has taken out for that rental. So if there is full insurance, the initial damage value will be replaced with 0, if there is partial insurance, the initial damage value will be halved and if there is no insurance the initial damage value will be kept the same. f) If any accessories were rented, the quantity for that accessory should be increased by 1. 7) Old rentals – 6 months from the checkin date should be deleted from the rental table. 8)
 
12 month old vehicles (from date of purchase) will no longer be available for rent, and will be put up for sale. The database should automatically remove the old vehicle from the vehicle table and add it to the sale table. The data that is to be entered is similar to what is in the vehicle table, except for a slight variation: the FuelType, EngineCapacity and VehicleType will be added to the description field. (The FuelType and VehicleType should be the actual type not the number and the EngineCapacity should have the word ‘litre’ after the value) NOTE:
 
If a vehicle has become 12 months old and is currently out on rental (ie the checkin date is NULL), it should not be removed from the vehicle table. Therefore, any rentals that are returned should also check for 12 month old vehicles. Page 7 of 11 Script 3: The third SQL script is to include SQL statements that will clear the whole database after it has been populated. Save this as DeleteFirstname_ idnumberql.
 
Column names and constraints Customer Constraint CustomerID (PK) From 1000 – 9999 Lastname Up to 20 letters Firstname Up to 15 letters Address Up to 30 letters Postcode 4 digits State From (QLD, ACT, NSW, WA, SA, TAS, NT, VIC) CreditCard Must be 16 digits Phone 10 digit will start with 0 MemberDate Date Accessory Constraint AccessoryID (PK) From 10 – 99 Description Up to 30 letters Cost A number in the form of 999.99 Qty A whole number up to 999 Insurance Constraint InsuranceID (PK) A single letter from A – Z CurRENT CarRENTals Entity relationship diagram Page 9 of 11 InsuranceType Up to 15 letters Price A number in the form of 999.99 Penalty Constraint PenaltyType (PK) .
 
The word penalty followed by a single number (eg. Penalty1) MinDay Up to 3 digits MaxDay Up to 3 digits Cost A number in the form of 999.99 Vehicle Constraint Rego (PK) A combination of 3 numbers then 3 letters. No personalised plates Description Up to 60 letters Make Up to 15 letters Model Up to 15 letters Year 4 digits (not a date) Value A whole number up to 6 digits EngineCapacity A number in the form of 9.9 FuelTank Up to 3 digits (whole number) Odometer Up to 6 digits (whole number) PurchaseDate Date NextUpdate Date VehicleTypeID (FK) A single digit (whole number) FuelTypeID (FK) A single digit (whole number) FuelType Constraint FuelTypeID (PK) A single digit (whole number) FuelType Up to 15 letters CurrentCost A number in the form of 99.99
 
Rental Constraint RentalID (PK) From 1 -999 BookingDate Default current date BookingMethod Up to 15 letters Checkout Date RentDays A whole number up to 2 digits Checkin Must be after or equal to checkout date DueDate Date OdometerIn Up to 6 digits (whole number) FuelLevel A number from: (1, 0.75, 0.5, 0.25, 0) Damage 'Y' or 'N' Payment_ID (FK) From 1 – 9 Rego (FK) A combination of 3 numbers then 3 letters. No personalised plates CustomerID (FK) From 1000 – 9999 InsuranceID (FK) A single letter from A – Z Discount Constraint Page 10 of 11 DiscountType (PK) A combination of 1 letter and 2 digits MinDay Up to 3 digits MaxDay Up to 3 digits Percent In the form of .99 VehicleType Constraint VehicleTypeID .
 
(PK) A single digit VehicleType Up to 15 letters Charge In the form of 99.99 RentAccessory Constraint RentalID (PK) (FK) From 1 – 999 AccessoryID (PK) (FK) From 10 – 99 Qty A whole number up to 999 RentalCharge Constraint RentalID (PK) (FK) From 1 – 999 Rent A number in the form of 9999.99 Damage A number in the form of 99999.99 Fuel A number in the form of 999.99 Overdue A number in the form of 9999.99 PayType Constraint Payment_ID (PK) From 1 – 9 Pay_Type up to 15 letters Sale Constraint Rego (PK) A combination of 3 numbers then 3 letters.
 
No personalised plates Description Up to 150 letters Make Up to 15 letters Model Up to 15 letters Year 4 digits (not a date) Odometer Up to 6 numbers (whole number) Price A whole number in the form of 999999 Page 11 of 11 Appendix A Marking Sheet for ICT320 Task 2 Student name: Student ID: Items Maximum Marks Marks Obtained Script 1: Database Creation (35 marks) Table creation 20 marks Inserting Data 5 marks Sequence Creation and use 10 marks 35 Script 2: Additional Functionality (60 marks) (1) 2.5 marks (2) 2.5 marks (3) 2.5 marks (4) 15 marks (5) 25 marks (6) 2.5 marks (7) 5 marks (8) 5 marks 60 Script 3: Database Deletion (5 marks) 5 Total = 100.
Download Sample Now

Earn back the money you have spent on the downloaded sample by uploading a unique assignment/study material/research material you have. After we assess the authenticity of the uploaded content, you will get 100% money back in your wallet within 7 days.

Upload
Unique Document

Document
Under Evaluation

Get Money
into Your Wallet

Total 23 pages

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). ICT320 Database Programming. Retrieved from https://myassignmenthelp.com/free-samples/ict320-database-programming.

"ICT320 Database Programming." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/ict320-database-programming.

My Assignment Help (2020) ICT320 Database Programming [Online]. Available from: https://myassignmenthelp.com/free-samples/ict320-database-programming
[Accessed 28 January 2023].

My Assignment Help. 'ICT320 Database Programming' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/ict320-database-programming> accessed 28 January 2023.

My Assignment Help. ICT320 Database Programming [Internet]. My Assignment Help. 2020 [cited 28 January 2023]. Available from: https://myassignmenthelp.com/free-samples/ict320-database-programming.


Stuck on Any Question

Our best expert will help you with the answer of your question with best explanation.

question
We will use e-mail only for:

arrow Communication regarding your orders

arrow To send you invoices, and other billing info

arrow To provide you with information of offers and other benefits

Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

loader
250 words
Error goes here

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

We Can Help!

Get top notch assistance from our best tutors !
Excel in your academics & career in one easy click!

icon

Other Samples

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

icon

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?
close
Subtraction Payment required!

Only one step away from your solution of order no.