Microsoft SQL Server

Objective:

Question 1: Create the CUSTOMER Table

• customer_id as a primary key with auto-increment functionality.
• first_name as a string of maximum length 255 characters, not null.
• last_name as a string of maximum length 255 characters, not null.
• email as a unique string, not null.
• phone_number as a string, not null.
• gender as a string, not null.
• address as a string of maximum length 500 characters, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 2: Create the RESTAURANT Table with Constraints

• restaurant_id as a primary key with auto-increment functionality.
• restaurant_name as a string with a maximum length of 255 characters, not null.
• location as a string with a maximum length of 255 characters, null.
• contact_number as a string, null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 3: Create the MENU_ITEM Table

• menu_item_id as a primary key with auto-increment functionality.
• item_name as a string of maximum length 255 characters, not null.
• item_category as a string of maximum length 255 characters, not null.
• price as a decimal with precision (10,2), not null.
• restaurant_id as a foreign key referencing the RESTAURANT table, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• Add a CHECK constraint to ensure that the item_category is in (Desserts, Soup, Drinks, Main Course, Starter).

Question 4: Create the COUPON Table

• coupon_id as the primary key with auto-increment functionality.
• coupon_code as a string, unique and not null.
• coupon_name as a string, unique and not null.
• discount_percentage as a decimal (5,2), not null.
• start_date as a date, not null.
• end_date as a date, not null.
• active_flag as a boolean, default to true, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• Add a CHECK constraint to ensure that the discount_percentage is between 0 and 100.

Question 5: Create the ORDER Table with a Foreign Key

• order_id as a primary key with auto-increment functionality.
• order_date as a timestamp with the default value of the current timestamp.
• total_price as a decimal (10,2), not null.
• status as a string (e.g., "Pending", "Delivered", "Cancelled"), not null.
• customer_id as a foreign key referencing the CUSTOMER table, not null.

Question 6: Define the ORDER_ITEM Table

• order_id as a foreign key referencing the ORDER table.
• menu_item_id as a foreign key referencing the MENU_ITEM table, not null.
• quantity as an integer, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• Set up a composite primary key using order_id and menu_item_id.

Question 7: Define the ORDER_COUPON Table

• order_id as a foreign key referencing the ORDER table.
• coupon_id as a foreign key referencing the COUPON table.
• created_at as a datetime field, default it to system timestamp, not null.

Question 8: Create the PAYMENT Table

Write a DDL SQL statement to create a PAYMENT table with the following details:

• payment_id as the primary key with auto-increment functionality.
• payment_date as a timestamp, defaulting to the current timestamp.
• amount as a decimal (10, 2), not null.
• discount_amount as a decimal (10, 2), null.
• payment_method as a string (e.g., "Credit Card", "Cash", "Online"), not null.
• order_id as a foreign key referencing the ORDER table, not null.

Question 9: Create the REFUND Table with Constraints

Write a DDL SQL statement to create a REFUND table:

• refund_id as a primary key with auto-increment functionality.
• refund_amount as a decimal (10, 2), not null.
• refund_date as a date field, not null.
• order_id as a number, not null.
• created_at as a datetime field, default it to system timestamp, not null.
• Add a CHECK constraint to ensure the refund_amount is greater than 0.

Question 10: Create the DELIVERY_AGENT Table

• agent_id as a primary key with auto-increment functionality.
• first_name as a string with a maximum length of 255 characters, not null.
• last_name as a string with a maximum length of 255 characters, not null.
• phone_number as a string, not null.
• email as a string, not null.
• vehicle_number as a string with a maximum length of 50 characters, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 11: Define the DELIVERY Table with Relationships

• delivery_id as a primary key with auto-increment functionality.
• delivery_date as a timestamp with the default value as the current date.
• pickup_time as a time, null.
• delivered_time as a time, null.
• status as a string (e.g., "In Transit", "Delivered"), not null.
• agent_id as a foreign key referencing the DELIVERY_AGENT table, not null.
• order_id as a foreign key referencing the ORDER table.
• created_at as a datetime field, not null.

Question 12: Create the REVIEW Table

• review_id as a primary key with auto-increment functionality.
• rating as an integer, not null.
• review_text as a string of maximum length 500 characters, nullable.
• customer_id as a foreign key referencing the CUSTOMER table, not null.
• restaurant_id as a foreign key referencing the RESTAURANT table, not null.
• created_at as a datetime field, default it to system timestamp, not null.

Question 13: Create the AUDIT_LOG Table

• log_id as a primary key with auto-increment functionality.
• log_date as a date field, not null.
• action as a string to describe the action performed.
• user_id as a foreign key referencing the CUSTOMER table.

Question 14: Create the CUSTOMER_LOGIN Table

• customer_id as a foreign key referencing the CUSTOMER table.
• login_id can use alpha numeric login id or email as login id, not null.
• password must encrypt the password before storing it in the database table, not null.
• active_flag use numeric, 0 or 1, you can lock the customer login when required, not null.
• last_login_datetime as a timestamp, last successfull login date for a given employee.

Question 15: Add a Column to the CUSTOMER Table

Question 16: Alter a Column in the COUPON Table

Question 17: Drop a Column from the DELIVERY_AGENT Table

Question 18: Rename the AUDIT_LOG Table

Question 19: Drop the CUSTOMER_LOGIN Table

Question 20: Add a Primary Key on the ORDER_COUPON Table

• Set up a composite primary key using order_id and coupon_id.

Question 21: Add a Foreign Key column to the ORDER Table

Question 22: Alter the REFUND Table to Add a Foreign Key

Question 23: Alter the CUSTOMER_LOGIN Table to drop a Foreign Key

Question 24: Add a CHECK Constraint on the CUSTOMER Table

Question 25: Add a CHECK Constraint on the REVIEW Table

Question 26: Add a CHECK Constraint on the COUPON Table

Question 27: Add a default Constraint on the DELIVERY Table

Question 28: Add Indexes to the CUSTOMER and RESTAURANT Tables

• Add an index on the last_name column in the CUSTOMER table.
• Add an index on the restaurant_name column in the RESTAURANT table.

Question 29: Drop an Index from the RESTAURANT Table

Question 30: Enforce UNIQUE constraints on all applicable tables.

Sample ERD Data Model for Food Delivery System