Refer to the given schema. Write a query to create customer and branch tables with the constraints mentioned.

To create the customer and branch tables with the specified constraints, you can use the following SQL queries.

1. Create the Branch Table

CREATE TABLE branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(100) NOT NULL,
    branch_location VARCHAR(100) NOT NULL,
    CONSTRAINT unique_branch_name UNIQUE (branch_name)
);

2. Create the Customer Table

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    branch_id INT,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(15),
    FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
);

Explanation:

1. Branch Table:

   • branch_id is defined as an INT type and is a primary key.
   • branch_name is a VARCHAR type with a constraint ensuring it cannot be null and is unique.
   • branch_location is another VARCHAR field that also cannot be null.

2. Customer Table:

   • customer_id is a primary key with INT type.
   • customer_name is a VARCHAR type that cannot be null.
   • branch_id establishes a foreign key relationship with the branch table.
   • email is unique across customers, and phone_number allows a maximum length of 15 characters.

Summary

These SQL queries create the branch and customer tables and enforce the required constraints such as primary keys, unique constraints, and foreign key relationships.