Question

Provide Query to create Database

In this database at least a table of customers, sales, employees, suppliers, products, the detail of the sale, and the equipment (Laptop and Destop) which facilitates billing and inventory management by the database is needed. number of brands and units of computers sold at retail or wholesale. The company does not have a database and they use an old way of information that can also be easily lost. They need a database to have organized all the information about the employees who do the work, the client who purchases the services, the sales that are made and what type of sale was made, the peripherals of the computers. The products that are buy.

DR Computers is a company dedicated of sales. This company focuses on the Installation and Computer repair. In addition, they work with air sales repair and maintenance. Currently, they do not have a database.

Specific objectives:

Provide Query to create Database

Answer 1

//Creating a Database for DR computers.

CREATE DATABASE DRCDb

//Create first Table for storing customer details.

CREATE TABLE customers (customer_id INT ,
first_name VARCHAR (100) NOT NULL,
last_name VARCHAR (100) NOT NULL,
phone VARCHAR (20),
email VARCHAR (25) NOT NULL UNIQUE,
street VARCHAR (25),
city VARCHAR (50),
state VARCHAR (25),
zip_code VARCHAR (5), CONSTRAINT PRIMARY KEY(customer_id)
);
//Create employee table for storing employee details

CREATE TABLE employees (emp_id INT ,
first_name VARCHAR (100) NOT NULL,
last_name VARCHAR (100) NOT NULL,
email VARCHAR (25) NOT NULL UNIQUE,
phone VARCHAR (25),CONSTRAINT PRIMARY KEY(emp_id));
//Create table for products to store details about each and every products

CREATE TABLE products (
product_id INT ,
product_name VARCHAR (25) NOT NULL,
brand_id INT NOT NULL,
category_id INT NOT NULL,
model INT NOT NULL,
product_price DECIMAL (12, 2) NOT NULL,CONSTRAINT PRIMARY KEY(product_id)
FOREIGN KEY (category_id)
REFERENCES categories (category_id),
FOREIGN KEY (brand_id)
REFERENCES brands (brand_id)
);

//Create table for category to store details about the product belongs to which category of computer peripherals
CREATE TABLE categories (
category_id INT ,
category_name VARCHAR (25) NOT NULL,CONSTRAINT PRIMARY KEY(category_id)
);

CREATE TABLE brands(
brand_id INT ,
brand_name VARCHAR (25) NOT NULL),CONSTRAINT PRIMARY KEY(brand_id);

//Create table for sales category to store details about the sale either the sale is a retail sale or a wholesale sale
CREATE TABLE sale_categories (
scategory_id INT,
scategory_name VARCHAR (25) NOT NULL,CONSTRAINT PRIMARY KEY(scategory_id)
);

//Create table for various suppliers
CREATE TABLE suppliers(supplier_id INT ,
supplier_name VARCHAR (25) NOT NULL,
brand_id INT NOT NULL,
category_id INT NOT NULL,
product_id INT NOT NULL,
prdct_qnty INT NOT NULL,CONSTRAINT PRIMARY KEY(supplier_id)
FOREIGN KEY (category_id)
REFERENCES categories (category_id),
FOREIGN KEY (brand_id)
REFERENCES brands(brand_id),

FOREIGN KEY (product_id)
REFERENCES products(product_id)
);

CREATE TABLE customer_orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE NOT NULL,
emp_id INT NOT NULL,
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ,
FOREIGN KEY (emp_id)
REFERENCES employees (emp_id)

);

CREATE TABLE sales_details (
sale_id INT PRIMARY KEY,
order_id INT NOT NULL
emp_id INT NOT NULL,
scategory_id INT NOT NULL,

FOREIGN KEY (customer_id)
REFERENCES customers (customer_id) ,
FOREIGN KEY (emp_id)
REFERENCES employees (emp_id) ,

FOREIGN KEY (scategory_id)
REFERENCES sale_categories ( scategory_id) );