projects
SQL Database Project for Database 1 - UTA
Finished Project
Project Requirements
Assume that the following requirements were collected for this application: 1. The database keeps track of CUSTOMERs. Each CUSTOMER has a unique IdNo (assume this is a unique integer generated by the system for each new CUSTOMER, such as 1, 2, 3, ...), a Name (assume this is string consisting of an initial and last name only for simplicity, such as J.Smith), and an Address (a string of up to 40 characters with spaces). 2. The database keeps track of PUBLICATIONs, which are categorized based on their type. There are two main types: NEWSPAPER, and MAGAZINE, plus another type called OTHER to accommodate publications that do not fall into one of the two main types (we will not implement OTHER in this project). 3. A PUBLICATION has a Name (unique for each publication), Frequency, and Type. The Frequency for a MAGAZINE can be weekly, monthly, or quarterly, and for a NEWSPAPER it can be daily or weekly. A MAGAZINE can have different subscription periods and rates (for example, 12 issues for $20; 24 issues for $30). Assume that each magazine has one or more Rates determined by the NumberOfIssues in the subscription period. 4. The database will keep track of the current (active) SUBSCRIPTIONs of each CUSTOMER. For MAGAZINE subscriptions, the information kept will include NumberOfIssues, StartDate, and EndDate (the EndDate can be calculated from the StartDate, NumberOfIssues, and Frequency of the MAGAZINE). 5. Assume that all NEWSPAPER subscriptions for daily newspapers are measured in NoOfMonths. However, there are different subscription rates: for 7-day subscription (all issues including Saturday and Sunday), 5-day subscription (Monday through Friday), and 2-day subscription (Saturday and Sunday only). For weekly newspapers, the rate is based on NumberOfIssues (as in the case of magazines). You will first design an EER diagram based upon the SUBSCRIPTIONs database requirements specified above, and create an EER schema diagram and documentation report describing your design choices. As part of this assignment, you should identify any missing or incomplete requirements, and explicitly state them in your documentation. You should also explicitly state any assumptions you made that were not part of the requirements listed above. The second part of the assignment will be to map the EER schema design to a relational schema diagram, and create the tables corresponding to the relational schema using the ORACLE DBMS (or MySQL). You will add to your report a listing of the CREATE TABLE statements. Specify as many constraints (key, referential integrity) as you can in the relational schema. You should state the choices you made during the EER-to-relational mapping, and the reasons for your choices. The third part of the project is to load some data into the database, and apply certain update transactions and retrieval queries. You will create your own data. Include at least 20 customers, at least 10 magazines of different frequencies, and at least 7 newspapers (some should be weekly). For each publication, enter at least two subscription rates, and at least two current subscriptions.
Database Structure
-- phpMyAdmin SQL Dump -- version 2.6.4-pl2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: May 22, 2006 at 07:45 PM -- Server version: 4.1.18 -- PHP Version: 4.3.11 -- -- Database: `amerkhal_db1` -- -- -------------------------------------------------------- -- -- Table structure for table `CUSTOMER` -- CREATE TABLE IF NOT EXISTS `CUSTOMER` ( `Id_No` int(11) NOT NULL default '0', `Name` varchar(30) NOT NULL default '', `Address` varchar(80) NOT NULL default '', PRIMARY KEY (`Id_No`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `MAGAZINE` -- CREATE TABLE IF NOT EXISTS `MAGAZINE` ( `Name` varchar(50) NOT NULL default '', `Frequency` varchar(12) NOT NULL default '', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `NEWSPAPER` -- CREATE TABLE IF NOT EXISTS `NEWSPAPER` ( `Name` varchar(50) NOT NULL default '', `Frequency` varchar(12) NOT NULL default '', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `OTHER` -- CREATE TABLE IF NOT EXISTS `OTHER` ( `Name` varchar(50) NOT NULL default '', `Frequency` varchar(12) default NULL, PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `PUBLICATION` -- CREATE TABLE IF NOT EXISTS `PUBLICATION` ( `Name` varchar(50) NOT NULL default '', `Type` char(1) NOT NULL default '', PRIMARY KEY (`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `RATE_MAGAZINE` -- CREATE TABLE IF NOT EXISTS `RATE_MAGAZINE` ( `Name` varchar(50) NOT NULL default '', `NoOfIssues` int(11) NOT NULL default '0', `Price` double NOT NULL default '0', PRIMARY KEY (`Name`,`NoOfIssues`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `RATE_NEWSPAPER_DAILY` -- CREATE TABLE IF NOT EXISTS `RATE_NEWSPAPER_DAILY` ( `Name` varchar(50) NOT NULL default '', `Type` varchar(12) NOT NULL default '', `No_of_months` int(11) NOT NULL default '0', `Price` double NOT NULL default '0', PRIMARY KEY (`Name`,`Type`,`No_of_months`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `RATE_NEWSPAPER_WEEKLY` -- CREATE TABLE IF NOT EXISTS `RATE_NEWSPAPER_WEEKLY` ( `Name` varchar(50) NOT NULL default '', `NoOfIssues` int(11) NOT NULL default '0', `Price` double NOT NULL default '0', PRIMARY KEY (`Name`,`NoOfIssues`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `SUBSCRIBE_MAGAZINE` -- CREATE TABLE IF NOT EXISTS `SUBSCRIBE_MAGAZINE` ( `Cust_Id` int(11) NOT NULL default '0', `Name` varchar(50) NOT NULL default '', `NoOfIssues` int(11) NOT NULL default '0', `StartDate` date NOT NULL default '0000-00-00', `EndDate` date NOT NULL default '0000-00-00', PRIMARY KEY (`Cust_Id`,`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `SUBSCRIBE_NEWSPAPER_DAILY` -- CREATE TABLE IF NOT EXISTS `SUBSCRIBE_NEWSPAPER_DAILY` ( `Cust_Id` int(11) NOT NULL default '0', `Name` varchar(50) NOT NULL default '', `Type` varchar(12) NOT NULL default '', `No_of_months` int(11) NOT NULL default '0', `StartDate` date NOT NULL default '0000-00-00', `EndDate` date NOT NULL default '0000-00-00', PRIMARY KEY (`Cust_Id`,`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `SUBSCRIBE_NEWSPAPER_WEEKLY` -- CREATE TABLE IF NOT EXISTS `SUBSCRIBE_NEWSPAPER_WEEKLY` ( `Cust_Id` int(11) NOT NULL default '0', `Name` varchar(50) NOT NULL default '', `NoOfIssues` int(11) NOT NULL default '0', `StartDate` date NOT NULL default '0000-00-00', `EndDate` date NOT NULL default '0000-00-00', PRIMARY KEY (`Cust_Id`,`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `SUBSCRIBE_OTHER` -- CREATE TABLE IF NOT EXISTS `SUBSCRIBE_OTHER` ( `Cust_Id` int(11) NOT NULL default '0', `Name` varchar(50) NOT NULL default '', `StartDate` date NOT NULL default '0000-00-00', PRIMARY KEY (`Cust_Id`,`Name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
