how to create a metadeta fore this SQL script: -- creating the clinic DataBase CREATE DATABASE clinic; USE clinic; -- creating the doctor table with the doctor _id as a primary key CREATE TABLE doctor( doctor_id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(15), specilization VARCHAR(15), phone VARCHAR(10), gender VARCHAR(10), PRIMARY KEY (doctor_id) ); -- creating the patient table with the patient_id as a primary key CREATE TABLE patient( patient_id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(15), age INT, gender VARCHAR(10), weight INT, cinic VARCHAR(10), PRIMARY KEY (patient_id) ); -- creating the apointment table with the id int as a primary key CREATE TABLE appointments( id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, doctor_id INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- creating the tests table with the test_id as a primary key CREATE TABLE tests( test_id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(10), duration VARCHAR(10), patient_id INT NOT NULL, doctor_id INT NOT NULL, PRIMARY KEY (test_id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- creating the labortory table with the id int as a primary key CREATE TABLE labortory( id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(10), timing VARCHAR(15), PRIMARY KEY (id) ); -- creating the mediciones table with the medicines_id as a primary key CREATE TABLE medicines( medicine_id INT NOT NULL AUTO_INCREMENT, medicine_name VARCHAR(10), prescription VARCHAR(10), patient_id INT NOT NULL, doctor_id INT NOT NULL, PRIMARY KEY (medicine_id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- creating the bill table with the bill_id as a primary key CREATE TABLE bill( bill_id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, doctor_id INT NOT NULL, bill_date DATETIME, PRIMARY KEY (bill_id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- ADDING total in bill using alter ALTER TABLE bill ADD total INT; -- Inserting a value In doctor,patient,medicines,bill INSERT INTO doctor (`name`, specilization, phone, gender) VALUES('ibn alhajam','cardiologist','+931122','MALE'); INSERT INTO patient (`name`, age, gender, weight, cinic) VALUES('ali',20,'M',50,'34501699'); INSERT INTO medicines (medicine_name, prescription, patient_id, doctor_id) VALUES('leflox','2 a day',1,1); INSERT INTO bill (patient_id, doctor_id, bill_date, total) VALUES(1,1,'2020-06-12',5000); SELECT * FROM doctor; SELECT * FROM patient; SELECT * FROM medicines; SELECT * FROM bill; -- Displaying values from doctor where name is ALI SELECT * FROM patient WHERE `name`='ali'; -- counting total number of patients SELECT COUNT(patient_id) FROM patient; -- Displaying values from doctor where name ends in a SELECT * FROM doctor WHERE `name` LIKE '%a'; -- Displaying values of patient along with their medicines SELECT m.medicine_name,m.prescription FROM patient p JOIN medicines m ON m.patient_id=p.patient_id WHERE p.`name`='ali'; -- Displaying values of patient along with their medicines and total bill SELECT m.medicine_name,m.prescription,b.total,p.`name` FROM patient p JOIN medicines m ON m.patient_id=p.patient_id JOIN bill b ON b.patient_id=p.patient_id WHERE p.`name`='ali'; -- Creating Views CREATE VIEW disp AS SELECT m.medicine_name,m.prescription FROM patient p JOIN medicines m ON m.patient_id=p.patient_id WHERE p.`name`='ali'; CREATE VIEW display2 AS SELECT m.medicine_name,m.prescription,b.total,p.`name` FROM patient p JOIN medicines m ON m.patient_id=p.patient_id JOIN bill b ON b.patient_id=p.patient_id WHERE p.`name`='ali'; -- CREATNG ROLES FOR THE VIEWS CREATE ROLE IF NOT EXISTS admin1; GRANT CREATE ON clinic.* TO admin1; GRANT CREATE VIEW ON clinic.* TO admin1; CREATE role IF NOT EXISTS admin2; GRANT ALTER ON clinic.* TO admin2;
how to create a metadeta fore this SQL script: -- creating the clinic DataBase CREATE DATABASE clinic; USE clinic; -- creating the doctor table with the doctor _id as a primary key CREATE TABLE doctor( doctor_id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(15), specilization VARCHAR(15), phone VARCHAR(10), gender VARCHAR(10), PRIMARY KEY (doctor_id) ); -- creating the patient table with the patient_id as a primary key CREATE TABLE patient( patient_id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(15), age INT, gender VARCHAR(10), weight INT, cinic VARCHAR(10), PRIMARY KEY (patient_id) ); -- creating the apointment table with the id int as a primary key CREATE TABLE appointments( id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, doctor_id INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- creating the tests table with the test_id as a primary key CREATE TABLE tests( test_id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(10), duration VARCHAR(10), patient_id INT NOT NULL, doctor_id INT NOT NULL, PRIMARY KEY (test_id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- creating the labortory table with the id int as a primary key CREATE TABLE labortory( id INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(10), timing VARCHAR(15), PRIMARY KEY (id) ); -- creating the mediciones table with the medicines_id as a primary key CREATE TABLE medicines( medicine_id INT NOT NULL AUTO_INCREMENT, medicine_name VARCHAR(10), prescription VARCHAR(10), patient_id INT NOT NULL, doctor_id INT NOT NULL, PRIMARY KEY (medicine_id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- creating the bill table with the bill_id as a primary key CREATE TABLE bill( bill_id INT NOT NULL AUTO_INCREMENT, patient_id INT NOT NULL, doctor_id INT NOT NULL, bill_date DATETIME, PRIMARY KEY (bill_id), FOREIGN KEY (patient_id) REFERENCES patient(patient_id), FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id) ); -- ADDING total in bill using alter ALTER TABLE bill ADD total INT; -- Inserting a value In doctor,patient,medicines,bill INSERT INTO doctor (`name`, specilization, phone, gender) VALUES('ibn alhajam','cardiologist','+931122','MALE'); INSERT INTO patient (`name`, age, gender, weight, cinic) VALUES('ali',20,'M',50,'34501699'); INSERT INTO medicines (medicine_name, prescription, patient_id, doctor_id) VALUES('leflox','2 a day',1,1); INSERT INTO bill (patient_id, doctor_id, bill_date, total) VALUES(1,1,'2020-06-12',5000); SELECT * FROM doctor; SELECT * FROM patient; SELECT * FROM medicines; SELECT * FROM bill; -- Displaying values from doctor where name is ALI SELECT * FROM patient WHERE `name`='ali'; -- counting total number of patients SELECT COUNT(patient_id) FROM patient; -- Displaying values from doctor where name ends in a SELECT * FROM doctor WHERE `name` LIKE '%a'; -- Displaying values of patient along with their medicines SELECT m.medicine_name,m.prescription FROM patient p JOIN medicines m ON m.patient_id=p.patient_id WHERE p.`name`='ali'; -- Displaying values of patient along with their medicines and total bill SELECT m.medicine_name,m.prescription,b.total,p.`name` FROM patient p JOIN medicines m ON m.patient_id=p.patient_id JOIN bill b ON b.patient_id=p.patient_id WHERE p.`name`='ali'; -- Creating Views CREATE VIEW disp AS SELECT m.medicine_name,m.prescription FROM patient p JOIN medicines m ON m.patient_id=p.patient_id WHERE p.`name`='ali'; CREATE VIEW display2 AS SELECT m.medicine_name,m.prescription,b.total,p.`name` FROM patient p JOIN medicines m ON m.patient_id=p.patient_id JOIN bill b ON b.patient_id=p.patient_id WHERE p.`name`='ali'; -- CREATNG ROLES FOR THE VIEWS CREATE ROLE IF NOT EXISTS admin1; GRANT CREATE ON clinic.* TO admin1; GRANT CREATE VIEW ON clinic.* TO admin1; CREATE role IF NOT EXISTS admin2; GRANT ALTER ON clinic.* TO admin2;
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
Related questions
Question
100%
how to create a metadeta fore this SQL script:
-- creating the clinic DataBase
CREATE DATABASE clinic;
USE clinic;
-- creating the doctor table with the doctor _id as a primary key
CREATE TABLE doctor(
doctor_id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(15),
specilization VARCHAR(15),
phone VARCHAR(10),
gender VARCHAR(10),
PRIMARY KEY (doctor_id)
);
-- creating the patient table with the patient_id as a primary key
CREATE TABLE patient(
patient_id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(15),
age INT,
gender VARCHAR(10),
weight INT,
cinic VARCHAR(10),
PRIMARY KEY (patient_id)
);
-- creating the apointment table with the id int as a primary key
CREATE TABLE appointments(
id INT NOT NULL AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- creating the tests table with the test_id as a primary key
CREATE TABLE tests(
test_id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10),
duration VARCHAR(10),
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
PRIMARY KEY (test_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- creating the labortory table with the id int as a primary key
CREATE TABLE labortory(
id INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(10),
timing VARCHAR(15),
PRIMARY KEY (id)
);
-- creating the mediciones table with the medicines_id as a primary key
CREATE TABLE medicines(
medicine_id INT NOT NULL AUTO_INCREMENT,
medicine_name VARCHAR(10),
prescription VARCHAR(10),
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
PRIMARY KEY (medicine_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- creating the bill table with the bill_id as a primary key
CREATE TABLE bill(
bill_id INT NOT NULL AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
bill_date DATETIME,
PRIMARY KEY (bill_id),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
-- ADDING total in bill using alter
ALTER TABLE bill ADD total INT;
-- Inserting a value In doctor,patient,medicines,bill
INSERT INTO doctor (`name`, specilization, phone, gender) VALUES('ibn alhajam','cardiologist','+931122','MALE');
INSERT INTO patient (`name`, age, gender, weight, cinic) VALUES('ali',20,'M',50,'34501699');
INSERT INTO medicines (medicine_name, prescription, patient_id, doctor_id) VALUES('leflox','2 a day',1,1);
INSERT INTO bill (patient_id, doctor_id, bill_date, total) VALUES(1,1,'2020-06-12',5000);
SELECT * FROM doctor;
SELECT * FROM patient;
SELECT * FROM medicines;
SELECT * FROM bill;
-- Displaying values from doctor where name is ALI
SELECT * FROM patient WHERE `name`='ali';
-- counting total number of patients
SELECT COUNT(patient_id) FROM patient;
-- Displaying values from doctor where name ends in a
SELECT * FROM doctor WHERE `name` LIKE '%a';
-- Displaying values of patient along with their medicines
SELECT m.medicine_name,m.prescription
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
WHERE p.`name`='ali';
-- Displaying values of patient along with their medicines and total bill
SELECT m.medicine_name,m.prescription,b.total,p.`name`
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
JOIN bill b ON b.patient_id=p.patient_id
WHERE p.`name`='ali';
-- Creating Views
CREATE VIEW disp AS
SELECT m.medicine_name,m.prescription
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
WHERE p.`name`='ali';
CREATE VIEW display2 AS
SELECT m.medicine_name,m.prescription,b.total,p.`name`
FROM patient p
JOIN medicines m ON m.patient_id=p.patient_id
JOIN bill b ON b.patient_id=p.patient_id
WHERE p.`name`='ali';
-- CREATNG ROLES FOR THE VIEWS
CREATE ROLE IF NOT EXISTS admin1;
GRANT CREATE ON clinic.* TO admin1;
GRANT CREATE VIEW ON clinic.* TO admin1;
CREATE role IF NOT EXISTS admin2;
GRANT ALTER ON clinic.* TO admin2;
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 5 steps with 4 images
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY