Hey I am working on a database final project and I am almost done but it would be awesome if some database guru could look over my erd and my sql code and tell me if anything seems amiss.
for the project we just had to design a database with 5 or so tables for anything. I made up a fictional investment banking firm, obviously not as detailed as if I really had to make a DB for stock trading.
here is my ERD
http://img11.imageshack.us/img11/1689/erdiagram.jpg
and here is my sql code
CREATE DATABASE investment ;
USE investment;
CREATE TABLE brokers (
broker_id INT(10) NOT NULL AUTO INCREMENT,
license VARCHAR(50) NULL,
total_commission DECIMAL(18,2) NULL,
last_name VARCHAR(50) UNIQUE NULL,
first_name VARCHAR(50) NULL,
clients INT NULL,
PRIMARY KEY ( broker_id )
);
CREATE TABLE customers (
cust_id INT(10) NOT NULL AUTO INCREMENT,
ytd DECIMAL(18,2) NULL,
last_name VARCHAR(50) UNIQUE NULL,
first_name VARCHAR(50) NULL,
balance DECIMAL(10,2) NULL,
PRIMARY KEY ( cust_id )
);
CREATE TABLE orders (
order_id INT(10) NOT NULL AUTO INCREMENT,
date DATE NULL,
cust_id INT(10) NULL,
broker_id INT(10) NULL,
stock_ticker VARCHAR(40) NULL,
purchase_price DECIMAL(18,2) NULL,
shares INT(10) NULL,
buy_limit INT(10) NULL,
sell_limit INT(10) NULL,
PRIMARY KEY ( order_id ),
FOREIGN KEY ( cust_id ) REFERENCES CUSTOMERS(cust_id)
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY ( broker_id ) REFERENCES BROKERS( broker_id )
ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY ( stock_ticker ) REFERENCES SECURITIES( stock_ticker )
ON UPDATE CASCADE ON DELETE CASCADE,
);
CREATE TABLE trades (
trade_id INT(10) NOT NULL AUTO INCREMENT,
order_id INT(10) NULL,
price_per_share DECIMAL(18,2) NULL,
date DATE NULL,
commission DECIMAL(18,2) NULL,
trade_type VARCHAR(40) NULL,
PRIMARY KEY( trade_id ),
FOREIGN KEY( order_id ) REFERENCES ORDERS(order_id)
ON UPDATE CASCADE ON DELETE CASCADE,
);
CREATE TABLE securities (
stock_id INT(10) NOT NULL AUTO INCREMENT,
stock_ticker VARCHAR(40) UNIQUE NULL,
mkt_value DECIMAL(10,2) NULL,
stock_exchange VARCHAR(40) NULL,
security_type VARCHAR(40) NULL,
PRIMARY KEY( stock_id, stock_ticker )
);
CREATE TABLE mutual_funds (
mf_id INT(10) NOT NULL AUTO INCREMENT,
5ytd DECIMAL(4,2) NULL,
ytd DECIMAL(4,2) NULL,
name VARCHAR(40) NULL,
PRIMARY KEY( mf_id )
);
CREATE TABLE bonds (
bond_id INT(10) NOT NULL AUTO INCREMENT,
10ytd DECIMAL(4,2) NULL,
ytd DECIMAL(4,2) NULL,
name VARCHAR(40) NULL,
PRIMARY KEY( bond_id )
);
CREATE TABLE stocks (
stock_id INT(10) NOT NULL AUTO INCREMENT,
ytd DECIMAL(4,2) NULL,
mtd DECIMAL(4,2) NULL,
name VARCHAR(40) NULL,
PRIMARY KEY(stock_id)
);and I dont want to ask too much because I dont want to cheat obviously, but any cool queries you think I could do I dont need the sql code.
Thanks in advanced hope that you guys can help me out.
