0

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.

3
Contributors
4
Replies
7
Views
8 Years
Discussion Span
Last Post by mikeandike22
0

I have a question why you chose to create 3 separate tables for Stock, bond and mutual funds. According to me you can create one table with same columns and one more column say stock_type (Stock,Bond,Mutal), you can also create one master table for stock type so that, in future if any other type of stock is added you can easily manange without disturbing database design.
Also I would suggest to keep period id instead of 5ytd, or 10tyd like columns.

0

I have a question why you chose to create 3 separate tables for Stock, bond and mutual funds. According to me you can create one table with same columns and one more column say stock_type (Stock,Bond,Mutal), you can also create one master table for stock type so that, in future if any other type of stock is added you can easily manange without disturbing database design.
Also I would suggest to keep period id instead of 5ytd, or 10tyd like columns.

No I would not do that , that will make the design messy.

0

I decided to make those three tables just as a way to have more tables I still havent chosen an attribute that each would have that would be truly unique.

I also was wondering if there is anyway to implement a supertype/subtype relationship in SQL

0

Ive made some updates to my database
Here is my new SQL dump and sqlfairy print out.

-- MySQL dump 10.11
--
-- Host: localhost    Database: investment
-- ------------------------------------------------------
-- Server version	5.0.75-0ubuntu10

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `bonds`
--

DROP TABLE IF EXISTS `bonds`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `bonds` (
  `bond_id` int(10) NOT NULL auto_increment,
  `name` varchar(40) default NULL,
  `maturity` date NOT NULL,
  `high_yieldyn` varchar(1) NOT NULL,
  `bond_rate` decimal(10,0) NOT NULL,
  PRIMARY KEY  (`bond_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `brokers`
--

DROP TABLE IF EXISTS `brokers`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `brokers` (
  `broker_id` int(10) NOT NULL auto_increment,
  `license` varchar(50) default NULL,
  `total_commission` decimal(18,2) default NULL,
  `last_name` varchar(50) default NULL,
  `first_name` varchar(50) default NULL,
  `clients` int(11) default NULL,
  PRIMARY KEY  (`broker_id`),
  UNIQUE KEY `last_name` (`last_name`),
  UNIQUE KEY `last_name_2` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `customers`
--

DROP TABLE IF EXISTS `customers`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `customers` (
  `cust_id` int(10) NOT NULL auto_increment,
  `ytd` decimal(18,2) default NULL,
  `last_name` varchar(50) default NULL,
  `first_name` varchar(50) default NULL,
  `balance` decimal(10,2) default NULL,
  PRIMARY KEY  (`cust_id`),
  UNIQUE KEY `last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `mutual_funds`
--

DROP TABLE IF EXISTS `mutual_funds`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `mutual_funds` (
  `mf_id` int(10) NOT NULL auto_increment,
  `name` varchar(40) default NULL,
  `NAV` decimal(10,0) NOT NULL,
  `ERV` decimal(10,0) NOT NULL,
  `mgmt_fee` decimal(10,0) NOT NULL,
  PRIMARY KEY  (`mf_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `orders`
--

DROP TABLE IF EXISTS `orders`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `orders` (
  `order_id` int(10) NOT NULL auto_increment,
  `date` date default NULL,
  `o_cust_id` int(10) default NULL,
  `o_broker_id` int(10) default NULL,
  `o_stock_ticker` varchar(40) default NULL,
  `purchase_price` decimal(18,2) default NULL,
  `shares` int(10) default NULL,
  `buy_limit` int(10) default NULL,
  `sell_limit` int(10) default NULL,
  PRIMARY KEY  (`order_id`),
  KEY `o_cust_id` (`o_cust_id`),
  KEY `o_cust_id_2` (`o_cust_id`),
  KEY `o_stock_ticker` (`o_stock_ticker`),
  KEY `o_broker_id` (`o_broker_id`),
  CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`o_stock_ticker`) REFERENCES `securities` (`stock_ticker`) ON UPDATE CASCADE,
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`o_cust_id`) REFERENCES `customers` (`cust_id`) ON UPDATE CASCADE,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`o_broker_id`) REFERENCES `brokers` (`broker_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `securities`
--

DROP TABLE IF EXISTS `securities`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `securities` (
  `stock_id` int(10) NOT NULL auto_increment,
  `stock_ticker` varchar(40) NOT NULL default '',
  `mkt_value` decimal(10,2) default NULL,
  `stock_exchange` varchar(40) default NULL,
  `security_type` varchar(40) default NULL,
  `10YTD` decimal(10,0) NOT NULL,
  `5YTD` decimal(10,0) NOT NULL,
  `YTD` decimal(10,0) NOT NULL,
  `MTD` decimal(10,0) NOT NULL,
  PRIMARY KEY  (`stock_id`,`stock_ticker`),
  UNIQUE KEY `stock_ticker` (`stock_ticker`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `stocks`
--

DROP TABLE IF EXISTS `stocks`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `stocks` (
  `stock_id` int(10) NOT NULL auto_increment,
  `Company` varchar(40) default NULL,
  PRIMARY KEY  (`stock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `trades`
--

DROP TABLE IF EXISTS `trades`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `trades` (
  `trade_id` int(10) NOT NULL auto_increment,
  `t_order_id` int(10) default NULL,
  `price_per_share` decimal(18,2) default NULL,
  `date` date default NULL,
  `commission` decimal(18,2) default NULL,
  `trade_type` varchar(40) default NULL,
  PRIMARY KEY  (`trade_id`),
  KEY `t_order_id` (`t_order_id`),
  CONSTRAINT `trades_ibfk_1` FOREIGN KEY (`t_order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-05-04 16:29:58

[img]http://img412.imageshack.us/img412/946/sqldb.png[/img]

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.