database management system - gnuopen.gnu.ac.kr/lecslides/2017-2-db/dblec01_intro... ·...
TRANSCRIPT
![Page 1: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/1.jpg)
DatabaseManagementSystem
Lecture1
IntroductiontoRelationalDatabase
*SomematerialsadaptedfromR.Ramakrishnan,J.Gehrke
![Page 2: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/2.jpg)
Today’sAgenda• CourseLayout
• IntroductiontoRelationalDatabase
• OverviewofSQLandMySQL
DatabaseManagementSystem 2
![Page 3: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/3.jpg)
CourseLayout
DatabaseManagementSystem 3
![Page 4: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/4.jpg)
AboutthisCourse• Instructor• SeongjinLee
• Email:[email protected]
• Office:407-314
• OfficeHour:EveryThursday11:00-12:00orMakeappointment
• Class• Time:Thursday16:00-19:00
• Place:407-202
• Coursewebpage• http://203.255.57.228/MediaWiki/index.php?title=Database
• Textbook• DatabaseManagementSystem,3rd Ed.,Ramakrishnan&Gerhke,McGrawHill,2003
DatabaseManagementSystem 4
![Page 5: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/5.jpg)
AboutthisCourse• Goal– Tocovermajortopicsindatabasemanagementsystem
• Evaluation• Attendance– 10%
• Quiz– 10%Shortquizzesatthebeginningofmostclasses
• Assignments– 10%
• Project– 20%
• Midterm– 30%
• Final- 30%
• Closedbookandnotes
• Requestforregradewithinoneweekuponreturn;describereasonsinwriting
• whatandwhythescoreisincorrectorunfair
• Thewrittenargumentmustbeself-contained
DatabaseManagementSystem 5
![Page 6: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/6.jpg)
Topics• RelationalDatabase
• SQL
• LogicalDatabaseDesign• ConceptualModeling(Entity-RelationshipDiagram)
• Normalization
• DatabaseInternals• StorageandIndexing
• QueryOptimization
• PhysicalDatabaseDesign
• TransactionandRecovery
DatabaseManagementSystem 6
![Page 7: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/7.jpg)
AboutthisCourse• ReadingAssignments– Duebeforefollowingclassperiod
• Upto6Homeworkassignments(maybeless)• Pleaseturninassignmentsbytheduedate(checkthewebsite)
• Attendance• Participationisimportantpartofthiscourse
• 3absenceswithoutpriorarrangementwillloweryourgradebyoneletter(eachsubsequent1absenceswillloweragradebyoneletter)
DatabaseManagementSystem 7
![Page 8: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/8.jpg)
AboutthisCourse• AcademicHonesty• Assignments,quizzes,andexamsdoneindividually
• Nolying,cheating,copying
• Iffound,nogradeforthatparticularassessment
• Suspiciousworkwillbequestionedthoroughly
DatabaseManagementSystem 8
![Page 9: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/9.jpg)
AboutthisCourse• Noclasseson추석 thatisonOctober5th
• MidtermandFinal• Closebookandnotes
• MidtermonOct.19th (inclass)
• FinalonDec.14th (inclass)
• Quizzes• Togivefeedbackonyourunderstandingofmaterialaswellashelpwithmaterial
• atthebeginningofmostoftheclasses
• Basedonthepreviousclassmaterial
DatabaseManagementSystem 9
![Page 10: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/10.jpg)
IntroductiontoRelationalDatabase
DatabaseManagementSystem 10
![Page 11: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/11.jpg)
WhatIsaDBMS?• Averylarge,integratedcollectionofdata.
• Modelsreal-worldenterprise.• Entities (e.g.,students,courses)
• Relationships (e.g.,MadonnaistakingCS564)
• ADatabaseManagementSystem(DBMS)isasoftwarepackagedesignedtostoreandmanagedatabases.
DatabaseManagementSystem 11
![Page 12: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/12.jpg)
Filesvs.DBMS• Applicationmuststagelargedatasetsbetweenmainmemoryandsecondarystorage(e.g.,buffering,page-orientedaccess,32-bitaddressing,etc.)
• Specialcodefordifferentqueries
• Mustprotectdatafrominconsistencyduetomultipleconcurrentusers
• Crashrecovery
• Securityandaccesscontrol
DatabaseManagementSystem 12
![Page 13: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/13.jpg)
WhyUseaDBMS?• Dataindependenceandefficientaccess.
• Reducedapplicationdevelopmenttime.
• Dataintegrityandsecurity.
• Uniformdataadministration.
• Concurrentaccess,recoveryfromcrashes.
DatabaseManagementSystem 13
![Page 14: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/14.jpg)
WhyStudyDatabases??(1/2)• Itiscriticaltogovernment,business,science,etc.
• Manytechcompaniesarebuildondatamanagement(Google,Amazon,Facebook,Netflix,etc.)• ortheyofferdatabaseproducts(IBM,Oracle,Microsoft,etc.)
• Shiftfromcomputation toinformation• atthe“lowend”:scrambletowebspace(amess!)
• atthe“highend”:scientificapplications
• Datasetsincreasingindiversityandvolume.• Digitallibraries,interactivevideo,HumanGenomeproject,EOSproject
• ...needforDBMSexploding
DatabaseManagementSystem 14
![Page 15: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/15.jpg)
WhyStudyDatabases??(2/2)• Itspansmajorareasofcomputerengineering• Operatingsystems(file,memory,processmanagement)
• Theory(languages,algorithms,complexity)
• ArtificialIntelligence(knowledge-basedsystems,logic,search)
• SoftwareEngineering(applicationdevelopment)
• Datastructures(trees,hashtables)
• Datamaybeverylarge• Amazon>42TB
• Youtube >45TB
• AT&%>323TB
• NationalEnergyResearchScientificComputingCenter>2.8Petabytes
1TB=1,000,000,000,000Bytes1PB=1,000,000,000,000,000Bytes
DatabaseManagementSystem 15
![Page 16: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/16.jpg)
DataModels• Adatamodel isacollectionofconceptsfordescribingdata.
• Aschema isadescriptionofaparticularcollectionofdata,usingtheagivendatamodel.
• Therelationalmodelofdataisthemostwidelyusedmodeltoday.
• Mainconcept:relation,basicallyatablewithrowsandcolumns.
• Everyrelationhasaschema,whichdescribesthecolumns,orfields.
DatabaseManagementSystem 16
![Page 17: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/17.jpg)
LevelsofAbstraction• Manyviews,singleconceptual(logical)schema andphysicalschema.• Viewsdescribehowusersseethedata.
• Conceptualschemadefineslogicalstructure
• Physicalschemadescribesthefilesandindexesused.
☛ Schemas are defined using DDL; data is modified/queried using DML.
Physical Schema
Conceptual Schema
View 1 View 2 View 3
![Page 18: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/18.jpg)
Example:UniversityDatabase• Conceptualschema:
Students(sid:string,name:string,login:string,age:integer,gpa:real)
Courses(cid:string,cname:string,credits:integer)
Enrolled(sid:string,cid:string,grade:string)
• Physicalschema:• Relationsstoredasunorderedfiles.
• IndexonfirstcolumnofStudents.
• ExternalSchema(View):Course_info(cid:string,enrollment:integer)
DatabaseManagementSystem 18
![Page 19: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/19.jpg)
DataIndependence*• Applicationsinsulatedfromhowdataisstructuredandstored.
• Logicaldataindependence:Protectionfromchangesinlogicalstructureofdata.
• Physicaldataindependence:Protectionfromchangesinphysicalstructureofdata.
☛ One of the most important benefits of using a DBMS!
DatabaseManagementSystem 19
![Page 20: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/20.jpg)
ConcurrencyControl• ConcurrentexecutionofuserprogramsisessentialforgoodDBMSperformance.• Becausediskaccessesarefrequent,andrelativelyslow,itisimportanttokeeptheCPUhummingbyworkingonseveraluserprogramsconcurrently.
• Interleavingactionsofdifferentuserprogramscanleadtoinconsistency:e.g.,checkisclearedwhileaccountbalanceisbeingcomputed.
• DBMSensuressuchproblemsdon’tarise:userscanpretendtheyareusingasingle-usersystem.
DatabaseManagementSystem 20
![Page 21: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/21.jpg)
Transaction:AnExecutionofaDBProgram• Keyconceptistransaction,whichisanatomicsequenceofdatabaseactions(reads/writes).
• Eachtransaction,executedcompletely,mustleavetheDBinaconsistentstate ifDBisconsistentwhenthetransactionbegins.• Userscanspecifysomesimpleintegrityconstraintsonthedata,andtheDBMSwillenforcetheseconstraints.
• Beyondthis,theDBMSdoesnotreallyunderstandthesemantics ofthedata.(e.g.,itdoesnotunderstandhowtheinterestonabankaccountiscomputed).
• Thus,ensuringthatatransaction(runalone)preservesconsistencyisultimatelytheuser’sresponsibility!
DatabaseManagementSystem 21
![Page 22: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/22.jpg)
SchedulingConcurrentTransactions• DBMSensuresthatexecutionof{T1,...,Tn}isequivalenttosomeserialexecutionT1’...Tn’.• Beforereading/writinganobject,atransactionrequestsalockontheobject,andwaitstilltheDBMSgivesitthelock.Alllocksarereleasedattheendofthetransaction.(Strict2PLlockingprotocol.)
• Idea:IfanactionofTi(say,writingX)affectsTj(whichperhapsreadsX),oneofthem,sayTi,willobtainthelockonXfirstandTjisforcedtowaituntilTicompletes;thiseffectivelyordersthetransactions.
• WhatifTjalreadyhasalockonYandTilaterrequestsalockonY?(Deadlock!)TiorTjisaborted andrestarted!
DatabaseManagementSystem 22
![Page 23: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/23.jpg)
EnsuringAtomicity• DBMSensuresatomicity (all-or-nothingproperty)evenifsystemcrashesinthemiddleofaXact.
• Idea:Keepalog (history)ofallactionscarriedoutbytheDBMSwhileexecutingasetofXacts:• Before achangeismadetothedatabase,thecorrespondinglogentryisforcedtoasafelocation.(WALprotocol;OSsupportforthisisofteninadequate.)
• Afteracrash,theeffectsofpartiallyexecutedtransactionsareundone usingthelog.(ThankstoWAL,iflogentrywasn’tsavedbeforethecrash,correspondingchangewasnotappliedtodatabase!)
DatabaseManagementSystem 23
![Page 24: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/24.jpg)
TheLog• Thefollowingactionsarerecordedinthelog:• Tiwritesanobject:Theoldvalueandthenewvalue.
• Logrecordmustgotodiskbefore thechangedpage!
• Ticommits/aborts:Alogrecordindicatingthisaction.
• LogrecordschainedtogetherbyXactid,soit’seasytoundoaspecificXact(e.g.,toresolveadeadlock).
• Logisoftenduplexedandarchivedon“stable”storage.
• Alllogrelatedactivities(andinfact,allCCrelatedactivitiessuchaslock/unlock,dealingwithdeadlocksetc.)arehandledtransparentlybytheDBMS.
DatabaseManagementSystem 24
![Page 25: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/25.jpg)
Databasesmakethesefolkshappy...• EndusersandDBMSvendors
• DBapplicationprogrammers• E.g.,smartwebmasters
• Databaseadministrator(DBA)• Designslogical/physicalschemas
• Handlessecurityandauthorization
• Dataavailability,crashrecovery
• Databasetuningasneedsevolve
Must understand how a DBMS works!
DatabaseManagementSystem 25
![Page 26: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/26.jpg)
StructureofaDBMS(Fig.1.3,p.20)• AtypicalDBMShasalayeredarchitecture.
• Thisisoneofseveralpossiblearchitectures;eachsystemhasitsownvariations.
![Page 27: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/27.jpg)
SummaryofChapter1• DBMSusedtomaintain,querylargedatasets.
• Benefitsincluderecoveryfromsystemcrashes,concurrentaccess,quickapplicationdevelopment,dataintegrityandsecurity.
• Levelsofabstractiongivedataindependence.
• ADBMStypicallyhasalayeredarchitecture.
• DBAsholdresponsiblejobsandarewell-paid!J
• DBMSR&Disoneofthebroadest,mostexcitingareasinCS.
DatabaseManagementSystem 27
![Page 28: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/28.jpg)
TheRelationalModelChapter3to3.3
Theterminology
DatabaseManagementSystem 28
![Page 29: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/29.jpg)
RelationalDatabase• Assumethefollowingtable(A.K.ARelation)hasbeendefinedtokeeptrackofstudents
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
DatabaseManagementSystem 29
![Page 30: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/30.jpg)
Terminology• Thenameofthetable(relation):Students
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
DatabaseManagementSystem 30
![Page 31: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/31.jpg)
Terminology• Thenameoftheattributes(columns):SID,NAME,Department,GPA)
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
DatabaseManagementSystem 31
![Page 32: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/32.jpg)
Terminology• Schemaofthetable• Definitionandstructureoftherelation(includestypesandconstraints)
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
DatabaseManagementSystem 32
![Page 33: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/33.jpg)
Constraints• Foreveryattributeofeveryrelation,theschemaspecifiesallowablevalues
• Theallowablevaluesforanattributeiscalledthe“domain”oftheattribute
Account(Number:integer,name:string,Balance:currency,type:string)
DatabaseManagementSystem 33
![Page 34: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/34.jpg)
Terminology• Eachentryintherelationiscalled“row”,“tuple”,or“record”
• Instanceoftheschemaisthecurrentsetofrows
instancerows
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
DatabaseManagementSystem 34
![Page 35: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/35.jpg)
Terminology• “Degree”ofrelationisthenumberofattributes
• “Cardinality”ofrelationisthenumberofrowsinthecurrentinstance
Degreeofthisrelationis4(therearefourattributes,SID,Name,Department,GPA)
Cardinality
Cardinalityofthisinstanceis6(Thereare6rows)
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
DatabaseManagementSystem 35
![Page 36: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/36.jpg)
Terminology• EachTablehaskey
• Thevalueofthekeymustbeunique
• Whatisthekeyfortheeachrelations
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
Number ChkNo Date Amount
7003003 123 29-Aug-2017 840
7003003 124 30-Aug-2017 320
Check
DatabaseManagementSystem 36
![Page 37: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/37.jpg)
Terminology• Keyconsistsofoneormoreattributes
• Generallyunderlinethekeyattributes
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
Number ChkNo Date Amount
7003003 123 29-Aug-2017 840
7003003 124 30-Aug-2017 320
Check
DatabaseManagementSystem 37
![Page 38: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/38.jpg)
Constraints• SeetheDepositRelations
• Howcanwepreventitfromhappening?• UseforeignKey
• Deposit.Accnt isaforeignkey thatreferencesAccount.Number
• Calledenforcingreferentialintegrity
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
7003011 5 27-Aug-2017 2100
Deposit
DatabaseManagementSystem 38
![Page 39: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/39.jpg)
Terminology• Foreignkeysmayormaynotbepartofthekeyfortherelation
• Deposit.AccntisnotpartofthekeyforDeposit
• Check.Numberispartofthekeyforcheck
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
Number ChkNo Date Amount
7003003 123 29-Aug-2017 840
7003003 124 30-Aug-2017 320
Check
DatabaseManagementSystem 39
![Page 40: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/40.jpg)
MoreonSchema• Selectthetables,withanameforeachtable• adatabaseschemamayhavemultipletables
• Eachtablehasitsownschema
• Selectattributesforeachtableandgivethedomainforeachattribute
• Specifythekey(s)foreachtable• therecanbemorethan1keyforatable
• Specifyallappropriateforeignkeys
DatabaseManagementSystem 40
![Page 41: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/41.jpg)
Exercise• Createarelationwithonekey
• Createarelationwithsomeforeignkeysbasedonthefirstrelation
DatabaseManagementSystem 41
![Page 42: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/42.jpg)
StructuredQueryLanguage(SQL)Chapter3.4
DatabaseManagementSystem 42
![Page 43: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/43.jpg)
StructuredQueryLanguage(SQL)• ItisthelanguageusedtotalktoDBMS,andservesmanypurposes
• Todefineaboveschema
Account(Number:integer,name:character,Balance:currency,type:character)
CREATETABLEAccount(NumberintegerNOTNULL,Namecharacter,Balancecurrency,Typecharacter,PRIMARYKEY(Number)
)DatabaseManagementSystem 43
![Page 44: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/44.jpg)
StructuredQueryLanguage(SQL)• Toquerythedatabase
• Toinsertrowsintoatable:
INSERTINTOAccountVALUES(106,“LucyLou”,124000,“savings”);
SELECT*FROMAccountWHEREType=“checking”;
DatabaseManagementSystem 44
![Page 45: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/45.jpg)
QueryExample1• Showtheaccountthatmadedepositmorethan500
• SQLqueriesreturnnewtablesrepresentingtheanswertothequery
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTAccnt,AmountFROMDepositWHEREAmount>500;
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
DatabaseManagementSystem 45
![Page 46: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/46.jpg)
QueryExample1cont’d• SQLqueriesreturnnewtablesrepresentingtheanswertothequery
Accnt Amount
7003009 840
Deposit
DatabaseManagementSystem 46
![Page 47: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/47.jpg)
QueryExample2• Showthetransactionnumber3
• EachrowischeckedtoseeifWHEREclauseevaluatestotrue
• “*”Returneverycolumn
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECT*FROMDepositWHERETxID=3;
Accnt TxID Date Amount
7003003 3 26-Aug-2017 100
DatabaseManagementSystem 47
![Page 48: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/48.jpg)
Exercises• Writethequerytoshowallinformationfromaccountswithcheckingtype
• Howaboutallaccountswithsavingstype
• Ifyouwanttoknowtheowneroftheaccountswithcheckingtype,howwouldyouwritethequery
DatabaseManagementSystem 48
![Page 49: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/49.jpg)
QueryExample3
• Whatwouldbetheoutcomeofthequery?
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
SELECT*FROMAccountWHEREType=“checking”
ANDType=“savings”;
Accnt TxID Date Amount
Answerisempty
DatabaseManagementSystem 49
![Page 50: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/50.jpg)
EmptyQueryResults
• Itisnoterror,andcanbeinformativeinasense
• Becauseofthedomainofthetypeattributewillnevergiveanyresulttothequery
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
SELECT*FROMAccountWHEREType=“checking”
ANDType=“savings”;
Account
DatabaseManagementSystem 50
![Page 51: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/51.jpg)
EvaluatingtheSQLQuery1. FROMclausetellstheinputtables
2. Whereclauseisevaluatedforallcombinationsofrowsfromtheinputtables
3. SELECTclausedecideswhichattributesremainsinthequeryresult
SELECTAccnt,AmountFROMDepositWHEREAmount>500;
DatabaseManagementSystem 51
![Page 52: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/52.jpg)
Queriesovertwotables:a”Join”
• WhichrowsfromwhichtablesareevaluatedintheWHEREclause?
• “A”isacorrelationnameforAccount
• “D”isacorrelationnameforDeposit• Correlationnameactsasalocalvariable
• Holdsonerowfromthecorrespondingtable
• Tablenamecanalsobeusedasacorrelationname,butitislonger
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account AND
A.Balance>2,200,000;
DatabaseManagementSystem 52
![Page 53: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/53.jpg)
QueryExample4
• Checkeverycombinationofonerowfromeachtable
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
DatabaseManagementSystem 53
![Page 54: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/54.jpg)
QueryExample4
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
EvaluateeachrowIfnottrue,Throw!
Number Name Balance Type Accnt TxID Date AmountIntermediateQueryResultTable
DatabaseManagementSystem 54
![Page 55: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/55.jpg)
QueryExample4
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
EvaluateeachrowIfnottrue,Throw!
Number Name Balance Type Accnt TxID Date AmountIntermediateQueryResultTable
DatabaseManagementSystem 55
![Page 56: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/56.jpg)
QueryExample4
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
EvaluateeachrowIftrue,Take!
Number Name Balance Type Accnt TxID Date Amount
7003003 AlfredHitchcock 4,400,200 Savings 7003003 3 26-Aug-2017 100
IntermediateQueryResultTable
DatabaseManagementSystem 56
![Page 57: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/57.jpg)
QueryExample4
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
EvaluateeachrowIftrue,Take!
Number Name Balance Type Accnt TxID Date Amount
7003003 AlfredHitchcock 4,400,200 Savings 7003003 3 26-Aug-2017 100
7003009 JasonBourn 3,025,000 Checking 7003009 4 26-Aug-2017 840
IntermediateQueryResultTable
DatabaseManagementSystem 57
![Page 58: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/58.jpg)
QueryExample4
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
EvaluateeachrowIftrue,Take!
Name Balance
AlfredHitchcock 4,400,200
JasonBourn 3,025,000
FinalQueryResultTable
DatabaseManagementSystem 58
![Page 59: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/59.jpg)
UsefulKeyword:DISTINCT• Suppose
• DISTINCTremovesduplicaterows
SELECTDISTINCTA.Name,A.BalanceFROMAccountA,DepositDWHEREA.Number=D.Account ANDA.Balance>2,200,000;
Number Name Balance Type Accnt TxID Date Amount
7003003 AlfredHitchcock 4,400,200 Savings 7003003 3 26-Aug-2017 100
7003009 JasonBourn 3,025,000 Checking 7003009 4 26-Aug-2017 840
7003009 JasonBourn 3,025,000 Checking 7003009 5 27-Aug-2017 1000
IntermediateQueryResultTable
Name Balance
AlfredHitchcock 4,400,200
JasonBourn 3,025,000
JasonBourn 3,025,000
FinalQueryResultTableName Balance
AlfredHitchcock 4,400,200
JasonBourn 3,025,000
FinalQueryResultTable
w/oDISTINCT w/DISTINCT
DatabaseManagementSystem 59
![Page 60: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/60.jpg)
Exercise
• Howmanyrowswillbeinthequeryresult?
• Howmaycolumnswillbeinthequeryresult?
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Accnt TxID Date Amount
7003007 1 16-Aug-2017 200
7003001 2 19-Aug-2017 400
7003003 3 26-Aug-2017 100
7003009 4 26-Aug-2017 840
Deposit
SELECTA.Number,A.Name,D.Date,D.AmountFROMAccountA,DepositDWHEREA.Number=D.Account ANDD.Amount>100;
DatabaseManagementSystem 60
![Page 61: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/61.jpg)
NotesonQueries• Aqueryisexpressedagainsttheschema
• But,thequeryisexecutedagainsttheinstance,thatisagainstthedata
• Theresultofaqueryisalwaysatable• resultingtabledonotalwayshaveaname
• Attributesdeducedfrominputtables
• resultmaynothaveanyrows
DatabaseManagementSystem 61
![Page 62: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/62.jpg)
NotesonQueries• SelfJoins– Here,A1andA2refertocopiesofthesameinstance
SELECTA1.Number,A2.NumberFROMAccountA1,AccountA2WHEREA1.Balance=A2.BalanceAND
A1.Number>A2.Number;
DatabaseManagementSystem 62
![Page 63: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/63.jpg)
NotesonQueries
• Namingattributesandqueryresult
• MyTablecanbeusedasatableinsubsequentqueries
• Remembertodeletethetemporarytables!
Number Name Balance Type
7003001 JaneSmith 1,000,000 Savings
7003003 AlfredHitchcock 4,400,200 Savings
7003005 TakumiFujiwara 2,230,000 Checking
7003007 Brian Mills 1,200,000 Savings
7003009 JasonBourn 3,025,000 Checking
Account
Owner Amount
TakumiFujiwara 2,230,000
JasonBourn 3,025,000
MyTable
SELECTNameAsOwner,BalanceasAmountINTOMyTableFROMAccountWHEREType=“Checking”;
DatabaseManagementSystem 63
![Page 64: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/64.jpg)
TypicalDatabaseLifecycle
DesignDataModel
[ERDiagram]
CreateSchema[RelationalTables]
RemoveRedundancy
[Normalization]
DetermineIndexes
[PhysicalDesign]
ManageTables[SQL]
ImplementInterface
[Application]
[ch.2] [ch.3] [ch.21]
[ch.6,7] [ch.4,5] [ch.22]
DatabaseManagementSystem 64
![Page 65: Database Management System - GNUopen.gnu.ac.kr/lecslides/2017-2-DB/DBLec01_intro... · 2017-08-28 · Database Management System 3. About this Course •Instructor •Seongjin Lee](https://reader031.vdocuments.us/reader031/viewer/2022022012/5b1f9f887f8b9a47328b6635/html5/thumbnails/65.jpg)
ForNextWeek• Review– Quizonthematerial• Ch.1
• Ch.3to3.3
• Ch.5to5.2
• Readingassignments• Ch.4to4.2
• Ch.5.5
• Besureyouunderstand• thebasicterminology
• BasicSQLQueries(SELECT,FROM,WHERE)
DatabaseManagementSystem 65