CS50-SQL-FINAL-PROJECT

Certificate

Design Document

By Muhammed Ahmet Sekerci

Video overview: https://www.youtube.com/watch?v=4YJtizONyW8

Scope

This database is designed to easily store the data of a technology company's customers, employees and orders. As such, included in the database's scope is:

• Customers Contains customer identification information and deletion information

• Trademarks Includes product brands

• Categories Includes product categories

• Products It Product information is kept here

• Cities cities are found in this table

• Shops Information about the store is kept in this table.

• Departments department information is found in this table

• JobPositions job positions are found in this table

• Employees Basic information of the employees + job position information, department information, store information about which store they are in, starting date, salary, number of leaves and working status information are included in this table.

• Indemnity Employee compensation information is included in this table.

• Annual_Leave The leaves used by employees are listed in this table.

• Orders The store where the order was placed, the employee who sold it and the customer who purchased it, and the date it was sold are included in this table.

• Order_Item When an order is placed, the basket information for that order is included in this table.

• Other elements provided by a tech store are excluded example; -customer point -employee bonus -Extra information for each shop

Functional Requirements

Customers Can create orders, list them, and return orders. Can view information about products and stores

Representation

Entities

Customers:

• "id" indicates the identification number. For this reason INTEGER. Is the data type. There is always an ID of the customer PRIMARY KEY . ID number will be added automatically AUTOINCREMENT.
• "name" Customer name should not exceed 40 characters VARCHAR(40). This line cannot be empty NOT NULL.
• "surname" customer surname information. Cannot exceed 20 characters VARCHAR(20). This line cannot be empty NOT NULL.
• "phoneNumber" represents the phone number. Because there are variable length TEXT. This line cannot be empty. NOT NULL. Must be unique UNIQUE. Must contain only numbers CHECK("phoneNumber" GLOB '*[0-9]*').
• "email" because it will contain special characters TEXT. This line cannot be empty. NOT NULL Must be unique UNIQUE. @ sign control CHECK("email" LIKE '%@%').
• "address" address information. Should not exceed 70 characters NVARCHAR(70). This line cannot be empty NOT NULL.
• "deleted" This line will be used if the customer is deleted. There will be only numbers INTEGER. It should be automatically 0 when the customer first becomes a member, it has not been deleted yet DEFAULT 0. This line cannot be empty NOT NULL. Only deleted or not deleted will be checked 0 or 1 CHECK("deleted" BETWEEN 0 AND 1).

Trademarks:

• "id" indicates the identification number. For this reason INTEGER. Every brand should have a unique identity PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name" indicates the brand name. Brand name should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL. Must be unique UNIQUE.

Categories:

• "id" indicates the identification number. For this reason INTEGER. Each category should be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name"indicates the category name. Category name should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL. Category name must be unique UNIQUE.

Products:

• "id" product indicates the identification number. For this reason INTEGER. Every product should be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name" indicates the product name. Product name should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL.
• "stock" Indicates the number of stocks. The number of stocks must be numerical INTEGER. Should not be empty NOT NULL. Stock - should not be CHECK("stock" >= 0).
• "categoryId" Specifies the category number. Category number must be numeric INTEGER. Should not be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Categories' table is restricted.
• "trademarkId" Specifies the trademark number. Trademark number must be numeric INTEGER. Should not be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Trademarks' table is restricted.
• "price" indicates the product price. Fee must be numerical NUMERIC. Should not be empty NOT NULL. Fee - should not be CHECK("price" > 0).
• "productStatus" Indicates the status of the product. Status should not exceed 20 characters VARCHAR(20). Should not be empty NOT NULL. The product status should include some options CHECK("productStatus" IN ('sale','not on sale','stock expected').

A brand cannot have more than one product with the same name. CONSTRAINT unique_product_trademark UNIQUE ("trademarkId", "name").

Cities:

• "id" Shows city ID number. For this reason INTEGER. Each city number must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name" Specifies the city name. City name should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL.

Shops:

• "id" Shows Shop ID number. For this reason INTEGER. Each shop number must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name" Specifies the shop name. Shop name should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL.
• "cityId" Indicates the city number where it is located. For this reason INTEGER. Should not be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Cities' table is restricted.
• "shopStatus" Indicates shop status. Status should not exceed 12 characters VARCHAR(12). Should not be empty NOT NULL. Shop status should contain some options CHECK("shopStatus" IN('active', 'repair','not active').

Departments:

• "id" indicates the identification number. For this reason INTEGER. Each department should be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name" Specifies the department name. Department name should not exceed 30 characters VARCHAR(30). Should not be empty NOT NULL. Department name must be unique UNIQUE.

JobPositions:

• "id" indicates the identification number. For this reason INTEGER Every job position should be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "name" Specifies the job position name. job position name should not exceed 50 characters VARCHAR(50) should not be empty NOT NULL job position name must be unique UNIQUE.

Employees:

• "id" indicates the identification number. For this reason INTEGER. Every employee must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "identificationNumber" represents the employee population identification number. Can contain numbers and letters TEXT. Should not be empty NOT NULL. Identification number must be unique UNIQUE.
• "name" specifies the employee name. Employee name should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL.
• "surname" Specifies the employee surname. Employee surname should not exceed 50 characters VARCHAR(50). Should not be empty NOT NULL.
• "gender" Specifies gender information. Gender information should not exceed 15 characters VARCHAR(15). Should not be empty NOT NULL.
• "phoneNumber" Specifies the working phone number. There are variable length TEXT. This line cannot be empty NOT NULL. Must be unique UNIQUE. Must contain only numbers CHECK("phoneNumber" GLOB '*[0-9]*').
• "email" Specifies the employee e-mail address. It will contain special characters TEXT. This line cannot be empty NOT NULL. Must be unique UNIQUE. @ sign control CHECK("email" LIKE '%@%').
• "address" represents address information. Cannot exceed 70 characters NVARCHAR(70). This line cannot be empty NOT NULL.
• "jobPositionId" indicates the job position number. For this reason INTEGER. This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'JobPositions' table is restricted.
• "departmentId" indicates the department number. For this reason INTEGER. This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Departments' table is restricted.
• "shopId" Shows the store number where it is located. for this reason INTEGER. this line cannot be empty. NOT NULL. The FOREIGN KEY. Constraint applied to the 'id' column in the 'Shops' table is restricted.
• "startDate" this job Date of start DATE. because it contains date. This line cannot be empty NOT NULL. Automatically gets the start date. DEFAULT CURRENT_DATE
• "salary" Contains salary information. Salary must be numerical NUMERIC. This line cannot be empty NOT NULL. Must be greater than 0 CHECK("indemnityAmount" >= 0).
• "annualLeave" Contains the number of permissions it has. For this reason INTEGER. This line cannot be empty NOT NULL. The automatically assigned value is 0 DEFAULT 0. Must be greater than 0 CHECK("annualLeave" > 0)
• "shopStatus" Shows the employee's working status. Status should not exceed 10 characters VARCHAR(10). Should not be empty NOT NULL. Employeestatus should contain some options CHECK("workingStatus" IN ('work','annual leave','terminated').

Indemnity:

• "id" indicates the identification number. For this reason INTEGER Each compensation must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "employeeId" Shows the employee's number for this reason INTEGER. This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Employees' table is restricted.
• "terminationDate" date of compensation. Data type DATE. This line cannot be empty NOT NULL. Automatically gets that day's date DEFAULT CURRENT_DATE.
• "workingDay" total number of days worked by the employee. For this reason INTEGER. This line cannot be empty NOT NULL. Must be greater than 0 CHECK("workingDay" > 0).
• "indemnityAmount" Contains compensation fee information. Fee must be numerical NUMERIC. This line cannot be empty NOT NULL. Must be greater than 0 CHECK("indemnityAmount" >= 0).

Annual_Leave:

• "id" indicates the identification number. For this reason INTEGER. Each compensation must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "employeeId" Shows the employee's number. For this reason INTEGER. This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Employees' table is restricted.
• "annualLeaveUsed" number of permissions used. For this reason INTEGER. This line cannot be empty. NOT NULL

Orders:

• "id" indicates the identification number. For this reason INTEGER. Each order must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "customerId" customer ID who placed the order. For this reason INTEGER. This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Customers' table is restricted.
• "shopId" Store number where the order was placed. For this reason INTEGER This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Shops' table is restricted.
• "orderDate" Includes order date. For this reason DATE. This line cannot be empty NOT NULL. Automatically gets that day's date DEFAULT CURRENT_DATE.
• "employeeId" Shows the number of the Employee who created the order. For this reason INTEGER. This line cannot be empty NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Employees' table is restricted.

Order_Item:

• "id" indicates the identification number. For this reason INTEGER. Each order-item must be unique PRIMARY KEY. Id should be auto incrementing AUTOINCREMENT.
• "orderId" order ID of the product. For this reason INTEGER. This line cannot be empty. NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Orders' table is restricted.
• "productId" product ID in the orders. For this reason INTEGER. This line cannot be empty. NOT NULL. The FOREIGN KEY constraint applied to the 'id' column in the 'Product' table is restricted.
• "quantity" Information on how many pieces the product is. For this reason INTEGER. This line cannot be empty NOT NULL. Quantity must be greater than 0 CHECK("quantity" > 0).
• "price" total price of the product. Fee must be numerical NUMERIC. Should not be empty NOT NULL. Fee - should not be CHECK("price" > 0).
• "orderItemStatus" Indicates the status of the product in the order. Status should not exceed 20 characters VARCHAR(20). Should not be empty NOT NULL. Getting ready default value DEFAULT 'getting ready'. Product status should contain some options CHECK("orderItemStatus" IN ('getting ready','delivered','refunded','cancelled').

Relationships

The entity relationship diagram below describes the relationships between entities in my database.

Descriptions:

• There can be more than one store in a city, but the same store cannot be in many cities.(one-to-many relationship)
• There can be more than one employee in a store, but an employee cannot work in more than one store at the same time.(one-to-many relationship)
• Multiple orders can be placed from one store, but two stores cannot be included in the same order.(one-to-many relationship)
• There can be more than one employee in a department, but the same employee cannot be in two departments.(one-to-many relationship)
• There may be more than one employee in a job position, but the same employee cannot be in two job positions.(one-to-many relationship)
• The employee has a compensation, the same compensation represents the one employee. (One to one relationship)
• An employee can take leave at different times, but the same leave cannot represent more than one employee.(one-to-many relationship)
• A customer can place more than one order, but the same order cannot represent two customers.(one-to-many relationship)
• An employee can create multiple orders but one order cannot represent two employees.(one-to-many relationship)
• There can be multiple items in one order, but the items cannot represent two identical orders.(one-to-many relationship)
• A product can be in more than one order product table, but the order product id represents the same product only once. (one-to-many relationship)
• There can be multiple items in one order and one item can be in multiple orders.(many-to-many relationship)
• There can be more than one product in a category, but a product cannot be in 2 categories.(one-to-many relationship)
• A product has one brand, but the same brand may have multiple products.(one-to-many relationship)

Optimizations

Indexing

• "customer_search" To get faster results for user searches. "name","surname","email","address"
• "product_search" To speed up product searches."name"
• "city_search" To speed up city searches. "name"
• "shop_search" To speed up store searches. "name"
• "employee_search" To speed up employee searches. "name","surname","email","address"

Views

• "employee_indemnity" Shows employee compensation information.
• "order_content" It shows the detailed content of the order placed.
• "order_totalprice" It shows the total amount of the order placed.
• "shop_city" Shows store city connection information.
• "employee_annualleave" Shows the permissions used by employees.
• "employee_information" Shows information about employees.
• "order_information" Shows the information of the order placed.
• "product_details" Shows information about products.

Triggers

• "employee_active_shop_control" It checks the status of the shop from the shop information entered when adding employees.
• "employee_department_control" It checks whether the department exists or not from the department information entered when adding an employee.
• "employee_jobPosition_control" When adding an employee, it checks whether there is a job position from the job position information entered.
• "employee_shop_control"It checks whether the shop exists from the shop information entered when adding an employee.
• "annual_leave_employee_check" Checks employee status and presence while employee is taking leave.
• "annual_leave_check" When taking leave, the employee checks whether he/she has enough leave and that the number of leaves is greater than 0.
• "use_annual_leave" After the employee takes leave, it is deducted from the leave count and its status is changed to on leave.
• "indemnity_employee_controls" When adding compensation, it is taken into account whether the employee is an employee or not.
• "indemnity_employee_workstatus" When compensation is added, the employee's situation changes.
• "sales_shop_active_control" The condition of the store is checked before the sale is made.
• "sales_shop_control" Before making a sale, it is checked whether the store exists or not.
• "sales_employee_control" The employee's status and existence are checked before the sale is made.
• "sales_customer_control" Before the sale is made, the customer's deletion status and existence are checked.
• "product_control" Before the sale is made, the sales status and existence of the product is checked.
• "product_stock_control" Stock control of the product is carried out before sales are made.
• "order_cancellation_refund_procedures" If the order is canceled, stock is added to the product.
• "order_delivery_transactions" The product stock decreases when the order product status is delivered.
• "product_stock_end" When the product stock count is 0, it changes its status to waiting for stock.
• "adding_product_stock" When stock is added to the product, its status is updated as sale.
• "order_state_update" When the sale is completed without any problems, the product order status is updated as delivered.

Limitations

• interstore transactions
• detailed account transactions (E.g. Indemnity fee)
• update checks, transactions with the data in the program are considered.

This database may not represent the detailed databases of large technology companies, but it was designed with inspiration from them.