Database Management System

This article discusses the Hair Salon Database and its functionalities. It also includes the ER diagram, functional dependency diagram, relational schema, database semantics, security commands, security policies, and bibliography.

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),

12
DATABASE MANAGEMENT SYSTEM
(2, 'Kevin Neeves', 6469897, 600),
(3, 'Michael Phelps', 6469743, 500),
(4, 'Trevor Morgan', 6469644, 550),
(5, 'David Benioff', 7469625, 450);
-- --------------------------------------------------------
--
-- Table structure for table `suppliers`
--
DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
`SupplierID` int(11) NOT NULL,
`SupplierName` varchar(50) NOT NULL,
`SupplierConatct` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `suppliers`

13
DATABASE MANAGEMENT SYSTEM
--
INSERT INTO `suppliers` (`SupplierID`, `SupplierName`, `SupplierConatct`) VALUES
(1, 'Harry Winks', 7464757),
(2, 'Raaga Ltd', 7469645),
(3, 'Loreal', 7469123),
(4, 'Brazilian Wax', 7469789),
(5, 'Park Avenue', 7469696);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `appointments`
--
ALTER TABLE `appointments`
ADD PRIMARY KEY (`AppointmentID`),
ADD KEY `CustomerID` (`CustomerID`),
ADD KEY `ProductID` (`ProductID`),

14
DATABASE MANAGEMENT SYSTEM
ADD KEY `ServiceID` (`ServiceID`),
ADD KEY `StylistID` (`StylistID`);
--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
ADD PRIMARY KEY (`CustomerID`);
--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`ProductID`),
ADD KEY `SupplierID` (`SupplierID`);
--
-- Indexes for table `services`
--
ALTER TABLE `services`

15
DATABASE MANAGEMENT SYSTEM
ADD PRIMARY KEY (`ServiceID`);
--
-- Indexes for table `stylist`
--
ALTER TABLE `stylist`
ADD PRIMARY KEY (`StylistID`);
--
-- Indexes for table `suppliers`
--
ALTER TABLE `suppliers`
ADD PRIMARY KEY (`SupplierID`);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `appointments`

16
DATABASE MANAGEMENT SYSTEM
--
ALTER TABLE `appointments`
ADD CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`CustomerID`)
REFERENCES `customer` (`CustomerID`),
ADD CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`ProductID`)
REFERENCES `products` (`ProductID`),
ADD CONSTRAINT `appointments_ibfk_3` FOREIGN KEY (`ServiceID`)
REFERENCES `services` (`ServiceID`),
ADD CONSTRAINT `appointments_ibfk_4` FOREIGN KEY (`StylistID`) REFERENCES
`stylist` (`StylistID`);
--
-- Constraints for table `products`
--
ALTER TABLE `products`
ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`SupplierID`) REFERENCES
`suppliers` (`SupplierID`);
COMMIT;
Update Commands
UPDATE customer SET CustomerName = "Kenny James" WHERE CustomerID = 1;

17
DATABASE MANAGEMENT SYSTEM
Join Operation
SELECT suppliers.SupplierName, products.ProductName FROM products INNER JOIN
suppliers ON products.ProductID = suppliers.SupplierID
View Creation Command
CREATE VIEW product_suppliers AS
SELECT suppliers.SupplierName, products.ProductName FROM products INNER JOIN
suppliers ON products.ProductID = suppliers.SupplierID;

18
DATABASE MANAGEMENT SYSTEM
Trigger Command
CREATE TRIGGER `date` BEFORE INSERT ON `appointments` FOR EACH ROW IF
NEW.date > CURRENT_DATE() THEN SIGNAL SQLSTATE '02000' SET
MESSAGE_TEXT = 'Warning: date can not be greater than current date!'; END IF;