Oracle database help?

[wporter]

i am currently working on a project in one of my classes that deals with building a database using oracle, is anyone familiar with this? i need some help with some things, let me know. thanks alot!

[dcasper70]

Whatcha need? Details please.

I've done a good amount of oracle stuff, but it's a such a huge beast that I may be only able to point you in a direction....

[wporter]

yeah im just doing a project thats pretty simple. we just have like 9 tables, and gotta have them connected by PK's and FK's, we also need some sequences and triggers. i just cant figure out what the point of a sequence is, and im havin some problems with the trigger. think you could help?

[dcasper70]

A sequence is actually pretty handy.
Say you have a unique ID field, let's call it Emp_Num (employee number), and every time you insert a new employee you want to give them the next ID number.
Instead of getting the Max(Emp_Num) + 1 in the employee table, just create a sequence. It will keep an incremental list for you.

So an insert can be
Insert into Employee (EMP_NUM, F_NAME, L_NAME)
Values
seqEmployee.NextVal,'Willy','Porter';

Or something like that.


What are you doing with the trigger?

[wporter]

im still trying to figure it out. this is my first database class, and this is our first example. we have a college situation where we have 9 tables, students, departments, faculty, etc. he wants us to have students and classes and to be able to show student schedules. he wants sequences and triggers. the trigger has an audit log which keeps track of the old grade, new grade, user, and sysdate. i have somewhat of an idea on the code for the trigger, but im really confused about some of the simple stuff like which FK should go where, and im not sure my ERD model is correct either. does this sound simple to u?

[dcasper70]

[ QUOTE ]
does this sound simple to u?

[/ QUOTE ]
yes

Hey, your an Oracle noob. Gotta start somewhere...


Feel free to pm me or post some more specific stuff.

[ohgeetee]

for your assignment, every table is going to need a PK or primary key. This is a column that is unique, that you can use to narrow down data later. in the students table, you might think to make name unique at first, but think of how many Jim Smiths there will be, and even keeping middle name there will still be dupes. Using a sequence on a student# field would work best.

for department, you could use a dept ID that is unique, or dept name if there wont ever be departments that are the same name. Most DB purists will say to always use a sequenced ID field.

when coming up with foreign keys, you need to figure out what type of relationship the items have. 1 to 1, 1 to many, or many to many. 1 to 1 are items generally in the same table, so 1 to many and many to many are the 2 types you will be dealing with. For instance classes. classes can have many students, but students can only take the class once, generally. this means that in your class table, you want your student_id field as a foreign key.

many to many is a bit trickier, it takes a middle table to allow the many - many link. lets say a teacher could belong to multiple departments for some reason, and of course a department could have multiple teachers. to setup this relationship, you would have teacher table with teacher_id as its pk, department table with department_id as its pk, then a table called teacher_department that has teacher_id and department_id, which tracks the links.

pk's define tables, fk's define relationships

[dcasper70]

OP pm'd me a list of tables and pks/fks.

Here's what it looks like graphically:

[buckslayer80]

LOL Oraclements [img]/images/graemlins/shocked.gif[/img]

[kerowo]

Shouldn't a class have a department?