School Assignment? Project Due Tomorrow? Chat LIVE With A Programming Expert!

Programming Help

Web Development



What's Here?

  • Members: 340,103
  • Replies: 920,374
  • Topics: 154,922
  • Snippets: 4,854
  • Tutorials: 1,257
  • Total Online: 4,758
  • Members: 135
  • Guests: 4,623
Welcome to Dream.In.Code
Become an Expert!

Join 340,103 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 4,758 people online right now. Registration is fast and FREE... Join Now!



ER Diagram and Foreign KEY relations in mysql

Page 1 of 1

ER Diagram and Foreign KEY relations in mysql Rate Topic: -----

#1 inf4my  Icon User is offline

  • New D.I.C Head
  • Pip
  • Group: Members
  • Posts: 26
  • Joined: 19-November 07


Dream Kudos: 0

Posted 03 May 2009 - 03:24 PM

Hello Im working on my final project for databases and was wondering if someone could help me out with the foreign key relationship part.
Here is a dump of my database right now.

-- 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,
  `10ytd` decimal(4,2) default NULL,
  `ytd` decimal(4,2) default NULL,
  `name` varchar(40) default NULL,
  PRIMARY KEY  (`bond_id`)
) ENGINE=MyISAM 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`)
) ENGINE=MyISAM 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=MyISAM 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,
  `5ytd` decimal(4,2) default NULL,
  `ytd` decimal(4,2) default NULL,
  `name` varchar(40) default NULL,
  PRIMARY KEY  (`mf_id`)
) ENGINE=MyISAM 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`)
) ENGINE=MyISAM 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,
  PRIMARY KEY  (`stock_id`,`stock_ticker`),
  UNIQUE KEY `stock_ticker` (`stock_ticker`)
) ENGINE=MyISAM 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,
  `ytd` decimal(4,2) default NULL,
  `mtd` decimal(4,2) default NULL,
  `name` varchar(40) default NULL,
  PRIMARY KEY  (`stock_id`)
) ENGINE=MyISAM 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,
  `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`)
) ENGINE=MyISAM 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-03 23:23:57


And here is the E-R diagram that I made for the database.
Posted Image

When I use that dump of the database and sqlfairy it comes out like this
Posted Image

not making any connections between the databses.

This post has been edited by inf4my: 03 May 2009 - 03:25 PM

Was This Post Helpful? 0
  • +
  • -

#2 AdaHacker  Icon User is offline

  • D.I.C Regular
  • PipPipPip
  • Group: Members
  • Posts: 466
  • Joined: 17-June 08


Dream Kudos: 0

Posted 04 May 2009 - 05:25 AM

I'm not clear on exactly what you're looking for. However, I do notice there are no foreign keys declared in your database dump. This is not surprising, as the dump uses MyISAM for your storage engine. If you're not aware, MyISAM doesn't support foreign key constraints - it simply ignores them. If you want the tables to have foreign keys, you need to switch to the InnoDB engine.
Was This Post Helpful? 0
  • +
  • -

#3 inf4my  Icon User is offline

  • New D.I.C Head
  • Pip
  • Group: Members
  • Posts: 26
  • Joined: 19-November 07


Dream Kudos: 0

Posted 04 May 2009 - 07:37 AM

Thanks, yea I am new to setting up databases. I am just looking for general look over and hoping any blaring mistakes I made can be caught today.
Was This Post Helpful? 0
  • +
  • -

#4 inf4my  Icon User is offline

  • New D.I.C Head
  • Pip
  • Group: Members
  • Posts: 26
  • Joined: 19-November 07


Dream Kudos: 0

Posted 04 May 2009 - 08:35 AM

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


Posted Image
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1

Fast Reply

  

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users



Live Help!

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter Fan Us On Facebook

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month