The Relational ModelAdministriviaOutlineReview: What is a database?Review: What is a DBMS?Review: ACID propertiesReview: DBMS componentsSlide 8Review: How does a DBMS work?Review: Typical architecture for DB applicationsSummary: Benefits of a DBMSMinibase is a Java-based DBMSIntermissionA brief history of databasesSlide 15So what’s the big deal about the relational data model?Slide 17Relational Database: DefinitionsEx: Instance of Students RelationSQL - A language for Relational DBsSQL OverviewCreating Relations in SQLTable Creation (continued)Adding and Deleting TuplesKeysKeys are the key to data independence!Slide 27Slide 28Let’s return to our bank…Exercises to test your understanding…The Relational ModelCS 186, Spring 2007, Lecture 2Cow book Section 1.5, Chapter 3Mary RothAdministrivia•Homework 0 –Due next Tuesday, Jan 23 10 p.m.–Submission instructions added to homework description–Class account forms here if you need them•Discussion sections will meet today•Questions?Outline•What we learned last time–What is and what good is a DBMS anyway?–Components of a DBMS•New stuf–A brief history of databases–The relational data modelReview: What is a database?•A collection of data organized for rapid search and retrieval–Data collection has some logical meaning, and some reason for it to be organized in a particular way.Review: What is a DBMS?•A software system designed to manage a database.–Think big and lots of data•300,000,000 bank accounts–Think mission critical•1,000,000 transactions a day•You’d need a DBMS to:–Help you find things fast–Help you keep track of what’s going onReview: ACID properties•A DBMS ensures a database has ACID properties:•Atomicity – nothing is ever half baked; database changes either happen or they don’t.•Consistency – you can’t peek at the data til it is baked; database changes aren’t visible til they are commited•Isolation – concurrent operations have an explainable outcome; multiple users can operate on a database without conflicting•Durability – what’s done is done; once a database operation completes, it remains even if the database crashesReview: DBMS components•A DBMS is like an ogre; it has layers–We’re going to learn about these layers all semester–We’re going to build several layers in our homework projectsQuery Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBReview: DBMS componentsQuery Optimizationand ExecutionRelational OperatorsAccess MethodsBuffer ManagementDisk Space ManagementDB•Makes efficient use of disk space-> Think 300,000,000 accounts!•Makes efficient use of RAM-> Think 1,000,000 simultaneous requests!•Provides generic ways to combine data-> Do you want a list of customers and accounts or the total account balance of all customers?•Figures out the best way to answer a question-> There is always nore than 1 way to skin a cat…!•Provides efficient ways to extract data-> Do you need 1 record or a bunch?Database application•Talks to DBMS to manage data for a specific task-> e.g. app to withdraw/deposit money or provide a history of the accountReview: How does a DBMS work?Query Optimizationand ExecutionRelational OperatorsAccess MethodsBuffer ManagementDisk Space ManagementCustomer accounts stored on diskQuery in:e.g. “Select min(account balance)”Data out:e.g. 2000Database appReview: Typical architecture for DB applicationsDBMSCommand line GUIJDBC/ODBC appApp serverJDBC/ODBCWeb browser1. Enter queries, etc.By typing text2. Graphically compose queries, look at data3. Embed database access in a program4. Embed database access in a web applicationSummary: Benefits of a DBMS1. Data independence–applications worry about what data they want, not how it is stored2. Efficient data access–DBMS is smart about how to retrieve data3. Data integrity and security–DBMS won’t let you corrupt data4. Centralized administration–stored data on single server and let people specialize in managing it5. Concurrent access–Handles multiple users efficiently and recoverably6. Reduced application development time –Derived from 1-5Minibase is a Java-based DBMSQuery Optimizationand ExecutionRelational OperatorsAccess MethodsBuffer ManagementDisk Space ManagementDB•Provided for you•Homework 1•Homework 4•Homework 3 (Pencil-work)•Homework 2Database application•Homework 5Intermission•Get up and stretch•Ask a quick question•Get a drink of waterA brief history of databases•Birth of the DBMS parallels adoption of computer over 1960s and 1970s•1960s: IBM introduced IMS–36 years old!–‘Legacy’ technology, but still important!•100,000,000 bank transactions a day move money through IMS system•A bank manages over 300,000,000 online bank accounts on IMS•One production IMS system has been running for over 8 years without down time or a crashA brief history of databases•1970: Ted Codd introduced the relational data model –Revolutionary idea that spurred a flurry of DBMS activity–…at IBM (System R, DB2)–…at Universities like Berkeley (Ingres)–…at Oracle (it was born!!)•Ted Codd won the Turing award in 1981•Larry Ellison became a gillionaireSo what’s the big deal about the relational data model?•What is the first benefit of a DBMS?–Data independence•A Data Model is key to data independence –It’s the link that provides an abstraction between user’s view of the world and bits stored in computer1010111101Student (sid: string, name: string, login: string, age: integer, gpa:real)So what’s the big deal about the relational data model?•It is now the most widely used data model.•Before 1970, there were other data models…–Network–Hierarchical (IMS)•But they didn’t really provide data independence–If the data layout changed, the application had to change–If you wanted to change the layout, you often had to bring the whole system down–Changes had to occur over scheduled system down time.•Slow! Annoying! Expensive!•The relational model changed all that.Relational Database: Definitions•Relational database: a set of relations. •Relation: made up of 2 parts:–Schema : specifies name of relation, plus name and type of each column. •e.g. Students(sid: string, name: string, login: string, age: integer, gpa: real) –Instance : a table, with rows and columns. •#rows = cardinality•#fields = degree / arity•You can
View Full Document
Unlocking...