OraFAQ Forum: SQL & PL/SQL » Time and Day problem :( please experts help me!

Home » SQL & PL/SQL » SQL & PL/SQL » Time and Day problem :( please experts help me!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time and Day problem :( please experts help me! [message #169034]

Mon, 24 April 2006 15:07

Rose_250
Messages: 17
Registered: April 2006

Junior Member

Hi every body,

I am using deveoper2000 R5, SQL and also PL*SQL.. I am preparing scheduling Information System for my graduation project.I made tables in SQL BD(courses, instructors, faculty, department, and rooms), and i also made the forms..

I have to generate a standard report (including include the courseid, coursename, instructor name, no. of credit hours, day, time, room, section..etc). The system should assign a course for ,example, in day & time with a specific room.

In each day, we have lectures from 8:00a.m to 8:00p.m
I divide the time either 1 hours (8-9a.m,9-8...7-8p.m) or
1:30 hours (8 - 9:30a.m,...6:30-8p.m)
Assume that the time (for attending a course) should be divided in to 3 options:
either 1 lecture per week; can be in (Sat,Sun,Mon,Tues,Wed)
or 2 lecture per week (Sun - Tues)
or 3 lecture per week (Sat-Mon-Wed)
I have tried to make create view, but how can i make it with multiple table? should i join them with common fields?

should I create another table that include timefrom, timeto, day, roomname, section?and if i create it I should make date datetype for timefrom and timeto, and I don't want the date to be displayed?!!!!

or shall I make a scheduling_table and then add timefrom, timeto,day, roomname, section fields and then declare them in the developer (using pl/sql)? and in the code; it may include loop that fixes time/day probelm, for example, 8-9 need ++1 and 8-9:30 need ++1 and half?

Please I want to finish this as soon as possible, I want to graduaaaaaaate plz...Can any body guide my?!!!

Becuase I am a begginer in learing oracle, and I know this project is very difficult but with your cooperation there is no difficulties..

Thanks for people who share their knowledge and experiences..

Report message to a moderator

Re: Time and Day problem [message #169046 is a reply to message #169034]

Mon, 24 April 2006 16:28

gssunil
Messages: 7
Registered: January 2006
Location: Chicago

Junior Member

Rate me[ 5 votes ]
1.	I feel the same	3 / 60%
2.	Not a good idea	1 / 20%
3.	You are good	1 / 20%

Yes, there should be a table similar to Scheduling_tbl with Structure as follows: There should be a form to enter data into it.

Instructor,
CourseID,
Date,(Day should be derived from Date)
Time_Slot,
Room

The form will be filled in when an instructor is assigned a Time_slot and room.

Now time_slot should have 20 values predefined (12 hourly values and 8 - 1.5 hour values eg. 1230-1400 would be one value)

The best way to create a report is not the availability report but an existing Assignment report.

You can of course join tables with common columns to create a view for the report.

Let me know if you need more info on this.

Report message to a moderator

Re: Time and Day problem [message #169328 is a reply to message #169046]

Wed, 26 April 2006 05:08

Rose_250
Messages: 17
Registered: April 2006

Junior Member

Hi Mr. Sunil

Thanks for your reply..

I have created the Timetable with the following field
CREATE TABLE Timetable
( SECTION NUMBER(3) PRIMARY KEY,
TIME_FROM DATE,
TIME_TO DATE,
DAY VARCHAR2(12),
CLASSROOM VARCHAR2(5)
);

Section is become like a serial number(1,2,3,....)TO PREVENT DUPLICATION, and this section should be linked to the courses_table as a foreign key..

The problem is how I can enter the value of the time
and how I can derived the day from it..

shall I use to_char or to_date.. i just want to display the day and time without the date...how i can do that?!

Please see the attached file..

Thanks in advance

Attachment: TIMETABLE EXAMPLE.xls
(Size: 14.00KB, Downloaded 586 times)

Report message to a moderator

Re: Time and Day problem [message #169636 is a reply to message #169328]

Thu, 27 April 2006 18:30

Rose_250
Messages: 17
Registered: April 2006

Junior Member

Hi Experts,

Would you please guide me?

Is it better to use varchar2 (data type) for time_from and time_to instead of date?!!

and about the day, shall I also use varchar2 to enter 3 days in one time, for example,

Time_from Time_to Days
--------- -------- --------
8:00 AM 9:00 AM Sat-Mon-Wed

or there is another way to do that?!!!

Thanks in advance..

Report message to a moderator

Re: Time and Day problem [message #170004 is a reply to message #169636]

Mon, 01 May 2006 12:26

andrew again
Messages: 2577
Registered: March 2000

Senior Member

You would always try to store date & time in date datatypes (or timestamp if you needed extra precision) beciase it will:
1.) always ensure that values stored are in a valid range (0-23 hrs, 0-59 min, sec)
2.) you can do date arithmetic on the values
3.) they sort correctly.
4.) am/pm is a locale specific representation
5.) ideally you should store multiple days separately - not as a list. The correct way is to have a many-many mapping between your table and a table containing the 7 days of the week - or as a set of app controlled permitted values. Having said that, it can be valid to have repeating fields like days of the week (although it breaks normalization rules). It enforces than each week only has exactly 7 days, and that each day occurs only once. That is difficult to enforce in a parent-child relationship.

To store just the time component - use a fixed date outside your regular range of valid dates) e.g.
insert into my_tab(my_date_col) values to_date('01-01-1980', 'dd-mm-yyyy') + 16/24; (4pm)

Report message to a moderator

Re: Time and Day problem [message #170436 is a reply to message #170004]

Wed, 03 May 2006 17:53

Rose_250
Messages: 17
Registered: April 2006

Junior Member

A big thanks for you Mr. Andrew,
Thats exactly what I want to know and what I want to understand..But what I didn't understand how can I make this type of relationship in SQL,i.e., manay to manay relationship between these two tables;

I appreciate your replay and thank you again..

Report message to a moderator

Previous Topic:	Update values of a column
Next Topic:	Oracle sqldeveloper

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:08:19 CDT 2024