Dvdrental Guide
// Update return date await pool.query( UPDATE rental SET return_date = NOW() WHERE rental_id = $1 , [rentalId]);
RETURN v_late_fee;
It is highly regarded for training because its schema design mimics real-world database design practices, including: Normalized to minimize data redundancy. dvdrental
: The film table stores core data (titles, release years, ratings), while actor , category , and their respective "bridge" tables ( film_actor , film_category ) manage the many-to-many relationships inherent in cinema.
CREATE OR REPLACE PROCEDURE process_late_fee_payment( p_customer_id INTEGER, p_amount NUMERIC ) LANGUAGE plpgsql AS $$ DECLARE v_total_owed NUMERIC; BEGIN -- Calculate total late fees for customer SELECT COALESCE(SUM(GREATEST(0, (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration) * 0.50), 0) INTO v_total_owed FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id WHERE r.customer_id = p_customer_id AND r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration; IF p_amount >= v_total_owed THEN -- Insert payment record INSERT INTO payment ( customer_id, staff_id, rental_id, amount, payment_date ) SELECT p_customer_id, 1, -- Default staff ID NULL, v_total_owed, NOW() WHERE v_total_owed > 0; // Update return date await pool
: The store , staff , and inventory tables define the physical constraints of the business, tracking which DVDs are available at specific locations and managed by whom.
You can create stored procedures to handle business logic, such as updating inventory or inserting new actor records. Conclusion You can create stored procedures to handle business
// POST /api/rentals/:rentalId/return app.post('/api/rentals/:rentalId/return', async (req, res) => const rentalId = req.params;
The dvdrental database is perfect for running various levels of SQL queries. Basic Data Retrieval ( SELECT ) Practice retrieving specific information from tables.
The dvdrental database is a conceptual simulation of a physical DVD rental store's information system. It is modeled after a real-world scenario, containing data about films, actors, customers, rentals, payments, and store locations.