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