Introduction to Database Systems CSE 444StaffCommunicationsTextbookOther TextsPowerPoint PresentationTraditional Database ApplicationWhat we need from a database:Slide 9Trying Without a DBMSSlide 11Functionality of a DBMSBuilding an Application with a DBMSSchema Design and ImplementationQuerying a DatabaseQuery OptimizationTraditional and Novel Data ManagementDatabase IndustryThe Study of DBMSCourse (Rough) OutlineOutline (Continued)Slide 23StructureThe ProjectIntroduction to Database SystemsCSE 444Lecture #1April 1st, 2002Staff•Instructor: Alon Halevy–Sieg, Room 310, [email protected] –Office hours: Wednesday 2:30-3:30– (or by appointment) •TA’s: Luna Dong and Man Chun Liu–Sieg 226b, {lunadong,manchun}@cs.washington.edu–Office hours: TBACommunications•Web page: http://www.cs.washington.edu/444/ •Mailing list: send email to majordomo@cs saying (in body of email): subscribe cse444Textbook•Database Systems: The Complete Book, by Garcia-Molina, Ullman and Widom, 2002•Comments on the textbook.Other Texts•Database Management Systems, Ramakrishnan–very comprehensive•Fundamentals of Database Systems, Elmasri and Navathe–very widely used•Foundations of Databases, Abiteboul, Hull and Vianu –Mostly theory of databases•Data on the Web, Abiteboul,Buneman,Suciu–XML and other new/advanced stuffAvailable on reserve, at the libraryTraditional Database ApplicationSuppose we are building a system to store the information about:•students•courses•professors•who takes what, who teaches whatWhy use a DBMS ?What we need from a database:•store the data for a long period of time–large amounts (100s of GB)–protect against crashes–protect against unauthorized use•allow users to query/update: –who teaches “CSE142”–enroll “Mary” in “CSE444”•allow several (100s, 1000s) users to access the data simultaneously•allow administrators to change the schema–add information about Tas•We want the database to allow us to focus on the application logic!Trying Without a DBMSWhy Direct Implementation Won’t Work:•Storing data: file system is limited–size less than 4GB (on 32 bits machines)–when system crashes we may loose data–password-based authorization insufficient•Query/update:–need to write a new C++/Java program for every new query–need to worry about performance•Concurrency: limited protection–need to worry about interfering with other users–need to offer different views to different users (e.g. registrar, students, professors)•Schema change:–need to rewrite virtually all applicationsFunctionality of a DBMS•Data Definition Language - DDL•Data Manipulation Language - DML–query language•Storage management•Transaction Management–concurrency control–recoveryBuilding an Application with a DBMS•Requirements modeling (conceptual, pictures)–Decide what entities should be part of the application and how they should be linked.•Schema design and implementation–Decide on a set of tables, attributes.–Define the tables in the database system.–Populate database (insert tuples).•Write application programs using the DBMS–way easier now that the data management is taken care of.addressname fieldProfessorAdvisesTakesTeachesCourseStudentname categoryquarternamessnConceptual ModelingcidSchema Design and Implementation•Tables:•Separates the logical view from the physical view of the data.SSN Name Category123-45-6789 Charles undergrad234-56-7890 Dan grad… …SSN CID123-45-6789 CSE444123-45-6789 CSE444234-56-7890 CSE142…Students: Takes:CID Name QuarterCSE444 Databases fallCSE541 Operating systems winterCourses:Querying a Database•Find all courses that “Mary” takes•S(tructured) Q(uery) L(anguage)•Query processor figures out how to answer the query efficiently. select C.namefrom Students S, Takes T, Courses Cwhere S.name=“Mary” and S.ssn = T.ssn and T.cid = C.cidselect C.namefrom Students S, Takes T, Courses Cwhere S.name=“Mary” and S.ssn = T.ssn and T.cid = C.cidQuery OptimizationImperative query execution plan:select C.namefrom Students S, Takes T, Courses Cwhere S.name=“Mary” and S.ssn = T.ssn and T.cid = C.cidselect C.namefrom Students S, Takes T, Courses Cwhere S.name=“Mary” and S.ssn = T.ssn and T.cid = C.cidDeclarative SQL queryPlan: tree of Relational Algebra operators, choice of algorithms at each operatorIdeally: Want to find best plan. Practically: Avoid worst plans!Goal:StudentsTakessid=sidsnamename=“Mary” cid=cidCoursesTraditional and NovelData Management•Traditional Data Management:–relational data for enterprise applications–storage–query processing/optimization–transaction processing•Novel Data Management:–Integration of data from multiple databases, warehousing.–Data management for decision support, data mining.–Exchange of data on the web: XML.Database Industry•Relational databases are a great success of theoretical ideas.•Big DBMS companies are among the largest software companies in the world. •Oracle•IBM (with DB2)•Microsoft (SQL Server, Microsoft Access)•Sybase•$20B industry.The Study of DBMS•Several aspects:–Modeling and design of databases–Database programming: querying and update operations–Database implementation•DBMS study cuts across many fields of Computer Science: OS, languages, AI, Logic, multimedia, theory...Course (Rough) Outline•Database design:–Entity Relationship diagrams–ODL (object-oriented design language)–Modeling constraints•The relational model:–Relational algebra–Transforming E/R models to relational schemas•XML: a data format for the WebOutline (Continued)•SQL (“intergalactic dataspeak”) –Views and triggers •Advanced query languages:–Recursive queries and datalog–Object-oriented features–Queries for XMLOutline (Continued)•Storage and indexing•Query optimization•Transaction processing and recovery•Advanced topicsStructure•Prerequisites: Data structures course (CSE-326 or equivalent).•Work & Grading:–Homework 25%: 5 of them, some light programming.–Project: 30% - see next.–Midterm: 15%–Final: 25%–Intangibles: 5%The Project•Goal: design end-to-end database application.•Work in groups of 3-4 (start forming now).•Topic: you select. Suggestions on the web site.•Timetable for project milestones.•Be
View Full Document
Unlocking...