SQL ? you said SQL ? .... Help ...

[anaxis]

Hello

I have some little things to do in SQL at school.
Even if our required soft is MySQL we are to write down all the foreign keys and index as well.


Here's the "code" we wrote for now.
For those who know some of this language, is this code totally wrong or at least do some parts are right ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CREATE DATABASE bibliotheque ;

CREATE TABLE lecteur (
code_lecteur INT NOT NULL AUTO_INCREMENT ,
code_historique INT NOT NULL AUTO_INCREMENT ,
code_biblio INT NOT NULL AUTO_INCREMENT ,
nom VARCHAR (25) NOT NULL ,
prenom VARCHAR (25) NOT NULL ,
adresse VARCHAR (50) ,
code_postal INT ,
ville VARCHAR(15) ,
telephone VARCHAR (20) NOT NULL ,
date_inscription DATE NOT NULL ,
PRIMARY KEY (code_lecteur) ,
FOREIGN KEY (code_historique , code_biblio) ,
INDEX (nom, prenom)
) ;


CREATE TABLE enregistre (
code_biblio INT NOT NULL AUTO_INCREMENT ,
code_historique INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (code_biblio , code_historique)
) ;


CREATE TABLE historique (
code_historique INT NOT NULL AUTO_INCREMENT ,
date_emprunt DATE NOT NULL ,
date_retour DATE (date_emprunt +15) NOT NULL ,
PRIMARY KEY (code_historique)
) ;


CREATE TABLE medias (
code_biblio INT NOT NULL AUTO_INCREMENT ,
code_type INT NOT NULL AUTO_INCREMENT ,
annee YEAR NOT NULL ,
titre_media VARCHAR (50) NOT NULL ,
etat_media ENUM ('disponible' , 'non disponible') ,
auteur_realisateur VARCHAR (25) NOT NULL ,
PRIMARY KEY (code_biblio) ,
FOREIGN KEY (code_type)
INDEX (auteur_realisateur , titre)
) ;


CREATE TABLE type (
code_type INT NOT NULL AUTO_INCREMENT ,
designation_type VARCHAR (10)
PRIMARY KEY (code_type)
) ;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Milamber]

you should try to test-run it on a working database server...(preferable one on your own computer, just in case it messes some things up...

by visually checking the syntax, it seems ok, just a few commas/typos,
I am not sure you can create indices in the create table statement itself since there is a Create/drop Index statement, but it could be , and it certainly is usefull..

You could add some defaults too and you specify the table type in mysql by putting TYPE=... (e.g. MyISAM) after the parenhesis of the create table statements

corrected typos:

CREATE TABLE medias (
code_biblio INT NOT NULL AUTO_INCREMENT ,
code_type INT NOT NULL AUTO_INCREMENT ,
annee YEAR NOT NULL ,
titre_media VARCHAR (50) NOT NULL ,
etat_media ENUM ('disponible' , 'non disponible') ,
auteur_realisateur VARCHAR (25) NOT NULL ,
PRIMARY KEY (code_biblio) ,
FOREIGN KEY (code_type),
INDEX (auteur_realisateur , titre_media)
) ;


CREATE TABLE type (
code_type INT NOT NULL AUTO_INCREMENT ,
designation_type VARCHAR (10),
PRIMARY KEY (code_type)
) ;



[This message has been edited by Milamber (edited 09 February 2002).]

[Milamber]

now a semantical question

whats the use for the code_historique,
code_biblio ?

i see you have included them in your "lecteur" table as wel as in the "enregistre" table.

on top it doesn't seem logical for a table to exist solely from a primary key and nothing else..

if these 2 atributes means what i think they do, you should remove te attributes (code_historique, code_biblio) form "lecteur" and add a "code_lecteur" to the "enregistre" table

[devilkin]

Anaxis,

I would recommend (just as milamber did) that you install a local mysql on a pc somewhere (even under windows if you have to), and preferably grab a copy of apache and php and phpMyAdmin on the way... phpMyAdmin allows you to easily generate all your tables, and it can give you the generation scripts back too... So you're sure that they are really correct :-)

Jan

------------------
Human Stupidity is no match for Artificial Intelligence.

[anaxis]

Well the code_stuff etc
are the result from cardinalities
1-n
n-m
1-1
between two tables
so some become foreign key...

and yes, I'd like to test, but MySQL doesnt recognize foreign key and Index other than the primary key
so, it's even more confusing

[Lightfeather]

If Mysql doesn't do that, try going to the page of Oracle, they have a free downloadable Oracle data-base (only accessible via localhost) And I think that you can easily find a tools to check the syntax.



[This message has been edited by Lightfeather (edited 10 February 2002).]

[anaxis]

Hello,

So today I installed EasyPhP
It's cool, it installed everything in one click ! just what I needed !


Anyway, I entered the code ...
Well, with some changes, as some dork in my group wanted to make changes but he even wanted to seperate one table into two and one of those two with only one entry....
I dont know but it didnt seem very good and easy to maintain and I dont even talk about the requests we had to do after that.
I did my meaniepooh and said no and it works better without this stupid one entry table.
Maybe, it's logical to put a day in another table but I dont see why then ...

But this auto-proclamed pro never uses MySQL or stuff like that but Access and claims that cardinalities are never used and that people working with DB all day long never use them, use Access and like little table with only one entry .....

Maybe, I dont know

Anyway,

I entered my stuff and it worked

and even the requests worked too !

So, very thank you for your help (the dutch lesson was very helpful too ! yes yes ! It's true !)