SQL DDL to Diagram

CREATE DATABASE IF NOT EXISTS StockExchange;

USE StockExchange;

​

CREATE TABLE Companies (

  companyID INT AUTO_INCREMENT,

  name VARCHAR(255) NOT NULL,

  industry VARCHAR(255),

  PRIMARY KEY (companyID)

);

​

CREATE TABLE Stocks (

  stockID INT AUTO_INCREMENT,

  symbol VARCHAR(10) NOT NULL,

  companyID INT,

  IPO_date DATE,

  PRIMARY KEY (stockID),

  FOREIGN KEY (companyID) REFERENCES Companies(companyID)

);

​

CREATE TABLE StockPrice (

  priceID INT AUTO_INCREMENT,

  stockID INT,

  date DATE,

  open_price DECIMAL(10,2),

  close_price DECIMAL(10,2),

  high_price DECIMAL(10,2),

  low_price DECIMAL(10,2),

  volume BIGINT,

  PRIMARY KEY (priceID),

  FOREIGN KEY (stockID) REFERENCES Stocks(stockID)

);

​

CREATE TABLE Brokers (

  brokerID INT AUTO_INCREMENT,

  name VARCHAR(255) NOT NULL,

  contact VARCHAR(255),

  PRIMARY KEY (brokerID)

);

​

CREATE TABLE Transactions (

  transactionID INT AUTO_INCREMENT,

  brokerID INT,

  stockID INT,

  quantity INT,

  transaction_type ENUM('BUY', 'SELL'),

  transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  transaction_price DECIMAL(10,2),

  PRIMARY KEY (transactionID),

  FOREIGN KEY (brokerID) REFERENCES Brokers(brokerID),

  FOREIGN KEY (stockID) REFERENCES Stocks(stockID)

);