Write Two CREATE USER Statements for the Publisher -Author - Books - Marketing: Database -- drop the database Publisher -Author - Books - MarketingDB if it already exists DROP DATABASE IF EXISTS PublisherAuthorBooksMarketingDB; -- create a database with name Publisher -Author - Books - MarketingDB CREATE DATABASE IF NOT EXISTS PublisherAuthorBooksMarketingDB; -- ensure that the database is used USE PublisherAuthorBooksMarketingDB; -- create the table for Publisher DROP TABLE IF EXISTS Publisher; CREATE TABLE IF NOT EXISTS Publisher ( pid VARCHAR(25), pubName VARCHAR(50), email VARCHAR(50), address VARCHAR(50), phone VARCHAR(15), CONSTRAINT Publisher_PK PRIMARY KEY(pid) ); -- create the table for Author DROP TABLE IF EXISTS Author; CREATE TABLE Author( socSecNum VARCHAR(15), authName VARCHAR (50), phone VARCHAR (15), address VARCHAR (50), email VARCHAR (50), CONSTRAINT Author_PK PRIMARY KEY (socSecNum) ); -- create the table for Marketer DROP TABLE IF EXISTS Marketer; CREATE TABLE Marketer( mid VARCHAR(25), mName VARCHAR (255), address VARCHAR(50), phone VARCHAR (15), contact VARCHAR (50), CONSTRAINT Marketer_FK PRIMARY KEY (mid) ); -- create the table for Book DROP TABLE IF EXISTS Book; CREATE TABLE Book( publisher_FK VARCHAR(25), author_FK VARCHAR(15), isbn VARCHAR(25), title VARCHAR(50), yearP DATE, CONSTRAINT Book_PK PRIMARY KEY (isbn), CONSTRAINT Book_FK1 FOREIGN KEY (publisher_FK) REFERENCES Publisher(pid), CONSTRAINT Book_FK2 FOREIGN KEY (author_FK) REFERENCES Author(socSecNum) ); -- create the table for Commisions DROP TABLE IF EXISTS Commissions; CREATE TABLE Commissions ( cnum VARCHAR (25), cDate date, pid_FK VARCHAR (25), mid_FK VARCHAR (25), isbn_FK VARCHAR (25), CONSTRAINT Commissions_PK PRIMARY KEY(cnum), CONSTRAINT Commissions_FK1 FOREIGN KEY (mid_FK) REFERENCES Marketer(mid), CONSTRAINT Commissions_FK2 FOREIGN KEY (pid_FK) REFERENCES Publisher(pid), CONSTRAINT Commissions_FK3 FOREIGN KEY (isbn_FK) REFERENCES Book(isbn) ); -- create the table for Contract DROP TABLE IF EXISTS Contracts; CREATE TABLE Contracts ( contractNum VARCHAR(25), pid_FK VARCHAR(25), socSecNum_FK VARCHAR(15), Odate DATE, Sdate DATE, CONSTRAINT Contracts_PK PRIMARY KEY(contractNum), CONSTRAINT Contracts_FK1 FOREIGN KEY(pid_FK) REFERENCES Publisher(pid), CONSTRAINT Contracts_FK2 FOREIGN KEY(socSecNum_FK) REFERENCES Author(socSecNum) )
Write Two CREATE USER Statements for the Publisher -Author - Books - Marketing:
-- drop the database Publisher -Author - Books - MarketingDB if it already exists
DROP DATABASE IF EXISTS PublisherAuthorBooksMarketingDB;
-- create a database with name Publisher -Author - Books - MarketingDB
CREATE DATABASE IF NOT EXISTS PublisherAuthorBooksMarketingDB;
-- ensure that the database is used
USE PublisherAuthorBooksMarketingDB;
-- create the table for Publisher
DROP TABLE IF EXISTS Publisher;
CREATE TABLE IF NOT EXISTS Publisher
(
pid VARCHAR(25),
pubName VARCHAR(50),
email VARCHAR(50),
address VARCHAR(50),
phone VARCHAR(15),
CONSTRAINT Publisher_PK PRIMARY KEY(pid)
);
-- create the table for Author
DROP TABLE IF EXISTS Author;
CREATE TABLE Author(
socSecNum VARCHAR(15),
authName VARCHAR (50),
phone VARCHAR (15),
address VARCHAR (50),
email VARCHAR (50),
CONSTRAINT Author_PK PRIMARY KEY (socSecNum)
);
-- create the table for Marketer
DROP TABLE IF EXISTS Marketer;
CREATE TABLE Marketer(
mid VARCHAR(25),
mName VARCHAR (255),
address VARCHAR(50),
phone VARCHAR (15),
contact VARCHAR (50),
CONSTRAINT Marketer_FK PRIMARY KEY (mid)
);
-- create the table for Book
DROP TABLE IF EXISTS Book;
CREATE TABLE Book(
publisher_FK VARCHAR(25),
author_FK VARCHAR(15),
isbn VARCHAR(25),
title VARCHAR(50),
yearP DATE,
CONSTRAINT Book_PK PRIMARY KEY (isbn),
CONSTRAINT Book_FK1 FOREIGN KEY (publisher_FK)
REFERENCES Publisher(pid),
CONSTRAINT Book_FK2 FOREIGN KEY (author_FK)
REFERENCES Author(socSecNum)
);
-- create the table for Commisions
DROP TABLE IF EXISTS Commissions;
CREATE TABLE Commissions
(
cnum VARCHAR (25),
cDate date,
pid_FK VARCHAR (25),
mid_FK VARCHAR (25),
isbn_FK VARCHAR (25),
CONSTRAINT Commissions_PK PRIMARY KEY(cnum),
CONSTRAINT Commissions_FK1 FOREIGN KEY (mid_FK)
REFERENCES Marketer(mid),
CONSTRAINT Commissions_FK2 FOREIGN KEY (pid_FK)
REFERENCES Publisher(pid),
CONSTRAINT Commissions_FK3 FOREIGN KEY (isbn_FK)
REFERENCES Book(isbn)
);
-- create the table for Contract
DROP TABLE IF EXISTS Contracts;
CREATE TABLE Contracts
(
contractNum VARCHAR(25),
pid_FK VARCHAR(25),
socSecNum_FK VARCHAR(15),
Odate DATE,
Sdate DATE,
CONSTRAINT Contracts_PK PRIMARY KEY(contractNum),
CONSTRAINT Contracts_FK1 FOREIGN KEY(pid_FK)
REFERENCES Publisher(pid),
CONSTRAINT Contracts_FK2 FOREIGN KEY(socSecNum_FK)
REFERENCES Author(socSecNum)
)
Step by step
Solved in 3 steps
