Home » SQL & PL/SQL » SQL & PL/SQL » Time and Day problem :( please experts help me!
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..
|
|
|
|
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
|
|
|
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..
|
|
|
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)
|
|
|
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..
|
|
|
Goto Forum:
Current Time: Tue Apr 23 05:39:57 CDT 2024
|