-
Object Oriented Code Generator for PHP-MySQL Web Applications
Date: 02/17/06 (Java Web) Keywords: php, templates, mysql, database, sql, web
MyDBO is an object-oriented code generator for PHP-MySQL Web application developers. It is designed to automate CRUD operations when creating Web applications. It creates code for accessing database tables without bogging down the developer with connections or SQL queries. It uses templates to generate code. You can create your own templates. I think it fills a [...]
-
The eternal database question.
Date: 02/17/06 (SQL Server) Keywords: sql
In your opinion, should a table's clustered index be the primary key? Always? Never? Under certain conditions? When? Why or why not?
If it's not the primary key, what are your rules for determining which index should be clustered?
EDIT: I'm actually going to use this to ask a real queston, too. In SQL Server 2000.
I have a table with roughly 5,500 lines, which means I don't want to do this manually. The data is concatenated from two different tables, so the ID field has a number of duplicates. What I'd like to do is wipe out the data in the ID column (which is arbitrary) and have the system bulk assign IDs when I set the column to identity. I've tried about twenty different ways to do this with no success. various rand() functions are moving far too fast for me to get different values (and nothing else in the table is unique, or close enough to it, for me to use as a more random seed than millisecond).
Is there any way to get SQL Server to bulk-assign identity column values, so I can get done with this and move on? Thanks.Source: http://community.livejournal.com/sqlserver/43610.html
-
Don't cry for me oh mySQL (the truth is Oracle can't have you)
Date: 02/17/06 (Open Source) Keywords: mysql, sql
Don't cry for me, oh mySQL. The truth is that Oracle can't have you. Through open source's wild days, its mad existance, it keeps its promise, so keep your distance."
-
MySQL video and presentation up
Date: 02/20/06 (MySQL Communtiy) Keywords: mysql, sql, google
The first hour of a 2-hour workshop on what to do with a MySQL instance you've inherited. Very detailed, goes into real examples using shell prompts.
Unfortunately, the settings were incorrect on the video recorder, so only the 1st hour was captured. Presented at the Boston MySQL Meetup Group on February 13th, 2006.
Special thanks to!['[info]']('http://stat.livejournal.com/img/userinfo.gif')
lensman for securing the MIT space.
google video
flash slides
pdf slides (this might be broken, please let me know)
worksheet
As always, stylistic criticism as well as content criticism is appreciated. -
2place.org - free web-hosting.
Date: 02/20/06 (Web Hosts) Keywords: css, html, sql, web, hosting
Just free web-hosting at http://www.2place.org.
Features:
- Hosting of your .com/.net/.org domain
- E-mail forwarding *@yourdomain.com to your e-mail
- FTP access
- 50 mb space
- No CGI, no SQL - just static site (HTML, CSS, Flash, images and etc.).
- Free Sub-domain in 2place.orgSource: http://community.livejournal.com/webhosts/33925.html
-
PEAR
Date: 02/21/06 (PHP Development) Keywords: php, mysql, sql, apache
I am trying to install PEAR on a Windows XP machine. I have Apache, PHP and MySql running. I followed the installation instructions very carefully. However all my paths were screwed after the installation was complete. I reinstalled PEAR and now everything looks correct. However (there is always a however) the PEAR_Error package seems to be missing. When I make a new DB object I get a message saying Pear_Error cannot be found.
Using the list function for the CLI I can see the packages that are installed. However when I try to use the CLI to verify the packages it says they cant be found.
Has anyone else loaded PEAR in the same environment as me? Are there problems with the CLI and Windows?
Thanks. -
PHP/MySQL Developer job posting
Date: 02/22/06 (PHP Community) Keywords: php, mysql, rss, css, html, database, sql, java, web, linux, apache
I hope job postings are welcome here.
We are looking to hire a PHP/MySQL Applications Developer at our offices in Charlestown, MA (part of Boston). This is a full-time, on-site position. If you are interested in applying, please send your resume, links to successful projects, and a code sample (100 or more lines showing your coding abilities and standards) to mo at smarterliving dot com.
PHP/MySQL Applications Developer
We are looking for a smart, creative, motivated self-starter who wants to join a strong team of developers in a rapidly growing company, building some of the leading consumer websites in the travel industry.
The PHP/MySQL Applications Developer will be a part of our Applications Development team developing scalable, database-driven web applications for our production websites (www.smartertravel.com and www.bookingbuddy.com) in a LAMP (Linux, Apache, MySQL, PHP) environment.
We offer competitive salary & benefits and a great work environment in T-accessible office space with parking on the Charlestown/Somerville line.
Job Qualifications:
The ideal candidate has 2-5 years experience developing with PHP 4-5 in a professional team environment using Object-Oriented Design principles. A strong understanding of XHTML, CSS, MySQL and JavaScript is also required. Experience with AJAX, JSON, RSS or other emerging technologies is a plus.
The successful candidate will communicate well, be committed to delivering quality work on an aggressive schedule, and must be able to function independently as well as part of a team. Must document code faithfully and take good coding practices seriously.
To apply:
To apply please provide a resume and cover letter including links to successful projects and summarize your participation. A code sample sufficiently long to show coding style and design methodology is also required. In addition, please indicate the best time to contact you, between the hours of 9:00 a.m. and 6:30 p.m., Monday through Friday.
IMPORTANT: Any submission not including code samples and links to working online applications that the candidate has written will be ignored.
The location for this position is in Charlestown, MA. It is a full-time, on-site position.
X-posted. -
HWCompany v11.1.7
Date: 02/22/06 (PHP Community) Keywords: templates, mysql, database, sql
New additions to the languages, support and templates... and changes in the database, sessions and parser...
Changes:
Database:- updated version
Languages:- added small-script codes for more language strings in site
- updated the german descriptions
Parser:- added small-script parser for the language strings
Sessions:- added cookies to remember skin, language and login
- added failsafe sessions
Templates:- removed strings now in the small-script
- overviewed and cleared of errors
I hope this version will be even better as progress continues to move on...
I really love working on this site... it teaches me much...
In the future, I' m gonna move to another server, with MySQL... but I still have to see what that contains...
If they use a contract, things can get out of hand, so I have to watch out...
I already have my wishes written down for them... so we' ll see...
If you want to test the site, I' ve got the links here:
There you go, enjoy your visit!
As far as the script for the winamp controller, I have no idea why it doesn' t work on the computers at "work"...
It seems like they have a secure network, but then the IT guy tells me it' s not secured... it makes me go blah! lol
I' ll try find a way around, because I' m just me, and I have to get through this...
Kash
PS:
You may think I' m crazy, right?
WELL DAMN RIGHT YOU ARE! LOL -
PHP/MySQL Developer job position
Date: 02/22/06 (PHP Development) Keywords: php, mysql, rss, css, html, database, sql, java, web, linux, apache
I hope job postings are welcome here.
We are looking to hire a PHP/MySQL Applications Developer at our offices in Charlestown, MA (part of Boston). This is a full-time, on-site position. If you are interested in applying, please send your resume, links to successful projects, and a code sample (100 or more lines showing your coding abilities and standards) to mo at smarterliving dot com.
PHP/MySQL Applications Developer
We are looking for a smart, creative, motivated self-starter who wants to join a strong team of developers in a rapidly growing company, building some of the leading consumer websites in the travel industry.
The PHP/MySQL Applications Developer will be a part of our Applications Development team developing scalable, database-driven web applications for our production websites (www.smartertravel.com and www.bookingbuddy.com) in a LAMP (Linux, Apache, MySQL, PHP) environment.
We offer competitive salary & benefits and a great work environment in T-accessible office space with parking on the Charlestown/Somerville line.
Job Qualifications:
The ideal candidate has 2-5 years experience developing with PHP 4-5 in a professional team environment using Object-Oriented Design principles. A strong understanding of XHTML, CSS, MySQL and JavaScript is also required. Experience with AJAX, JSON, RSS or other emerging technologies is a plus.
The successful candidate will communicate well, be committed to delivering quality work on an aggressive schedule, and must be able to function independently as well as part of a team. Must document code faithfully and take good coding practices seriously.
To apply:
To apply please provide a resume and cover letter including links to successful projects and summarize your participation. A code sample sufficiently long to show coding style and design methodology is also required. In addition, please indicate the best time to contact you, between the hours of 9:00 a.m. and 6:30 p.m., Monday through Friday.
IMPORTANT: Any submission not including code samples and links to working online applications that the candidate has written will be ignored.
The location for this position is in Charlestown, MA. It is a full-time, on-site position.
X-posted. -
Stored Procedures
Date: 02/23/06 (SQL Server) Keywords: sql
Hi again,
I posted before about how I was coming from Oracle to SQL Server so apologies in advance if this is a stupid question.
I've been using Stored Procedures to return select statements.
What I'm wondering is if they can return data from multiple select statements or variables?
For example
A customer table, and a customer phones table which can have 4 records for the customer.
I want to return customer, phone1, phone2, phone3, phone4
If I just joined the tables I'd get
customer, phone1
customer, phone2 etc.
In Oracle I'd write a function which would get the customer, have a second query to go through the phones table and assign each one to a variable and then return the customer no and the variables.
Can this be done in SQL Server?Source: http://community.livejournal.com/sqlserver/43823.html
-
TIME ZONE
Date: 02/25/06 (MySQL Communtiy) Keywords: mysql, sql
Server timezone is GMT-6.
What is the fast way to get GMT date using current_date() function or any other in request?
Server version is MySql 4.0 so ADDTIME(), CONVERT_TZ(), UTC_DATE(), DATEDIFF() and other cool MySql 4.1 functions does'nt work :( -
MySQL/PHP connection error
Date: 02/26/06 (PHP Community) Keywords: cms, php, mysql, database, sql, hosting
I programmed a CMS for one of my client and put it up on my own site in a test directory while it was in development. It was working just fine. They finally got their own hosting set up with Network Solutions so I uploaded all my files, set up the database and changed the username, password and db name in my scripts. Now I'm getting the following error: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
I've double checked that I have the right username and the right password.
I'm using the following code to connect: $link = mysql_connect('localhost', $user, $pass) or die("Could not connect : " . mysql_error());
When I log into phpMyAdmin I see the following: MySQL 4.1.16-log running on localhost as eclipse@localhost so I'm assuming that 'localhost' is right. What else could be wrong? I've tried emailing tech support at Network Solutions but I doubt anyone there will get back to me. It's a pretty basic error but i can't figure out what else I would've needed to change when switching the site from my server to theirs. -
Connection error
Date: 02/26/06 (MySQL Communtiy) Keywords: cms, php, mysql, database, sql, hosting
I programmed a CMS for one of my client and put it up on my own site in a test directory while it was in development. It was working just fine. They finally got their own hosting set up with Network Solutions so I uploaded all my files, set up the database and changed the username, password and db name in my scripts. Now I'm getting the following error: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
I've double checked that I have the right username and the right password.
I'm using the following code to connect: $link = mysql_connect('localhost', $user, $pass) or die("Could not connect : " . mysql_error());
When I log into phpMyAdmin I see the following: MySQL 4.1.16-log running on localhost as eclipse@localhost so I'm assuming that 'localhost' is right. What else could be wrong? I've tried emailing tech support at Network Solutions but I doubt anyone there will get back to me. It's a pretty basic error but i can't figure out what else I would've needed to change when switching the site from my server to theirs. -
mysqld
Date: 02/26/06 (IT Professionals) Keywords: mysql, sql, linux
How can I limit the number of mysqld processes (threads)? Under Linux.
I have the following options in my.cnf:
set-variable = max_connections=3
set-variable = thread_concurrency=3
But I still have 11 child threads of mysqld-max. How can I limit this number?Source: http://community.livejournal.com/itprofessionals/34451.html
-
Uploading/Emailing Files
Date: 03/01/06 (PHP Community) Keywords: mysql, database, sql
Hey all,
I'm trying to build a form that submits its contents to a database, with the exception of two file fields that are supposed to be emailed to someone. I can get the form working fine as usual, but for whatever reason, the file emailing section is not working. It acts like the files don't exist. Any suggestions? Also, I can't upload these files to my server because my admins are nazis - is there any way I can store a word doc or pdf in a mySQL db?
Thanks! -
Mambo / Joomla Password - How to Reset / Recover
Date: 03/02/06 (Java Web) Keywords: mysql, database, sql
Mambo / Joomla passwords cannot be recovered as they are set using a one-way hash function (MD5). However they can be reset to new values. The user information is stored in the MySQL database (obviously) in the mos_users (for MAMBO) or jos_users (for JOOMLA). The prefix (mos or jos) can be configured and may be named [...]
Source: http://blog.taragana.com/index.php/archive/mambo-joomla-password-how-to-reset-recover/
-
[mysql 4.1] viewing all InnoDB locks
Date: 03/04/06 (MySQL Communtiy) Keywords: mysql, sql
Does anybody know method to view all locks, hold by concrete thread?
"show engine innodb status" shows only "waiting for this lock"...
I need info such as "Last detected deadlock"(part "holds these locks:"), but for each current thread.
Can't find that neither standard man or "mysql internals"... -
HELP
Date: 03/05/06 (MySQL Communtiy) Keywords: database, sql
So i'm in a sql class and i have no idea what i'm doing. *yikes* I need to...
I'd like to have at least five transactions, with at least two
different movies rented each, and one where they bring back a movie
(RL_INOUT).
You have to decide:
1. which employees are going to make those transactions
2. Which customers are going to rent the movies/games
3. Which movies those customers are going to rent.
Yeah... I'm LOST!
Our current table structure
Here's what I've got for our database structure so far. Discuss here!
Here are the tables (called out by SELECT * FROM TABLE_NAME and DESC TABLE_NAME)
ACCT:
Code:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| ACCT_ADDRESS | char(50) | YES | | NULL | |
| CITY | char(50) | YES | | NULL | |
| ACCT_STATE | char(2) | YES | | NULL | |
| ACCT_ZIP | decimal(5,0) | YES | | NULL | |
| ACCT_DEBT | decimal(6,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
+------------+------------------+-----------------+------------+----------+----- ------+
| ACCT_ID | ACCT_ADDRESS | CITY | ACCT_STATE | ACCT_ZIP | ACCT_DEBT |
+------------+------------------+-----------------+------------+----------+----- ------+
| 8132546892 | 85916 FOXHILL | HAMTRAMMICK | MI | 48325 | 15.50 |
| 8217394659 | 74985 BELINDA | CLINTON TWP | MI | 48310 | 0.00 |
| 8221354897 | 28486 STAPLER | SPINDLE | MI | 48310 | 15.00 |
| 8231645797 | 78456 GARFIELD | LAWSON | MI | 47158 | 25.00 |
| 8312584695 | 3125 CHARITY | LIVONIA | MI | 48310 | 0.00 |
| 8574962138 | 3131 ALBANY | LANSING | MI | 48317 | 2.50 |
| 8579462312 | 28547 RUTHER | DEARBORNE | MI | 48315 | 5.00 |
| 8779654251 | 25816 GEORGIA | KNOB CREEK | MI | 47861 | 0.00 |
| 8796452135 | 25781 SOMESTREET | FAIRFIELD | MI | 48631 | 7.50 |
| 8896354187 | 85749 CHECKERS | TIMBUCKTWO | MI | 48156 | 0.00 |
| 8899775521 | 45612 LAYFETTE | MOUND | MI | 48356 | 5.00 |
| 8974653127 | 31582 RYAN | MADISON HEIGHTS | MI | 48197 | 0.00 |
| 8976542315 | 95846 MARK ORR | ROYAL OAK | MI | 48312 | 5.00 |
| 8995542368 | 21058 SALEM | TROY | MI | 48213 | 7.50 |
| 9478652351 | 20487 PINEBROOK | ALLEN PARK | MI | 48321 | 10.50 |
+------------+------------------+-----------------+------------+----------+----- ------+
15 rows in set (0.00 sec)
CUSTOMER:
Code:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| CUST_NUM | decimal(2,0) | | PRI | 0 | |
| CUST_FIRST_N | char(15) | YES | | NULL | |
| CUST_LAST_N | char(15) | YES | | NULL | |
| CUST_BDAY | date | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
+------------+----------+--------------+-------------+------------+
| ACCT_ID | CUST_NUM | CUST_FIRST_N | CUST_LAST_N | CUST_BDAY |
+------------+----------+--------------+-------------+------------+
| 8132546892 | 1 | MELISSA | LEMMINS | 1950-02-18 |
| 8217394659 | 1 | DESRA | JACKSON | 1981-01-27 |
| 8221354897 | 1 | JUSTIN | MARLBORO | 1987-06-25 |
| 8231645797 | 1 | SARAH | HOPPINS | 1980-08-26 |
| 8312584695 | 1 | LARRY | BROWN | 1968-03-22 |
| 8574962138 | 1 | SUE | EVANS | 1983-03-17 |
| 8579462312 | 1 | JAMES | RAUSCH | 1956-01-16 |
| 8779654251 | 1 | DAN | COOLMEN | 1985-01-08 |
| 8796452135 | 1 | BOB | RAMSEY | 1975-02-14 |
| 8896354187 | 1 | ANDREA | TYLER | 1985-07-13 |
| 8899775521 | 1 | CHRIS | JOANS | 1984-11-02 |
| 8974653127 | 1 | TIFFANY | SCHULTZ | 1985-07-25 |
| 8976542315 | 1 | ROBBY | SMITH | 1982-04-23 |
| 8995542368 | 1 | JESSE | SAAD | 1973-04-20 |
| 8995542368 | 2 | MARY | SAAD | 1976-03-24 |
| 9478652351 | 1 | JOANN | TIMBERSHMIT | 1973-05-18 |
+------------+----------+--------------+-------------+------------+
16 rows in set (0.01 sec)
INVENTORY:
Code:
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| INV_ID | char(12) | | PRI | | |
| INV_MEDIA | char(3) | YES | | NULL | |
| INV_TITLE | char(30) | YES | | NULL | |
| RT_TYPE | char(2) | YES | MUL | NULL | |
| RATE_CODE | char(4) | YES | MUL | NULL | |
| INV_COPIES | decimal(4,0) | YES | | NULL | |
| INV_ONHAND | decimal(4,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
| INV_ID | INV_MEDIA | INV_TITLE | RT_TYPE | RATE_CODE | INV_COPIES | INV_ONHAND |
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
| 012236189411 | DVD | WAITING... | NV | UR | 7 | 4 |
| 012569500921 | DVD | GONE WITH THE WIND | OV | G | 2 | 2 |
| 012569593237 | DVD | MILLION DOLLAR BABY | NV | UR | 2 | 0 |
| 013388200177 | GCN | RESIDENT EVIL 4 | NG | M | 4 | 1 |
| 014633147025 | PS2 | NEED FOR SPEED UNDERGROUND | OG | E | 3 | 2 |
| 024543044789 | DVD | FIGHT CLUB | OV | R | 5 | 2 |
| 025192211928 | DVD | CINDERELLA MAN | NV | PG13 | 8 | 3 |
| 043396067660 | DVD | BLACK HAWK DOWN | OV | R | 3 | 3 |
| 045496870461 | N64 | SUPER SMASH BROS | OG | E | 4 | 3 |
| 047875810211 | XBO | CALL OF DUTY 2: BIG RED ONE | NG | T | 4 | 0 |
| 085391773726 | DVD | THE MATRIX | OV | R | 3 | 2 |
| 097360184044 | DVD | CLUE | OV | G | 1 | 1 |
| 097363365143 | DVD | ALONG CAME A SPIDER | NV | R | 2 | 1 |
| 710425274107 | PS2 | GRAND THEFT AUTO: SAN ANDREAS | NG | AO | 3 | 2 |
| 786936161571 | DVD | PULP FICTION | OV | R | 3 | 3 |
| 788687500012 | PS2 | HITMAN 2: SILENT ASSASSIN | NG | M | 2 | 0 |
| 794043749728 | DVD | THE NOTEBOOK | NV | PG13 | 4 | 1 |
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
17 rows in set (0.00 sec)
RATING:
Code:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| RATE_CODE | char(4) | | PRI | | |
| RATE_DESC | char(30) | YES | | NULL | |
| RATE_MINAGE | decimal(2,0) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
+-----------+--------------------------+-------------+
| RATE_CODE | RATE_DESC | RATE_MINAGE |
+-----------+--------------------------+-------------+
| AO | Adults Only | 18 |
| E | Everyone | 1 |
| E10 | Everone 10+ | 10 |
| EC | Early Childhood | 1 |
| G | General Audiences | 1 |
| M | Mature | 17 |
| NC17 | Restricted 17 years | 17 |
| PG | Parental Guidence | 6 |
| PG13 | Strong Parental Guidence | 13 |
| R | Restricted | 17 |
| T | Teen | 13 |
| UR | Unrated | 21 |
| X | Mature Content | 21 |
+-----------+--------------------------+-------------+
13 rows in set (0.00 sec)
RENT_TYPE:
Code:
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| RT_TYPE | char(2) | | PRI | | |
| RT_DESC | char(30) | YES | | NULL | |
| RT_LENGTH | decimal(2,0) | YES | | NULL | |
| RT_FEE | decimal(4,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
+---------+-----------+-----------+--------+
| RT_TYPE | RT_DESC | RT_LENGTH | RT_FEE |
+---------+-----------+-----------+--------+
| NG | New Game | 5 | 5.00 |
| NV | New Video | 2 | 6.00 |
| OG | Old Game | 2 | 4.00 |
| OV | Old Video | 7 | 5.00 |
| S1 | Special 1 | 2 | 3.00 |
| S2 | Special 2 | 2 | 1.50 |
+---------+-----------+-----------+--------+
6 rows in set (0.00 sec)
EMPLOYEE:
Code:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| EMP_ID | char(4) | | PRI | | |
| EMP_FIRST_N | char(15) | YES | | NULL | |
| EMP_LAST_N | char(15) | YES | | NULL | |
| EMP_ADDRESS | char(50) | YES | | NULL | |
| EMP_CITY | char(50) | YES | | NULL | |
| EMP_STATE | char(2) | YES | | NULL | |
| EMP_ZIP | decimal(5,0) | YES | | NULL | |
| EMP_WAGES | decimal(4,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
| EMP_ID | EMP_FIRST_N | EMP_LAST_N | EMP_ADDRESS | EMP_CITY | EMP_STATE | EMP_ZIP | EMP_WAGES |
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
| 0001 | TIM | WOJOWSKI | 38592 WEST | MACOMB | MI | 48035 | 15.00 |
| 0002 | LARRY | HILLIARD | 29438 NORTH AVE | MT. CLEMENS | MI | 38940 | 9.70 |
| 0003 | WILLIAM | JOHNSON | 29438 GRATIOT | MT. CLEMENS | MI | 38940 | 9.50 |
| 0004 | ANTHONY | WALKER | 24882 FAIRCHILD | MACOMB | MI | 48042 | 9.50 |
| 0005 | GEORGE | TOMLIN | 28784 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
| 0006 | BOB | BATES | 28743 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
6 rows in set (0.00 sec)
These tables do not have data yet!!
Code:
TRANSACTION:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| ACCT_ID | char(10) | YES | MUL | NULL | |
| CUST_NUM | decimal(2,0) | YES | | NULL | |
| TRANS_DATE | date | YES | | NULL | |
| EMP_ID | char(4) | YES | MUL | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
RENT_LINE:
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| INV_ID | char(10) | | PRI | | |
| RENT_QTY | decimal(2,0) | YES | | NULL | |
| RL_INOUT | char(3) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Following is all of the SQL code so far:
Code:
CREATE DATABASE BAKER_DVD;
USE BAKER_DVD;
CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) , -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address 1
EMP_CITY CHAR(50) , -- Address 2
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
PRIMARY KEY (EMP_ID) ) ENGINE=INNODB;
CREATE TABLE RATING
(RATE_CODE CHAR(04) , -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
PRIMARY KEY (RATE_CODE) ) ENGINE=INNODB;
CREATE TABLE ACCT
(ACCT_ID CHAR(10) , -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
CITY CHAR(50) , -- City of residence
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
PRIMARY KEY (ACCT_ID) ) ENGINE=INNODB;
CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) , -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
PRIMARY KEY (RT_TYPE) ) ENGINE=INNODB;
CREATE TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) , -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
PRIMARY KEY (ACCT_ID, CUST_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID) ) ENGINE=INNODB;
CREATE TABLE INVENTORY
(INV_ID CHAR(12) , -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating, (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
PRIMARY KEY (INV_ID),
FOREIGN KEY (RT_TYPE) REFERENCES RENT_TYPE(RT_TYPE),
FOREIGN KEY (RATE_CODE) REFERENCES RATING(RATE_CODE) ) ENGINE=INNODB;
CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10,0) , -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(02) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
PRIMARY KEY (TRANS_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID) ) ENGINE=INNODB;
CREATE TABLE RENT_LINE
(TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_ID CHAR(10) , -- Relavant video code
RENT_QTY DECIMAL(02,0) , -- Quantity rented, usually one.
RL_INOUT CHAR(03) , -- Item In or Out status
PRIMARY KEY (TRANS_NUM, RENT_NUM),
FOREIGN KEY (TRANS_NUM) REFERENCES TRANSACTION(TRANS_NUM) ) ENGINE=INNODB;
Here's the data already entered:
Rent Type and Rating:
Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );
INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );
Inventory:
Code:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');
INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');
INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');
INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');
INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');
INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');
INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');
INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG13', '8', '3');
INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');
INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');
INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');
INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');
INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');
INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');
INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');
Customers and Accounts:
Code:
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');
INSERT INTO CUSTOMER
VALUES
('8574962138', 1, 'SUE', 'EVANS', '19830317');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'LARRY', 'SCHULTZ', '19810322');
INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');
INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');
INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');
INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');
INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');
INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');
INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');
INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');
INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');
INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');
INSERT INTO CUSTOMER
VALUES
('8995542368', 2, 'MARY', 'SAAD', '19760324');
INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');
INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');
INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');
INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');
INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI', '48197', 0);
INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315', '5.00');
INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');
INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310', 0);
INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');
INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');
INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');
INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310', 0);
INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');
INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861', 0);
INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');
INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');
INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156', 0);
Employee:
Code:
INSERT INTO EMPLOYEE
VALUES ('0001', 'TIM', 'WOJOWSKI', '38592 WEST', 'MACOMB', 'MI', '48035', '15.00');
INSERT INTO EMPLOYEE
VALUES ('0002', 'LARRY', 'HILLIARD', '29438 NORTH AVE', 'MT. CLEMENS', 'MI', '38940', '9.70');
INSERT INTO EMPLOYEE
VALUES ('0003', 'WILLIAM', 'JOHNSON', '29438 GRATIOT', 'MT. CLEMENS', 'MI', '38940', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0004', 'ANTHONY', 'WALKER', '24882 FAIRCHILD', 'MACOMB', 'MI', '48042', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0005', 'GEORGE', 'TOMLIN', '28784 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0006', 'BOB', 'BATES', '28743 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');
Discuss here. (the text after the -- is a comment.)
Last edited by enigma.0Z on Fri Mar 03, 2006 4:14 am; edited 7 times in total
Tue Jan 31, 2006 10:44 am
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
ER Diagram is complete... I'll get started on the Inventory Table Today... if anybody needs to know any of the info, like the vid num code, let me know... ttyl homies...
Mon Feb 20, 2006 3:33 am
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
I slightly restructured a bit, but mostly with what the primary keys are. The modified tables are as follows:
CREATE DATABASE BAKER_DVD;
CREATE TABLE INVENTORY
(INV_VID_CODE CHAR(12) PRIMARY KEY, -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
);
CREATE TABLE RATING
(RATE_CODE CHAR(04) PRIMARY KEY, -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
);
CREATE TABLE ACCT
(ACCT_ID CHAR(10) PRIMARY KEY, -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
);
CREAT TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) PRIMARY KEY, -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
);
CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) PRIMARY KEY, -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
);
CREATE TABLE RENT_LINE
(RENT_ID DECIMAL(10,0) PRIMARY KEY, -- ID Code for movie rental
TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_VID_CODE CHAR(10) , -- Relavant video code
RL_INOUT CHAR(03) , -- Item In or Out status
);
CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10) PRIMARY KEY, -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(04) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
);
CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) PRIMARY KEY, -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
);
let me know if any of this will cause any problems... hopefully not, because during my boredom, I had completed the data for 3 of the tables: INVENTORY, RATING, and RENT_TYPE. Hopefully nobody had their hearts set on those because they're done now. If you want the info for those tables, here are the codes:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');
INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');
INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');
INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');
INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');
INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');
INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');
INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG-13', '8', '3');
INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');
INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');
INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');
INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');
INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');
INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');
INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');
**MOD EDIT: Rent type and rating have already been made **
INSERT INTO RATING
VALUES
('G', 'GENERAL AUDIENCES', '0');
INSERT INTO RATING
VALUES
('PG', 'PARENTAL GUIDANCE SUGGESTED', '0');
INSERT INTO RATING
VALUES
('PG-13', 'PARENT STRONGLY CAUTIONED', '13');
INSERT INTO RATING
VALUES
('R', 'RESTRICTED', '17');
INSERT INTO RATING
VALUES
('NC-17', 'NO ONE 17 AND UNDER ADMITTED', '18');
INSERT INTO RATING
VALUES
('EC', 'EARLY CHILDHOOD', '3');
INSERT INTO RATING
VALUES
('E', 'EVERYONE', '6');
INSERT INTO RATING
VALUES
('E10+', 'EVERYONE 10 AND OLDER', '10');
INSERT INTO RATING
VALUES
('T', 'TEEN', '13');
INSERT INTO RATING
VALUES
('M', 'MATURE', '17');
INSERT INTO RATING
VALUES
('AO', 'ADULTS ONLY', '18');
INSERT INTO RENT_TYPE
VALUES
('NR', 'NEW RELEASE', '2', '3.50');
INSERT INTO RENT_TYPE
VALUES
('AC', 'ACTION, ADVENTURE', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('CL', 'CLASSIC', '7', '2.00');
INSERT INTO RENT_TYPE
VALUES
('SF', 'SCI-FI', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('CO', 'COMEDY', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('DR', 'DRAMA', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('SH', 'SHOOTER', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('SI', 'SIMULATOR', '5', '2.50');
I post the correct values tonight during class.
** End edit **
For the rest of you all, good luck on whatever the hell your part is. Again to enigma, the ER diagram is done. I'll have it for you tomorrow during class at the latest. If i can find a way to e-mail it to you in my spare time, i will do so. Peace yall..
Mon Feb 20, 2006 6:17 am
ncamb02
Newbie
Joined: 31 Jan 2006
Posts: 1
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');
INSERT INTO CUSTOMER
VALES
('8574962138', 1, 'SUE', 'EVANS', '19830317');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');
INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');
INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');
INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');
INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');
INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');
INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');
INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');
INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');
INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');
INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');
INSERT INTO CUSTOMER
VALUES
('8995542369', 2, 'MARY', 'SAAD', '19760324');
INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');
INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');
INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');
INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');
INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI '48197');
INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315' '5.00');
INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');
INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310');
INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');
INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');
INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');
INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310');
INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');
INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861');
INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');
INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');
INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156');
________________________________________________________________________________ _____________________
I HAVE A COUPLE QUESTIONS... HOPE THE DATA IS RIGHT BUT IF NOT ITS A COUPLE QUICK FIXES. FIRST, THE TABLE STRUCTURE HAS DECIMAL(02) WITHIN THE CUSTOMER TABLE FOR CUST_NUM... I DIDNT KNOW IF YOU WANTED THAT AS A DECIMAL OR JUST A TWO DIGIT NUMBER? SECOND UNDER THE ACCT TABLE NOT EVERY CUSTOMER SHOULD HAVE A SECONDARY ADDRESS SHOULD THEY? AS I WAS TYPING IT IN... DONT U THINK THEY SHOULD HAVE A TELEPHONE #? IF ANYONE WANTS TO ME TO FIX NETHIN ILL BE AVAILABLE ALL DAY BE4 CLASS SO GIVE ME A CALL @ 586-855-9290 ... ALRIGHT IF NOT SEE U GUYS IN CLASS
Mon Feb 20, 2006 4:24 pm
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
I didn't think we would need a second address, that's why I didn't put it in my revised table structure... By the way, I'm still waiting for an opinion on whether that was the way yall wanted to go... let me know... If you want to meet up before class, Nick, I'll be in the learning center from now (12:00) till class... let me know... later...
Tue Feb 21, 2006 2:01 am
Invinciblemoron
Newbie
Joined: 26 Jan 2006
Posts: 2
Technically we need the second address for the city address. I coded with room for the City address
Code:
insert into employee
values ('0001', 'Tim', 'Wojowski', '38592 West', 'Macomb", 'MI', '48035', '15.00');
insert into employee
values ('0002', 'Larry', 'Hilliard', '29438 North Ave', 'Mt. Clemens' 'MI', '38940', '9.70');
insert into employee
values ('0003', 'William', 'Johnson', '29438 Gratiot', 'Mt. Clemens 'MI', '38940', '9.50');
insert into employee
values ('0004', 'Anthony', 'Walker', '24882 Fairchild', 'Macomb', 'MI', '48042', '9.50');
insert into employee
values ('0005', 'George', 'Tomlin', '28784 Cotton', 'Chesterfield', 'MI', '48315', '9.50');
insert into employee
values ('0006, 'Bob', 'Bates', 28743 Cotton', 'Chesterfield', 'MI', '48315', '9.50');
_________________
You have Employees
5 employees is gewd.
place in code bracket and use insert statements
Tue Feb 21, 2006 6:04 am
enigma.0Z
Site Admin
Joined: 25 Jan 2006
Posts: 8
I've already did rating and rent_type. That's what I handed out to everyone. We need the address_2 because addresses have two lines.
As far as the inventory entries go, we just need to change them to match what I handed out last class (I'll edit them myself).
Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years old" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );
INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );e
As far as dec(02) in customer, that's just the number of the customer under that account number:
for instance Account 12345 Customer 1 is a dad, customer 2 is a mom, and 3, 4, and 5 are their kids -
I have no idea what i'm doing. HELP!
Date: 03/05/06 (SQL Server) Keywords: database, sql
So i'm in a sql class and i have no idea what i'm doing. *yikes* I need to...
I'd like to have at least five transactions, with at least two
different movies rented each, and one where they bring back a movie
(RL_INOUT).
You have to decide:
1. which employees are going to make those transactions
2. Which customers are going to rent the movies/games
3. Which movies those customers are going to rent.
Yeah... I'm LOST!
Our current table structure
Here's what I've got for our database structure so far. Discuss here!
Here are the tables (called out by SELECT * FROM TABLE_NAME and DESC TABLE_NAME)
ACCT:
Code:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| ACCT_ADDRESS | char(50) | YES | | NULL | |
| CITY | char(50) | YES | | NULL | |
| ACCT_STATE | char(2) | YES | | NULL | |
| ACCT_ZIP | decimal(5,0) | YES | | NULL | |
| ACCT_DEBT | decimal(6,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
+------------+------------------+-----------------+------------+----------+----- ------+
| ACCT_ID | ACCT_ADDRESS | CITY | ACCT_STATE | ACCT_ZIP | ACCT_DEBT |
+------------+------------------+-----------------+------------+----------+----- ------+
| 8132546892 | 85916 FOXHILL | HAMTRAMMICK | MI | 48325 | 15.50 |
| 8217394659 | 74985 BELINDA | CLINTON TWP | MI | 48310 | 0.00 |
| 8221354897 | 28486 STAPLER | SPINDLE | MI | 48310 | 15.00 |
| 8231645797 | 78456 GARFIELD | LAWSON | MI | 47158 | 25.00 |
| 8312584695 | 3125 CHARITY | LIVONIA | MI | 48310 | 0.00 |
| 8574962138 | 3131 ALBANY | LANSING | MI | 48317 | 2.50 |
| 8579462312 | 28547 RUTHER | DEARBORNE | MI | 48315 | 5.00 |
| 8779654251 | 25816 GEORGIA | KNOB CREEK | MI | 47861 | 0.00 |
| 8796452135 | 25781 SOMESTREET | FAIRFIELD | MI | 48631 | 7.50 |
| 8896354187 | 85749 CHECKERS | TIMBUCKTWO | MI | 48156 | 0.00 |
| 8899775521 | 45612 LAYFETTE | MOUND | MI | 48356 | 5.00 |
| 8974653127 | 31582 RYAN | MADISON HEIGHTS | MI | 48197 | 0.00 |
| 8976542315 | 95846 MARK ORR | ROYAL OAK | MI | 48312 | 5.00 |
| 8995542368 | 21058 SALEM | TROY | MI | 48213 | 7.50 |
| 9478652351 | 20487 PINEBROOK | ALLEN PARK | MI | 48321 | 10.50 |
+------------+------------------+-----------------+------------+----------+----- ------+
15 rows in set (0.00 sec)
CUSTOMER:
Code:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| CUST_NUM | decimal(2,0) | | PRI | 0 | |
| CUST_FIRST_N | char(15) | YES | | NULL | |
| CUST_LAST_N | char(15) | YES | | NULL | |
| CUST_BDAY | date | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
+------------+----------+--------------+-------------+------------+
| ACCT_ID | CUST_NUM | CUST_FIRST_N | CUST_LAST_N | CUST_BDAY |
+------------+----------+--------------+-------------+------------+
| 8132546892 | 1 | MELISSA | LEMMINS | 1950-02-18 |
| 8217394659 | 1 | DESRA | JACKSON | 1981-01-27 |
| 8221354897 | 1 | JUSTIN | MARLBORO | 1987-06-25 |
| 8231645797 | 1 | SARAH | HOPPINS | 1980-08-26 |
| 8312584695 | 1 | LARRY | BROWN | 1968-03-22 |
| 8574962138 | 1 | SUE | EVANS | 1983-03-17 |
| 8579462312 | 1 | JAMES | RAUSCH | 1956-01-16 |
| 8779654251 | 1 | DAN | COOLMEN | 1985-01-08 |
| 8796452135 | 1 | BOB | RAMSEY | 1975-02-14 |
| 8896354187 | 1 | ANDREA | TYLER | 1985-07-13 |
| 8899775521 | 1 | CHRIS | JOANS | 1984-11-02 |
| 8974653127 | 1 | TIFFANY | SCHULTZ | 1985-07-25 |
| 8976542315 | 1 | ROBBY | SMITH | 1982-04-23 |
| 8995542368 | 1 | JESSE | SAAD | 1973-04-20 |
| 8995542368 | 2 | MARY | SAAD | 1976-03-24 |
| 9478652351 | 1 | JOANN | TIMBERSHMIT | 1973-05-18 |
+------------+----------+--------------+-------------+------------+
16 rows in set (0.01 sec)
INVENTORY:
Code:
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| INV_ID | char(12) | | PRI | | |
| INV_MEDIA | char(3) | YES | | NULL | |
| INV_TITLE | char(30) | YES | | NULL | |
| RT_TYPE | char(2) | YES | MUL | NULL | |
| RATE_CODE | char(4) | YES | MUL | NULL | |
| INV_COPIES | decimal(4,0) | YES | | NULL | |
| INV_ONHAND | decimal(4,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
| INV_ID | INV_MEDIA | INV_TITLE | RT_TYPE | RATE_CODE | INV_COPIES | INV_ONHAND |
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
| 012236189411 | DVD | WAITING... | NV | UR | 7 | 4 |
| 012569500921 | DVD | GONE WITH THE WIND | OV | G | 2 | 2 |
| 012569593237 | DVD | MILLION DOLLAR BABY | NV | UR | 2 | 0 |
| 013388200177 | GCN | RESIDENT EVIL 4 | NG | M | 4 | 1 |
| 014633147025 | PS2 | NEED FOR SPEED UNDERGROUND | OG | E | 3 | 2 |
| 024543044789 | DVD | FIGHT CLUB | OV | R | 5 | 2 |
| 025192211928 | DVD | CINDERELLA MAN | NV | PG13 | 8 | 3 |
| 043396067660 | DVD | BLACK HAWK DOWN | OV | R | 3 | 3 |
| 045496870461 | N64 | SUPER SMASH BROS | OG | E | 4 | 3 |
| 047875810211 | XBO | CALL OF DUTY 2: BIG RED ONE | NG | T | 4 | 0 |
| 085391773726 | DVD | THE MATRIX | OV | R | 3 | 2 |
| 097360184044 | DVD | CLUE | OV | G | 1 | 1 |
| 097363365143 | DVD | ALONG CAME A SPIDER | NV | R | 2 | 1 |
| 710425274107 | PS2 | GRAND THEFT AUTO: SAN ANDREAS | NG | AO | 3 | 2 |
| 786936161571 | DVD | PULP FICTION | OV | R | 3 | 3 |
| 788687500012 | PS2 | HITMAN 2: SILENT ASSASSIN | NG | M | 2 | 0 |
| 794043749728 | DVD | THE NOTEBOOK | NV | PG13 | 4 | 1 |
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
17 rows in set (0.00 sec)
RATING:
Code:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| RATE_CODE | char(4) | | PRI | | |
| RATE_DESC | char(30) | YES | | NULL | |
| RATE_MINAGE | decimal(2,0) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
+-----------+--------------------------+-------------+
| RATE_CODE | RATE_DESC | RATE_MINAGE |
+-----------+--------------------------+-------------+
| AO | Adults Only | 18 |
| E | Everyone | 1 |
| E10 | Everone 10+ | 10 |
| EC | Early Childhood | 1 |
| G | General Audiences | 1 |
| M | Mature | 17 |
| NC17 | Restricted 17 years | 17 |
| PG | Parental Guidence | 6 |
| PG13 | Strong Parental Guidence | 13 |
| R | Restricted | 17 |
| T | Teen | 13 |
| UR | Unrated | 21 |
| X | Mature Content | 21 |
+-----------+--------------------------+-------------+
13 rows in set (0.00 sec)
RENT_TYPE:
Code:
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| RT_TYPE | char(2) | | PRI | | |
| RT_DESC | char(30) | YES | | NULL | |
| RT_LENGTH | decimal(2,0) | YES | | NULL | |
| RT_FEE | decimal(4,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
+---------+-----------+-----------+--------+
| RT_TYPE | RT_DESC | RT_LENGTH | RT_FEE |
+---------+-----------+-----------+--------+
| NG | New Game | 5 | 5.00 |
| NV | New Video | 2 | 6.00 |
| OG | Old Game | 2 | 4.00 |
| OV | Old Video | 7 | 5.00 |
| S1 | Special 1 | 2 | 3.00 |
| S2 | Special 2 | 2 | 1.50 |
+---------+-----------+-----------+--------+
6 rows in set (0.00 sec)
EMPLOYEE:
Code:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| EMP_ID | char(4) | | PRI | | |
| EMP_FIRST_N | char(15) | YES | | NULL | |
| EMP_LAST_N | char(15) | YES | | NULL | |
| EMP_ADDRESS | char(50) | YES | | NULL | |
| EMP_CITY | char(50) | YES | | NULL | |
| EMP_STATE | char(2) | YES | | NULL | |
| EMP_ZIP | decimal(5,0) | YES | | NULL | |
| EMP_WAGES | decimal(4,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
| EMP_ID | EMP_FIRST_N | EMP_LAST_N | EMP_ADDRESS | EMP_CITY | EMP_STATE | EMP_ZIP | EMP_WAGES |
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
| 0001 | TIM | WOJOWSKI | 38592 WEST | MACOMB | MI | 48035 | 15.00 |
| 0002 | LARRY | HILLIARD | 29438 NORTH AVE | MT. CLEMENS | MI | 38940 | 9.70 |
| 0003 | WILLIAM | JOHNSON | 29438 GRATIOT | MT. CLEMENS | MI | 38940 | 9.50 |
| 0004 | ANTHONY | WALKER | 24882 FAIRCHILD | MACOMB | MI | 48042 | 9.50 |
| 0005 | GEORGE | TOMLIN | 28784 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
| 0006 | BOB | BATES | 28743 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
6 rows in set (0.00 sec)
These tables do not have data yet!!
Code:
TRANSACTION:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| ACCT_ID | char(10) | YES | MUL | NULL | |
| CUST_NUM | decimal(2,0) | YES | | NULL | |
| TRANS_DATE | date | YES | | NULL | |
| EMP_ID | char(4) | YES | MUL | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
RENT_LINE:
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| INV_ID | char(10) | | PRI | | |
| RENT_QTY | decimal(2,0) | YES | | NULL | |
| RL_INOUT | char(3) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Following is all of the SQL code so far:
Code:
CREATE DATABASE BAKER_DVD;
USE BAKER_DVD;
CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) , -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address 1
EMP_CITY CHAR(50) , -- Address 2
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
PRIMARY KEY (EMP_ID) ) ENGINE=INNODB;
CREATE TABLE RATING
(RATE_CODE CHAR(04) , -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
PRIMARY KEY (RATE_CODE) ) ENGINE=INNODB;
CREATE TABLE ACCT
(ACCT_ID CHAR(10) , -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
CITY CHAR(50) , -- City of residence
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
PRIMARY KEY (ACCT_ID) ) ENGINE=INNODB;
CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) , -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
PRIMARY KEY (RT_TYPE) ) ENGINE=INNODB;
CREATE TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) , -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
PRIMARY KEY (ACCT_ID, CUST_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID) ) ENGINE=INNODB;
CREATE TABLE INVENTORY
(INV_ID CHAR(12) , -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating, (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
PRIMARY KEY (INV_ID),
FOREIGN KEY (RT_TYPE) REFERENCES RENT_TYPE(RT_TYPE),
FOREIGN KEY (RATE_CODE) REFERENCES RATING(RATE_CODE) ) ENGINE=INNODB;
CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10,0) , -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(02) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
PRIMARY KEY (TRANS_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID) ) ENGINE=INNODB;
CREATE TABLE RENT_LINE
(TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_ID CHAR(10) , -- Relavant video code
RENT_QTY DECIMAL(02,0) , -- Quantity rented, usually one.
RL_INOUT CHAR(03) , -- Item In or Out status
PRIMARY KEY (TRANS_NUM, RENT_NUM),
FOREIGN KEY (TRANS_NUM) REFERENCES TRANSACTION(TRANS_NUM) ) ENGINE=INNODB;
Here's the data already entered:
Rent Type and Rating:
Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );
INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );
Inventory:
Code:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');
INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');
INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');
INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');
INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');
INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');
INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');
INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG13', '8', '3');
INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');
INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');
INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');
INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');
INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');
INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');
INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');
Customers and Accounts:
Code:
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');
INSERT INTO CUSTOMER
VALUES
('8574962138', 1, 'SUE', 'EVANS', '19830317');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'LARRY', 'SCHULTZ', '19810322');
INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');
INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');
INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');
INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');
INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');
INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');
INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');
INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');
INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');
INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');
INSERT INTO CUSTOMER
VALUES
('8995542368', 2, 'MARY', 'SAAD', '19760324');
INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');
INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');
INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');
INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');
INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI', '48197', 0);
INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315', '5.00');
INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');
INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310', 0);
INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');
INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');
INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');
INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310', 0);
INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');
INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861', 0);
INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');
INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');
INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156', 0);
Employee:
Code:
INSERT INTO EMPLOYEE
VALUES ('0001', 'TIM', 'WOJOWSKI', '38592 WEST', 'MACOMB', 'MI', '48035', '15.00');
INSERT INTO EMPLOYEE
VALUES ('0002', 'LARRY', 'HILLIARD', '29438 NORTH AVE', 'MT. CLEMENS', 'MI', '38940', '9.70');
INSERT INTO EMPLOYEE
VALUES ('0003', 'WILLIAM', 'JOHNSON', '29438 GRATIOT', 'MT. CLEMENS', 'MI', '38940', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0004', 'ANTHONY', 'WALKER', '24882 FAIRCHILD', 'MACOMB', 'MI', '48042', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0005', 'GEORGE', 'TOMLIN', '28784 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0006', 'BOB', 'BATES', '28743 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');
Discuss here. (the text after the -- is a comment.)
Last edited by enigma.0Z on Fri Mar 03, 2006 4:14 am; edited 7 times in total
Tue Jan 31, 2006 10:44 am
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
ER Diagram is complete... I'll get started on the Inventory Table Today... if anybody needs to know any of the info, like the vid num code, let me know... ttyl homies...
Mon Feb 20, 2006 3:33 am
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
I slightly restructured a bit, but mostly with what the primary keys are. The modified tables are as follows:
CREATE DATABASE BAKER_DVD;
CREATE TABLE INVENTORY
(INV_VID_CODE CHAR(12) PRIMARY KEY, -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
);
CREATE TABLE RATING
(RATE_CODE CHAR(04) PRIMARY KEY, -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
);
CREATE TABLE ACCT
(ACCT_ID CHAR(10) PRIMARY KEY, -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
);
CREAT TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) PRIMARY KEY, -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
);
CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) PRIMARY KEY, -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
);
CREATE TABLE RENT_LINE
(RENT_ID DECIMAL(10,0) PRIMARY KEY, -- ID Code for movie rental
TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_VID_CODE CHAR(10) , -- Relavant video code
RL_INOUT CHAR(03) , -- Item In or Out status
);
CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10) PRIMARY KEY, -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(04) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
);
CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) PRIMARY KEY, -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
);
let me know if any of this will cause any problems... hopefully not, because during my boredom, I had completed the data for 3 of the tables: INVENTORY, RATING, and RENT_TYPE. Hopefully nobody had their hearts set on those because they're done now. If you want the info for those tables, here are the codes:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');
INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');
INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');
INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');
INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');
INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');
INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');
INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG-13', '8', '3');
INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');
INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');
INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');
INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');
INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');
INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');
INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');
**MOD EDIT: Rent type and rating have already been made **
INSERT INTO RATING
VALUES
('G', 'GENERAL AUDIENCES', '0');
INSERT INTO RATING
VALUES
('PG', 'PARENTAL GUIDANCE SUGGESTED', '0');
INSERT INTO RATING
VALUES
('PG-13', 'PARENT STRONGLY CAUTIONED', '13');
INSERT INTO RATING
VALUES
('R', 'RESTRICTED', '17');
INSERT INTO RATING
VALUES
('NC-17', 'NO ONE 17 AND UNDER ADMITTED', '18');
INSERT INTO RATING
VALUES
('EC', 'EARLY CHILDHOOD', '3');
INSERT INTO RATING
VALUES
('E', 'EVERYONE', '6');
INSERT INTO RATING
VALUES
('E10+', 'EVERYONE 10 AND OLDER', '10');
INSERT INTO RATING
VALUES
('T', 'TEEN', '13');
INSERT INTO RATING
VALUES
('M', 'MATURE', '17');
INSERT INTO RATING
VALUES
('AO', 'ADULTS ONLY', '18');
INSERT INTO RENT_TYPE
VALUES
('NR', 'NEW RELEASE', '2', '3.50');
INSERT INTO RENT_TYPE
VALUES
('AC', 'ACTION, ADVENTURE', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('CL', 'CLASSIC', '7', '2.00');
INSERT INTO RENT_TYPE
VALUES
('SF', 'SCI-FI', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('CO', 'COMEDY', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('DR', 'DRAMA', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('SH', 'SHOOTER', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('SI', 'SIMULATOR', '5', '2.50');
I post the correct values tonight during class.
** End edit **
For the rest of you all, good luck on whatever the hell your part is. Again to enigma, the ER diagram is done. I'll have it for you tomorrow during class at the latest. If i can find a way to e-mail it to you in my spare time, i will do so. Peace yall..
Mon Feb 20, 2006 6:17 am
ncamb02
Newbie
Joined: 31 Jan 2006
Posts: 1
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');
INSERT INTO CUSTOMER
VALES
('8574962138', 1, 'SUE', 'EVANS', '19830317');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');
INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');
INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');
INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');
INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');
INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');
INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');
INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');
INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');
INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');
INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');
INSERT INTO CUSTOMER
VALUES
('8995542369', 2, 'MARY', 'SAAD', '19760324');
INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');
INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');
INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');
INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');
INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI '48197');
INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315' '5.00');
INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');
INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310');
INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');
INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');
INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');
INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310');
INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');
INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861');
INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');
INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');
INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156');
________________________________________________________________________________ _____________________
I HAVE A COUPLE QUESTIONS... HOPE THE DATA IS RIGHT BUT IF NOT ITS A COUPLE QUICK FIXES. FIRST, THE TABLE STRUCTURE HAS DECIMAL(02) WITHIN THE CUSTOMER TABLE FOR CUST_NUM... I DIDNT KNOW IF YOU WANTED THAT AS A DECIMAL OR JUST A TWO DIGIT NUMBER? SECOND UNDER THE ACCT TABLE NOT EVERY CUSTOMER SHOULD HAVE A SECONDARY ADDRESS SHOULD THEY? AS I WAS TYPING IT IN... DONT U THINK THEY SHOULD HAVE A TELEPHONE #? IF ANYONE WANTS TO ME TO FIX NETHIN ILL BE AVAILABLE ALL DAY BE4 CLASS SO GIVE ME A CALL @ 586-855-9290 ... ALRIGHT IF NOT SEE U GUYS IN CLASS
Mon Feb 20, 2006 4:24 pm
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
I didn't think we would need a second address, that's why I didn't put it in my revised table structure... By the way, I'm still waiting for an opinion on whether that was the way yall wanted to go... let me know... If you want to meet up before class, Nick, I'll be in the learning center from now (12:00) till class... let me know... later...
Tue Feb 21, 2006 2:01 am
Invinciblemoron
Newbie
Joined: 26 Jan 2006
Posts: 2
Technically we need the second address for the city address. I coded with room for the City address
Code:
insert into employee
values ('0001', 'Tim', 'Wojowski', '38592 West', 'Macomb", 'MI', '48035', '15.00');
insert into employee
values ('0002', 'Larry', 'Hilliard', '29438 North Ave', 'Mt. Clemens' 'MI', '38940', '9.70');
insert into employee
values ('0003', 'William', 'Johnson', '29438 Gratiot', 'Mt. Clemens 'MI', '38940', '9.50');
insert into employee
values ('0004', 'Anthony', 'Walker', '24882 Fairchild', 'Macomb', 'MI', '48042', '9.50');
insert into employee
values ('0005', 'George', 'Tomlin', '28784 Cotton', 'Chesterfield', 'MI', '48315', '9.50');
insert into employee
values ('0006, 'Bob', 'Bates', 28743 Cotton', 'Chesterfield', 'MI', '48315', '9.50');
_________________
You have Employees
5 employees is gewd.
place in code bracket and use insert statements
Tue Feb 21, 2006 6:04 am
enigma.0Z
Site Admin
Joined: 25 Jan 2006
Posts: 8
I've already did rating and rent_type. That's what I handed out to everyone. We need the address_2 because addresses have two lines.
As far as the inventory entries go, we just need to change them to match what I handed out last class (I'll edit them myself).
Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years old" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );
INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );e
As far as dec(02) in customer, that's just the number of the customer under that account number:
for instance Account 12345 Customer 1 is a dad, customer 2 is a mom, and 3, 4, and 5 are their kidsSource: http://community.livejournal.com/sqlserver/44667.html
-
any suggestions?
Date: 03/06/06 (PHP Community) Keywords: php, mysql, sql
A friend of mine is writing a comic book and he wants to post it online and make it a subscription based thing where every month or so he adds new content.
I'm going to be using php/mysql to make this happen, but beyond that the details are knda fuzzy. I've got most of it mapped out, the place I am having problems is when subscriptions come into play (this is only because I have never taken on this kind of project before).
Any suggestions as to the tools I would need to create a subsciption based site? I'm also trying to keep this as inexpensive as possibe >< Chances are he'll be using paypal.
Anything to point me in the proper direction would be much appreciated!
Thanks!
| Previous page | || | Next page |