PROFESSOR: THE WHOLE AREA OF DATABASE IS ONE OF THE OLDEST AREAS IN COMPUTER SCIENCE. STARTING FROM DAY ONE WE HAVE DATABASE BUT OF COURSE AT THAT TIME WE DON'T HAVE THE TERMINOLOGY. IN THE VERY OLD DAYS, WHAT WE HAVE ARE RECORDS, A RECORD MANAGEMENT SYSTEM. THE REASON THE COMPUTER IS USEFUL IS WE USE THAT FOR RECORD KEEPING, FOR MANAGEMENT OF FILES. SO, IN THE BEGINNING, THERE WAS THE RECORD. AND THERE WAS NO BILL GATES, OKAY, BUT THERE WAS THE RECORD. AND THE RECORD WAS GOOD. SO, THE RECORD ACTUALLY CONSISTS OF A NUMBER OF FIELDS. SO HERE I GIVE EXAMPLE AND I DON'T THINK I NEED TO ELABORATE. SO IF I HAVE AN EMPLOYEE RECORD I WOULD DIVIDE THAT INTO FIELDS AND THE FIELDS I USUALLY CONSIST OF A NUMBER OF ALPHANUMERIC CHARACTERS, SO NAME, ADDRESS STUFF LIKE THAT AND OFTEN TIME FIXED LENGTHS. THEY RESERVE THE NUMBER OF BITS -- BYTES FOR A CERTAIN FIELD. PRETTY SOON PEOPLE FIND IT'S INCONVENIENT SO THEN THERE IS VARIABLE -- THE GOOD THING ABOUT FIXED LENGTH RECORDS IS YOU CAN READ IT IN -- IN THE VERY OLD DAYS WE HAVE THE PUNCH CARDS. LET'S SEE, HOW MANY OF YOU HAVE ACTUALLY SEEN IBM PUNCH CARD? WOW. SURPRISING. WHERE DID YOU SEE IT? STUDENT: CLASS. PROFESSOR: SOMEBODY BROUGHT IT IN CLASS. GOOD. YEAH. SO PEOPLE WILL PUNCH THE CARDS, 80 COLUMN CARD AND THEN YOU START TO STORE RECORDS. IT'S VERY IMPORTANT TO BE ABLE TO LAY OUT THE RECORD THAT WILL FIT, INTEGRATE MULTIPLE OF CARDS. SO THE STORED RECORD IS ALSO CALLED THE PHYSICAL RECORD. PHYSICAL AND LOGICAL IS BASICALLY THE TWO SIDES OF THE SAME COIN. THE PHYSICAL MEANS HOW THE THEY ACTUALLY STORE THE INFORMATION BUT LOGICAL MEANS HOW YOU'RE GOING TO REFER TO THE INFORMATION. SO AT THAT TIME THERE WAS NO DIFFERENCE BETWEEN THE LOGICAL AND PHYSICAL. IT'S THE SAME WHEN WE'RE READING THE RECORD. WE STORE THEM AND WE CAN ACCESS THEM. SO THE TERM USED AT THAT TIME WAS CALLED U-N-I RECORD PROCESSING. THAT MEANS THAT YOU PROCESS ONE RECORD AT A TIME. AND FOR MANY APPLICATIONS SUCH AS PAYROLL, SUCH AS BILLING, THE UNI RECORD PROCESSING. SO YOU GO FROM THE BEGINNING TO END OF THE FILE AND IT GOES THROUGH THAT ONLY ONCE AND IT MAKES SENSE AND YOU PROCESS EVERYTHING THAT WAY. NOW THAT WAS GOOD IN THE VERY OLD DAYS. BUT GRADUALLY PEOPLE FOUND THAT THIS IS NOT SUFFICIENT. FIRST OF ALL, WE'RE GOING TO HAVE MORE AND MORE APPLICATION PROGRAMS. SO FOR DIFFERENT FUNCTIONS, WE NEED TO PROVIDE DIFFERENT APPLICATION PROGRAMS. SO THIS IS THE FIRST EVOLUTION SO WE WILL HAVE APPLICATION PROGRAMS THAT WILL SHARE AND ACCESS THE SAME FILE. AT THAT TIME THERE IS NO SUCH WORD CALLED DATABASE. IT'S A FILE. YOU ACCESS THE RECORDS STORED IN THE FILE AND PROCESS THEM. BUT PRETTY SOON PEOPLE GET SMARTER SAYING THAT WAIT, WE ARE DUPLICATING A LOT OF THINGS. IN ALL THE APPLICATIONS WE HAVE TO DO THINGS VERY SIMILARLY. WE FIRST HAVE TO OPEN THE FILE. WE HAVE TO ACCESS THE FILE AND READ IN THE RECORD, STORE IN THE BUFFER, UPDATE IT AND WRITE IT OUT. SO WE FIND THAT THIS ARE MANY FUNCTIONS THAT ARE IN COMMON. THIS REFERS TO -- WE CAN TALK ALL OF THE FUNCTIONS, OPEN FILE, READ FILE, CLOSE FILE AND MAKE A PACKAGE WHICH IS CALLED FILE MANAGER. THE FILE MANAGER CAN BE USED TO ACCESS ONE FILE, OF COURSE IT CAN ALSO BE USED TO ACCESS MULTIPLE FILES, SEVERAL FILES SO WE HAVE SITUATION, WE HAVE MANY APPLICATION PROGRAMS AND MANY FILES AND IN BETWEEN IS THE FILE MANAGER WHICH CAN SERVE MULTIPLE APPLICATION PROGRAMS TO ACCESS MULTIPLE FILES. AND THE OUTGROWTH OF THAT IS MODERN DATABASE MANAGEMENT SYSTEM OR DBMS WHERE YOU HAVE MULTIPLE USERS WITH MULTIPLE APPLICATIONS ACCESSING MULTIPLE FILES. AND THE FILES, IF WE USE ORIGINAL DATABASE -- RELATIONAL DATABASE, THESE ARE RELATIONAL FILES. SO THOSE PATCHES, ACTUALLY THEY REFER TO THOSE FILES OR RELATIONAL TABLES AND THEY MAY HAVE SOME COMMONALITY, THEY MAY DUPLICATE IN PART, BUT QUITE OFTEN THEY WILL BE DIFFERENT. OKAY. SO FROM THIS VIEWPOINT THE DATABASE IS A COLLECTION OF WHAT WE CALL PERSISTENT DATA. PERSISTENT AS OPPOSED TO TRANSIENT MEANS THAT DATA WILL STAY IN THE DATABASE FOR A PROLONGED PERIOD OF TIME. NOW IF IT IS TRANSIENT DATA WE PROBABLY STORE IN SOME TEMPORARY STORAGE AREA. BUT THE DATABASE CONTAINS INFORMATION OF VALUE FOR A LONGER PERIOD OF TIME. SO THAT'S WHY WE SAY IT'S PERSISTENT DATA. AND THESE ARE USED BY APPLICATION SYSTEMS OF SOME ENTER PRICE OF SOME BUSINESS. NOW THE TWO POINTS I WANT YOU TO ALWAYS REMEMBER ABOUT DATABASE IS THAT IT IS USED BECAUSE OF THESE TWO PROPERTIES, DATA SHARING AND DATA INDEPENDENCE. SO YOU PEOPLE ASK WHY DO WE USE DATABASE? THE FIRST POINT IS THAT DATA SHARING. WHAT DOES THIS MEAN? DATA SHARING. YES? STUDENT: I'M JUST GOING TO TAKE A GUESS, IF YOU HAVE LIKE A DATABASE OR DATA SITTING ON THE SERVER AND HAVE MULTIPLE CLIENTS THAT CAN ACCESS THAT DATA. PROFESSOR: RIGHT. IF YOU HAVE MEDICAL APPLICATIONS, MULTIPLE CLINICS CAN SHARE THE INFORMATION. OVER THE INTERNET THIS ALSO MEANS THAT THE CLIENTS CAN SHARE THE INFORMATION OVER A DISTANCE WHICH IS VERY, VERY IMPORTANT THESE DAYS. SO SHARING IS THE MAJOR DRIVING FORCE TOWARDS THE DATABASE. SO IF SOMEBODY INPUTTED DATA THEN THE OTHER CLIENTS USING DATABASE TECHNOLOGY AND COMMUNICATION TECHNOLOGY, WILL BE ABLE TO SHARE THAT. SO THIS IS THE MAJOR THING. ONE ABOUT OTHER POINT, DATA INDEPENDENCE? YES? STUDENT: WHERE YOU CAN THINK OF THE DATA AS IT RELATES TO REAL WORLD OBJECTS RATHER THAN CS STRUCTURES? PROFESSOR: RIGHT. THIS MEANS THAT WE DON'T HAVE TO START FROM SCRATCH FOR THE PHYSICAL STRUCTURE EVERY TIME. WE CAN THINK ABOUT THAT AS A LOGICAL CONCEPT. ESPECIALLY IN THE INTERNET RELATED WORK OF PEOPLE DEALING WITH -- PEOPLE ARE DEALING MORE AND MORE WITH LOGICAL CONCEPTS RATHER THAN THE NITTY GRITTY THAT WE'RE CONCERNED ABOUT. SO THE USER THEY'RE -- THEY DON'T CARE ABOUT MANAGING AND STORING, THEY ONLY DEAL WITH LOGICAL WAY. THAT'S WHY YOU CAN SEE THE MOVEMENT, FOR EXAMPLE, IN THE HTML WORLD FROM HTML TO XML. SO, BECAUSE WE CAN CHAIR DATA AND BECAUSE WE CAN MAINTAIN HIGHER LEVEL OF ABSTRACTION, THIS MAKES DATA MUCH MORE USEFUL OR WHAT WE CALL WE CAN AD VALUE TO THE INFORMATION. THESE ARE THE TWO PRIMARY REASONS AND I TELL YOU I ALWAYS PUT IN THE QUIZ SOMETHING, JUST TO MAKE SURE YOU UNDERSTAND WHY WE GOT DATABASE ROUTE. WE WANT TO SHARE DATA AND WE WANT TO HAVE A HIGHER LEVEL ABSTRACTION. AND DATABASE SYSTEM CONSISTS OF FOLLOWING ELEMENTS. AND HARDWARE, SOFTWARE AND USERS AND THEY HAVE DIFFERENT TYPE OF USERS WHICH INCLUDES APPLICATION PROGRAMMER AND THE END USER AND THEN THE DATABASE ADMINISTRATOR. ACTUALLY TWO TYPE OF ADMINISTRATOR. THE DATA ADMINISTRATOR AND THE DATABASE ADMINISTRATOR AND THESE TWO ARE ACTUALLY DIFFERENT IN CONCEPT. ONE IS MORE TECHNICAL. THE DATABASE ADMINISTRATOR IS MORE TECHNICAL AND HE OR SHE IS DEALING WITH HOW WE STRUCTURE THE DATA, HOW WE VIEW THE SCHEME, HOW WE DO THE MAINTENANCE, WHAT ARE THE CHECK POINTS, ALL THOSE DETAILS TO MAINTAIN A DATABASE. THE DATABASE ADMINISTRATOR, ON THE OTHER HAND, THIS IS NOT DISTRICT ATTORNEY, IT'S NOT, IT'S ACTUALLY A GUY OR GIRL, VERY HIGH IN THE COMPANY, IT'S A HIERARCHY WHO DECIDES FUNDAMENTALLY WHAT SHOULD BE IN THE DATABASE. SO FOR A CERTAIN BUSINESS, FOR STRATEGY REASONS THEY WILL MAKE CERTAIN DECISIONS AS TO WHAT WILL BE PERTINENT IN THE DATABASE. OKAY. THERE ARE A NUMBER OF DIFFERENT APPROACHES FOR THE DATABASE SYSTEM. AND OVER THIS COURSE WE ARE GOING TO TALK ABOUT HIERARCHICAL DATABASE STRUCTURE, NETWORK DATABASE STRUCTURE, RELATIONAL WHICH IS THE MOST POPULAR, SO WHEN WE TALK ABOUT ORACLE, E BASE, ALL THESE ARE BASED UPON RELATIONAL TECHNOLOGY. BUT MORE AND MORE WE SEE ADVANCES IN OBJECT ORIENTED DATABASE, ESPECIALLY FOR YOUR GENERATION WHO ARE WELL-TRAINED IN C PLUS PLUS AND JAVA AND OBJECT ORIENTED BECAUSE MORE NATURAL APPROACH. STILL IN A DATABASE WORK, IF YOU LOOK ALL SIDES, THE MAJORITY STILL USE RELATIONAL APPROACH. SO WE'LL CONCENTRATE AT FIRST ON THE RELATIONAL APPROACH, ESPECIALLY SINCE SOME OF THE MOST POPULAR DATABASE LIKE ORACLE ARE STILL RELATIONAL. BUT THEN WE WILL MOVE OUT TO THE OTHER CONCEPT. NOW, JUST A GLIMPSE OF WHAT WILL COME NEXT IS THAT WE'LL DEAL WITH THE RELATIONAL DATABASE BOTH AS THEORETICAL CONCEPT AS WELL AS THE TOOL. THE REASON THE RELATIONAL APPROACH IS POPULAR IS THAT IT TAKES ON A VERY INTUITIVE NOTION NAMELY TABLES. AND USE THAT AS THE METAPHOR YOU MAY SAY TO MANAGE INFORMATION. EVERYBODY UNDERSTAND TABLES RIGHT AWAY. SO IF YOU STORE INFORMATION IN TABULAR FORM THE END USER WILL BE VERY HAPPY BECAUSE THEY CAN UNDERSTAND THAT RIGHT AWAY. SO THE RELATIONAL APPROACH, THE GIST OF IT IS THAT INFORMATION IS STORED IN TABLES WHAT WE CALL RELATIONS. BUT THEN WE CAN DEFINE OPERATORS TO EXTRACT INFORMATION FROM THE TABLES AND WITH THOSE OPERATORS WE COMPUTE LANGUAGES SUCH AS SEQUEL. WHICH WILL ENABLE THE APPLICATION PROGRAMMER TO WRITE PROGRAMS IN THIS HIGH LEVEL LANGUAGE, EXTRACT INFORMATION, ACCESS INFORMATION FROM THE RELATIONAL DATABASE. JUST TO GIVE YOU A SIMPLE IDEA, WE'LL GO INTO MORE DETAIL LATER, THIS IS A TABLE ABOUT WINES. SO THERE ARE DIFFERENT KIND OF WINE AND THE YEAR IT IS PRODUCED, AND THE QUANTITY, NUMBER OF BOTTLES IN STOCK. IT IS A TABLE. IT ALSO EXPRESS A RELATIONSHIP, WHAT RELATIONSHIP? RELATIONSHIP BETWEEN THE TYPE OF WINE, THE YEAR IT WAS MADE AND THE QUANTITY. THAT RELATIONSHIP IS EXPRESSING. NOW, IF I WANT TO KNOW WHAT BRAND WE CARRY IN THIS WINE CELLAR, WHAT DO I DO? IF I JUST WANT TO FIND WHAT DIFFERENT BRAND OF WINE WE HAVE? YES? STUDENT: SELECT THE NAMES -- END STAGE NAMES. PROFESSOR: IN OTHER WORDS YOU SELECT FIRST COLUMN, RIGHT? NOW, LATER WE WILL LEARN IN SQL WE HAVE PROGRAMS THAT CAN DO THAT, RELATIONAL ALGEBRA, YOU PROJECT IT ON TO THE FIRST COLUMN AND GET THE TYPE OF WINES. ON THE OTHER HAND, IF I WANT TO FIND OUT THE DETAIL ABOUT CHARDONNAY, HOW MANY BOTTLES AND WHAT YEAR IT WAS MADE, WE MAKE A HORIZONTAL SELECTION, SELECTING A ROW RATHER THAN COLUMN. THAT WILL GIVE US THE INFORMATION ABOUT A TYPE OF WINE, THE YEAR, ET CETERA. OKAY? SO THESE ARE FUNDAMENTAL OPERATIONS THAT WE CAN DEFINE AND USE THAT TO BUILD A LANGUAGE. SO, WE'LL STUDY THE DIFFERENT DATABASE APPROACHES, BUT FIRST OF ALL, WE'LL STUDY HOW WE CAN CAPTURE THE INFORMATION TO BUILD A DATABASE. SO IF YOU GOOD WORK FOR A COMPANY AND THEN THAT COMPANY DECIDES TO COMPUTERIZE, OF COURSE, THIS HYPOTHETICAL BECAUSE MOST COMPANIES ARE ALREADY COMPUTERIZED. BUT IF THEY DECIDE TO BUILD A DATABASE FOR THE ENTER PRICE, OR FOR NEW BUSINESS, WHAT INFORMATION WE PUT IN DATABASE? HOW DO WE STRUCTURE THAT? OKAY? THIS IS VERY USEFUL BECAUSE IT WILL ENABLE US TO DEFINE A CONCEPTUAL DATABASE AND THEN FLUSH IT OUT, PUT DATA INTO IT AND ACTUALLY VIEW THE ACTUAL DATABASE. SO IN THE FIRST PART OF THE COURSE WE'LL GOOD THROUGH THIS SEQUENCE OF THE USER'S REQUIREMENTS, FROM THE REQUIREMENTS, DEFINE A CONCEPTUAL DATABASE AND FROM THAT WE CAN CONSTRUCT THE SO-CALLED DATABASE SCHEMA. ONCE THE SCHEMA IS DEFINED WE CAN USE A LANGUAGE LIKE SQL TO ACTUAL CONSTRUCT THE DATABASE AND THEN USE YOUR ORACLE 8 OR 8-I TO VIEW THE DATABASE ON YOUR PERSONAL COMPUTERS. OKAY. SO THIS GIVES YOU A QUICK OVERVIEW OF WHAT IS A DATABASE AND WHAT WE WILL COVER. NOW, IF YOU TAKE A LOOK AT EXERCISES YOU WILL NOTICE THAT THE FIRST EXERCISE IS ON PAPER. SO THIS IS TO GET YOU STARTED OUT. IT'S DESIGNED TO DESIGN A CONCEPTUAL DATABASE. IN THE MEANTIME YOU SHOULD ACQUIRE ORACLE SYSTEM AND START TRYING TO INSTALL THAT. IF YOU RUN INTO PROBLEM, THAT'S WHEN YOU CAN GET HELP FROM THE TEACHING ASSISTANT, OR EXCHANGE INFORMATION WITH YOUR FELLOW STUDENTS.