Home » SQL & PL/SQL » SQL & PL/SQL » Help with Creating a Database
Help with Creating a Database [message #428934] |
Fri, 30 October 2009 20:06  |
flash89
Messages: 3 Registered: October 2009
|
Junior Member |
|
|
Can someone help me create this database thanks in advance, this is what I like to do
I am using Oracle 10G Express Edition
There is a database about students and the courses they take. The database has 3 tables: student, course, and registration. The logical definition of the tables is:
Course(c_id,c_name,c_credits)
Student(s_id,s_name,s_major)
Registration(s_id,c_id,semester,year,grade)
where the primary key has been underlined and foreign keys are in italics.
The school has 4 majors: 'CST', 'MAT', 'PSY', and 'NUR'.
The student table has three attributes:
1.s_id which is a 3 digit number,
2.s_name which is up to 20 characters, and
3.s_major a 3 character string which must be one of the majors.
The course table has three attributes:
1.c_code, a 7 character code, which begins with the name of a major and ends with 4 numbers. (Examples might be CST3504 or MAT1209)
2.c_name which is a character string of length at most 20; and
3.c_credits which is an integer between 1 and 4. A course is from a major if c_code begins with the string naming the major.
The registration table records the courses that students take. (It is an associative entity). It has 5 attributes:
1.s_id which is a foreign key pointing to the student table,
2.c_id which is a foreign key pointing to the course table,
3.semester which is one of 'FALL' or 'SPRING',
4.year which is a 4 digit integer, and
5.grade which is one of 'A', 'B', 'C', 'D', or 'F'.
The first 4 attributes together form the key of the registration table
|
|
|
|
Re: Help with Creating a Database [message #428936 is a reply to message #428934] |
Fri, 30 October 2009 20:33   |
flash89
Messages: 3 Registered: October 2009
|
Junior Member |
|
|
create table student(
s_id number(3),
s_name varchar(20),
s_major varchar(3)
);
Create table course(
c_code varchar(7),
c_name varchar(20),
c_credits number(4)
),
so far that is what I have gotten, I am having difficulty setting up the constraints for the table and how to create foreign keys. I have looked up guides but I am still confused, I am new at this.
|
|
|
|
Re: Help with Creating a Database [message #429479 is a reply to message #428934] |
Wed, 04 November 2009 02:13   |
flash89
Messages: 3 Registered: October 2009
|
Junior Member |
|
|
When I try to insert the registration value below I get
SQL> insert into Registration values ('100','CST1100','FALL',2009,'A');
insert into Registration values ('100','CST1100','FALL',2009,'A')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.FK1) violated - parent key not found
I cannot figure out what I have set up incorrectly, I believe the foreign keys are set correctly, or do I have an error in my coding?
drop table supplier cascade constraints;
create table registration(
s_id number(3),
c_id varchar(7),
semester varchar(10),
check(upper(semester) IN ('FALL','SPRING')),
year number(4),
check(length(trim(year))=4),
grade varchar(1),
check (upper(grade) in ('A','B','C','D','F')),
constraint fk1 foreign key (s_id) References student,
constraint fk2 foreign key (c_id) References course,
constraint pk primary key (s_id,c_id)
);
insert into Registration values ('100','CST1100','FALL',2009,'A');
|
|
|
Re: Help with Creating a Database [message #429485 is a reply to message #429479] |
Wed, 04 November 2009 03:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) DO NOT do things like this in the SYSTEM account.
SYSTEM is not for developing applications in - it is for system administration. Go and create yourself another user account and do your development there.
2) Your error message indicates that there is no row in the table STUDENT with a primary key value of 100.
You can deduce this from the message telling you the name of the foreign key that was violated. You look at this foreign key, and that tells you the table that it was looking for a parent key in.
|
|
|
|
Goto Forum:
Current Time: Wed Feb 12 18:09:20 CST 2025
|