Solved

SQL Errors 2

Posted on 2011-02-23
2
413 Views
Last Modified: 2012-05-11
I'm trying to understand the error in the following:
drop database if exists University;
create database University;
use University;

create table department(
dept_name varchar(20),
building  varchar(15),
budget    numeric(12, 2),
check (budget > 0),
index(dept_name),
primary key(dept_name)
);

create table instructor(

id varchar(20),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8, 2),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id)
);



create table course(

course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
check (credits > 0),
foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(course_id)

);

create table classroom(

building varchar(15),
room_number varchar(7),
capacity numeric(4, 0),
primary key(building,room_number),
index(building), #new index
index(room_number) #new index
);


create table section(

course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),

foreign key(course_id) references course(course_id)
    on delete cascade,
foreign key(building) references classroom(building)
    on delete set null,
foreign key(room_number) references classroom(room_number)
    on delete set null,
primary key(course_id,sec_id,semester,year),
#index(building),
#index(room_number),
index(course_id), #new index # doees it have anything to do with the indices?
index(sec_id), #new index
index(semester), #new index
index(year) #new index
);

create table teaches(

id varchar(20),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),


foreign key(course_id) references section(course_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(sec_id) references section(sec_id) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(semester) references section(semester) #department(sec_id) -> section(sec_id)
    on delete cascade,
foreign key(year) references section(year)
    on delete cascade,
foreign key(id) references instructor(id)
    on delete cascade,
primary key(id,course_id,sec_id,semester,year)
);





create table advisor(

s_id varchar(5),
i_id varchar(5),

foreign key(i_id) references instructor(id)
    on delete set null,

foreign key(s_id) references student(id)
    on delete cascade,

#primary key(s_id),
#index(s_id)
);

create table student(

id varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),

foreign key(dept_name) references department(dept_name)
    on delete set null,
primary key(id),
index(id) #new index

);

create table takes(

id varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),

    foreign key(course_id) references section(course_id)
        on delete cascade,

    foreign key(sec_id) references section(sec_id)
        on delete cascade,

    foreign key(semester) references section(semester)
        on delete cascade,

    foreign key(year) references section(year) #section(year) -> section(year)
        on delete cascade,

    foreign key(id) references student(id)
        on delete cascade,

    primary key(id,course_id,sec_id,semester,year)

);


create table prereq(

course_id varchar(8),
prereq_id varchar(8),


    foreign key(course_id) references course(course_id)
        on delete cascade,

    foreign key(prereq_id) references course(course_id),

    primary key(course_id,prereq_id)

);

create table timeslot(

time_slot_id varchar(5),
day2 varchar(1),
start_hr int,
start_min int,
end_hr int,
end_min int,

primary key(time_slot_id,day2,start_hr,start_min)	                                 	        	
);

load data infile 'i:/department.txt' into table department fields terminated by ',';
load data infile 'i:/course.txt' into table course fields terminated by ',';
load data infile 'i:/classroom.txt' into table classroom fields terminated by ',';
load data infile 'i:/section.txt' into table section fields terminated by ',';
load data infile 'i:/instructor.txt' into table instructor fields terminated by ',';
load data infile 'i:/teaches.txt' into table teaches fields terminated by ',';
load data infile 'i:/advisor.txt' into table advisor fields terminated by ',';
load data infile 'i:/student.txt' into table student fields terminated by ',';
load data infile 'i:/takes.txt' into table takes fields terminated by ',';
load data infile 'i:/prereq.txt' into table prereq fields terminated by ',';
load data infile 'i:/timeSlot.txt' into table timeSlot fields terminated by ',';

/* SQL Error (1064): 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 ')' at line 14 */

Open in new window

University.zip
0
Comment
Question by:JCW2
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 34965663
On your advisor table, you commented out the primary key and index and the last foreign key still has a "," at the end, but nothing following it except for the ending ");"
0
 

Author Closing Comment

by:JCW2
ID: 34965733
Thank you for your help.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi: barcode reading on android platform 1 51
T-SQL: Do I need CLUSTERED here? 13 59
SQL replication over high latency link 10 58
SQL Query assistance 16 36
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video teaches viewers about errors in exception handling.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question