lib database complete data

lib  database complete data


# MySQL-Front 3.2  (Build 14.3)

/*!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 latin1 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='SYSTEM' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;


# Host: localhost    Database: lib
# ------------------------------------------------------
# Server version 5.0.27-community-nt

#
# Table structure for table bookissue
#

DROP TABLE IF EXISTS `bookissue`;
CREATE TABLE `bookissue` (
  `ModeratorId` varchar(11) default NULL,
  `BookIssueId` varchar(11) NOT NULL default '',
  `BookId` varchar(11) default NULL,
  `UserId` varchar(11) default NULL,
  `Card` varchar(11) default NULL,
  `DateOfIssue` date default NULL,
  `DateOfReturn` date default NULL,
  `GropId` varchar(11) NOT NULL,
  PRIMARY KEY  (`BookIssueId`),
  UNIQUE KEY `ModeratorId` (`ModeratorId`),
  UNIQUE KEY `UserId` (`UserId`),
  UNIQUE KEY `BookId` (`BookId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;

#
# Dumping data for table bookissue
#


#
# Table structure for table bookorder
#

DROP TABLE IF EXISTS `bookorder`;
CREATE TABLE `bookorder` (
  `OrderNo` varchar(11) NOT NULL default '',
  `OrderId` varchar(11) default NULL,
  `OrderBookName` varchar(50) default NULL,
  `OrderAuthor` varchar(100) default NULL,
  `OrderNoOfCopies` int(11) default NULL,
  `OrderPublisherName` varchar(50) default NULL,
  `OrderEdition` varchar(30) default NULL,
  `OrderPrice` int(11) default NULL,
  `OrderOther` varchar(100) default NULL,
  `ModeratorId` varchar(11) NOT NULL default '',
  PRIMARY KEY  (`OrderNo`),
  KEY `OrderId` (`OrderId`),
  KEY `M` (`ModeratorId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

#
# Dumping data for table bookorder
#


#
# Table structure for table category
#

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `DepartmentId` varchar(11) default NULL,
  `CategoryId` varchar(11) NOT NULL,
  `CategoryName` varchar(50) default NULL,
  PRIMARY KEY  (`CategoryId`),
  UNIQUE KEY `DepartmentId` (`DepartmentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table category
#


#
# Table structure for table finedetails
#

DROP TABLE IF EXISTS `finedetails`;
CREATE TABLE `finedetails` (
  `BookIssueId` varchar(11) NOT NULL default '',
  `UserId` varchar(11) NOT NULL default '',
  `BookId` varchar(11) default NULL,
  `FineDate` date default NULL,
  `FineAmount` int(11) default NULL,
  `ReceiptNo` varchar(11) default NULL,
  `GropId` varchar(11) NOT NULL,
  PRIMARY KEY  (`BookIssueId`),
  KEY `UserId` (`UserId`),
  KEY `gId` (`GropId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

#
# Dumping data for table finedetails
#


#
# Table structure for table groups
#

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `GroupId` varchar(11) NOT NULL default '',
  `GroupName` varchar(30) default NULL,
  `NoOfDays` int(11) NOT NULL,
  `FineAmount` int(11) NOT NULL,
  `ModeratorId` varchar(11) NOT NULL default '',
  PRIMARY KEY  (`GroupId`),
  KEY `m` (`ModeratorId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;

#
# Dumping data for table groups
#


#
# Table structure for table librarydepartment
#

DROP TABLE IF EXISTS `librarydepartment`;
CREATE TABLE `librarydepartment` (
  `DepartmentId` varchar(11) NOT NULL,
  `DepartmentName` varchar(50) default NULL,
  `ModeratorId` varchar(11) NOT NULL default '',
  PRIMARY KEY  (`DepartmentId`),
  KEY `ModeratorId` (`ModeratorId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table librarydepartment
#


#
# Table structure for table login
#

DROP TABLE IF EXISTS `login`;
CREATE TABLE `login` (
  `AdminId` varchar(20) NOT NULL default '',
  `Password` varchar(10) NOT NULL,
  `adminname` varchar(11) default NULL,
  `Username` varchar(11) default NULL,
  PRIMARY KEY  (`AdminId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

#
# Dumping data for table login
#


#
# Table structure for table moderator
#

DROP TABLE IF EXISTS `moderator`;
CREATE TABLE `moderator` (
  `ModeratorId` varchar(11) NOT NULL default '',
  `GroupId` varchar(11) default NULL,
  `ModeratorName` varchar(50) default NULL,
  `CreationDate` datetime NOT NULL,
  `MUserName` varchar(20) default NULL,
  `Mpassword` varchar(11) default NULL,
  `Mqualification` varchar(11) default NULL,
  `AdminId` varchar(20) default NULL,
  PRIMARY KEY  (`ModeratorId`),
  UNIQUE KEY `username` (`MUserName`),
  KEY `AdminId` (`AdminId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

#
# Dumping data for table moderator
#


#
# Table structure for table purchase
#

DROP TABLE IF EXISTS `purchase`;
CREATE TABLE `purchase` (
  `PurchaseId` varchar(11) NOT NULL default '',
  `OrderNo` varchar(11) default NULL,
  `BookName` varchar(100) default NULL,
  `PurchaseNoOfCopies` int(11) default NULL,
  `PublisherName` varchar(100) default NULL,
  `Author` varchar(50) default NULL,
  `Edition` varchar(11) default NULL,
  `Price` int(11) default NULL,
  `PurchaseDate` date default NULL,
  `AdminId` varchar(20) default NULL,
  PRIMARY KEY  (`PurchaseId`),
  KEY `OrderNO` (`OrderNo`),
  KEY `UserName` (`AdminId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;

#
# Dumping data for table purchase
#

INSERT INTO `purchase` VALUES ('',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

#
# Table structure for table subcategory
#

DROP TABLE IF EXISTS `subcategory`;
CREATE TABLE `subcategory` (
  `CategoryId` varchar(11) default NULL,
  `SubcategoryId` varchar(11) NOT NULL default '',
  `SubcategoryName` varchar(50) default NULL,
  PRIMARY KEY  (`SubcategoryId`),
  UNIQUE KEY `Categoryid` (`CategoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;

#
# Dumping data for table subcategory
#


#
# Table structure for table topics
#

DROP TABLE IF EXISTS `topics`;
CREATE TABLE `topics` (
  `SubcategoryId` varchar(11) default NULL,
  `BookId` varchar(11) NOT NULL default '0',
  `TopicName` varchar(50) default NULL,
  `Rack` int(11) default NULL,
  `PurchaseId` varchar(11) NOT NULL default '',
  PRIMARY KEY  (`BookId`),
  UNIQUE KEY `SubcategoryId` (`SubcategoryId`),
  KEY `PurchaseId` (`PurchaseId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

#
# Dumping data for table topics
#


#
# Table structure for table userregister
#

DROP TABLE IF EXISTS `userregister`;
CREATE TABLE `userregister` (
  `UserId` varchar(11) NOT NULL default '',
  `GroupId` varchar(11) default NULL,
  `UserUserName` varchar(100) default NULL,
  `UserPassword` varchar(30) default NULL,
  `UserDeptName` varchar(50) default NULL,
  `Batch` varchar(11) default NULL,
  `NoOfBookAllowed` int(11) default NULL,
  `CardOne` varchar(11) default NULL,
  `CardTwo` varchar(11) default NULL,
  `CardThree` varchar(11) default NULL,
  `UserRenewal` varchar(50) default NULL,
  `ModeratorId` varchar(11) default NULL,
  PRIMARY KEY  (`UserId`),
  UNIQUE KEY `Moderator` (`ModeratorId`),
  UNIQUE KEY `group` (`GroupId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED;

#
# Dumping data for table userregister
#


#
#  Foreign keys for table bookissue
#

ALTER TABLE `bookissue`
  ADD FOREIGN KEY (`BookId`) REFERENCES `topics` (`BookId`),
  ADD FOREIGN KEY (`ModeratorId`) REFERENCES `moderator` (`ModeratorId`);

#
#  Foreign keys for table bookorder
#

ALTER TABLE `bookorder`
  ADD FOREIGN KEY (`ModeratorId`) REFERENCES `moderator` (`ModeratorId`);

#
#  Foreign keys for table category
#

ALTER TABLE `category`
  ADD FOREIGN KEY (`DepartmentId`) REFERENCES `librarydepartment` (`DepartmentId`);

#
#  Foreign keys for table finedetails
#

ALTER TABLE `finedetails`
  ADD FOREIGN KEY (`BookIssueId`) REFERENCES `bookissue` (`BookIssueId`),
  ADD FOREIGN KEY (`GropId`) REFERENCES `groups` (`GroupId`),
  ADD FOREIGN KEY (`UserId`) REFERENCES `bookissue` (`UserId`);

#
#  Foreign keys for table groups
#

ALTER TABLE `groups`
  ADD FOREIGN KEY (`ModeratorId`) REFERENCES `moderator` (`ModeratorId`);

#
#  Foreign keys for table librarydepartment
#

ALTER TABLE `librarydepartment`
  ADD FOREIGN KEY (`ModeratorId`) REFERENCES `moderator` (`ModeratorId`);

#
#  Foreign keys for table moderator
#

ALTER TABLE `moderator`
  ADD FOREIGN KEY (`AdminId`) REFERENCES `login` (`AdminId`);

#
#  Foreign keys for table purchase
#

ALTER TABLE `purchase`
  ADD FOREIGN KEY (`AdminId`) REFERENCES `login` (`AdminId`),
  ADD FOREIGN KEY (`OrderNo`) REFERENCES `bookorder` (`OrderNo`);

#
#  Foreign keys for table subcategory
#

ALTER TABLE `subcategory`
  ADD FOREIGN KEY (`CategoryId`) REFERENCES `category` (`CategoryId`);

#
#  Foreign keys for table topics
#

ALTER TABLE `topics`
  ADD FOREIGN KEY (`PurchaseId`) REFERENCES `purchase` (`PurchaseId`),
  ADD FOREIGN KEY (`SubcategoryId`) REFERENCES `subcategory` (`SubcategoryId`);

#
#  Foreign keys for table userregister
#

ALTER TABLE `userregister`
  ADD FOREIGN KEY (`GroupId`) REFERENCES `groups` (`GroupId`),
  ADD FOREIGN KEY (`ModeratorId`) REFERENCES `moderator` (`ModeratorId`);


/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!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 */;

Posted in: