GIDForums  

Go Back   GIDForums > Computer Programming Forums > MySQL / PHP Forum
Reload this Page I need help on My SQL Database Creation Statement
User Name
Password
Register FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
 
Thread Tools Search this Thread Rate Thread
  #1  
Old 03-Mar-2008, 19:58
asdfg asdfg is offline
New Member
  
Join Date: May 2007
Posts: 26
asdfg is on a distinguished road
Red face

I need help on My SQL Database Creation Statement

Hi All

I am New to MySQL, I need a help on creation Statement for the my following statement(SQL).

Please check is it correctness, and please to suggest me the correct statement, I attached my Databases structure on pdf.

Followings are my owns.

For the Mmeber Table Creation:

Quote:
CREATE TABLE MEMBER(Member_ID CHAR (10) NOT NULL,University_ID CHAR (17),Name VARCHAR (100),Registration_Date DATE,Catergory CHAR(3) NOT NULL CHECK (Catergory IN ('ACS','NAS','LIB','GST'),Renewed_Date DATE,PRIMARY KEY (Member_ID) UNIQUE (University_ID),);

This is for the Book

Quote:
CREATE TABLE BOOK (ISBN CHAR(10) NOT NULL,TITLE VARCHAR(100),AUTHOR VARCHAR(100),PUBLICATION VARCHAR(100),PUBLICATION_DATE DATETIME,NUMBER_OF_COPY DECIMAL,PRIMARY KEY (ISBN));

This is for the Copies Table:

Quote:
CREATE TABLE COPIES (ACCESS_NO CHAR(6),ISBN CHAR(10) NOT NULL,RECEIVED_DATE DATE,AVAILABILITTY BOOLEAN NOT NULL DEFAULT 'TRUE',PRIMARY KEY (ACCESS_NO),FOREIGN KEY(ISBN) REFERENCES BOOK ON UPDATE CASCADE);

This Is for the Reserved Copy:

Quote:
CREATE TABLE RESERVED_COPY(Member_ID CHAR (10) NOT NULL,ISBN CHAR(10) NOT NULL, ISBN CHAR(10) NOT NULL, PRIMARY KEY (Member_ID,ISBN),FOREIGN KEY(ISBN) REFERENCES BOOK,MEMBER ON UPDATE CASCADE);

These are my statements, Only Book statement running others are giving the 150 error.

Please to solve my problem soon.
Attached Images
File Type: pdf ER Diagram For the Library System.pdf (86.0 KB, 10 views)
  #2  
Old 03-Mar-2008, 20:09
TurboPT's Avatar
TurboPT TurboPT is offline
Senior Member
  
Join Date: Feb 2006
Location: Atlanta, GA
Posts: 1,234
TurboPT is a jewel in the roughTurboPT is a jewel in the roughTurboPT is a jewel in the rough

Re: I need help on My SQL Database Creation Statement

There's a stray comma near the end of Member table creation...

I'll try the others...
__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!
  #3  
Old 03-Mar-2008, 21:20
TurboPT's Avatar
TurboPT TurboPT is offline
Senior Member
  
Join Date: Feb 2006
Location: Atlanta, GA
Posts: 1,234
TurboPT is a jewel in the roughTurboPT is a jewel in the roughTurboPT is a jewel in the rough

Re: I need help on My SQL Database Creation Statement

Minor changes...(compare to your original post), but basically:
1. member table (dropped unique at the end, append to University_ID)
2. copies (type boolean is converted to a tinyint; only takes 1, 0)
3. reserved_copy table (foreign key change, column 'ISBN' listed twice)
Code:
CREATE TABLE MEMBER(Member_ID CHAR (10) NOT NULL,
                    University_ID CHAR (17) UNIQUE,
                    Name VARCHAR (100),
                    Registration_Date DATE, 
                    Catergory CHAR(3) NOT NULL CHECK (Catergory IN ('ACS','NAS','LIB','GST')),
                    Renewed_Date DATE, 
                    PRIMARY KEY (Member_ID));

CREATE TABLE BOOK (ISBN CHAR(10) NOT NULL,
                    TITLE VARCHAR(100),
                    AUTHOR VARCHAR(100),
                    PUBLICATION VARCHAR(100),
                    PUBLICATION_DATE DATETIME,
                    NUMBER_OF_COPY DECIMAL,
                    PRIMARY KEY (ISBN));

CREATE TABLE COPIES (ACCESS_NO CHAR(6),
                    ISBN CHAR(10) NOT NULL,
                    RECEIVED_DATE DATE,
                    AVAILABILITTY BOOLEAN NOT NULL DEFAULT 1,
                    PRIMARY KEY (ACCESS_NO),
                    FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN) ON UPDATE CASCADE);

CREATE TABLE RESERVED_COPY(Member_ID CHAR (10) NOT NULL,
                           ISBN CHAR(10) NOT NULL, 
                           PRIMARY KEY (Member_ID,ISBN),
                           FOREIGN KEY(ISBN) REFERENCES BOOK(ISBN) 
                              ON UPDATE CASCADE,
                           FOREIGN KEY(member_id) REFERENCES member(member_id) 
                              ON UPDATE CASCADE);
__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!
  #4  
Old 03-Mar-2008, 23:55
asdfg asdfg is offline
New Member
  
Join Date: May 2007
Posts: 26
asdfg is on a distinguished road

Re: I need help on My SQL Database Creation Statement

Dear Turbo,

Thakn you very much, My problem is,

FOREIGN KEY(member_id) REFERENCES member(member_id)
ON UPDATE CASCADE);


In this If I want to be set both UPDATE and DELETE constrains?
  #5  
Old 04-Mar-2008, 02:45
asdfg asdfg is offline
New Member
  
Join Date: May 2007
Posts: 26
asdfg is on a distinguished road
Thumbs down

Re: I need help on My SQL Database Creation Statement

Dear Turbo

These are the Error Coming while executes the SQL Commands.

Attached PDF
Attached Images
File Type: pdf Error on SQL.pdf (99.9 KB, 4 views)
  #6  
Old 04-Mar-2008, 05:28
TurboPT's Avatar
TurboPT TurboPT is offline
Senior Member
  
Join Date: Feb 2006
Location: Atlanta, GA
Posts: 1,234
TurboPT is a jewel in the roughTurboPT is a jewel in the roughTurboPT is a jewel in the rough

Re: I need help on My SQL Database Creation Statement

Alright, where's your silent 'strip-spaces' command? (just kidding)...

1. Member, seems to missing the final ')' to create table, plus a few fields, as it appears on the PDF, don't have a space between the column name and the type.

2. Book, same spacing issue for the ISBN column.

3. Copies, fixing #2 should resolve this, but check the spaces after the column names, again.

4. Reserved_copy, look at the error message, what's with the ampersand [&]? Also, check column name spacing.

5. Borrowed_copy, appears to have a stray comma (error hints at location) in the first foreign key declaration, check column spaces too.
__________________
Use the force...read the source!!
WYCIWYG -- what you code is what you get!
  #7  
Old 08-Mar-2008, 04:13
asdfg asdfg is offline
New Member
  
Join Date: May 2007
Posts: 26
asdfg is on a distinguished road
Red face

Re: I need help on My SQL Database Creation Statement

Hi Turbo,

Thank you Very Much for your help on the above problem, I got the solution for that.
 
 
« Previous Thread | Next Thread »

Recent GIDBlogNot selected for officer school by crystalattice

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
getting an error while compiling and running using different IDE. jaro C Programming Language 0 25-Aug-2006 09:14
functions seems not to exit from a certain code block jaro C Programming Language 3 21-Mar-2006 23:08
Microsoft SQL Server :: SQL Server message 241, state 1, severity 16 lyuboe MySQL / PHP Forum 0 11-May-2005 04:31
Compiling error when doing SQL database in C++ confused_pig C++ Forum 2 12-Jan-2005 21:08
SQL statement skyloon MySQL / PHP Forum 2 12-Oct-2003 06:48

Network Sites: GIDNetwork · GIDWebHosts · GIDSearch · Learning Journal by J de Silva, The

All times are GMT -6. The time now is 16:48.

Contact Us - GIDForums™ - Archive - Top

vBulletin, Copyright © 2000 - 2010, Jelsoft Enterprises Ltd.