ICT320 Database Programming Task 2: Data Tables and Data Types Report

This document presents a solution for ICT320 Database Programming Task 2, focusing on the creation and definition of data tables and data types for a car rental database system. It includes detailed specifications for various tables such as Customer, Accessory, Insurance, Penalty, VehicleType, FuelType, Discount, Payment, Vehicle, Rental, RentAccessory, RentalCharge, and Sale. Each table's fields, data types, and reasons for selecting those data types are thoroughly explained, ensuring data integrity and efficient storage. The report also includes primary key (PK) and foreign key (FK) designations to establish relationships between tables. References to database normalization principles are provided to support the design choices.

ICT320
Database Programming
Task 2
Student ID-
Student Name-
1 | P a g e

Report
Data Tables and Data types
Customer
Field Datatype Reason
CustomerID (PK) INT(4) The field is containing
max 9999
Lastname VARCHAR(20) The field is containing up
to 20 characters
Firstname VARCHAR(15) The field is containing up
to 15 characters
Address VARCHAR(30) The field is containing up
to 30 characters
Postcode CHAR(4) The field is containing 4
characters
State ENUM Fixed data sets
CreditCard CHAR(16) The field is containing 16
characters
Phone CHAR(10) The field is containing 10
characters
MemberDate Date
Accessory
Field Datatype Reason
AccessoryID (PK) INT(2) The field is containing
max 99
Description VARCHAR(30) The field is containing up
to 30 characters
Cost Decimal(5,2) The field is containing
max 999.99
Qty INT(3) The field is containing
max 999
2 | P a g e

Insurance
Field Datatype Reason
InsuranceID (PK) CHAR(1) The field is containing 1
character
InsuranceType VARCHAR(15) The field is containing up
to 15 characters
Price Decimal(5,2) The field is containing
max 999.99
Penalty
Field Datatype Reason
PenaltyType (PK) VARCHAR(10) The field is containing up
to 10 characters
MinDay INT(3) The field is containing
max 999
MaxDay INT(3) The field is containing
max 999
Cost Decimal(5,2) The field is containing
max 999.99
VehicleType
Field Datatype Reason
VehicleTypeID (PK) INT(1) The field is containing
max 9
VehicleType VARCHAR(15) The field is containing up
to 15 characters
Charge Decimal(4,2) The field is containing
max 99.99
FuelType
3 | P a g e

Field Datatype Reason
FuelTypeID (PK) INT(1) The field is containing
max 9
FuelType VARCHAR(15) The field is containing up
to 15 characters
CurrentCost Decimal(5,2) The field is containing
max 999.99
Discount
Field Datatype Reason
DiscountType
(PK)
CHAR(3) The field is containing 3
characters
MinDay INT(3) The field is containing
max 999
MaxDay INT(3) The field is containing
max 999
Percent Decimal(2,2) The field is containing
max .99
PayType
Field Datatype Reason
Payment_ID (PK) INT(1) The field is containing
max 9
Pay_Type VARCHAR(15) The field is containing up
to 15 characters
Vehicle
Field Datatype Reason
Rego (PK) CHAR(6) The field is containing 6
characters
Description VARCHAR(60) The field is containing up
to 60 characters
Make VARCHAR(15) The field is containing up
to 15 characters
Model VARCHAR(15) The field is containing up
to 15 characters
Year CHAR(4) The field is containing 4
characters
Value INT(6) I The field is containing
max 999999
4 | P a g e

EngineCapacity Decimal(2,1) The field is containing
max 9.9
FuelTank INT(3) The field is containing
max 999
Odometer INT(6) The field is containing
max 999999
PurchaseDate Date
NextUpdate Date
VehicleTypeID (FK) INT(1) The field is containing
max 9
FuelTypeID (FK) INT(1) The field is containing
max 9
Rental
Field Datatype Reason
RentalID (PK) INT(3) The field is containing
max 999
BookingDate Timestamp
BookingMethod VARCHAR(15) The field is containing
up to 15 characters
Checkout Date
RentDays INT(2) The field is containing
max 99
Checkin Date
DueDate Date
OdometerIn INT(6) The field is containing
max 999999
FuelLevel Enum
Damage CHAR(1) The field is containing 1
character
Payment_ID (FK) INT(1) The field is containing
max 9
Rego (FK) CHAR(6) The field is containing 6
character
CustomerID (FK) INT(4) The field is containing
max 9999
InsuranceID (FK) CHAR(1) The field is containing 1
character
RentAccessory
5 | P a g e

Field Datatype Reason
RentalID (PK) (FK) INT(3) The field is containing
max 999
AccessoryID (PK) (FK) INT(2) The field is containing
max 99
Qty INT(3) The field is containing
max 999
RentalCharge
Field Datatype Reason
RentalID (PK) (FK) INT(3) The field is containing max
999
Rent Decimal(6,2) The field is containing max
9999.99
Damage Decimal(7,2) The field is containing max
99999.99
Fuel Decimal(5,2) The field is containing max
999.99
Overdue Decimal(6,2) The field is containing max
9999.99
Sale
Field Datatype Reason
Rego (PK) CHAR(6) The field is containing
6 characters
Description VARCHAR(150) The field is containing
up to 150 character
Make VARCHAR(15) The field is containing
up to 15 character
Model VARCHAR(15) The field is containing
up to 15 character
Year CHAR(4) The field is containing
4 characters
Odometer INT(6) The field is containing
max 999999
Price INT(6) The field is containing
max 999999
(Carlo Zaniolo 1982)
(P. Brombarg n.d.)
6 | P a g e

References
P. Brombarg,(n.d.), SQL SERVER Database Normalization Basics for Developers, [Online].
Available: http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-
developers.aspx.
Carlo Zaniolo, A New Normal Form for the Design of Relational Database Schemata,
September 1982
7 | P a g e