Practice & Assessment
Test your understanding of Data Modeling and Keys
Multiple Choice Questions
5In an ER diagram, how is a many-to-many relationship implemented in a relational database?
What is a candidate key?
What happens with ON DELETE CASCADE on a foreign key?
Why is a surrogate key generally preferred over a natural key?
Which of the following best describes referential integrity?
Coding Challenges
1Design and Query an E-Commerce Schema
Create an e-commerce database with the following tables: customers (customer_id, name, email, city), products (product_id, name, category, price, stock_quantity), orders (order_id, customer_id, order_date, status), and order_items (order_id, product_id, quantity, unit_price). Add all appropriate primary and foreign keys. Then write queries to: (1) Find all orders placed by a specific customer. (2) Calculate the total revenue per product category. (3) Find customers who have placed more than 3 orders. (4) List all products that have never been ordered.
Mini Project
Hospital Management System โ ER Design to SQL
Design a complete Hospital Management System from ER diagram to SQL implementation. Entities required: patients (patient_id, name, date_of_birth, blood_group, contact), doctors (doctor_id, name, specialization, license_number), appointments (appointment_id, patient_id, doctor_id, appointment_date, status, notes), wards (ward_id, ward_name, capacity, floor), admissions (admission_id, patient_id, ward_id, doctor_id, admission_date, discharge_date). First write a text description of all relationships and their cardinalities. Then write the full SQL DDL with all primary keys, foreign keys, and constraints. Finally write queries to: find all patients treated by a specific doctor, find wards at full capacity, and calculate the average length of stay per ward.