Home » SQL & PL/SQL » SQL & PL/SQL » Help with Creating a Database
icon4.gif  Help with Creating a Database [message #428934] Fri, 30 October 2009 20:06 Go to next message
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 #428935 is a reply to message #428934] Fri, 30 October 2009 20:16 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Can someone help me create this database

I don't think so.
I believe you need to make a schema.

Visit http://tahiti.oracle.com

read Concept Manual & SQL Reference Manual

CREATE USER

CREATE TABLE

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.




Re: Help with Creating a Database [message #428936 is a reply to message #428934] Fri, 30 October 2009 20:33 Go to previous messageGo to next message
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 #428937 is a reply to message #428936] Fri, 30 October 2009 20:48 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I am having difficulty setting up the constraints for the table and how to create foreign keys.

The SQL Reference Manual has examples.

http://asktom.oracle.com contains many, many fine coding examples.

Re: Help with Creating a Database [message #429479 is a reply to message #428934] Wed, 04 November 2009 02:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Help with Creating a Database [message #429543 is a reply to message #429485] Wed, 04 November 2009 08:24 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And S_ID is a number, yet you are inserting a string into that column.
VARCHAR2 is the proper datatype for strings.
Previous Topic: attach pdf file from local computer and send as email
Next Topic: Need help converting Access SQL to Oracle
Goto Forum:
  


Current Time: Sun Dec 04 10:32:51 CST 2016

Total time taken to generate the page: 0.05368 seconds