mySQL help

[Axel]

Hello Habboxforum, I come back to you in an hour of desperate need.

I'm trying to create a database in mySQL, which has 6 tables, but only 3 of them work... here is the sql...

http://img526.imageshack.us/img526/3618/sql.png

And here are the errors I get...


ERROR 1007 (HY000): Can't create database 'shopping'; database exists
Database changed
Query OK, 0 rows affected (0.03 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '(), T
otal_Price Double, Cust_Login varchar(20), primary key(Shopping_Cart_ID), f' at
line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '), Pr
ice DOUBLE(), primary key(Shopping_Cart_ID), primary key(Item_Code), foreig' at
line 1
Query OK, 0 rows affected (0.02 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ' Cate
gory_Code int(5), primary key(Item_Type_Number), foreign key(Category_Code)' at
line 1
Query OK, 0 rows affected (0.02 sec)

I can't figure why I'm getting errors... any help is really appreciated.

[Jin]

Not sure what possessed you to create it as a png :S

Okay so I am debugging your code which hasn't been made easier as I have had to use OCR software to convert it into text.

There are quite a few basic issues here.

Such as DATE() instead of DATE.
Foreign Keys Referencing fields in tables which haven't been created yet.
Fields being created without a data type (item_Type.Graphics)


create database shopping;
use shopping;
create table customer(First_Name varchar(15), Last_Name varchar(20), Age int(3), House_Number_Dr_Name varchar(20), street varchar(20), city varchar(20), state varchar(20), Post_code char(8), Phone_Number char(11), Email varchar(40), cust_Login varchar(20), cust_Login_Password varchar(15), primary key(cust_Login));
create table shopping_cart(shopping_cart_ID int(8), date DATE, Total_Price Double, cust_Login varchar(20), primary key(shopping_cart_ID), foreign key(cust_Login) references customer(cust_Login));
create table item_Type(item_Type_Number int(6), Name varchar(50), Graphic varchar(50), category_code int(5), primary key(item_Type_Number), foreign key(category_code) references category(category_code));
create table item_inventory(stock int, Price Double, item_code int(7), Product_Name varchar(30), size varchar(10), colour varchar(10), item_Type_Number int(6), primary key (Item_code), foreign key(item_Type_Number) references item_Type(Item_Type_Number));
create table category(Name varchar(30), Description varchar(100), category_code int(5), primary key(category_code));
create table shopping_cart_item(shopping_cart_ID int(8), item_code int(7), Quantity int(2), Price DOUBLE, primary key(shopping_cart_ID), primary key(item_code), foreign key(shopping_cart_ID) references shopping_cart(shopping_cart_ID));

The above is your corrected code, I can't remember all the errors in it but here are the major ones that would flaw your database design.

First of all, you can't have multiple primary keys for one table. So I assumed shopping_cart_item was meant to be a composite key.
Secondly you must define datatypes for every field. So for item_Type.Graphics I just made it a varchar(50) as I dont really know what this field is for.

Looking at the use of camel case (which at university level no one really uses anymore) and the sql as an image I am assuming that this is for CPT6 in AQA Computing. In which case you may need to reference this in your documentation if you want credit for it legitimately.