database — RSS

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 [...]

Source: http://blog.taragana.com/index.php/archive/object-oriented-code-generator-for-php-mysql-web-applications/
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.

Source: http://community.livejournal.com/php/417360.html
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:
- normal site
- skin: HWCompany
- skin: Electric
- language: English
- language: Dutch
- language: German
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

Source: http://community.livejournal.com/php/417088.html
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.

Source: http://community.livejournal.com/php_dev/66526.html
A simple, customizable frontend script for a database?

Date: 02/23/06 (PHP Community) Keywords: database, web

Hi, I have such a simple question-
What is a simple, customizable frontend script that I can use for a website I'm building at work?

We are a health access organization, and our website features a provider database. Unfortunately, I can't find a good script that does what I need. DaDaBik is EXACTLY what I want, but the server the site is on does not have magic_quotes enabled, so it won't work.

Basically, I need to be able to:

- search
- add records
- customize it completely to blend in with the site

Any help or ideas you have would be great! Thanks in advance!

Source: http://community.livejournal.com/php/417786.html
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.

Source: http://community.livejournal.com/php/418712.html
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.

Source: http://community.livejournal.com/mysql/86856.html
INI file questions

Date: 02/28/06 (C Sharp) Keywords: database, java

Hi all!

I've been trying to go from beginner's Java to C#, as I prefer C# much more. I just love the feel of it and the ability to design the forms much like VB.NET is a huge time saver for me. Still, there's some things that I just can't seem to figure out (nor can seem to locate on the internet).

Basically I'm going to be making a text adventure game in C#, but I like to store all of the data in INI files. I'd like to have one .txt file and inside it would store every room with exits and interactive objects. For example:

[0001]
Name=Town Square
Exits=north, south
North=0002
South=0003

[0002]
Name=North of Town Square
Exits=north, south, east, west
North=0004
South=0001
East=0005
West=0006
NPCs=A strange old man

etc etc etc.

And another file for all of the NPCs, what they'd say to the player and so on. But the problem is.. I'm not sure how to actually READ ini files in C# (or Java, or C, or C++, or VB.NET for that matter). I'm not sure if there's a better way of doing this. I don't know any database commands, or if I can even run them (or if any of the player who eventually download this can run them either). I figured with this I could include the rooms.txt as part of the program (as a resource, like images) and that way there'd be no question of players messing with them or peeking at them to see where to go/do/etc.

So my real reason for this post is to ask for help on learning how to read/write INIs (I may want to write a player file in INI format, then encrypt it).

Any help or tips would be most appreciated.

Source: http://community.livejournal.com/csharp/50870.html
HELP!!

Date: 03/01/06 (Web Development) Keywords: database, asp, web

Newbie...

Hi guys, I am new to asp, and frankly, it scares the hell out of me. I manage to muddle through for the most part, but I admit, I am floored by the following.

I need a form.

Requirements of said form, are as follows:
- Capture information (name, address, phone, email - the usual) where I can access it (database, email, whatever)
- Redirect once information has been provided to a download link where they can access a paper

That's it. That's all. And being a bit clueless, I cannot figure this out.

ANY advice, directions to help/how-to, advice et al would be receieved with outrageous gratitude. Hearty scorning will also be weathered gracefully since I thoroughly deserve it.

The website in question is Galkal.com.au

Thanks in desperate anticipation!
Dominique

Source: http://community.livejournal.com/webdev/302045.html
Newbie...

Date: 03/01/06 (Asp Dot Net) Keywords: database, asp, web

Hi guys, I am new to asp, and frankly, it scares the hell out of me. I manage to muddle through for the most part, but I admit, I am floored by the following.

I need a form.

Requirements of said form, are as follows:
- Capture information (name, address, phone, email - the usual) where I can access it (database, email, whatever)
- Redirect once information has been provided to a download link where they can access a paper

That's it. That's all. And being a bit clueless, I cannot figure this out.

ANY advice, directions to help/how-to, advice et al would be receieved with outrageous gratitude. Hearty scorning will also be weathered gracefully since I thoroughly deserve it.

The website in question is Galkal.com.au

Thanks in desperate anticipation!
Dominique

Source: http://community.livejournal.com/aspdotnet/58747.html
FileMaker

Date: 03/01/06 (Web Development) Keywords: php, software, database, asp, jsp, web, microsoft

Has anyone here ever used FileMaker Pro (http://www.filemaker.com/)? I have one client who is starting up a Pilates studio and she's looking at http://www.pilatessoftware.com for her internal management. She would also like to have the website interact with it (ie. have members log in and register for classes, etc.) It seems that the network ready version of the Get Physical (pilatessoftware.com) uses FileMaker as it's underlying database. The FileMaker site has the following:

ODBC/JDBC source
Exchange data with other applications via the ODBC/JDBC source support in FileMaker Server 8 Advanced. For example, you can replicate FileMaker information to other database products, use other reporting tools with FileMaker data, or build JSP or ASP websites based on FileMaker data, using popular tools such as Lasso Studio, Dreamweaver MX 2004, Adobe GoLive CS, and Microsoft FrontPage.

So it seems that I can get the info from the program but does anyone know if I'd be able to send info back to it? or is it a one-way channel?

x-posted to webdesign, php

Source: http://community.livejournal.com/webdev/302360.html
FileMaker

Date: 03/01/06 (WebDesign) Keywords: php, software, database, asp, jsp, web, microsoft

Has anyone here ever used FileMaker Pro (http://www.filemaker.com/)? I have one client who is starting up a Pilates studio and she's looking at http://www.pilatessoftware.com for her internal management. She would also like to have the website interact with it (ie. have members log in and register for classes, etc.) It seems that the network ready version of the Get Physical (pilatessoftware.com) uses FileMaker as it's underlying database. The FileMaker site has the following:

ODBC/JDBC source
Exchange data with other applications via the ODBC/JDBC source support in FileMaker Server 8 Advanced. For example, you can replicate FileMaker information to other database products, use other reporting tools with FileMaker data, or build JSP or ASP websites based on FileMaker data, using popular tools such as Lasso Studio, Dreamweaver MX 2004, Adobe GoLive CS, and Microsoft FrontPage.

So it seems that I can get the info from the program but does anyone know if I'd be able to send info back to it? or is it a one-way channel?

x-posted to webdev, php

Source: http://community.livejournal.com/webdesign/1073667.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?

<?php // Connect to Database include('functions/mail_attached.php'); //connect info omitted if ($_POST['submitted'] == "yes") { //are the files there? echo "Submission if works "; //files are there, let's fix the HTML and newlines $newOffAdd = strip_tags($_POST['office_address']); // remove all HTML $newOffAdd = nl2br($newOffAdd); // make the line breaks in to br tags $newHomeAdd = strip_tags($_POST['home_address']); // remove all HTML $newHomeAdd = nl2br($newHomeAdd); // make the line breaks in to br tags $newPolBack = strip_tags($_POST['political_background']); // remove all HTML $newPolBack = nl2br($newPolBack); // make the line breaks in to br tags //prepare the data to be inserted $insert_string = "insert into fellowsapp (`fellowship_date`,`fellow_name`,`fellow_title`,`office_address`,`home_address`,`office_phone`,`home_phone`,`assistant_name`,`office_fax`,`home_fax`,`fellow_email`,`fellow_ssn`,`recommended_by`,`political_background`) VALUES(\"" . $HTTP_POST_VARS['fellow_date'] . "\",\"" . $HTTP_POST_VARS['fellow_name'] . "\",\"" . $HTTP_POST_VARS['fellow_title'] . "\",\"" . $newOffAdd . "\",\"" . $newHomeAdd . "\",\"" . $_POST['office_phone'] . "\",\"" . $_POST['home_phone'] . "\",\"" . $HTTP_POST_VARS['assistant_name'] . "\",\"" . $HTTP_POST_VARS['office_fax'] . "\",\"" . $HTTP_POST_VARS['home_fax'] . "\",\"" . $HTTP_POST_VARS['fellow_email'] . "\",\"" . $HTTP_POST_VARS['fellow_ssn'] . "\",\"" . $HTTP_POST_VARS['recommended_by'] . "\",\"" . $newPolBack . "\");"; //insert the data $result = mysql_query($insert_string); //mail $mail_to = "jessica_jacobs@ksg.harvard.edu"; $message = "Dear Eric, Here are the files that go with " . $_POST['fellow_name'] . "'s fellow application. Enjoy! Jess"; $result2 = mail_attached($mail_to,$webmaster_email,"Fellows Application Files",$message,$_FILES); //tell them they succeeded echo "Thank you for your submission! We'll be contacting you shortly. "; } else {} //tell them they don't have any files //echo "You must supply a bio and study group topic information. "; //} ?> AND MY INCLUDE IS BELOW: <? /* Shamelessly stolen from http://www.theukwebdesigncompany.com/articles/php-file-attachments.php, then adjusted to my needs, found not to work and modified using ideas from http://www.phpdiscuss.com/article.php?id=423&group=php.lang and elsewhere. $filenames is designed basically to just be $_FILES; otherwise, use an array of the form: $filenames = array( "key1" => array( "error" => "0", "tmp_name" => "full_path_to_file", "name" => "file_name", "size" => "size", "type" => "type")); */ function mail_attached($to, $from, $subject, $message, $filenames, $headers = '') { $unique_sep = md5(uniqid(time())); // Set up the top headers $headers .= "From: $from\n"; $headers .= "MIME-Version: 1.0\nContent-Type: multipart/mixed;boundary=\"$unique_sep\";\n"; $headers .= "charset=\"iso-8859-1\"\nContent-Transfer-Encoding:7bit\n\n"; $headers .= "If you are reading this, then your e-mail client does not support MIME.\n"; $headers .= "Sent with mail_attached() (c) 2002 John Coggeshall\n\n"; $headers .= "--$unique_sep\n"; // Include the text portion of the e-mail $headers .= "Content-Type: text/plain; charset=\"iso-8859-1\"\n"; $headers .= "Content-Transfer-Encoding: 7bit\n\n"; $headers .= $message . "\n\n"; // Now the attachment foreach($filenames as $val) { if($val['error'] != 4 && file_exists($val['tmp_name'])) { $headers .= "--$unique_sep\n"; $headers .= "Content-Type: {$val['type']}; name=\"{$val['name']}\"\n"; $headers .= "Content-Transfer-Encoding: base64\n"; $headers .= "Content-Disposition: attachment\n\n"; $linesz= $val['size']+1; $fp= fopen($val['tmp_name'], 'rb'); $headers .= chunk_split(base64_encode(fread($fp,$linesz))); } } $headers .= "--$unique_sep--\n"; if(!mail($to, $subject, $message, $headers)) return false; else return true; } /* End of mail_attached() function */ ?>

Thanks!

Source: http://community.livejournal.com/php/421008.html
FileMaker

Date: 03/01/06 (PHP Community) Keywords: software, database, asp, jsp, web, microsoft

Has anyone here ever used FileMaker Pro (http://www.filemaker.com/)? I have one client who is starting up a Pilates studio and she's looking at http://www.pilatessoftware.com for her internal management. She would also like to have the website interact with it (ie. have members log in and register for classes, etc.) It seems that the network ready version of the Get Physical (pilatessoftware.com) uses FileMaker as it's underlying database. The FileMaker site has the following:

ODBC/JDBC source
Exchange data with other applications via the ODBC/JDBC source support in FileMaker Server 8 Advanced. For example, you can replicate FileMaker information to other database products, use other reporting tools with FileMaker data, or build JSP or ASP websites based on FileMaker data, using popular tools such as Lasso Studio, Dreamweaver MX 2004, Adobe GoLive CS, and Microsoft FrontPage.

So it seems that I can get the info from the program but does anyone know if I'd be able to send info back to it? or is it a one-way channel?

x-posted to webdesign, webdev

Source: http://community.livejournal.com/php/420815.html
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/
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

Source: http://community.livejournal.com/mysql/87972.html
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 kids

Source: http://community.livejournal.com/sqlserver/44667.html
question on test environments

Date: 03/06/06 (IT Professionals) Keywords: database

so - here's the scenario.

production is in forest 1
test has been set up in forest 2
100+ gpo objects in the production environment

test environment naming convention is: test.production environment's name

now, what i want to do is mimic the AD structure of the production environment in the test environment... as to OU's, GPO's, accounts, permissions, etc.

how do i best go about doing this? active directory migration tool? backup the AD database and gpo object folder and copy them over? either or both not possible?

some of my questions... is any of the above possible, or do i have to manually re-create the ou's and accounts/permissions because of the test environment being a different name? or does it matter?

Source: http://community.livejournal.com/itprofessionals/35345.html
Query help...

Date: 03/08/06 (SQL Server) Keywords: database, sql

I am trying to transform data from an old Image database to MSSQL and I have hit a wall on this one table.

The schema of the Image table is

old_table(
id char(10),
desc_text char(70),
eff_yrq_beg char(4),
ln_num char(2))

There are multiple records (ln_num) for each id that in order create a description text block.

What I am envisioning is a table

new_table(
id varchar(10),
description varchar(500),
eff_yrq_beg char(4))

In the new table the description is a concatenation of the desc_text from the first table in order by ln_num.

Is there any way that you can think to do this without cursors?

Source: http://community.livejournal.com/sqlserver/45175.html
MySQL Performance Optimization - A Lesson

Date: 03/09/06 (Java Web) Keywords: mysql, database, sql

The MySQL database supporting this blog was consuming massive amount of CPU, which effectively lead to site going down 3-4 times this week. I knew an obvious culprit. However reality differed. I knew for sometime I had a jumbo query, which was not cached, and is likely to contribute to MySQL server load: [...]

Source: http://blog.taragana.com/index.php/archive/mysql-performance-optimization-a-lesson/

antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home