PROFESSOR: YES? STUDENT: WHAT ARE WE SUPPOSED -- DOES ORACLE HAVE IT'S OWN TOOLS FOR CONTENT? DOES ORACLE 8 HAVE ITS OWN TOOLS? PROFESSOR: YES. DID YOU CHECK MY NOTES? I TOLD YOU THAT IN THE ORACLE NOTES THERE ARE EXTENSIVE DISCUSSION, ACTUALLY I DIDN'T WRITE IT, I MUST GIVE CREDIT TO THE FORMER TEACHING ASSISTANT. IF YOU HAVEN'T FOUND THAT PART OF THE NOTE, THIS IS IN ORACLE SECTION. I UNLOCK IT AGAIN SO YOU HAVE A CHANCE TO COPY THAT. AND THERE IS -- YEAH. RIGHT. HIT ALL OF THEM. OH, SORRY IT'S NOT YOUR FAULT. IT'S HERE. OKAY. FINALLY GOT IT. THEY VERY GOOD NOTES HOW TO USE EXCEL AND VISUAL BASIC TOGETHER WITH ORACLE TO DO THE NEXT EXERCISE. BASICALLY THIS EXERCISE IN PRINCIPLE IS VERY SIMPLE. WHAT I WOULD LIKE YOU TO DO IS TO CREATE A FORM WITH A FEW SAYING QUERY ONE, TWO, QUERY FOUR SO WHEN YOU HIT A BUTTON IT RUNS THE CORRESPONDING QUERY. THAT'S ALL THERE IS TO IT. YES? STUDENT: I LOOKED AT THE NOTES BUT I HAVEN'T STARTED DOING IT YET, CAN WE GET BY WITH THE DBA THAT COMES WITH EXCEL OR DO WE NEED TO USE VISUAL BASIC PROPER? PROFESSOR: YOU DON'T NEED VISUAL BASIC. RIGHT. YOU COULD DO IT IN VISUAL BASIC, THOUGH, CREATE A BUTTON, EVERYTHING. IN OTHER WORDS, THE REASON THAT YOU PUT IN EXCEL IS THAT IT JUST HAPPENED TO BE ON THAT CD AND THAT IS ALWAYS A GOOD IDEA, WHY NOT, THAT YOU CAN LEARN HOW TO COMBINE EXCEL WITH -- SO YOU CAN USE EITHER ONE. NOW, THIS ONE YOU CAN USE ALMOST ANYTHING AND WHAT I WANT IS JUST CREATE A FORM WITH A FEW BUTTONS SO YOU CAN RUN QUERIES. BUT THE NEXT ONE, THE PROJECT, MINNIE PROJECT YOU MUST USE -- MINI PROJECT YOU MUST USE WEB PAGE SO YOU HAVE CONTINUITY. THE PROBLEM THAT TA HAS EXECUTING PROGRAM, YOU ARE AGAIN ALLOWED TO HAND HER, LILY, EVERYTHING ON PAPER. SO PRINT UP EVERYTHING, PRINT OUT YOUR SOURCE CODE AND YOUR EXECUTION HISTORY AND THEN GIVE IT, STAPLED TO HER. PROBLEM WITH LOOSE FORM WE ALWAYS END UP WITH SITUATION LIKE THAT, WE CANNOT FIND EASILY EXERCISES AND NOT SO GOOD. STUDENT: YOU ARE SAYING WE CAN USE ACCESS DATABASE? PROFESSOR: THE DATABASE ACTUALLY DOESN'T MATTER. IT'S INTERFACE I'M LOOKING FOR. STUDENT: OKAY. PROFESSOR: SO BASICALLY, IF YOU USE EXCEL, THE NOTES TELLS YOU HOW TO CREATE A FORM IN EXCEL AND EACH ONE OF THEM BECOME A MACRO IN BOTH CORRESPONDING QUERY. NOW, THE REASON THAT I ASK YOU TO LEARN THIS USING EXCEL IS THAT LATER ON YOU MIGHT FIND IT USEFUL BECAUSE LAST TIME WE USED THAT MAINLY BECAUSE TEACHING ASSISTANT WE FOUND THIS TOOL AND LATER ON GOT QUITE A FEW MESSAGES FROM SOME OF THE STUDENT SAYING THAT THEY FOUND THIS VERY USEFUL AT WORK AND IT IS NOT SURPRISING BECAUSE MOST SECRETARIES AND ADMINISTRATORS LIKE EXCEL. BECAUSE IT'S JUST A FORM. SO WE USE THAT FOR ALMOST ANY PURPOSES. IT'S DESIGNED MAINLY LIKE A BALANCE SHEET, SPREADSHEET SO YOU CAN PUT IN NUMBERS, THEY CAN GENERATE COLUMNS SUM AND ROW SUM. THAT'S THE ORIGINAL PURPOSE, SO YOU CAN DO TAX AND STUFF LIKE THAT. YOU PROBABLY NOTICE PEOPLE USE THOSE FORM ALMOST FOR ANY PURPOSES. RECENTLY I FILLED OUT APPLICATION FOR SOMEONE TO BE A FELLOW THEY USED EXCEL FORM. THEY REALLY DON'T HAVE TO BECAUSE IT'S IN CHARACTERS JUST SECRETARIES GET USED TO THAT. ANOTHER QUESTION? STUDENT: YEAH. LIKE SAY WE DO THE PROJECT AND WE USE VISUAL BASIC, -- PROFESSOR: NOT PROJECT, EXERCISE. STUDENT: EXERCISE. PROFESSOR: THE PROJECT YOU MUST USE WEB PAGE. STUDENT: I JUST GOT THE WORDS MIXED UP. IF WE DO EXERCISE IN VB, CAN WE USE LIKE A THIRD PARTY GRID CONTROL, LIKE A COMPONENT? PROFESSOR: I HOPE NOT BECAUSE ALL THESE YOU CAN FIND ON MARKET. EVEN TEMPLATES YOU CAN CUSTOMIZE TO DO THIS KIND OF THING. STUDENT: RIGHT. PROFESSOR: JUST DO IT THE HARD WAY. JUST A LEARNING EXPERIENCE. NOT TO SAY YOU CANNOT DO IT THAT WAY LATER ON. OKAY. SO, MY POINT IS SIMPLY THAT IN THE COMMERCIAL WORK, EVERYBODY, ALMOST EVERYBODY USE SPREADSHEET. NOW THE BEAUTY OF THIS TOOL IS -- I MEAN ORACLE IS THAT YOU CAN UNLOAD WHAT IS IN THE SPREADSHEET INTO THE DATABASE AND VICE VERSA. OKAY? THAT'S REALLY NICE BECAUSE THINK ABOUT THAT. SO IF EXAMPLE HE USED SPREAD SHEET TO DO SOME FORECASTING CALCULATION, CHANCES ARE PEOPLE LIKE TO SAVE THAT LATER ON AND IF YOU SAVE IN SPREAD SHEET HE CAN ONLY DO LIMITED THINGS TO DO THIS KIND OF COLUMN, ROWS CHECKING. IF YOU PUT IN DATABASE, IT CAN REALLY BE USED MUCH WIDER BASIS. SO, NOW WE CAN INTEGRATE ALL OF THESE SO YOU CAN PUT DATA IN THE SPREAD SHEET AND THEN LOAD INTO THE DATABASE AND CONVERSE IS ALSO TRUE, YOU CAN TAKE THE DATA FROM DATABASE AND LOAD IT INTO SPREAD SHEET. SO THAT CAN BECOME HANDY IF YOU GO TO WORK FOR SOME COMPANY LATER ON AT LEAST THAT'S ONE MORE TRICK THAT YOU KNOW. OKAY? SO THAT'S THE PRIMARY PURPOSE FOR THIS EXERCISE. BUT IF YOU FEEL THAT YOU REALLY JUST WANT TO USE VB AND DO THE INTERFACE, I HAVE NO OBJECTION. YOU CAN DO IT THAT WAY AS WELL. BUT SINCE THIS IS AVAILABLE WHY NOT LEARN IT. OKAY. SO, YOU CAN GO THROUGH THE NOTES. THEY ARE ACTUALLY STEP BY STEP INSTRUCTIONS HOW TO USE EXCEL WITH ORACLE DATABASE. AND THEY ARE ALSO ASSOCIATED MACROS. YOU CAN CUSTOMIZE THAT FOR YOUR PURPOSE. AGAIN, AS EARLY YOU CAN USE THE BULLETIN BOARD TO EXCHANGE INFORMATION. OKAY. SO MUCH FOR EXERCISE 5. AND THEN ON THURSDAY LILY CHOW WHO IS THE OTHER TA SHE'LL BE HERE TO COLLECT THESE EXERCISES. AND SHE'S ALSO GOING TO BE THE ONE WHO WILL GRADE EXERCISE 5. SO YOU CAN TALK TO HER. ANY QUESTIONS? YEAH? STUDENT: WHEN DO YOU ANTICIPATE WE'LL GET OUR MERCHANDISE TERMS BACK? PROFESSOR: YES, THALUS IS STILL WORKING ON IT. THAT'S ALL I CAN SAY. I HOPE HE FINISHED TODAY, I THINK HE GOT SICK OR TOTALLY TURN OFF AFTER YESTERDAY'S MIDTERM. THEY HAD VERY HARD MIDTERM IN ARCHITECTURE SO HALF THE CLASS WAS SICK. OR PRETEND TO BE SICK OR SOMETHING. OKAY. ANYWAY, NEXT TUESDAY HOPEFULLY. HE'LL BE HERE TO HANDLE YOUR QUIZ. OKAY. SO NOW LET ME GO THROUGH THE MIDTERM WITH YOU, I MEAN SOLUTION OF THE MIDTERM. LIKE I SAID, YOU WILL GET MIDTERM BACK HOPEFULLY NEXT TUESDAY BUT THERE IS NO HARM THAT I GO THROUGH SOME OF THE PROBLEMS WITH YOU. I'M SORRY. AGAIN, THIS IS SO UNCLEAR. ANYWAY, I READ IT LOUD TO YOU. THE FIRST PROBLEM, WHEN YOU HAVE TAKEN THE MIDTERM, I WAS LOOKING AT ALMOST EVERYBODY GOT IT. WE ARE TALKING ABOUT MIN,MAX NOTATION AND IF WE HAVE THE NOTATION ZERO FIVE THIS MEANS THAT THE PARTICIPATION OF BOOKS IN THE BORROW RELATION IS PARTIAL BECAUSE NOT EVERYBODY -- NOT EVERY BOOK IS TO BE BORROWED. AND IF PATRON CAN BORROW UP TO FIVE -- A PATRON CAN BORROW UP TO FIVE BOOKS. MAYBE THIS LIBRARY LIKES TO LIMIT NUMBER OF BOOKS TO BORROW. I THINK EVERYBODY GOT THAT. THE SECOND PROBLEM, WELL, IF YOU DIDN'T STUDY THAT PART OBVIOUSLY YOU MISS THAT, THAT'S ONE POINT. YOU NEED TO GO THROUGH THE DATA TWICE. FIRST YOU NEED TO GO THROUGH IT FROM TOP TO BOTTOM AND THEN FROM BOTTOM TO TOP. OKAY? SO IF I START FROM THE TOP, THE FIRST STRING BEFORE A, BEFORE ADAM IS NO STRING, EMPTY STRING, SO THE SHORTER STRING DISTINGUISH ANOTHER STRING AND ANOTHER STRING IS JUST ONE LETTER. SO IT'S JUST A. THEN TO TELL ADAM AND APPLE APART, I NEED A AND P. THEN COME CAM TO TELL CAM AND APPLE APART I NEED ONLY ONE LETTER, C. TO TELL CAM AND CANDY APART I NEED THREE LETTERS, CAN, AND FINALLY I NEED CL TO TELL CLAM AND CANDY APART. SO IF YOU GOT THAT, IT'S HALF THE POINTS. THEN YOU NEED TO RUN THE SAME ALGORITHM BACKWARD AND THEN TAKE LONGER OF THE TWO STRINGS. WHY LONGER? BECAUSE WE WANT TO BE ABLE TO DISTINGUISH BOTH AGAINST THE STRING ABOVE IT AND THE STRING BELOW IT. SO IF YOU RUN IT BACKWARD AND COMBINE THE TWO YOU END UP WITH SOMETHING LIKE THIS. STUDENT: BETWEEN CAM AND CANDY, IF YOU ALREADY JUST C FOR CAM, WOULDN'T IT POSSIBLE YOU ONLY NEED CA FOR CANDY? PROFESSOR: WELL I'M RUNNING THE SAME ALGORITHM THIS WAY, OKAY? STUDENT: RIGHT. I MEAN THE ONE GOING DOWN. PROFESSOR: THE FIRST IS CL AND THEN COMES CA THEN I NEED CAM. STUDENT: I MEAN ON THE ONE GOING DOWN. PROFESSOR: ON THE ONE GOING DOWN? I NEED CAM. BECAUSE IF I JUST HAVE CA IT'S NOT SUFFICIENT TO TELL THEM APART. I NEED THREE LETTERS TO TELL. SEE? THAT'S THE SAME PREFIX. YOU LOOK AT IT THIS WAY. IF THEY HAVE PREFIX THE SAME FOR TWO LETTERS, YOU NEED THREE LETTERS TO TELL THEM APART. THEY SHARE FIRST TWO LETTERS SO THEY HAVE FIRST PREFIX OF TWO LETTERS I NEED ONE MORE TO TELL THEM APART. OKAY. SO YEN WAY, SO THIS ONE I THINK BECAUSE YOU DIDN'T STUDY THAT YOU MAY NOT GET IT BUT THAT'S JUST ONE POINT. YES? STUDENT: WHEN YOU'RE DOING BOTTOM UP WHY DID YOU SAY FOR CANDY YOU NEEDED THREE TO DISTINGUISH IT FROM CLAM? PROFESSOR: OH, THIS IS ALREADY RESULT OF THE MERGING OF THE TWO. SO IF I JUST RUN ALGORITHM BACKWARD, THE FIRST ONE WILL BE C, THE NEXT ONE TO TELL THEM APART I NEED CA AND THEN CAM. AND THEN NEXT ONE IS A AND NEXT IS AD, THEN I TAKE THE FIRST RESULT AND SECOND RESULT AND EACH CASE PAIRWISE I TAKE LONGER OF THE TWO. BUT THEN AGAIN IF YOU MISSED PART OF IT IT'S JUST HALF A POINT. THIS IS JUST TO MAKE SURE BECAUSE I DID SEND YOU A MESSAGE I'M GOING TO TEST ON COMPRESSION. NEXT ONE, I THINK THIS SHOULD BE INGRAINED IN YOU. IN OTHER WORDS, IF WE ARE TALKING WITH A DATABASE SYSTEM IT HAS TO BE THREE LEVELS, THIS IS VERY IMPORTANT FROM ARCHETECTURAL POINT OF VIEW, EXTERNAL, CONCEPTUAL AND INTERNAL. THE NEXT IS TO CONSTRUCT ER DIAGRAM. STUDENT: I USED DIFFERENT WORDS FOR NUMBER TWO. I THINK I USED EXTERNAL PHYSICAL, USER GROUP. PROFESSOR: THE TA WILL TAKE THAT IN CONSIDERATION TO SEE HOW CLOSE IT IS. I LEAVE JUDGMENT TO HIM AND THAT'S THALUS. EACH TIME I TELL YOU WHO GRADES IT. SO IF YOU HAVE SOME QUESTIONS YOU TALK TO THE SPECIFIC TA WHO IS GRADED IT. WE HAVE TWO SO IT'S -- I HAVE TO DISTINGUISH THAT. THE NEXT ONE IS OBVIOUS. BASICALLY YOU WANT TO DRAW A DIAGRAM SOMETHING LIKE THAT. AND ON ONE HAND WE HAVE CUSTOMER WHO WILL RENT CARS. AND SINCE WE RESTRICT OURSELF THAT EACH CUSTOMER CAN RENT EXACTLY ONE CAR BECAUSE I CANNOT IMAGINE WHY CUSTOMER WOULD RENT TWO. HE CANNOT DRIVE TWO CARS. WE ARE TALKING ABOUT THE SELF-SERVICE DRIVING, SO THE CAR CUSTOMER RELATIONSHIP IS ONE, ONE. THEN OF COURSE YOU HAVE THOSE ATTRIBUTES HANGING FROM THE ENTITIES AND THE RELATIONSHIPS. AND THEN HOW ABOUT THE CLASS DIAGRAM? WELL, I SIMPLIFIED IT. I DIDN'T ASK FOR THE WHOLE THING I NOTICE TWO OF YOU ACTUALLY DREW THE WHOLE DIAGRAM WHICH IS VERY GOOD ALTHOUGH I HAVE TO SAY I WON'T GIVE YOU BONUS POINT. YOU OVERDID IT WHICH IS GOOD. YOU BASICALLY HAVE NOTATION LIKE THAT, THIS IS CLASS CALLED CUSTOMER AND YOU HAVE A WHOLE BUNCH OF ATTRIBUTES AND THEN YOU HAVE SOME OPERATION, ADD, DELETE, STUFF LIKE THAT. THAT'S ALL YOU HAVE TO DO. IF YOU CONTINUE ON TO FINISH THE WHOLE DIAGRAM, YOU WILL HAVE THREE CLASSES AND ONE IS RENT CLASS, ONE IS THE CAR CLASS AND ONE IS THE CUSTOMER CLASS. AND IN THIS CASE IT HAPPENS THE RELATIONSHIP IS ONE, ONE ALL THE WAY. ONLY ONE CUSTOMER CAN RENT ONE CAR WITH ONLY ONE RENT RELATIONSHIP BETWEEN THEM. YEAH? STUDENT: ON ER DIAGRAM FOR THE RENT RELATION, THE ACTUAL RETURN TIME ATTRIBUTE WOULD THAT JUST BE A SEPARATE ATTRIBUTE? THAT WOULDN'T BE DERIVED OR ANYTHING WOULD IT? PROFESSOR: RIGHT. I PUT IT AS SEPARATE ATTRIBUTE. THIS IS KIND OF IMPORTANT. THEY WILL USE THAT TO PENALIZE YOU OR SOMETHING. SO THAT'S WHY I PUT IT THERE, YEAH. IN FACT, SOME STUDENT ALSO ASKED ME ABOUT CREDIT CHECKING. NOW THIS IS SIMPLIFIED PROBLEM. OBVIOUSLY FROM A COMMERCIAL, FROM BUSINESS POINT OF VIEW, THEY WILL PUT GREAT CARE IN CREDIT CHECKING, THERE SHOULD BE SEPARATE RELATIONS ON CREDIT CHECKING, SOMETHING LIKE THAT, THIS IS MUCH SIMPLIFIED ANSWER. YEAH? STUDENT: ON RELATIONSHIP CONSTRAINTS, WHEN I READ THAT, I, COULDN'T A BUSINESS RENT MORE THAN ONE CAR AT A TIME? I DON'T WANT TO LIKE SPLIT HAIRS. PROFESSOR: YEAH, IT COULD. I THINK I SAID IN THE PROBLEM SOMEWHERE THAT -- STUDENT: CAN IT BE ONE TO N THEN? PROFESSOR: IF YOU N TO N AND HAVE YOU A FOOTNOTE, DID HAVE YOU A FOOTNOTE? STUDENT: YEAH. PROFESSOR: OKAY THAT WILL BE TAKEN INTO CONSIDERATION. REMEMBER IN THE FIRST DESIGN PROBLEM I SAID YOU MAKE A DESIGN DECISION YOU HAVE TO EXPLAIN. STUDENT: I THOUGHT YOU DIDN'T WANT FOOTNOTES ON MIDTERM. PROFESSOR: I ACTUALLY ENCOURAGED SIMPLE SOLUTION. IT'S JUST HE WAS SAYING THAT HE CHOOSED OTHER WAY THAT HE HAVE TO EXPLAIN THAT TO ME, TO THE TA THAT HE DID MAKE THAT CHOICE. SO HE WILL TAKE THAT INTO CONSIDERATION. HOW MUCH POINT HE WILL TAKE OFF I DON'T KNOW SO YOU CAN, AGAIN, -- YOU WILL KNOW WHEN YOU GET IT BACK. ANY OTHER QUESTIONS? STUDENT: ON THE ER DIAGRAM, THE PARTICIPATION, WOULD THAT BE FULL AND PARTIAL? PROFESSOR: WHAT DO YOU THINK? STUDENT: WELL, TO BE A CUSTOMER HAVE YOU TO RENT THE CAR, RIGHT? SO EVERY CUSTOMER WOULD RENT. YEAH. IF THIS IS THE SO-CALLED. PROFESSOR: YEAH, IF THIS IS THE SO-CALLED, CURRENT CUSTOMER LESS. ON THE OTHER HAND IF IT IS THE CUSTOMER LIST, NAMELY -- GIVE ME EXAMPLE, HERTZ HAVE NUMBER ONE CLUB, GOLD NUMBER ONE CLUB, RIGHT? THESE ACTUALLY ARE POTENTIAL WHAT'S IT CALLED, PREFERRED CUSTOMER. SO DOES EVERY ONE RENTING CAR AT THE PRESENT TIME, PROBABLY NO. ON THE CAR SIDE IT'S OBVIOUS IT HAS TO BE PARTIAL. NOT EVERY CAR. SO MY DESIRE WOULD BE BOTH ARE PARTIAL BUT YOU CAN ARGUE IF THIS IS JUST CURRENT CUSTOMERS, YEAH. BUT YOU HAVE -- TO DO BUSINESS YOU WANT CUSTOMER LIST TO GO GREEN USE THAT FOR ADVERTISING, STUFF LIKE THAT. IT PROBABLY WON'T BE CURRENT LIST. THERE ARE A LOT OF DEVICE CHOICES THAT YOU HAVE TO MAKE. I'M NOT SAYING ONE OR THE OTHER IS CORRECT. QUESTIONS? SO THIS ONE PRETTY OBVIOUS. AND THEN OF COURSE WE HAVE DIVISION SO IDEA OF DIVISION IS THAT YOU MUST HAVE A TABLE OF EXACTLY TWO COLUMNS, AS THE DIVIDEND AND OTHER ONE IS ONE COLUMN AND YOU JUST CHECK WHETHER EVERY VALUE IS PRESENT. OKAY. SO HERE WE HAVE TWO TABLES. A1, A2, B1, B3, C2, C3 DIVIDED BY TABLE WITH JUST 1 AND 3. SO IF YOU HOLD THE FIRST VALUE A TO BE CONSTANT, AND ALL OF THEM ARE PRESENT WE RETAIN THAT, SO RESULT WOULD BE TABLE OF A AND B, C DOESN'T QUALIFY, RIGHT. IT DOESN'T HAVE ONE THERE. NOW THE B TREE. NOTICE THIS TIME I DIDN'T TEST ON EXTENDIBLE HASHING SO THAT MAY COME IN THE FINAL. SO YOU HAVE A SEQUENCE OF RECORDS CHARACTERIZED BY THE P'S AND YOU MAKE INSERTIONS. SO HERE IS WHAT I GOT. YOURS SOMETHING SIMILAR TO THAT? STUDENT: I CAN'T TELL. STUDENT: I CAN'T SEE. PROFESSOR: LET'S DO IT ON THE BOARD THEN. SO FIRST OF ALL P IS 4. 3 IS 4 MEANS HOW MANY P IS 4 MEANS HOW MANY KEYS PER NODE? STUDENT: THREE. PROFESSOR: P MINUS 1 IS THE NUMBER OF P'S AND P IS THE NUMBER OF POINTERS. IT WILL LOOK LIKE THIS. THIS IS YOUR NODE SO YOU HAVE FOUR POINTERS AND THREE KEYS. AND OUR SEQUENCE IS 5, 4, 3, 2, 1, 7, 6, 8, 9. 5 GOES HERE. FOUR COMES ALONG. IT IS CORRECT? NO. WHAT SHOULD I DO? I SHOULD SORT THEM. IN ANY NODE I WILL ALWAYS MAINTAIN THEM IN SORTED ORDER. THIS IS A LOCAL SORT. DOESN'T COST ME THAT MUCH. JUST A FEW ITEMS. SO THREE COMES ALONG SAME THING. IS IT THREE? 3, 4, 5. OKAY? STUDENT: YOU'RE NOT DRAWING -- ARE YOU DRAWING THE ACTUAL POINTERS TO THE RECORDS THAT ARE THE LEAVES OR DRAWING -- PROFESSOR: I DRAW THE KEYS. STUDENT: THE KEYS? WELL, WOULDN'T YOU HAVE THE FINAL, THE LEAF NODE THAT CONTAINS ACTUAL INDEX TO THE RECORDS, WOULDN'T 3, 4, 5 BE JUST ONE NODE AND THEN ONLY HAVE A KEY OF 5 ABOVE IT? PROFESSOR: YEAH. HERE IN THE NODE I ONLY KEEP THE KEYS. THE RECORD IS AT THE VERY BOTTOM. STUDENT: THAT'S WHAT I'M SAYING. YOU SHOULD ONLY HAVE -- HAVE YOU THE RECORD AT THE VERY BOTTOM WHICH IS A POINTER TO THE RECORD 3, RECORD 4 AND POINTER TO RECORD 5. ABOVE THAT HAVE YOU A KEY THAT POINTS TO THAT ONE LEAF NODE IN THE KEY WOULD BE 5 AND THERE IS NO OTHER KEYS? PROFESSOR: RIGHT. STUDENT: I GUESS I DON'T UNDERSTAND WHAT YOU'RE DRAWING. PROFESSOR: THIS IS JUST AN INDEX. STUDENT: ALL RIGHT. PROFESSOR: OKAY. SO, SO FAR, WE HAVE NOT SPLIT ANYTHING. SO NOW COMES 2. HOW SHOULD I SPLIT? HOW SHOULD WE SPLIT NOW? PROFESSOR: YES, 2 AND 3 GO TO ONE NODE. AND 4 AND 5 WILL GO INTO THE OTHER NODE. THAT'S THE REASON I PICK P EQUAL TO 4 THIS WAY WE ALWAYS HAVE EVEN SPLIT. SO THIS IS AFTER WE SPLIT ON THE SECOND LEVEL. HOW ABOUT HERE? STUDENT: 3. PROFESSOR: JUST 3, RIGHT. OKAY. WE WILL TAKE THE HIGHEST KEY HERE AND MOVE IT UP, HOW ABOUT HERE? STUDENT: NOTHING. PROFESSOR: NOTHING., RIGHT. SO WE ONLY NEED TO DO THIS AND THIS IS THIS POINTER, THIS IS THIS POINTER. OKAY? SO, NOW WE CONTINUE ON. WHAT'S THE NEXT ONE? THAT'S ONE. SO ONE IS LESS THAN THREE, SO WE GO THIS WAY LUCKILY WE STILL HAVE ROOM, SO I INSERT IT HERE. I THINK THAT'S WHAT I ASKED FOR, RIGHT? YEAH. GOT THE PICTURE, RIGHT? OKAY. SO I DON'T HAVE TO CONTINUE. BASICALLY THIS IS THE WAY YOU DO IT, JUST KEEP ON INSERTING AND SPLITTING. YEAH. OKAY. AND THEN THERE ARE A COUPLE OF TRUE AND FALSE QUESTIONS. A WEAK ENTITY DOES NOT HAVE ITS OWN KEY, THAT'S TRUE. A FILE CAN HAVE TWO PRIMARY KEYS, THAT'S FALSE. WHEN I SAY PRIMARY KEYS I MEAN WE DO HAVE TO IMPLEMENTED PRIMARY KEYS, IN OTHER WORDS, TWO PRIMARY KEYS AT WORK BUT AT ANY GIVEN TIME YOU CAN ONLY HAVE ONE PRIMARY KEY. THE DIVISION OPERATOR IS REQUIRED TO MAKE RELATIONAL ALGEBRA COMPLETE IS FALSE. NOTICE IN SQL IT DOESN'T SUPPORT DIVISION OPERATOR. THIS IS NOT VERY INTUITIVE AND VERY EXPENSIVE SO WE DON'T WANT TO HAVE THAT AROUND. WE DON'T HAVE TO HAVE DIVISION OPERATOR. AND WHERE CLAUSE IN THE SQL QUERY ALWAYS REQUIRED. NOT TRUE. WE CAN HAVE JUST SELECT FROM. THAT MAKES PERFECTLY GOOD QUERY. AND FINALLY WE HAVE TO TRANSLATIONS I WON'T GO INTO THAT BECAUSE THESE ARE BASICALLY WHAT YOU ALREADY DID IN THE EXERCISES. QUESTIONS? OKAY. SO YOU WILL GET DETAILS NEXT TIME WHEN THALUS GIVE YOU -- STUDENT: HOW MANY POINTS? PROFESSOR: 256789 NORMALLY PEOPLE WILL GET AN -- 25. NORMALLY PEOPLE GET 17, 18, SOME PEOPLE GET VERY GOOD SCORE. THERE IS A SPREAD BUT NORMALLY IT IS AROUND THAT. AS I MENTION IF YOU FEEL YOU NEED EXTRA POINTS, TRY TO HAND IN YOUR MINI PROJECT EARLY AND DO THE EXTRA POINT. THAT IS GOOD 16 POINTS. THAT WILL CERTAINLY LIFT YOUR GRADES UP. AND THAT WE WILL TART IN ANOTHER TWO WEEKS. TODAY WE START TALKING ABOUT NORMAL FORMS. NOW, NORMAL FORM IS IN THE HEART OF THE RELATIONAL DATABASE THEORY. THE REASON THAT IT IS IMPORTANT IS THAT IT ENABLE US TO GIVE CERTAIN PROBLEMS IN DATABASE DESIGN -- IF WE ARE NOT CAREFUL NOT USING NORMALIZED RELATIONS, THEN UPDATE COULD LEAD TO PROBLEMS, WHAT IS CALLED UPDATE ANOMALY SO WHAT I DO IS FIRST EXPLAIN TO YOU THE BASIC DEFINITIONS OF THE NORMAL FORMS, THEN WE WILL USE EXAMPLE TO ILLUSTRATE WHY NOT HAVING THE CERTAIN NORMAL FORM MAY LEAD TO PROBLEMS. IN FACT IN RELATIONAL DATABASE THEORY, NOW WE HAVE MANY MORE NORMAL FORM THAN THE BASIC THREE BUT IN TERMS OF PRACTICAL IMPORTANCE IT IS THESE THREE THAT ARE MOST IMPORTANT. WE START OBVIOUSLY FROM THE FIRST NORMAL FORM, THE FIRST NORMAL FORM IS THE ONE THAT WE JUST REQUIRE EVERYTHING TO BE ATOMIC. BY THAT WE MEAN IF YOU HAVE SOME VALUE IN YOUR RELATIONAL TABLE IT CAN EITHER BE A STRING OR A NUMBER AND THAT'S IT. NAMELY WE RULE OUT THE POSSIBLY OF HAVING AN IMBEDDED TABLE OR A NESTED TABLE OR NESTED RELATION. BECAUSE ONCE YOU START DOING THAT, YOU CAN'T -- YOU CAN HAVE ALL KINDS OF STRANGE AND WEIRD BEHAVIOR. NOT TO SAY THIS IS NOT USEFUL. SO IN THE FIRST EXAMPLE I SHOW HERE, THIS IS EXAMPLE WHERE YOU HAVE NESTED TABLE. SO FOR THE FIRST SUPPLIER S1, MAYBE THIS SUPPLIER SUPPLIES A BUNCH OF PARTS. SO WHY NOT REGARD IT AS SO-CALLED REPEATING GROUP? IN OTHER WORDS THIS GROUP REPEATS ITSELF, X NUMBER OF TIMES. THE VERY OLD HIERARCHICAL DATABASE SYSTEM WORKS THAT WAY. WE'LL TALK ABOUT THAT IN ANOTHER TWO WEEKS. SO IN OTHER WORDS, FROM THIS S NODE WE CAN HAVE A BUNCH OF REPEATED GROUP OF FIELDS. BUT IF DO YOU THAT, IT'S NO LONGER A VERY CLEAN TABLE BECOME A HIERARCHY. SO WE DON'T LIKE THAT, WE WANT TO FLATTEN IT OUT. BY THAT I MEAN, WE WILL ASSOCIATE S1 VALUE WITH EVERY MEMBER IN THIS REPEATING GROUP. SAME AS THIS SO IT BECOMES LIKE THIS. THE DOWNSIDE, OF COURSE, IS THAT IT -- THIS IS LESS EFFICIENT IN TERMS OF STORAGE, RIGHT? WE DON'T HAVE TO HAVE THIS S1 VALUE REPEATED BUT HERE WE DO THAT, SO IT'S LITTLE BIT MORE WASTEFUL IN TERMS OF STORAGE. BUT THE PLUS SIDE IS THAT NOW IT LOOKS UNIFORM. EVERYTHING LOOKS EXACTLY THE SAME. MAKES IT EASIER TO DESIGN PROGRAMS SO THAT WE CAN DO THE DATABASE RETRIEVAL AND MANIPULATION. SO THAT'S FIRST NORMAL FORM PURE AND SIMPLE, NAMELY, EVERY ENTRY HERE MUST BE ATOMIC. STRINGS OR NUMBERS. NO MORE. SO IF YOU USE ANY DATABASE SYSTEM ORACLE, D BASE WHATEVER, IT WILL ALWAYS OBEY THE FIRST NORMAL FORM PRINCIPLE. SO THAT ONE YOU DO NOT HAVE TO WORRY, IT ALREADY COMES IN HANDY. WHAT ABOUT THE OTHER NORMAL FORM? THIS CANNOT BE GUARANTEED NORMALLY UNLESS YOU PUT THE APPROPRIATE CONSTRAINT ON THE DATABASE. NOW, WE WILL ONLY LOOK AT SECOND AND THIRD NORMAL FORM ALTHOUGH LIKE I SAID, THE THEORY GOES BEYOND TO MORE. BUT THEY ARE MORE THEORETICAL INTEREST OR WHEN WE ARE DEALING WITH TEMPORAL DATABASE, THOSE ENTITIES, WHAT IS USEFUL WILL BET SECOND NORMAL FORM AND THIRD. 2NF AND 3NF TO DENOTE NORMAL FORMS. OKAY THE NORMAL FORM IDEA OF DEPENDENCY, OR FUNCTIONAL DEPENDENCY. IT'S DERIVED FROM THE IDEAS OF KEYS. EVERY RELATION MUST HAVE A KEY WE ALREADY DISCUSSED THAT. KEY IS THE ATTRIBUTE OR ATTRIBUTES THAT CAN UNIQUELY IDENTIFY EVERY RECORD OR EVERY ROW IN THE RELATION. SO IF I'VE GIVEN THE KEY, I CAN FIND ANOTHER VALUE OF OTHER ATTRIBUTES. SO IF I KNOW YOUR ASSOCIATE SECURITY NUMBER FROM THE TABLE -- SOCIAL SECURITY NUMBER FROM THE TABLE I CAN FIND YOUR NAME, ADDRESS, ET CETERA. SO THIS MEANS THAT THE SOCIAL SECURITY NUMBER DETERMINES THE REST OF THE ATTRIBUTES. OBVIOUSLY THE REST OF THE ATTRIBUTES DEPEND ON THIS KEY. SO WE WRITE IT IN A LITTLE ARROW THIS WAY. SO IF YOU SEE THIS NOTATION, THIS MEANS THAT IF I'M GIVEN SOCIAL SECURITY NUMBER IT DETERMINES THIS ATTRIBUTE. STUDENT: DID YOU SAY EVERY TABLE HAS TO HAVE A KEY? PROFESSOR: YES. STUDENT: EVEN IF IT'S POPULATED WITH WEAK ENTITIES. PROFESSOR: WEAK ENTITIES ONLY EXIST IN ER DIAGRAM IN DESIGN. ONCE WE REALIZE THAT INTERRELATIONAL DATABASE IT'S NO LONGER A SEPARATE THING. IT WILL BE ABSORBED INTO OUR DATABASE. SO EVERY TABLE YOU SEE IN RELATIONAL DATABASE YOU LOOK AT A TABLE IT MUST HAVE A KEY. OKAY. I KNOW WHAT YOU'RE THINKING, FOR DEPENDENCE, ONCE WE ARE DOING DATABASE DESIGN IT WILL BE ABSORBED INTO THE PRIMARY RELATION, I MEAN, IF YOU SEPARATE THEM OUT HAVE YOU TO RECORD WHO IS THE EMPLOYEE. WE WILL EXPAND. WHAT I MEAN IS FOLLOWING. SO IF I HAVE DEPENDENCE RELATION, SO LIKE WE HAVE THE TWO DEPENDENTS, MARY AND DOE, I CANNOT JUST CREATE A TABLE LIKE THIS. I HAVE TO CREATE A TABLE WITH THE KEY OF THE ONLY ENTITY, NAMELY THIS IS EMPLOYEE'S SOCIAL SECURITY NUMBER AND THIS IS THE DEPENDENT NAME. AND THEN THIS IS OTHER INFORMATION LIKE AGE. IN OTHER WORDS I HAVE TO KEEP OWNER ENTITIES KEY HERE. SO WHAT IS THE KEY OF THIS RELATION? STUDENT: SOCIAL SECURITY NUMBER. PROFESSOR: SOCIAL SECURITY NUMBER OR? SOCIAL SECURITY NUMBER AND NAME. IT IS A COMPOSITE KEY. SO EVERY RELATION MUST HAVE A KEY. STUDENT: AND THAT'S COMPOSITE -- PROFESSOR: THAT'S COMPOSITE. STUDENT: PEOPLE HAVE MULTIPLE DEPENDENTS. PROFESSOR: I WANT TO KEEP OWNER ENTITY KEY. STUDENT: WASN'T THERE A QUESTION ON THE TEST THAT SAID IF YOU HAVE ONE KEY -- PROFESSOR: ONLY HAVE ONE PRIMARY KEY, YES. IN THIS CASE, THIS COMBINATION IS THE PRIMARY KEY. STUDENT: BUT IT'S TWO THING THAT MAKE UP PRIMARY KEY. THAT'S WHY I PUT FALSE ON THE QUESTION. PROFESSOR: OKAY. THE KEY, THE WORD KEY, DOESN'T NECESSARILY MEAN JUST ONE ATTRIBUTE. OKAY? IN OTHER WORDS, IN THIS CASE, IF I JUST HAVE THE SOCIAL SECURITY NUMBER ITSELF, ONE ATTRIBUTE, IT IS NOT A UNIQUE KEY I HAVE TO USE BOTH. YEAH. SO THE PRIMARY KEY IS THE UNIQUE KEY WE IMPLEMENT FOR THE TABLE TO ADDRESS THAT. OKAY? SO YOU CANNOT HAVE TWO PRIMARY KEYS. YOU COULD HAVE A PRIMARY KEY AND ALTERNATE KEY, OKAY. ALTERNATE KEY IS LIKE VICE PRESIDENT. USUALLY DON'T USE THAT. OF COURSE HE LOOKS STIFF. STUDENT: ONCE THE TABLE IS CREATED DO YOU HAVE ABILITY TO CHANGE THE PRIMARY KEY? PROFESSOR: LIKE I JUST MENTIONED, IF I HAVE ALTERNATE KEY, AND FOR SOME REASON FOR THAT RELATION I WANT TO IMPLEMENT ALTERNATE KEY AS PRIMARY KEY I COULD DO THAT. STUDENT: AFTER THE TABLE IS STRUCTURED THE TABLE IS CREATED CAN YOU GO BACK AND ADD -- PROFESSOR: THAT'S -- I MEAN, REALISTICALLY THAT WOULD BE HARD. IT REQUIRES ME TO UNLOAD RELATION, RELOAD THAT AND REBUILD INDEXES. STUDENT: WHAT I'M GETTING AT IS, IF YOU HAVE A PRIMARY KEY BASED ON COMBINATION OF SOCIAL SECURITY NUMBER AND DEPENDENT'S FIRST NAME WHAT DO YOU DO IN A CASE WHERE YOU POPULATE THAT TABLE WITH 15,000 ENTRIES AND THAT EMPLOYEE HAS ANOTHER DEPENDENT OF SAME NAME POSSIBLY FROM A SECOND MARRIAGE? SEE WHAT I'M GETTING AT? PROFESSOR: I SEE WHAT YOU MEAN. IN OTHER WORDS, EMPLOYEE HAS TWO CHILDREN WITH SAME FIRST NAME? STUDENT: RIGHT. YOUR TABLE IS ALREADY CREATED, KEY WAS IN COUPLE YEARS AGO-. PROFESSOR: IN PRACTICE WHAT WILL HAPPEN IS THAT THAT DATA ENTRY CLERK OR THE PERSON THAT TOOK THE ENTRY WILL FORCEFULLY CHANGE ONE FIRST NAME INTO SOMETHING ELSE. STUDENT: ADD A LETTER OR NUMBER OR SOMETHING. PROFESSOR: YEAH. ARE THERE ANY PEOPLE FROM MALAYSIA HERE? PROBABLY NOT. IN MALAYSIA THE GENERAL PRACTICE IS THEY DON'T DISTINGUISH FIRST NAME AND LAST NAME. MAYBE THIS IS TRUE FOR MUSLIMS IN GENERAL. SO I USED TO HAVE A STUDENT WHOSE NAME IS MAMAN. HE IS CHINESE MALAYSIAN. I SAID WHAT'S YOUR NAME? HE SAID MAMAN. I SAID YOUR FIRST NAME. HE SAID MAMAN. HE HAS ONE NAME. MAMAN. IT CREATED PROBLEM WITH DATABASE SYSTEM. THIS GUY HAS NO FIRST NAME. AND YOU CAN NOT PUT IN NO STRING JUST NO STRING IS NOT ALLOWED. HE HAD TO BECOME A MAMAN MAMAN BECAUSE I HAD TO PUT SOMETHING THERE. STUDENT: HIS WIFE IS YELLING FOR HIS KIDS HOW DO THEY KNOW WHICH ONE TO ANSWER TO? PROFESSOR: MUST BE SOME WAY. SO, BASICALLY HERE EXPLAINING THE CONCEPT OF DEPEND DEPENDENCY. HERE YOU CAN SEE COMPOSITE KEY THERE. S NUMBER AND PART NUMBER TOGETHER IS THE KEY AND THAT DETERMINES THE QUANTITY SUPPLIED BY SUPPLIER TO SOME -- WELL, THE -- SORRY. THE QUANTITY THAT A SUPPLIER HAS FOR A CERTAIN ONE. SOCIAL SECURITY NUMBER AND NAME DETERMINES ADDRESS BUT NAME IS PROBABLY THE ALTERNATE KEY SO WE CAN SAY THAT ALTERNATE KEY SO WE CAN SAY THAT SSN DETERMINES ADDRESS. OKAY? SO NO PROBLEM. OKAY. NOW WE CAN GO BACK TO OUR OTHERS. WHAT IS SECOND NORMAL FORM? IT STATES THAT EVERY NONKEY ATTRIBUTE IS FULLY DEPENDENT ON THE PRIMARY KEY. EVERY NONKEY ATTRIBUTE. SO IF I HAVE A DATABASE AND I HAVE A SOCIAL SECURITY NUMBER, AND NAME AND ADDRESS AND GENDER AND I WANT TO DRAW THEIR DEPENDENCY RELATIONS, IT GOES LIKE THIS. SOCIAL SECURITY NUMBER DETERMINES NAME, IT ALSO DETERMINES ADDRESS AND IT ALSO DETERMINES GENDER. OR IF YOU PREFER, YOU CAN DRAW IT MORE NICELY AS WHAT WE CALL A DEPENDENCY DIAGRAM. OKAY. IN THAT CASE, SOCIAL SECURITY NUMBER DETERMINES ALL OF THE OTHER ATTRIBUTES SO EVERY NONKEY ATTRIBUTE DEPENDS ON THE SOCIAL SECURITY NUMBER. THIS IS SECOND NORMAL FORM. SO, IF I GIVE YOU A WHOLE BUNCH OF ATTRIBUTES AND ASK YOU TO DRAW THE DIAGRAM, YOU DO SOMETHING LIKE THIS. FIRST YOU DRAW THE PAIRWISE DEPENDENCY RELATION AND THEN YOU CAN COMBINE THEM TO DRAW A DIAGRAM LIKE THIS. IN FACT THIS DIAGRAM ALSO QUALIFY AS THIRD NORMAL FORM. WHY? BECAUSE EVERYBODY DEPENDS ON THE PRIMARY KEY AND IF YOU LOOK AT THE OTHER GUYS, THEY DON'T HAVE ANY MUTUAL DEPENDENCY. WHAT I MEAN THIS. IF YOU ARE GIVEN THE NAME YOU CANNOT DETERMINE THE ADDRESS. OR THE GENDER. IS THAT TRUE? MAYBE YOU CAN. OKAY. SO NOW IF THERE IS SUCH A RELATIONSHIP, I WILL DRAW SOME ADDITIONAL DEPENDENCY RELATIONS THERE. IN OTHER WORDS, THE ADDITION TO THE STRING MAY BE NAME, ALSO DETERMINES ADDRESS AND NAME DETERMINES GENDER. BECAUSE THE NAME IS ALTERNATE KEY. STUDENT: IS IT NO LONGER THIRD NORMAL FORM THEN? PROFESSOR: RIGHT. THIS SECOND NORMAL FORM. BECAUSE EVERYBODY STILL DEPENDS ON PRIMARY KEY, BUT NOW NOTICE THAT SINCE GOT A LITTLE BIT MESSED UP, THEY HAVE SOME MUTUAL DEPENDENCY SO IT'S NOT AS CLEAN. SO IF YOU END UP WITH A DIAGRAM LIKE THAT, THAT'S SECOND NORMAL FORM. THE ONE I HAD A MOMENT AGO, THAT'S THIRD NORMAL FORM WHERE EVERYBODY DEPENDS ONLY ON PRIMARY KEY BUT NOT ON EACH OTHER. THEY HAVE TO BE FULLY DEPENDENT ON PRIMARY KEY. NOW WHY THIS INSISTENCE? WELL, IT'S MAINLY BECAUSE IF WE HAVE WHAT WE CALL HIDDEN DEPENDENCY, THESE ARE THE HIDDEN DEPENDENCY, THEN IT MAY BE THAT WE ACTUALLY ARE TRYING TO ENCODE SOME INFORMATION THIS WAY. THIS IS NOT APPARENT IN THAT EXAMPLE BUT IF YOU LOOK AT ANOTHER EXAMPLE IT SHOWS VERY CLEARLY. SO LET'S LOOK AT A CASE WHERE WE HAVE SUPPLY NUMBER, STATUS, CITY, PART NUMBER AND QUANTITY. MAYBE BETWEEN CITY AND STATUS WE HAVE HIDDEN DEPENDENCY, SO IF YOU KNOW THE CITY, IT DETERMINES THE STATUS. IN OTHER WORDS, DIFFERENT CITIES HAVE DIFFERENT RANK, DIFFERENT STATUS. SO THAT IS ACTUALLY CAPTURED IN THE TABLE AS WELL. SO, IF YOU LOOK AT THIS THING, AND LOOK AT THE DIAGRAM, I HAVE SUPPLY NUMBER, PART NUMBER, THIS IS THE KEY COMBINED THEY DETERMINE QUANTITY. SUPPLY NUMBER ALONE ALSO DETERMINES STATUS AND CITY. AND CITY ALONE DETERMINES STATUS. FIRST OF ALL, WE DON'T LIKE IT BECAUSE IT'S NOT IN THE SECOND NORMAL FORM, BECAUSE EVERY NONKEY ATTRIBUTE DO NOT DEPEND ON THE ENTIRE KEY. IT MAY DEPEND ON ONLY PART OF IT. AND THEN THERE ARE THESE HIDDEN DEPENDENCIES WHICH CAN MESS THINGS UP. THE REASON IT MESSES UP IS THE FOLLOWING. SUPPOSE FOR SOME REASON I WANT TO ADD INFORMATION, SO WE ALREADY KNOW THAT PITTSBURGH IS STATUS ONE AND NOW SUDDENLY WE WANT TO ADD NEW YORK AS STATUS THREE. HOW AM I GOING TO DO THAT? THERE IS NO WAY. BECAUSE MY TABLE HAS ONE, TWO, THREE, FOUR, FIVE, FIVE ATTRIBUTES. I WANT TO ADD SOME INFORMATION WITH JUST TWO ATTRIBUTES. JUST THE CITIES NEW YORK AND STATUS IS THREE. I DON'T HAVE ANY VALUE FOR THE OTHER THREE. CAN'T ADD IT. THAT'S CALLED INSERTION ANOMALY. NAMELY YOU WANT TO PUT INFORMATION INTO THE TABLE BUT YOU CANNOT. LIKEWISE, THERE IS DELETION ANOMALY. IF I'M DOWN TO MY LAST TUPLE THAT INVOLVE NEW YORK, ONCE I LIMIT THAT THE INFORMATION ABOUT NEW YORK AND STATUS CAN BE LOST FOREVER WITHOUT ME EVER KNOWING IT. SO THIS IS WHAT WE DON'T LIKE TO SEE. SO, IN ORDER TO AVOID THIS TO HAPPEN, WHAT WE DO IS THAT WE GO THROUGH A PROCESS OF CONVERTING A RELATION FIRST NORMAL FORM INTO A WHOLE BUNCH OF SMALLER RELATIONS IN THIRD NORMAL FORM. OKAY. SO BASIC IDEA IS I HAVE BIG TABLE, LET'S CHOP IT UP AND COMPOSE IT INTO A WHOLE BUNCH OF SMALLER TABLES. IT'S EASIER TO MANAGE, MORE MEANINGFUL, SO IT WILL CAUSE LESS PROBLEMS. FOR MOST DATABASE SYSTEM, WHO IS DOING THAT? IT'S ACTUALLY THE DATABASE ADMINISTRATOR WHO IS DOING THAT. IN OTHER WORDS, WHEN YOU DESIGN DATABASE HAVE YOU TO DESIGN IT RIGHT. IF YOU DESIGN IT WRONGLY AND IT IS IN FIRST OR SECOND NORMAL FORM RATHER THAN THIRD, WILL THAT ALWAYS CAUSE PROBLEMS? NO. BECAUSE IF YOU DON'T ENCOUNTER THOSE STRANGE SITUATION, IT MAY NOT CAUSE PROBLEM FOR A LONG TIME. AND ALSO, MANY PEOPLE PREFER SIMPLICITY. AND THAT'S GREAT BIG TABLE SYNDROME, GBT. GBT STANDS FOR GREAT BIG TABLE. IN OTHER WORDS, WHEN YOU DESIGN DATABASE WHAT'S THE BEST WAY? HAVE A HUGE TABLE, PUT EVERYTHING THERE. IS THAT WRONG? NOT NECESSARILY. BECAUSE THE USER SOMETIMES LIKE THAT. YOU KNOW, IF YOU HAVE A BOSS WHO PREFERS SIMPLICITY, HE MAY LIKE THAT, HE CAN SEE EVERYTHING IN JUST ONE TABLE. OKAY? THAT'S THE GBT SYNDROME. MAY NOT BE BAD. I'M NOT SAYING THAT THIS IS NECESSARILY BAD BUT IT MAY CAUSE PROBLEMS. BUT YOU KNOW IF THERE ARE PROBLEMS, AS LONG AS YOU ARE AWARE OF THAT, YOU STILL CAN USE A SIMPLIFIED DESIGN. OKAY. NOW LET'S JUST GO THROUGH A FEW STEPS OF NORMALIZATION AND YOU WILL GO THROUGH SIMILAR PROBLEM IN THE QUIZ AS WELL AS EXERCISE. THE IDEA IS ACTUALLY VERY SIMPLE SO I CAN JUST TELL YOU IN TWO MINUTES AND LET YOU WORK OUT DETAIL LATER. SO, IF I HAVE A DEPENDENCY GRAPH, THIS IS THE DEPENDENCY GRAPH. LIKE THIS. FIRST LET ME ASK YOU, WHO IS THE KEY? STUDENT: A1. PROFESSOR: EVERYBODY DEPENDS ON A1. THAT IS NO QUESTION. IF I HAVE JUST ONE TABLE FOR ALL THESE ATTRIBUTES, IS IT IN THIRD NORMAL FORM? STUDENT: NO. PROFESSOR: NO. WHY NOT? STUDENT: A3 HAVE DEPENDENCY. PROFESSOR: RIGHT. IN OTHER WORDS, THEY HAVE -- THESE GUYS ARE THE NONKEY ATTRIBUTES AND THEY HAVE MUTUAL DEPENDENCIES THAT'S WHAT WE DON'T LIKE. HOW DO WE SOLVE PROBLEMS? BREAK IN TO TWO. IT'S RATHER OBVIOUS. CUT IT HERE AND MAKE A3 THE HEAD OF THE FAMILY SO WHAT YOU END UP IS TWO TABLES, R1 CONSISTS OF A1, A2 AND A3 AND R2 CONSISTS OF A3, A4, A5, THE PROBLEM IS SOLVED. AND THEN YOU USE JOIN TO COMBINE THE TABLES THROUGH THE SO-CALLED FOREIGN KEY BECAUSE NOW A3, WHICH IS NONKEY ATTRIBUTE BECOMES A KEY IN THE SECOND TABLE AND THAT GUY IS CALLED FOREIGN KEY. YOU KNOW FOREIGN KEY IS LIKE THE PRINCE WHO VISIT ANOTHER FOREIGN COUNTRY SO IN THAT COUNTRY HE PRETEND JUST TO BE ORDINARY CITIZEN. BUT ONCE HE GO BACK HOME HE IS THE KING, HE'S THE BOSS. SO THAT'S THE CONCEPT OF IT. FOREIGN KEY, SO THE IDEA IS VERY SIMPLE YOU START FROM THE KEY, TAKE ALL IMMEDIATE DESCENDANTS AND MAKE A RELATION AND CONTINUE THAT WAY. EVERYBODY SEE THAT? YEAH. WE CAN SPELL IT OUT ALGORITHM BUT I DON'T THINK IT'S NECESSARY. IT'S OBVIOUS. ALWAYS START FROM DEPENDENCY DIAGRAM, GO TO THE KEY, TAKE IMMEDIATE SUCCESSOR MAKE RELATION, TAKE IT OUT, AND THEN CONTINUE IN THAT FASHION. OKAY. SO WHEN YOU GO HOME, STUDY NORMAL FORM AND UPDATE ANOMALY.