Unformatted text preview:

DatabasesWhy? • Abstraction of logical from physical structure • Allows separation of a program’s “business logic” from concerns about traversal of the dataTypes of databases • Object – direct representation of programming language objects • Relational (<== dominant) – Tables – Operations • Select, project, union • Join (natural, inner, outer, left, right, …) – Indexes • Hierarchical (e.g., XML) – Parent-child • Network • Flat files (e.g., spreadsheet, text file)Hierarchical Model Figure from Association for Computing Machinery removed due to copyright restrictions. See Levin, Michael. "An introduction to DIAM: levels of abstraction in accessing information." Association for Computing Machinery, 1978.Network Model Figure from Association for Computing Machinery removed due to copyright restrictions. See Levin, Michael. "An introduction to DIAM: levels of abstraction in accessing information." Association for Computing Machinery, 1978.Relational Database Underlying Concepts • Individual entities • Their properties • Relations among them – 1-1 – 1-n (or n-1) – n-n • Integrity • TransactionsRelational Model Figure from Association for Computing Machinery removed due to copyright restrictions. See Levin, Michael. "An introduction to DIAM: levels of abstraction in accessing information." Association for Computing Machinery, 1978.Relational Algebra Operations • Select—subset of rows with conditions • Project—subset of columns • Join A and B – Outer: cross product of all rows in A and B, result includes all columns of each – Natural: select rows of cross-product in which matching columns have same values – Join on specific column relations (=, >, <, …) • Grouping operations (partition by criteria) • Summarization (count, max, min, average) 8MySQL SELECT syntax SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, ... [FROM table_references [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name' | INTO @var_name [, @var_name]] [FOR UPDATE | LOCK IN SHARE MODE]] 9MySQL SELECT examples select * from persnl_public where last_name=‘Bird’; select pat_num from persnl_public, ppr where persnl_public.persnl_id=ppr.provider_id and persnl_public.last_name=‘Bird’; select d.last_name,d.first_name from persnl_public as p, ppr, pat_demograph as d where p.persnl_id=ppr.provider_id and ppr.pat_num=d.pat_num and p.last_name=‘Bird’; select p.last_name,p.first_name,count(*) as c from persnl_public as p join ppr on p.persnl_id=ppr.provider_id group by p.persnl_id having c>1 order by c desc; 11MIT OpenCourseWarehttp://ocw.mit.edu HST.950J / 6.872 Biomedical Computing Fall 2010 For information about citing these materials or our Terms of Use, visit:


View Full Document
Download Databases
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Databases and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Databases 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?