Database Project: Hair Salon Management System using MySQL

This project details the development of a Hair Salon Database Management System using MySQL. It includes a comprehensive description of the database, covering entities like stylists, appointments, customers, suppliers, and products. The project features an ER diagram, a functional dependency diagram, and a relational schema, along with explanations of database semantics and security commands. It also provides SQL commands for creating tables, inserting data, updating records, performing join operations, creating views, and implementing triggers. The appendix includes relation tables and the SQL commands used in the project, offering a complete overview of the database implementation.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student:
Name of the University:
Author Note

1
DATABASE MANAGEMENT SYSTEM
Description of the Hair Salon Database
The Hair Salon Database would be storing the details of the stylist in the salon, the
appointments, customers, Suppliers and products of the salon. The database would be very
helpful for the users to obtain any type of data required by the organization for creating
important documents such as reports about the customers and the products and making
analysis about their business and make important decisions for the business.
ER Diagram

2
DATABASE MANAGEMENT SYSTEM
Functional Dependency Diagram
Relational Schema
Functional Dependencies
The Functional Dependency for the system is provided below:
Entities Dependencies
CustomerID CustomerName, CustomerAddress, CustomerContact
ServiceID ServiceName, Price
StylistID StylistName, StylistContact, StylistSalary
AppoinmentID Date, CustomerID, ServiceID, StylistID, ProductID
ProductID ProductName, ProductCost, SupplierID
SupplierID SupplierName, SupplierContact

3
DATABASE MANAGEMENT SYSTEM
Database Semantics
The relationship in between the different entities of the system is provided below:
Entity Relationship Entity
Customer 1 to many Appointment
Stylist 1 to 1 Appointment
Services 1 to many Appointment
Product 1 to many Appointment
Product Many to 1 Suppliers
Database Security Commands
The database security commands involve the backup and rollback commands. In
addition to this the grant and revoke commands are also used for the access control of the
database and users who would be using the database. The authentication process involves
defining the passwords and the usernames of the database.
Security policy Intended
The general security policies which are intended for the database are:
 Access control.
 Auditing.
 Authentication.
 Encryption.
 Integrity controls.
 Backups.

4
DATABASE MANAGEMENT SYSTEM
Bibliography
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., ... & Widom, J. (2016).
Stream: The stanford data stream management system. In Data Stream Management
(pp. 317-336). Springer, Berlin, Heidelberg.
Clifford, P., & Robinson, M. (2016). U.S. Patent Application No. 14/786,728.

5
DATABASE MANAGEMENT SYSTEM
Appendix
Relation Table
Create and Insert Commands
-- Database: `hairsalon`
--
CREATE DATABASE IF NOT EXISTS `hairsalon` DEFAULT CHARACTER SET latin1
COLLATE latin1_swedish_ci;
USE `hairsalon`;
-- --------------------------------------------------------
--
-- Table structure for table `appointments`
--

6
DATABASE MANAGEMENT SYSTEM
DROP TABLE IF EXISTS `appointments`;
CREATE TABLE `appointments` (
`AppointmentID` int(11) NOT NULL,
`Date` date NOT NULL,
`CustomerID` int(11) NOT NULL,
`ProductID` int(11) NOT NULL,
`ServiceID` int(11) NOT NULL,
`StylistID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `appointments`
--
INSERT INTO `appointments` (`AppointmentID`, `Date`, `CustomerID`, `ProductID`,
`ServiceID`, `StylistID`) VALUES
(1, '2018-11-07', 1, 1, 1, 1),
(2, '2018-11-08', 2, 2, 2, 2),
(3, '2018-11-10', 3, 3, 3, 3),

7
DATABASE MANAGEMENT SYSTEM
(4, '2018-11-05', 4, 4, 4, 4),
(5, '2018-11-02', 5, 5, 5, 5);
-- --------------------------------------------------------
--
-- Table structure for table `customer`
--
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`CustomerID` int(11) NOT NULL,
`CustomerName` varchar(50) NOT NULL,
`CustomerAddress` varchar(100) NOT NULL,
`CustomerContact` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `customer`
--

8
DATABASE MANAGEMENT SYSTEM
INSERT INTO `customer` (`CustomerID`, `CustomerName`, `CustomerAddress`,
`CustomerContact`) VALUES
(1, 'Jason Perez', '87 wellington road', 6469625),
(2, 'Casey Rodriguez', '62 Hastings road', 6464525),
(3, 'Ben Afflec', '55 kingston road', 6469678),
(4, 'Phillip Anderson', '89 Trevor Street', 6461543),
(5, 'Ferry Pier', '78 wellington Road', 6469600);
-- --------------------------------------------------------
--
-- Table structure for table `products`
--
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`ProductID` int(11) NOT NULL,
`ProductName` varchar(50) NOT NULL,
`ProductCost` int(11) NOT NULL,

9
DATABASE MANAGEMENT SYSTEM
`SupplierID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`ProductID`, `ProductName`, `ProductCost`, `SupplierID`)
VALUES
(1, 'Scissors and Trimmers', 50, 1),
(2, 'Lotion', 60, 2),
(3, 'Grooming kit', 70, 3),
(4, 'Brazilian Wax Cream', 50, 4),
(5, 'Bleach Cream', 60, 5);
-- --------------------------------------------------------
--
-- Table structure for table `services`
--

10
DATABASE MANAGEMENT SYSTEM
DROP TABLE IF EXISTS `services`;
CREATE TABLE `services` (
`ServiceID` int(11) NOT NULL,
`SeviceName` varchar(50) NOT NULL,
`Price` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `services`
--
INSERT INTO `services` (`ServiceID`, `SeviceName`, `Price`) VALUES
(1, 'Haircut', 100),
(2, 'Pedicure', 110),
(3, 'Manicure', 250),
(4, 'Waxing', 150),
(5, 'Bleach', 60);
-- --------------------------------------------------------

11
DATABASE MANAGEMENT SYSTEM
--
-- Table structure for table `stylist`
--
DROP TABLE IF EXISTS `stylist`;
CREATE TABLE `stylist` (
`StylistID` int(11) NOT NULL,
`StylistName` varchar(50) NOT NULL,
`StylistContact` int(11) NOT NULL,
`StylistSalary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `stylist`
--
INSERT INTO `stylist` (`StylistID`, `StylistName`, `StylistContact`, `StylistSalary`)
VALUES
(1, 'Harry Kane', 6469883, 500),