PROFESSOR: WHAT WE'LL
DO TODAY IS BASICALLY GIVE YOU A BIRD'S
EYE VIEW OF WHAT I PUT INTO THE CLASS
NOTES ABOUT ORACLE AND IF YOU HAVE NOT
STARTED IT'S GOOD TIME TO START INSTALLING
ORACLE 8, PERSONAL ORACLE AND TODAY I GO
THROUGH THE ESSENTIALS YOU NEED TO KNOW.
SO GET THROUGH THE SECOND EXERCISE.  SO IF
YOU GO TO THE ORACLE NOTES YOU'LL NOTICE
THAT QUITE A LOT OF MATERIALS AND THE
PREVIOUS ASSISTANT WORKED IT UP AND DID A
GOOD JOB OF CREATING THOSE NOTES.  LUCKILY
THEY'RE NOT OUT OF DATE.  MAYBE YOU WILL
FIND ONE OR TWO PLACES IT'S INCONSISTENT
WITH THE VERSION YOU HAVE BUT IN GENERAL
IT'S GOOD.  SO WHERE YOU SHOULD START IS
WITH THE GENERAL DIRECTIONS.  I GO THROUGH
THAT IN A MOMENT.  BUT IN THE GENERAL
DIRECTIONS A LOT OF MATERIALS SQL YOU
DON'T HAVE TO LOOK AT THAT RIGHT NOW.
THAT WILL COME LATER.  ALSO SOME IS ABOUT
VERSION 7 WHICH IS OBSOLETE.  DON'T WORRY
ABOUT THAT.  GUY OVER THE ESSENTIAL WITH
YOU.  THE MAIN THING YOU NEED TO DO IS TO
BE ABLE TO CREATE A NEW USER PROFILE,
WE'LL GO THROUGH THAT.  CREATE A NEW
PROJECT, AND THEN BE ABLE TO DEFINE YOUR
DATABASE AND THEN POPULATE IT.  THAT'S THE
CREATE NEW TABLE, CREATE NEW VIEW AND
FINALLY ENTER NEW INFORMATION INTO THE
DATABASE.  AND LUCKILY AS YOU CAN SEE FROM
ORACLE 8, EVERYTHING CAN BE DONE ALMOST
PAINLESSLY USING THE NAVIGATOR.  SO THESE
ARE THE THINGS YOU NEED TO READ AFTER I GO
THROUGH THIS WITH YOU TODAY.
     THEY ARE -- THERE ARE ALSO SECTIONS
EXPLAINING TO YOU THE UNIX VERSION OF
ORACLE AND HOW TO PROGRAM IN THE
PROGRAMMING LANGUAGE WITH SQL.  AND HOW TO
ADD NEW FUNCTIONS.  THESE YOU DO NOT NEED
FOR THE SECOND ASSIGNMENT, OKAY?  IN OTHER
WORDS, AND ALSO UNLESS YOU USE THE UNIX
VERSION YOU DON'T NEED TO BOTHER WITH
THAT.  BUT THIS IS THERE.  SO THAT YOU CAN
LOOK IT UP IF YOU ARE INTERESTED.  AND
THEN THIS PART, PROGRAMMING FOR ORACLE
USING EXCEL AND VISUAL BASIC YOU DON'T
NEED IT NOW BUT IT WILL BE VERY HELPFUL
FOR EXERCISE 4.  IN EXERCISE 4 I'M GOING
TO ASK YOU TO CREATE USER INTERFACE FOR
DATABASE SYSTEM.  OF COURSE, THERE ARE
MANY WAYS TO DO THAT, BUT IN EXERCISE 4 I
KILL ASK YOU TO CREATE A STANDARD VERSION.
INTERESTINGLY IN THE CD HAVE YOU THERE ARE
EXAMPLES OF USING EXCEL TO DO IT.  EXCEL
IS SPREAD SHEET.  NORMALLY YOU WOULDN'T
THINK YOU COULD CREATE USER INTERFACE
USING THAT, BUT THE BEAUTY OF NEW
INTEGRATED ENVIRONMENTS THAT YOU CAN
COMBINE THE FEATURES AND DATA FROM MANY
DIFFERENT TOOLS.  TO DO IT.  SO HERE THERE
ARE EXAMPLES TO SHOW YOU HOW YOU CAN
EXPORT DATA FROM EXCEL TO DATABASE AND
IMPORT DATA FROM DATABASE TO EXCEL.
THAT'S VERY USEFUL IF YOU GO TO WORK
BECAUSE MANY SECRETARIES, WHAT THEY KNOW
IS SPREAD SHEET BECAUSE THEY USUALLY LEARN
THAT SO THAT THEY CAN EASILY FILL OUT
TABLES BUT THEY DON'T KNOW ANYTHING ABOUT
DATABASE.  NOW SPREAD SHEETS VERY USEFUL.
UNFORTUNATELY IF YOU PUT SOMETHING IN
SPREAD SHEET IT'S DIFFICULT TO QUERY THAT.
WITH THE TOOLS WE HAVE NOW WE CAN EXPORT
DATA IN THE DATABASE AND LATER ON RELOAD
IT INTO EXCEL.  THAT WOULD MAKE IT EASY
AND MANY STUDENTS SAID THEY FOUND THIS
STUDENT VERY USEFUL AT WORK, THE STUDENTS
FROM INDUSTRY.  SO THIS YOU DON'T HAVE TO
READ IT NOW, LATER FOR THE FOURTH
ASSIGNMENT IF YOU ARE TO US EXCEL ORACLE
COMBINATION THAT'S A GOOD EXAMPLE.  LIKE I
SAID, THIS IS NOT ONLY OPTION.  YOU CAN DO
IT IN VISUAL BASIC.  THERE ARE MANY WAYS
OF DOING IT NOW.
     FINALLY, THERE ARE SECTIONS TALKING
ABOUT HOW TO INSTALL WEB BASE AND WEB BASE
EXAMPLES.  THIS WILL COME LATER WHEN YOU
DO THE PROJECT.  AS I SAID, I ASK YOU TO
GET THE TRI VERSION AND -- THE TRIAL
VERSION AND THEY WILL ALLOW TO YOU DO IT
FOR 60 DAYS, LONG TO DO THE PROJECT.  AS
YOU CAN SEE IT'S A LOT OF GOOD MATERIAL
FOR YOU TO USE BUT MOST IMPORTANT THING IS
REALLY THE FIRST FIVE, GENERAL DIRECTIONS
AND HOW TO CREATE -- HOW TO CREATE A NEW
USER AND DATABASE.
     OKAY.  LIKE I SAID IF YOU CLICK ON
GENERAL DIRECTIONS YOU GO TO A GLOBAL
OVERVIEW ABOUT ORACLE.  NOW, ORACLE NOW OF
COURSE IS PROBABLY THE MOST LUCRATIVE
DATABASE COMPANY AROUND.  IT STARTED BY A
FEW GUYS FROM BERKELEY AND THEY ARE --
THEY ARE DATABASE PROFESSORS AT UC
BERKELEY AND AT FIRST THIS WAS NOT -- IT
WAS NOTHING BUT THEN IT KEEP ON GROWING --
GROWING AND GROWING.  NOW ORACLE APPROACH
IS PRIMARY RELATIONAL DATABASE APPROACH
NAMELY THEIR PREVIOUS PRODUCTS UP TO
VERSION 7 IS PRIMARY RELATION.  THE
VERSION YOU HAVE VERSION 8 AND SUBSEQUENT
ENHANCEMENTS THEY CLAIM THEY CAN CORPORATE
OBJECT ORIENTED FEATURES SO THE VERSION
YOU HAVE DOES HAVE SOME OBJECT ORIENTED
FEATURE AND THEY CLAIM THEY CAN SUPPORT
MULTIMEDIA.  SO THIS IS VERY POWERFUL.
OKAY.
     NOW, THE WAY THAT THEY CAN SUPPORT
FOR EXAMPLE MULTIMEDIA IS INSTEAD OF JUST
SCREENS ON NUMBERS THEY ALLOW YOU TO PUT
BINARY OBJECTS INTO THE TABLE.  AND BINARY
OBJECTS.
     COME IN.  THERE IS A SEAT OVER THERE.
     SO BINARY OBJECTS CAN BE HUGE, CAN BE
SEVERAL GIGA BYTES.  SO IF YOU HAVE AUDIO
CLIP OR VIDEO CLIP FOR THAT MATTER, YOU
CAN ACTUALLY STICK IT INTO THE DATABASE
AND OF COURSE IF HAVE YOU TO WRITE
PRESENTATION TOOLS YOU CAN PRESENT THAT.
SO THIS CERTAINLY GREAT, BECAUSE THIS
MEANS A DATABASE CAN BE USED NOT ONLY JUST
FOR RECORD KEEPING OF STRINGS AND NUMBERS
YOU CAN ACTUALLY HAVE MULTIMEDIA OBJECTS
THERE.  BUT OF COURSE YOU PAY A BIG PRICE.
IN TERMS OF THE PERFORMANCE.  THAT BEING
SAID, IT'S STILL QUITE USEFUL.
     SO THE NEW VERSION THEY CALL IT
RELATIONAL OBJECT DATABASE, IN OTHER
WORDS, IT DOES HAVE THE OBJECT FLAVOR NOW.
     THE SERVER CAN RUN ON MASTER MODE SO
THAT EVERYBODY GOES TO THE HOST AND HOST
PROVIDE THE SERVICE OR IT CAN BE IN THE
CLASSICAL CLIENT SERVER ENVIRONMENT.
     SO, THE PERSONAL ORACLE 8 YOU NOTICE
THAT IT HAS MANY FEATURES THAT ARE
PREVIOUSLY AVAILABLE ONLY ON LARGE
DATABASE SYSTEMS, FOR EXAMPLE, YOU CAN
DEFINE USER VIEWS, YOU CAN DEFINE
SNAPSHOTS AND YOU CAN CREATE YOUR OWN USER
AND THE PRIVILEGE OF THE USER.  THAT'S
VERY NICE.  THIS MEANS THAT YOUR OWN
PERSONAL COMPUTER CAN ALMOST, I SAY
ALMOST, NOT QUITE, BECOME A GOOD SERVER.
IF YOU HAVE GOOD SECURITY.  THE MAIN
PROBLEM IS USUALLY SECURITY.  IF IT IS NOT
TOTALLY SECURE IT'S HARD TO MAKE IT TO
BE -- HARD TO MAKE PC TOTALLY SECURE, SO
STILL NOT ON PAR WITH THE BIGGER SYSTEMS.
     OKAY, SO THE ORACLE 8 THEY HAVE SECURITY
MECHANISM, BACK UP RECOVERY, SPACE
MANAGEMENT, OPEN CONNECTIVITY, BY THAT I
MEAN IT'S QUITE EASY FOR US TO CONNECT
ANYTHING TO ANYTHING USING ODBC.  THIS
MEANS THAT YOU CAN EASILY PUT YOUR
DATABASE SOURCE EITHER ON THE SAME MACHINE
OR SOMEWHERE ELSE.  WITHOUT TOO MUCH
TROUBLE.  ALL WE HAVE TO DO IS SET UP A
TABLE, ODBC MAPPING TABLE.  THIS IS GREAT.
THIS MEANS WE CAN DEVELOP APPLICATIONS.
WITHOUT WORRYING ABOUT WHERE IS THE DATA.
WHY?  BECAUSE THE MAPPING TABLE, THIS ODBC
WE CAN STUDY LATER.  BUT ONCE IT WORKS
THEN WE CAN EVEN HAVE THE DATABASE
DISTRIBUTED AND IT WILL STILL WORK.  THIS
MEANS IT MAKE THE JOB OF THE DEVELOPMENT
ALSO MUCH EASIER.  ALL THESE ARE VERY GOOD
THINGS.
     AND LIKE I MENTIONED, THERE ARE
EXTENSIONS OF THE ORACLE TO SUPPORT
MULTIMEDIA.  YOU CAN PUT VIDEO OBJECT, YOU
CAN PUT SPACIAL OBJECTS, SPACIAL DATA, YOU
KNOW MAPS, STUFF LIKE THAT, AND STICK THAT
IN THE DATABASE.  OF COURSE YOU CANNOT
EXPECT THE PERFORMANCE WILL BET SAME ONCE
YOU USE THOSE BLOBS.  THOSE BIG BINARY
OBJECTS.  SO ONCE YOU HAVE THOSE BINARY
OBJECTS IT REALLY SLOW YOU DOWN.
     HOWEVER IT'S STILL GOOD TO HAVE.  AND
THEN THE SQL LANGUAGE, WE COVER -- WE'LL
COVER THAT LATER ON, WE HAVE FORMS AND
REPORT GENERATOR AS PART OF THE SYSTEM.
     OKAY.  SOME TERMINOLOGY.  FIRST,
DATABASE.  WE ALL KNOW DATABASE IS
BASICALLY A COLLECTION OF RELATED EITHER
TABLES OR RELATED DATA ITEMS, RELATED
OBJECTS.  NOW THE MAIN THING HERE IS THAT
IF YOU HAVE A DATABASE, EVERYTHING IS
RELATED TO EVERYTHING ELSE.  THAT'S WHY
WHEN YOU GUYS DRAW ER DIAGRAM EVERYTHING
IS LINKED.  WHAT IF SOMETHING'S NOT
LINKED?  IF THE GRAPH IS DISCONNECTED?  IS
THAT ONE DATABASE OR TWO?  IT'S TWO.  IN
OTHER WORDS THE MOST IMPORTANT THING OF
DATABASE IS THIS INTERRELATED MASS SO WHEN
I DRAW ER DIAGRAM IT'S A RELATED DIAGRAM.
IF IT TURNS OUT THAT YOU CAN DRAW TWO
DIAGRAMS AND THEY ARE NOT NECESSARILY
CONNECTED, WHAT IN FACT YOU HAVE WILL BE
TWO SEPARATE DATABASES.  AND REASON IS
ALSO INTUITIVELY CLEAR IF THEY ARE NOT
RELATED, THIS MEANS WHEN YOU ARE USING
DATABASE ONE YOU ARE NOT USING DATABASE 2
OR VICE VERSA.  BECAUSE IF YOU ARE USING
BOTH THEN BY DEFINITION IT MUST BE
RELATED.  OKAY.  SO THAT CAN ALSO BE SEEN
AS A WAY OF TESTING.  IF YOU COME UP WITH
THOSE GRAPHS THAT ARE NOT CONNECTED YOU
BASICALLY HAVE SEPARATE DATABASES AND FOR
EACH SEPARATE DATABASE WE HAVE A
COLLECTION OF TABLES AND ASSOCIATED
PROGRAMS.
     ANOTHER NAME IS TABLESPACE.  THIS
MEANS THE PHYSICAL WAY, HOW WE STORE THOSE
TABLES, THIS RELATIONAL DATABASE IS
BASICALLY A CORRECTION OF TABLES.  AND A
TABLE HOLDS THE DATA FROM THE ORACLE
DATABASE.  AND FROM THE SYSTEM POINT OF
VIEW, IT HAPPENS THAT THE TABLE, TABLE
SPACE CORRESPONDS TO THE FILE, TO THE DATA
FILE.  THIS IS JUST A QUESTION OF
VIEWPOINTS.  SO IF I'M LOOKING AT MY FILE
SYSTEM, I CALL THEM FILES.  IF I LOOK AT
IT FROM THE DATABASE POINT OF VIEW I CALL
THEM TABLES.  AND SINCE THE TABLE ACTUALLY
OCCUPY PHYSICAL SPACE, MY STORAGE DEVICE,
IT CORRESPONDS TO THE TABLE SPACE.  AND
THEN OF COURSE THE OBJECTS.  THE STRUCTURE
DEFINED BY THE SQL STATEMENT, THE VIEWS,
SNAPSHOTS, THOSE KIND OF THING.  AND
INSTANCE IS A PORTION OF THE COMPUTER
MEMORY TO ACCESS AN OBJECT ORACLE DATABASE
AND FINALLY, APPLICATION.  IT'S A SET OF
PROGRAMS TO SOLVE SOME PARTICULAR PROBLEM.
     ALL THESE DETAILS ARE IN THE ONLINE
CLASS NOTES THAT YOU CAN TAKE A LOOK AT
LATER ON.  FOR THE ORACLE DATABASE, LIKE I
MENTIONED, BASICALLY WHAT YOU WANT TO
CREATE ARE TABLES BECAUSE THE TABLES ARE
WHAT WE CAN STORE THE DATA.  SO, ONCE YOU
CREATE YOUR USER AND YOUR PROJECT, NEXT
THING YOU DO IS TO CREATE THE TABLES.  AND
THE TABLES HAVE PHYSICAL EXISTENCE, SO
ONCE YOU CREATE A TABLE YOU CAN POPULATE
THE DATABASE BY INSERTING ALL INPUT DATA
INTO THE TABLES.
     ONCE YOU HAVE THE TABLES, YOU CAN
DEFINE ONE OR MORE VIEWS.  THE VIEWS ARE
VIRTUAL RELATIONS.  THEY ARE DEFINED ON
TOP OF THE TABLES.  I THINK I GAVE THE
EXAMPLE PREVIOUSLY, SO IF I HAVE SOME BASE
TABLE, AND I DECIDE TO EXTRACT PART OF IT,
THAT'S A VIEW.
     BUT IT'S MORE POWERFUL THAN THAT.
HERE I'M JUST DOING A SO-CALLED
PROJECTION.  I'M JUST TAKING SOME COLUMNS,
SUBSET AND THEN PUT IT THERE.  BUT WHAT IF
I ONLY WANT CERTAIN TYPE?  LET'S SAY THIS
IS, AGAIN, THE OLD FRIEND, EMPLOYEE
RELATION AND HAVE SALARY FIELD.  IF I WANT
TO DEFINE A TABLE WHERE THE SALARY IS OVER
50,000 I'M LOOKING AT THOSE EMPLOYEES WITH
HIGHER SALARY, THAT'S A VIEW.  HOW IS THIS
FIELD DEFINED?  HOW IS THIS VIEW DEFINED?
IT HAS SAME NUMBER OF COLUMNS, SAME NUMBER
OF ATTRIBUTES BUT THE RECORDS OR THE ROWS
IN THIS VIEW MUST SATISFY A CONDITION AND
THE CONDITION BEING?
     STUDENT:   SALARY OVER 50,000.
     PROFESSOR:   OKAY.  SO YOU CAN SEE
IT'S ALMOST LIKE A QUERY, RIGHT?  A QUERY
IS TO STATE CERTAIN CONDITIONS AND
SUBTRACT THAT BUT THE QUERY IS DYNAMIC THE
USER ENTRY QUERY AND PROCESS AND GET
ANSWER.  THE VIEW IS WHAT WE SOMETIMES
CALL LATENT QUERY.  IT IS WILL PREDEFINED.
PREDEFINED I MEAN WE PREDEFINE THE
CONDITION IN THIS CASE SALARY OVER 50,000.
SO GIVEN THE CONDITION WE CAN EXTRACT
THOSE TABLES TO CONSTRUCT THE VIEW.  DOES
VIEW EXIST?  DOES IT EXIST PHYSICALLY?
NO.  OKAY.  IT DOESN'T OCCUPY STORAGE.
WELL, OF COURSE, THE DEFINITION OCCUPIES
SOME STORAGE BUT IN ESSENCE IT'S
CONSTRUCTED WHERE WE NEED TO VIEW -- A
USER, AN APPLICATION NEEDS TO ACCESS IT.
     STUDENT:   YOU CAN RUN INSERT OR
UPDATE IT TO A VIEW OR DO YOU NEED TO DO
SOME SPECIAL PROGRAM --
     PROFESSOR:   DEPENDING ON THE SYSTEM
YOU TALKING ABOUT.  NORMALLY THE ANSWER IS
NO.  ALSO YOU DON'T WANT TO DO THAT. WHY?
     STUDENT:   BECAUSE THEY MIGHT BE
VIOLATING THE RULES OF THE VIEW WHEN THEY
DO THE INSERTION.
     PROFESSOR:   RIGHT.  AND ALSO
REMEMBER, I'M NOT JUST SUPPORTING ONE
VIEW.  I COULD BE SUPPORTING MULTIPLE
VIEWS IF I ALLOW ALL THESE APPLICATIONS TO
BE ABLE TO UPDATE THE VIEWS IT MAY CREATE
A LOT OF PROBLEMS.  I'D RATHER GIVE SOME
APPLICATION THE AUTHORITY TO UPDATE THE
BASE RELATIONS.
  OKAY.  AND THEN WE'LL
GET -- DEFINE INDEXES.  WE HAVEN'T TALKED
ABOUT IT, LATER WHEN WE TALK ABOUT
PHYSICAL DATA STRUCTURES, AND WE CAN
INTRODUCE OTHER INDEXES.  NOW, BY THE WAY,
ONE THING WE ARE PROGRESSING QUITE WELL,
SO ALTHOUGH ON THE SCHEDULE IT SAYS
THAT -- LET ME GO TO THE SCHEDULE FOR A
MOMENT.  WE'RE GOING TO SPEND ONE
WEEK-OKAY.  SO HERE WE ARE.  WE ARE
TALKING ABOUT ORACLE FUNDAMENTALS.  SO
NEXT WEEK WE'LL TALK RELATIONAL MODEL,
RELATIONAL ALGEBRA.  WE ARE PROGRESSING
WELL.  I WILL SQUEEZE THAT INTO ONE
LECTURE AND MOVE SQL UP.  THIS WAY WE'LL
SPEND MORE TIME ON PHYSICAL STRUCTURES.
INDEXES -- INDEXING, CONSTRUCTING B TREES.
ACTUALLY THESE ARE STUFF YOU'RE SUPPOSED
TO LEARN FROM THE DATA STRUCTURE CLASS BUT
I ALWAYS FIND SOME STUDENTS DON'T EVEN
KNOW.  LET ME TRY IT ASK, HOW MANY OF YOU
ALREADY BEEN EXPOSED TO B TREES?  GOOD.
OKAY.  HOW MANY BE EXPOSED TO HASHING?
OKAY.  SO WE'RE ALL SET.  SO WE'LL TALK
ABOUT B TREES, EXTENDED -- EXTENDED
B-TREES AND EXTENDIBLE HASHING.  THESE
PARTS ARE MORE TECHNICAL.
     STUDENT:   IT WAS NOT REAL INDEPTH.
     PROFESSOR:   I UNDERSTAND OF THE AT
LEAST YOU HEARD ABOUT IT.  BECAUSE WE HAVE
ONLY A WEEK.  SOMETIMES IT'S NOT ENOUGH.
SO WE HAVE -- WE'LL THINK ABOUT THAT.
     OKAY.  GOOD.  SO WE TALK ABOUT INDEX
AND SYNONYM IS THE -- SOMETIMES CALLED
ALIASES SO THAT WE CAN -- THIS IS TO
FACILITATE APPLICATION DESIGN.  SOMETIMES
HAVE YOU ONE GROUP WORKING ON A PROJECT
ANOTHER GROUP WORKING ON THE SAME PROJECT
AND IT'S JUST MORE CONVENIENT FOR THEM TO
CALL IT DIFFERENT NAME.
     AND THEN THEY ARE PRIVILEGES THAT ONE
CAN GRANT.  EVEN YOU'RE THE ONLY PERSON
USING YOUR PC, YOU SAY WHAT HECK, YOU GIVE
YOURSELF ALL OF THE PRIVILEGES.  SURE, YOU
CAN DO THAT.  BUT SOMETIMES ADVISABLE TO
GIVE YOURSELF LESS PRIVILEGE.  JUST LIKE
YOU HAVE -- AT HOME YOU-DID THE
REFRIGERATOR AT HOME.  YOU HAVE TO LIMIT
YOUR PRIVILEGE OR YOU OVEREAT.  OR THE ROW
IS THE GROUP OF PRIVILEGE THAT
COLLECTIVELY CAN BE GRANTED TO USERS.  SO
IT'S LIKE PROFILE.
     OKAY.  SO NOW WE GO THROUGH A LITTLE
BIT DETAIL.  THIS MAY BE TOO SMALL FOR YOU
BUT PLEASE PRINT OUT THE CORRESPONDING
PAGES ON IN THE NOTES.  NOW THE BASIC
STEPS THAT YOU'LL GO THROUGH AND I ASSURE
YOU IT WILL TAKE ONE HOUR OR SO.  BUT
PEOPLE WHO ARE EXPERIENCE PC USERS MAYBE
EVEN LESS TIME.  YOU NEED TO GO THROUGH IS
INSTALL THE ORACLE 8 AND THEN YOU GO
THROUGH THE MOTION ADDING NEW USERS,
ADDING NEW PROJECTS, AND CREATING NEW
TABLES AND CREATING NEW VIEWS AND FINALLY
ADDING SOME DATA INTO YOUR TABLE.
EVERYBODY CAN DID THAT QUICKLY.  NOW ONCE
HAVE YOU THAT, THEN YOU CAN DO THE QUERY
USING SQL.
     WHEN YOU TRY TO CREATE A TABLE YOU'LL
COME ACROSS THE TERM SUCH AS CHAR, THAT OF
COURSE IS CHARACTER ALPHA NUMERIC FIELD.
DATE.  LONG VARIABLE, LONG RAW.  THAT'S
WHAT I MENTIONED.  THIS IS WHERE YOU HAVE
LIKE VIDEO CLIP YOU CAN DEFINE THAT
ATTRIBUTE TO BE LONG RAW, A BLOB, THEN YOU
CAN INSTALL UP TO -- THAT'S THE OLDER ONE,
2GIGA BYTE.  NOW YOU CAN STORE SOMETHING
DECENT THERE.  IT'S PROBABLY MORE NOW.
THIS DOES SLOW THINGS DOWN.
     IT CAN HAVE NUMBER, RAW OBJECT.
THESE ARE SMALL BINARY OBJECTS UP TO 255
BYTES.  SMALL THINGS.  RAW ID'S THESE ARE
THE TYPE OF ID?
 VARCHAR THIS IS THE CHARACTER'S ALPHA NUMERIC
SCREEN BUT IT CAN BE LONGER BUT IF YOU HAVE
JUST NAMES AND ADDRESSES, NOW I'M GOING TO USE
YOUR CHARACTER FIELD WITH THE PREVIOUS -- IT'S
ONLY WHEN YOU HAVE LET'S SAY LIBRARY
INFORMATION SYSTEM, WE HAVE BOOK TITLES, BUT
IF YOU HAVE LIKE ABSTRACT, SYNOPSIS, A
PARAGRAPH DESCRIBING SOMETHING, THEN YOU NEED
TO USE THIS VARCHAR FIELD.
     OKAY.  LET'S START FROM THE VERY
BEGINNING.  OF COURSE FROM THE VERY
BEGINNING YOU CREATE ORACLE NAVIGATOR
WHICH IS AN ICON.  IF YOU DO IT RIGHT IN
YOUR INSTALLATION.  CLICK ON NAVIGATOR,
ORACLE NAVIGATOR AND YOU WILL SEE THE
PRIMARY SCREEN.  AND AS USUAL YOU WILL SEE
SOME STRUCTURE ON LEFT HAND WINDOW AND
THEN DETAILS WILL POP UP ON RIGHT HAND
WINDOW.  SO WHAT YOU WANT TO DO IS YOU GO
TO THE LEFT-HAND SIDE AND YOU GO UNDER
LOCAL DATABASE AND YOU FIND THE FOLDER
USER.  IF THERE ARE NO USERS CURRENTLY ON
YOUR SYSTEM, THAT WILL BE EMPTY, SO YOUR
RIGHT-HAND SIDE WILL CONTAIN NO ENTRY IF
YOU'RE THE FIRST USER.  IF THERE ARE
ALREADY EXISTING USERS, THEIR NAME WILL
POP UP HERE.  SO WHAT YOU WANT TO DO AT
THIS POINT IS CREATE A USER, LET'S SAY,
FOR YOU, FOR SMITH.  AND WHAT I DO IS OF
COURSE THE USUAL THING YOU CLICK ON VIEW,
WHICH WILL BE ON OF THE LEFT-HAND SIDE,
THE ICON.  SO WHEN YOU CLICK ON NEW, PAGE
TO CREATE A NEW USER WILL POP UP.
ACTUALLY, THESE TWO OVERLAYS ARE IN
REVERSE ORDER.  YOU WILL SEE GENERAL
CHARACTERISTICS AND THAT PAGE YOU ENTER
YOUR NAME AND PASS WORD.  AND THEN YOU
CLICK YOU WILL FIND THIS PAGES.  WHICH ARE
THE ROW AND PRIVILEGE.  IF YOU CLICK TO
ROW YOU CAN GO TO ROW AND CLICK ON
PRIVILEGE GO TO THE PRIVILEGE.  EASIEST
THING IS TO CLICK ON THE PICK ALL AND YOU
JUST GIVE YOURSELF ALL OF THE PRIVILEGE
AND ALL OF THE ROWS.  NOTHING WRONG WITH
THAT.  BUT LIKE I SAID, EVEN YOURSELF MAY
MAKE SOME MISTAKES.  SO IT'S SUGGESTIBLE,
ADVISABLE THAT IF YOU ONLY DOING RETRIEVAL
AND NO UPDATE YOU GIVE YOURSELF LESS
PRIVILEGE.
     OKAY.  SO YOU CAN INTRODUCE ANY
NUMBER OF USERS, THIS WAY.
     AFTER YOU HAVE CREATED USER, OF
COURSE YOU CREATE PROJECTS.  SO THE WAY OF
DOING LIBRARY INFORMATION SYSTEM PROJECT
OR IN YOUR CASE STUDENT INFORMATION
PROJECT AND THEN ENTER THE PROJECT FOLDER
YOU WILL AGAIN CLICK AND CREATE A NEW
PROJECT.  ALL OF THESE ARE PRETTY ROUTINE.
     NOW ONCE HAVE YOU CREATED A PROJECT,
NOW YOU CAN START DEFINING NEW TABLES,
INTRODUCING TABLES AND PUT THEM INTO THIS
PROJECT FOLDER.  NOW, OKAY, FROM THE
LOGICAL POINT OF VIEW THIS SHOULD HAPPEN
AFTER WE HAVE DONE THE CONCEPTUAL DATABASE
DESIGN, RIGHT?  LIKE WHAT WE DID IN FIRST
EXERCISE.  AFTER CONCEPTUAL DATABASE
DESIGN YOU KNOW WHAT TABLES YOU WANT, YOU
KNOW ALL OF THE ATTRIBUTE NAMES AND
WHATEVER, IT'S ALREADY LAID OUT FOR YOU.
YOU CAN START ADDING NEW TABLES INTO THIS
PROJECT AND YOU LICK ON NEW.  NOW, THE
PULL DOWN MENU WILL APPEAR AND HERE YOU
WILL SEE HOW RICH, HOW POWERFUL IS THIS
NEW VERSION OF ORACLE BECAUSE IT DOES GIVE
YOU LOTS AND LOTS OF OPTIONS.  I KNOW YOU
CANNOT READ VERY WELL BUT LATER ON YOU CAN
TRY ON YOUR OWN.  YOU CAN DEFINE NEW
TABLE, NEW VIEW, NEW INDEX, NEW SYNONYM
AND ET CETERA, ET CETERA, SYNONYM.  A LOT
OF THINGS WE CAN DO, SECURITY MEASURES,
STUFF LIKE THAT. AND PRETTY SOON YOU'LL
KNOW MORE ABOUT THIS STUFF THAN I DO.
BECAUSE THIS IS WHERE YOU WANT TO SPEND A
LOT OF TIME IF YOU WANT TO BE PRO
EFFICIENT SO YOU CAN CREATE DATABASE WITH
GOOD INDEX STRUCTURE, GOOD SECURITY
MEASURE THAT CAN BE APPLICATIONS.  FOR THE
CLASS PROJECT OF COURSE YOU DON'T HAVE TO
WORRY ABOUT ALL THESE, ALL YOU HAVE TO DO
IS CLICK ON FIRST ONE WHICH IS NEW TABLE.
     AND THEN A PAGE WILL SHOW UP TO GIVE
YOU TWO OPTIONS, EITHER YOU CAN USE THE
WIZARD AND WIZARD WILL TAKE YOUR HAND AND
GUIDE YOU THROUGH THE DEFINITION AND BUT
LIKE MOST THINGS THE WIZARD USUALLY NOT
POWERFUL ENOUGH SO YOU SHOULD CHOOSE
MANUAL OPTION.  SO CHOOSE MANUAL OPTION
WHEN YOU INTRODUCE NEW TABLES BECAUSE THIS
GIVE YOU BETTER CONTROL.  SO YOU CAN, FOR
EXAMPLE, DEFINE THE DIFFERENT TYPE OF
ATTRIBUTES, USUALLY THIS IS WHAT WE'RE
CONCERNED MOST AND USING THE WIZARD
SOMETIMES YOU HAVE NO WHO IS.
     OKAY.  SO BASICALLY SAME KIND OF
THING.  SO YOU WANT TO INTRODUCE THE NEW
TABLE, THE SYSTEM WILL ASK YOU WHAT'S THE
NAME OF THE TABLE, WHO OWNS IT.  THE
OWNER, REMEMBER IS VERY IMPORTANT BECAUSE
LATER ON WHEN WE TALK ABOUT SECURITY,
PRIVILEGE OF THE OWNER WILL BE PASSED ON,
INHERITED BY ALL THESE DATABASE OBJECTS.
NOW IN THIS CASE IT'S OWNED BY YOU, JUST
ENTER YOUR NAME.  OKAY.  AND THEN YOU'LL
BE PROMPTED TO ENTER THE NAME OF THE
DIFFERENT ATTRIBUTES AND THEIR TYPE AND OF
COURSE THERE IS DEFAULT AND THERE IS A POP
UP MENU YOU CAN MAKE SELECTIONS.  IF
YOU'RE NOT HAPPY WITH THE LIMITED
SELECTIONS, YOU CAN TYPE IT IN.  AND THEN
YOU SCROLL TO THE RIGHT.  
THE DEFAULT VALUE AND
THEN IF YOU SCROLL THE PAGE TO THE RIGHT
YOU WILL SEE OTHER THINGS.
     OKAY.  SO OH YEAH, OTHER THINGS ARE
IMPORTANT BECAUSE THAT'S WHAT YOU CAN
DEFINE THE KEY FIELD, FOR EXAMPLE.  IF YOU
DON'T DEFINE THE KEY FIELD THEN WHAT
SYSTEM WILL DO IS TO USE TUPLE ID AS THE
KEY.  BUT IS THAT USELESS TO US.  THAT IS
THE KEY WE CANNOT ACCESS.  YOU BETTER
DEFINE THE KEY.  IF YOU DON'T THE SYSTEM
WILL GIVE YOU A WARNING MESSAGE.  IT'S NOT
A CRIME NOT TO HAVE A KEY.  BECAUSE AS WE
KNOW YOU CAN PUT ALL ATTRIBUTES AND THAT
BECOMES THE KEY.  IF YOU SCROLL TO THE
RIGHT-HAND SIDE YOU CAN DEFINE THE KEY
FIELD.  INTRODUCE THAT. ONCE YOU ARE DONE,
YOU CLICK OKAY.  THEN YOU CREATE AN EMPTY
TABLE.
     NOW YOU DON'T HAVE TO PUT IN DATA
THIS TIME YOU CAN GO ON, CREATE OTHER
TABLES UNTIL YOU FINISH THE CONCEPTUAL
DESIGN AND QUIT.  LATER YOU COME BACK TO
INPUT DATA.  OR IF YOU WANT YOU CAN INPUT
DATA BY INSERTING TUPLES.
     STUDENT:   ONE THING I NOTICE YOU
PREFACE EVERY COLUMN NAME WITH THE TABLE
NAME UNDERSCORED AND COLUMN NAME.  IS THAT
A GOOD IDEA TO CARRY THROUGH THE
METHODOLOGY IN CREATING THE DATABASE?
THAT'S A LOGICAL WAY OF NAMING THINGS.
YOU DON'T.
     PROFESSOR:   YOU DON'T HAVE TO FOLLOW
THAT.
     STUDENT:   IT DOES MAKE IT EASE YE.
     PROFESSOR:   SOME PEOPLE WOULD PREFER
THE C AND JAVA COMBINATION.  CAPITAL
LETTER, AND THEN NO UNDERSCORE.  SOME
PEOPLE USE UNDERSCORE.  IT'S A MATTER OF
TASTE.  DOESN'T MATTER.
     SO WE CAN FINISH THE DESIGN, COME
BACK AND INPUT DATA OR IF YOU WANT TO GO
IMMEDIATELY TO THE TABLE AND START
INSERTING SOME TUPLES.  AGAIN THE STEPS
ARE SELF-GUIDED SO, I DON'T THINK YOU HAVE
ANY PROBLEM THERE.  YES?
     STUDENT:   I THINK ANOTHER ADVANTAGE
IT USING THAT WHERE YOU HAVE PET NAME AND
PET ID NUMBER IF YOU'RE JOINING THE TABLES
AND YOU CALL IT NAME AND ID NUMBER THERE
IS GOING TO AMBIGUITY BETWEEN THE TWO SO
WHAT YOU'RE DOING WHERE YOU PUT PET NAME
IN FRONT OF IT YOU DON'T HAVE TWO FIELDS
CALLED NAME.
     PROFESSOR:   COULD YOU DO THAT.  ON
THE OTHER HAND YOU WANT TO DEFINE UNIQUE
PRIMARY KEY, JUST USE THE PRIMARY KEY.
     STUDENT:   WHAT I WAS TALKING ABOUT,
YOU SAY SELECT NAME, FROM, AND THEN HAVE
YOU TWO TABLES.  IF THEY BOTH HAVE THE
FIELD CALLED NAME YOU ACTUALLY -- NOT
CALLING IT NAME, BUT PET NAME WORKS OUT.
THERE IS AN ADVANTAGE TO THAT.
     PROFESSOR:   OR USE ALIAS, USING
SYNONYM.  OKAY.  SO WE CAN POPULATE A
TABLE.  THIS IS GOOD ENOUGH.  THINGS FOR
THE CLASS EXERCISE.  YOU ONLY NEED FOR THE
PUT A IF YOU ENTRIES, OTHERWISE IMPORT
FROM THE EXTERNAL FILES.  SO POPULATE
DATABASE AND NOW ONE CAN START DEFINING
VIEWS.
     AGAIN, YOU GO TO THE LEFT-HAND ICON,
PULL DOWN MENU WILL SHOW UP AND YOU CLICK
NEW VIEW.  AND THIS WILL ALLOW YOU TO
CREATE A NEW VIEW.
     SAME STUFF.  THE SYSTEM WILL ASK YOU,
OF COURSE, TO SELECT BASE TABLE, SELECT A
TABLE AND IF IT IS PERFECTED IT WILL ASK
FOR USER PASS WORD, STUFF LIKE THAT.  AND
THEN THE TABLE WILL POP UP.  NOW WHAT DO
YOU DO IS THAT YOU DOUBLE CLICK ON THE
ATTRIBUTE YOU WANT TO SELECT.  EACH TIME
YOU DOUBLE CLICK IT WILL SHOW UP HERE.  IN
OTHER WORDS, THESE WILL BE THE COLUMNS
YOU'RE GOING TO PICK FOR THE VIEW.  BUT
THAT'S NOT IT, BECAUSE IF I CAN ONLY DO
THAT, I CAN ONLY DO THE PROJECTION, RIGHT?
THERE IS ALSO A CONDITION FIELD.  IN THE
CONDITION FIELD YOU CAN ENTER A PREDICATE.
LIKE NAME EQUALS MISS.  OR SALARY GRID
EQUALS 50,000.  IT IS THE CONDITION FIELD
THAT MAKES IT SO POWERFUL.  IN OTHER
WORDS, ONLY THE CONDITION IS SATISFIED.
THEN THIS ATTRIBUTE WILL BE INCLUDED IN
THE VIEW.  THAT'S WHY I SAY A MOMENT AGO
THIS IS BASICALLY WHAT WE CALL A LATENT
QUERY.  IT IS PREDEFINED AND WE USE IT TO
CONSTRUCT THE VIEWS.  THIS FEATURE I LIKE
VERY MUCH, THIS IS REALLY GOOD.  THE EARLY
VERSIONS DON'T HAVE VIEWS.  THIS VERSION
CAN GIVE YOU GREAT FLEXIBILITY AND YOU CAN
ALSO RENAME, LIKE YOU MENTIONED.  I CAN
RENAME THE ATTRIBUTE NAMES HERE SO THAT
INSTEAD OF CALLING IT LAST NAME, WHATEVER.
OKAY.
     AND HERE ARE MORE DETAILS.  HERE IS
THE CASE HERE WHEN INPUTTING A CONDITION
LIKE THE PET OWNER IS OWNER NAME THEN THAT
CONDITION WILL SHOW UP IN THE TABLE AND
THIS IS EQUIVALENT TO CONSTRUCTING A SQL
QUERY.  THIS IS SIMILAR FROM WHERE?  NOW
MOST INTERFACE THESE DAYS WILL ALLOW TO
YOU CONSTRUCT QUERIES IN THIS STEP BY STEP
FASHION AND THEN OF COURSE THE QUERY WILL
BE STORED.  YOU CAN RECALL THAT SQL QUERY.
NOW THIS IS THE GOOD TECHNIQUE IF THE
QUERY IS SIMPLE ENOUGH.  NORMALLY OF THE
SELECT FROM QUERY TYPE AND IT CAN COLUMN,
FROM THE ROWS, YOU CAN CONSTRUCT QUERIES.
FOR ANYTHING THAT IS MORE SOPHISTICATED WE
HAVE TO DO IT OURSELF.  THERE IS A NEED TO
KNOW SQL LANGUAGE BECAUSE THIS IS WHERE
THE REAL WORK CAN BE DONE OF THE WHAT I'M
SAYING IS SIMPLE QUERIES LIKE THIS WE
DON'T HAVE TO WORRY, THIS TABLE DRIVEN
TECHNIQUE WILL HELP US DO THAT.
     OKAY.  AND THEN POPULATE DATABASE SO
THAT'S IT.  SO THIS WAS SET UP BASIS FOR
YOU TO HAVE YOUR WORKING DATABASE.  WHAT I
SUGGEST IS OVER THE WEEKEND YOU START
DOING THAT.  IT'S NOTHING TO IT.  YOU CAN
FINISH THAT IN ONE OR TWO HOURS, NO
PROBLEM AND THEN NEXT WEEK, IF EVERYTHING
GOES RIGHT, WE WILL START WITH SQL QUERY,
SORRY, WE'LL START WITH OTHER THING AND
THEN THURSDAY SQL QUERY FOR THE NEXT
WEEKEND YOU CAN DO THE SECOND EXERCISE.
OKAY  .
     THAT'S IT.