I am new to databases. Using MySQL I am not certain how to make views relevant to the tables in my database. Can I get help explaining how to several views relative to the tables? Here are the tables I set up in MySQL. Employers: CREATE TABLE Employers ( employer_id INT NOT NULL PRIMARY KEY, company_name VARCHAR(50) NOT NULL, company_street VARCHAR(55) NOT NULL, company_city VARCHAR(55) NOT NULL, company_state VRCHAR(2) NOT NULL, company_zip VARCHAR(10) NOT NULL, company_contact VARCHAR(55) ); Customers: CREATE TABLE Customers ( customer_id INT NOT NULL PRIMARY KEY, customer_name VARCHAR(55) NOT NULL, customer_phone VARCHAR(15) NOT NULL, customer_email VARCHAR(40) NOT NULL, customer_education VARCHAR(55) NOT NULL, customer_experience VARCHAR(55) NOT NULL ); Reference: CREATE TABLE Referral ( customer_id INT NOT NULL PRIMARY KEY, referral_first_name VARCHAR(25) NOT NULL, referral_last_name VARCHAR(25) NOT NULL, referral_location VARCHAR(44) NOT NULL, referral_phone VARCHAR(15), reference_phone VARCHAR(15), CONSTRAINT fk_customer_id_1 FOREIGN KEY (customer_id) REFERENCES Customers (customer_id) ON UPDATE CASCADE ON DELETE CASCADE ); Positions: CREATE TABLE Positions ( customer_id INT NOT NULL, positions_id INT NOT NULL, positions_title VARCHAR(25) NOT NULL, CONSTRAINT pk_positions PRIMARY KEY (customer_id, positions_id), CONSTRAINT fk_customer_id_2 FOREIGN KEY (customer_id) REFERENCES Customers (customer_id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_positions_id FOREIGN KEY (positions_id) REFERENCES Positions (positions_id) ON UPDATE CASCADE ON DELETE NO ACTION ); Skills: CREATE TABLE Skills ( skill_id INT NOT NULL PRIMARY KEY, skill_ hardware VARCHAR(25) NOT NULL, skill_software VARCHAR(25) NOT NULL, proficiency_level_hardware VARCHAR(25) NOT NULL, proficiency_level_software VARCHAR(25) NOT NULL ); Interviews: CREATE TABLE Interviews ( interview_id INT NOT NULL PRIMARY KEY, interview_date DATE NOT NULL, interviewer VARCHAR(25), feedback VARCHAR(255) ); HiringStatus: CREATE TABLE HiringStatus ( hire_status_id INT NOT NULL PRIMARY KEY, customer_id INT NOT NULL, employer_id INT NOT NULL, hired VARCHAR(5), CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES Customers (customer_id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_employer_id FOREIGN KEY (employer_id) REFERENCES Employers (employer_id) ON UPDATE CASCADE ON DELETE CASCADE );
I am new to databases. Using MySQL I am not certain how to make views relevant to the tables in my
Here are the tables I set up in MySQL.
Employers:
CREATE TABLE Employers (
employer_id INT NOT NULL PRIMARY KEY,
company_name VARCHAR(50) NOT NULL,
company_street VARCHAR(55) NOT NULL,
company_city VARCHAR(55) NOT NULL,
company_state VRCHAR(2) NOT NULL,
company_zip VARCHAR(10) NOT NULL,
company_contact VARCHAR(55)
);
Customers:
CREATE TABLE Customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(55) NOT NULL,
customer_phone VARCHAR(15) NOT NULL,
customer_email VARCHAR(40) NOT NULL,
customer_education VARCHAR(55) NOT NULL,
customer_experience VARCHAR(55) NOT NULL
);
Reference:
CREATE TABLE Referral (
customer_id INT NOT NULL PRIMARY KEY,
referral_first_name VARCHAR(25) NOT NULL,
referral_last_name VARCHAR(25) NOT NULL,
referral_location VARCHAR(44) NOT NULL,
referral_phone VARCHAR(15),
reference_phone VARCHAR(15),
CONSTRAINT fk_customer_id_1 FOREIGN KEY (customer_id) REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
Positions:
CREATE TABLE Positions (
customer_id INT NOT NULL,
positions_id INT NOT NULL,
positions_title VARCHAR(25) NOT NULL,
CONSTRAINT pk_positions PRIMARY KEY (customer_id, positions_id),
CONSTRAINT fk_customer_id_2 FOREIGN KEY (customer_id) REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_positions_id FOREIGN KEY (positions_id) REFERENCES Positions (positions_id)
ON UPDATE CASCADE
ON DELETE NO ACTION
);
Skills:
CREATE TABLE Skills (
skill_id INT NOT NULL PRIMARY KEY,
skill_ hardware VARCHAR(25) NOT NULL,
skill_software VARCHAR(25) NOT NULL,
proficiency_level_hardware VARCHAR(25) NOT NULL,
proficiency_level_software VARCHAR(25) NOT NULL
);
Interviews:
CREATE TABLE Interviews (
interview_id INT NOT NULL PRIMARY KEY,
interview_date DATE NOT NULL,
interviewer VARCHAR(25),
feedback VARCHAR(255)
);
HiringStatus:
CREATE TABLE HiringStatus (
hire_status_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
employer_id INT NOT NULL,
hired VARCHAR(5),
CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES Customers (customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_employer_id FOREIGN KEY (employer_id) REFERENCES Employers (employer_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
