Assessment Database Design Project Due Date Weighting 60 Len

Assessment Database Design ProjectDue Dateweighting60length And Fo

Assessment: Database Design Project Due date: Weighting: 60% Length and Format : A word document and MySQL files Assessment Details: You will design and implement a database that support a firm involved in various activities. It must have stored functions and procedures. The database can be accessed by a specific user with suitable security and authentication facilities. Assessment addresses SLOs b Model, design and implement complex database structures (including databases distributed over a network) using a relational database management system (RDBMS) C Apply advanced SQL programming (often as a stored procedure) to fulfil complex data/information retrievals from an RDBMS d Develop and apply scripts and configure web server software as part of a Web database e Examine how modern RDBMSs manage multiple concurrent user access to a database as well as how these RDBMSs manage online or real-time backup and recovery processes Marking Criteria Assignment Requirements You have started a small consulting services business in developing databases.

WIN Car rental services is a small business in Sydney. As the name suggests they are in the business of car rentals. They approached you to develop a database for their business. Till date they are using an excel file to manage their business. The excel file is attached with this assignment document.

Have a look at it before you start working on the assignment. The entities required for the database are as follows: · Customer – customerId, lastName, firstName, address, postcode, state, phone, memberDate. · Insurance - insuranceId, insuranceType, price · Vehicle : rego, description, make, model, carYear, value, engineCapacity, odometer, purchaseDate, vehicleTypeId. · Rental : rentalId, bookingDate, bookingMethod, checkout, rentDays, checkin, dueDate, odometerIn, damage, paymentId, rego, customerId, insuranceId. · VehicleType : VehicleTypeID, VehicleType, Charge. · RentalCharge: RentalID, Rent. · PayType : Payment_ID, Pay_Type. Along with the database files you were asked to submit other documentation as listed below: Task 01: A.

ER Diagram in Crows Foot Notation. (Draw using Draw.io) a. Primary keys and foreign keys must be identified. b. All attributes must be identified. c. All relationships (both directions) must be clearly listed. B.

Data dictionary (create in Excel) C. Any other required information. Task 02: The following SQL files must be submitted. A. SQL Statements to create the database and insert at least 5 records in every table. · Create a database called “winCarRentals†that has all the above listed tables. · Insert all the data shown in the excel file. · Create a minimum of 5 customers.

You four people and me. · Create at least 3 rentals. B. SQL statements to incorporate functions and triggers in the database. · When data is inserted in the customer table, Customer MemberDate must be before or equal to the current date. · When data is inserted in the vehicle table, Vehicle’s PurchaseDate, must be before or equal to the current date. · When data is inserted in the rental table, rental booking date, checkoutdate must be after or equal to the current date. · When a vehicle is checked in (the check-in filed is set to null when the record is created, when the vehicle is returned, check in date is set to the returned date), the rental must be calculated and stored in the rental charge table.

C. SQL statements to run a number of report queries. · Write a query to print the list of customers who did not rent a vehicle. · Write a query to display the cars that are currently on rent. Submission Requirements Deliverables: A Microsoft Word document containing the ER diagram and the data dictionary to answer Task 1. Three SQL files ready to be uploaded to the WAMP server to satisfy Task 2.

Sample Paper For Above instruction

Introduction

The development of a comprehensive relational database for WIN Car Rental Services necessitates meticulous planning and implementation of various components, including the ER diagram, data dictionary, and SQL scripts. The goal is to create a secure, efficient, and scalable database that supports complex queries, functions, and triggers to facilitate the smooth operation of the rental business.

Database Design and ER Diagram

The first step involves designing an Entity-Relationship (ER) diagram utilizing crow's foot notation to visually represent the data model. The core entities—Customer, Insurance, Vehicle, Rental, VehicleType, RentalCharge, and PayType—each contain specific attributes and are interconnected through primary and foreign keys. For example, the Rental entity references Customer, Vehicle, Insurance, and Payment entities via respective foreign keys, establishing clear relationships. Primary keys such as customerId, insuranceId, rego, rentalId, VehicleTypeID, and paymentId uniquely identify each record, while foreign keys enforce referential integrity, ensuring consistency across tables.

The ER diagram illustrates one-to-many relationships between Customer and Rental, VehicleType and Vehicle, and Rental and RentalCharge, among others. Relationships are depicted bidirectionally, clarifying how entities interact within the database schema. This visual representation assists in understanding data flow and guidelines for database normalization.

Data Dictionary

The data dictionary, constructed in Excel, catalogs each attribute's name, data type, size, constraints (e.g., not null, unique), default values, and descriptions. For instance, the Customer's customerId is an integer serving as the primary key, while the memberDate is a date attribute indicating membership start date. Such detailed documentation facilitates database maintenance, updates, and ensures data integrity.

SQL Implementation

The SQL scripts to create the database—named 'winCarRentals'—include statements for table creation, with constraints to enforce data validity pertinent to the business rules. For example, Customer’s memberDate and Vehicle’s purchaseDate are validated through triggers to ensure they are not future dates. Similarly, rental booking and checkout dates are constrained to be not earlier than the current date.

Sample insert statements populate tables with at least five records each, providing a representative dataset for testing and development. For example, customer entries include five distinct customers with unique identifiers, and vehicle records reflect the current inventory.

Stored Procedures and Triggers

Advanced SQL programming involves creating stored procedures and triggers to automate data validation and calculations. For example, a trigger executes after insert into the rental table to calculate rental charges based on rental duration and vehicle type, storing the results in the RentalCharge table. Insertion triggers verify date constraints to prevent illogical data entry, maintaining the integrity of the rental process.

Reporting Queries

Two key report queries are demonstrated: one listing customers who have not rented any vehicle, and another displaying vehicles currently on rent. These queries utilize LEFT JOIN and INNER JOIN operations, respectively, to extract meaningful insights for management.

Conclusion

By integrating carefully designed ER diagrams, comprehensive data dictionaries, and robust SQL scripts—with functions and triggers—the WIN Car Rental Services database supports operational efficiency, data integrity, and security. Future enhancements might include web integration and improved concurrency management, aligning with modern RDBMS capabilities.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.