Due Date: December 3 (Thursday)
(1) Use Oracle Designer 2000 to create a reasonable ER diagram for the following database application. Generate the server model diagram and the DDL files. Execute the DDL files to create the tables, sequences, indices and primary keys.
Turn in:
The School of Natural and Applied Sciences (SNAS) needs to build a student advising database and you are asked to build a drastically simplified version. Only the name and phone of a person (a student or a faculty member) are needed to be stored. However, for a faculty member, an unique faculty id must also be recorded. A student majors in only one program. Each program has many faculty members. A faculty member may work for many programs. For example, Dr. Liaw is a faculty member for both the CS and CIS programs. A student has a faculty advisor and may see the advisor many times. A faculty advisor may record a note in an advising section for a student. The content and the date of every note must be stored.
(2) Write a Java program TableLister.java to list the contents of user supplied tables. Your program should automatically connect to your addb database account in Pioneer. When the user types a table name, your interpreter will list the content of this table. Additional commands supported by your interpreter should include: all (list all table names), help & ? (help) and exit (quit the interpreter). A session of the program below will explain the concept clearly. User input is in the color red. Note that the table is well-aligned (except for line wrapping) and there is a line separating the heading and the contents.
Store your program in the top level as TableLister.java in your Pioneer account. Turn in the program listing. Your program will be test run.
z:>java TableLister
Welcome to the Oracle Table Lister.
Table Lister>all
All tables in this database:
CALENDAR_EVENT
CALENDAR_UNIT
CATEGORIES
CLASSES
COURSE
CUSTOMERS
DEBUG_TABLE
DESTINATION
EMP
EMPLOYEE
EMPLOYEES
ENROL
EXCEPTION_TABLE
EXCEPTION_VIEW
H1_ADMINISTRATOR
H1_CHOICE
H1_COMMENT
H1_KEYWORD
H1_PERSON
H1_POSTTIME
H1_PROBLEMKEYWORD
H1_VOTE
H1_VOTINGPROBLEM
H3_COURSES
H3_ENROL
H3_MAJORS
H3_RAW_ENROL
H3_STUDENTS
HITS
LOG_TABLE
MAJOR_STATS
MYTABLE
NASNEWS_NEWS
NASNEWS_SETTER
ORDERS
ORDER_DETAILS
PERSONOBJECTS
PRODUCTS
REGISTERED_STUDENTS
ROOMS
RS_AUDIT
SHIPPERS
SINGLEROWTABLE
SOURCE
STUDENT
STUDENTS
SUPPLIERS
TEMP_TABLE
Table Lister>help
Type the table name to list its contents
or use the following commands:
? :
this help menu.
help: this
help menu.
all : list
all table names.
Table Lister>?
Type the table name to list its contents
or use the following commands:
? :
this help menu.
help: this
help menu.
all : list
all table names.
Table Lister>students
ID
FIRST_NAME
LAST_NAME
MAJOR
CURRENT_CREDITS
-----------------------------------------------------------------------------------------------------------------------
10000
Scott
Smith
Computer Science
3
10001
Margaret
Mason
History
3
10002
Joanne
Junebug
Computer Science
3
10003
Manish
Murgratroid Economics
3
10004
Patrick
Poll
History
3
10005
Timothy
Taller
History
3
10006
Barbara
Blues
Economics
3
10007
David
Dinsmore
Music
3
10008
Ester
Elegant
Nutrition
3
10009
Rose
Riznit
Music
3
10010
Rita
Razmataz
Nutrition
3
10011
Bun
Yue
Computer Science
9
10012
to
smith
Computer Science
null
150
So
So
Computer Science
3
20000
Bill
Clinton
Computer Science
10
20010
Bill
Gates
Computer Science
60
20030
Bill
Parson
Computer Science
3
20040
Bill
Lee
Computer Science
null
20050
Major
Lee
History
null
Table Lister>exit
Thank you for using the Table Lister