Answer to Question #231971 in Databases | SQL | Oracle | MS Access for Karthickraja Dhaks

Question

Business rules for hairdressers:

1. All entities should have surrogate primary keys.

2. Each hairdresser can work at multiple different salons over time, and every salon employs many different hairdressers.

3. The job title of a hairdresser at a specific salon must be stored in the database.

4. The name and surname of each hairdresser must be stored in the database.

5. The name of each salon must be stored in the database.

6. Every hairdresser is certified to have several different skills, and each skill can be mastered by many different hairdressers.

7. The name of each skill must be stored in the database.

8. The date that a specific hairdresser was certified to have a specific skill must be stored in the database. 

Answer 1

CREATE DATABASE hairdresser_db;

USE hairdresser_db;




CREATE TABLE hairdresser (

  hairdresser_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

  hairdresser_firstname  VARCHAR(30) NOT NULL,

  hairdresser_lastname VARCHAR(30) NOT NULL,

);




CREATE TABLE saloon (

  saloon_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

  saloon_name VARCHAR(30) NOT NULL,

);




CREATE TABLE saloon_hairdresser (

  saloon_hairdresser_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

  saloon_id int NOT NULL,

  hairdresser_id int NOT NULL,

  hairdresser_job_title VARCHAR(30) NOT NULL,

);




CREATE TABLE skill (

  skill_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

  skill_name VARCHAR(30) NOT NULL,

);




CREATE TABLE hairdresser_skill (

  hairdresser_skill_id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

  skill_id int NOT NULL,

  hairdresser_id int NOT NULL,

  sertification_date DATE NOT NULL,);