943,866 Members | Top Members by Rank

Web Development > Databases > Database Design > Database final project almost done want some proffesional insight
Ad:
Permalink
May 3rd, 2009
0

Database final project almost done want some proffesional insight

Expand Post »
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.
Reputation Points: 33
Solved Threads: 19
Nearly a Posting Virtuoso
mikeandike22 is offline Offline
1,496 posts
since May 2004
Permalink
May 4th, 2009
0

Re: Database final project almost done want some proffesional insight

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.
Reputation Points: 245
Solved Threads: 259
Nearly a Posting Virtuoso
urtrivedi is offline Offline
1,249 posts
since Dec 2008
Permalink
May 4th, 2009
0

Re: Database final project almost done want some proffesional insight

Click to Expand / Collapse  Quote originally posted by urtrivedi ...
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.
Reputation Points: 769
Solved Threads: 128
Banned
ithelp is offline Offline
1,910 posts
since May 2006
Permalink
May 4th, 2009
0

Re: Database final project almost done want some proffesional insight

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
Reputation Points: 33
Solved Threads: 19
Nearly a Posting Virtuoso
mikeandike22 is offline Offline
1,496 posts
since May 2004
Permalink
May 4th, 2009
0

Re: Database final project almost done want some proffesional insight

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
Reputation Points: 33
Solved Threads: 19
Nearly a Posting Virtuoso
mikeandike22 is offline Offline
1,496 posts
since May 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Database Design Forum Timeline: FoxPro Index files in c#
Next Thread in Database Design Forum Timeline: Mechanical Engineering company relational schema





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC