Database Design RSS Database Design RSS

Database final project almost done want some proffesional insight

Reply

•
•
Join Date: May 2004
Posts: 1,478
Reputation: mikeandike22 is an unknown quantity at this point 
Solved Threads: 18
mikeandike22's Avatar
mikeandike22 mikeandike22 is offline Offline
Nearly a Posting Virtuoso

Database final project almost done want some proffesional insight

 
0
  #1
May 3rd, 2009
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.
My Daniweb Blog: This,That, and Everything Else (Blog contest winner)

GetFirefox!
GetOpera!
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2008
Posts: 88
Reputation: urtrivedi is an unknown quantity at this point 
Solved Threads: 13
urtrivedi urtrivedi is offline Offline
Junior Poster in Training

Re: Database final project almost done want some proffesional insight

 
0
  #2
May 4th, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2006
Posts: 1,824
Reputation: ithelp is a name known to all ithelp is a name known to all ithelp is a name known to all ithelp is a name known to all ithelp is a name known to all ithelp is a name known to all 
Solved Threads: 117
ithelp's Avatar
ithelp ithelp is offline Offline
Posting Virtuoso

Re: Database final project almost done want some proffesional insight

 
0
  #3
May 4th, 2009
•
•
•
•
Originally Posted by urtrivedi View Post
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.
Assignment Help | Web Design Service | Homework Help | 498a
Programming Help | Need Help ? | Job Interview | Top 10 Hosts
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2004
Posts: 1,478
Reputation: mikeandike22 is an unknown quantity at this point 
Solved Threads: 18
mikeandike22's Avatar
mikeandike22 mikeandike22 is offline Offline
Nearly a Posting Virtuoso

Re: Database final project almost done want some proffesional insight

 
0
  #4
May 4th, 2009
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
My Daniweb Blog: This,That, and Everything Else (Blog contest winner)

GetFirefox!
GetOpera!
Reply With Quote Quick reply to this message  
•
•
Join Date: May 2004
Posts: 1,478
Reputation: mikeandike22 is an unknown quantity at this point 
Solved Threads: 18
mikeandike22's Avatar
mikeandike22 mikeandike22 is offline Offline
Nearly a Posting Virtuoso

Re: Database final project almost done want some proffesional insight

 
0
  #5
May 4th, 2009
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

http://img412.imageshack.us/img412/946/sqldb.png
My Daniweb Blog: This,That, and Everything Else (Blog contest winner)

GetFirefox!
GetOpera!
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the Database Design Forum
Thread Tools Search this Thread



assignment avatar backup bigbrother business calendar daniweb data database databasedesign design erd europe events hacker hp ibm medicine mobilephone mysql news recovery security server sql survey uk update
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC