ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

Ch. 15 FIT5, CIS 110 13F

?
?
M
mh-108

This document summarizes key concepts from Chapter 15 on database concepts. It discusses the differences between tables and databases, with rows representing records and columns representing properties. It introduces database terminology like entities, attributes, and metadata. XML is presented as a way to represent data structures with tags for identity and affinity. Relational databases and structured query language (SQL) are discussed. The advantages of relational database management systems (RDBMS) are outlined. MySQL is presented as a widely used open source RDBMS, with examples of using the SELECT operation.

1 of 36
Download to read offline
Chapter 15 Introduction to Database Concepts Read pp. 457-488 only Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Differences Between Tables and Databases ? When we think of databases, we think of tables of information: ¨C iTunes shows the title, artist, running time on a row row = a record (noun) for a track column = property Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Differences Between Tables and Databases ? When we think of databases, we think of tables of information: ¨C Your car¡¯s information is one line in the state¡¯s database of automobile registrations row = a record for a car column = property Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Differences Between Tables and Databases Rows = Records Cols = Properties Record a.k.a. Entity, Instance, Object Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Canada¡¯s Demographic Information Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The Database¡¯s Advantage ? Metadata is the key advantage of databases over other approaches to recording data as tables ¨C Database software can search for the <country> tag surrounding Canada ¨C Metadata: Data about Data Metadata == Semantic Markup Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The Database¡¯s Advantage ? The tags for the CIA database fulfill two key metadata roles: ¨C Define the identity of the data (-> instance, entity, object) ¨C Define the affinity of the data (-> properties) Tags enclose all data that is logically related. Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The Database¡¯s Advantage ? <country>, <population>, and similar tags have the role of identification because they label content ¨C instance, entity, object ? <demogData> tag has the role of implementing affinity because it groups an entity¡¯s properties Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley XML: Extensible Markup Language ? XML is a Meta-language: a language for creating new markup languages ¨C Use XML to create custom Tag sets ? All markup languages are self-describing languages Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Rules for Writing XML Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley XML Example ? Scenario: ¨C Create a database for the Windward Islands archipelago in the South Pacific ¨C Plan what information will be stored ¨C Develop those tags: <archipelago> <island> <iName> Tahiti </iName> <area>1048</area> </island> ? </archipelago> Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley XML Example ? Scenario: ¨C Create a database for the Windward Islands archipelago in the South Pacific ¨C Plan what information will be stored ¨C Develop those tags: <archipelago> <island> <iName> Tahiti </iName> <area>1048</area> </island> ? </archipelago> Affinity role Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley New root element Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Attributes in XML ? XML tags can have attributes ¨C Tag attribute values can be enclosed either in paired single or paired double quotes ¨C Use attributes for metadata, not for content Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Identity Rule: Label Data with Tags Consistently ¨C You can choose whatever tag names you wish to name data, but once you¡¯ve decided on a tag for a particular kind of data, you must always surround that kind of data with that tag. Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Affinity Rule: Group Related Data ¨C Enclose in a pair of tags all tagged data referring to the same entity, instance, object ¨C Grouping makes an association of the tagged data items as being related => properties of the entity. Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Collection Rule: Group Related Instances ¨C When you have several instances of the same kind of data, enclose them in tags Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Group Related Instances ¨C A group of five islands were grouped inside an <archipelago> tag ¨C A group of two archipelagos were grouped inside a <geo_feature> tag Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Relational Data & RDBMS ? Relational databases ¨C data are stored in tables (relations) in a db. ¨C A table stores information in rows (records) and columns (fields, properties). ¨C Relational databases include a Structured Query Language (SQL) => application programming interface (API) Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Relational Data & RDBMS ? Developed in the 1970s, relational databases are based on an underlying mathematical model, have great expressive power and are a core computer science technology that has migrated to all fields. ? The RDBMS market is dominated by Oracle, IBM DB2, and Microsoft SQL Server, which account for 85% of worldwide RDBMS revenue. Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Relational Data & RDBMS ? RDBMS Advantages: Power, speed, reliability, concurrency, abstraction, scalable. ? RDBMS in the CIT curriculum: CIT 281: MySQL intro; NoSQL intro. CIT 381: Database Design CIT 382: Data-APIs & Cloud Computing CIT 383: Networked Databases & Infrastructure Security Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Entities ? An entity is anything that can be identified by a fixed number of its characteristics (attributes) ¨C The attributes have names and values ¨C The values are the data that is stored in the table ? In relational databases, an attribute is a ¡°column of a table¡± Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Entities ? The tag used in affinity is the entity¡¯s name ? The tags within are its attributes ¨C ¡°island¡± is an entity ¨C ¡°name¡±, ¡°area¡±, and ¡°elevation are the attributes ¨C ¡°archipelago¡± is also an entity Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Table Instance for Island Entity Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Database Schemas ? Tags are a cumbersome way to define a table ? Database systems specify a table as a database scheme or database schema ? The schema is a collection of table definitions that gives the name of the table, lists the attributes, their data types, and identifies the primary key Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Database Schema for Island Table Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Database Schema for Nations Table Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Operations on Tables ? Select ? Project ? Union ? Difference ? Product These are generic operations that SQL implements with slightly different commands Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Case Study: MySQL Open-Source RDBMS ? MySQL is a widely used open source RDBMS ? It is named after co-founder Michael Widenius' daughter, My. ? SQL stands for Structured Query Language Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Connecting to MySQL ? Creating MySQL databases and tables requires communicating with a MySQL server ? CIS 110 uses SQL Fiddle to run MySQL in the browser ? CIT 281: install MySQL on your own virtual machine (AWS EC2) Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Connecting to MySQL ? MySQL Terminal ¨C command-line interface for interacting with the MySQL server ¨C Demo: MySQL Terminal on shell.uoregon.edu Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Select Operation ? SELECT * FROM Nations; ? SELECT Name, Capital FROM Nations; ? SELECT Name FROM Nations WHERE Interest=¡®Beach¡¯; Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Operations on Tables in MySQL Ch. 15 describes five general operations on tables: Select, Project, Union, Difference, and Product In MySQL, the first two are implented using the SELECT command. The second two use commands that operate on two tables and will not be covered in CIS 110 Wednesday, April 9, 14
Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Learning MySQL You will learn how to use MySQL in your Week 8 lab. Blackboard > Weekly Labs > Lab Week 8: LM: MySQL Wednesday, April 9, 14

More Related Content

Ch. 15 FIT5, CIS 110 13F

  • 1. Chapter 15 Introduction to Database Concepts Read pp. 457-488 only Wednesday, April 9, 14
  • 2. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Differences Between Tables and Databases ? When we think of databases, we think of tables of information: ¨C iTunes shows the title, artist, running time on a row row = a record (noun) for a track column = property Wednesday, April 9, 14
  • 3. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Differences Between Tables and Databases ? When we think of databases, we think of tables of information: ¨C Your car¡¯s information is one line in the state¡¯s database of automobile registrations row = a record for a car column = property Wednesday, April 9, 14
  • 4. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Differences Between Tables and Databases Rows = Records Cols = Properties Record a.k.a. Entity, Instance, Object Wednesday, April 9, 14
  • 5. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Canada¡¯s Demographic Information Wednesday, April 9, 14
  • 6. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The Database¡¯s Advantage ? Metadata is the key advantage of databases over other approaches to recording data as tables ¨C Database software can search for the <country> tag surrounding Canada ¨C Metadata: Data about Data Metadata == Semantic Markup Wednesday, April 9, 14
  • 7. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The Database¡¯s Advantage ? The tags for the CIA database fulfill two key metadata roles: ¨C Define the identity of the data (-> instance, entity, object) ¨C Define the affinity of the data (-> properties) Tags enclose all data that is logically related. Wednesday, April 9, 14
  • 8. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley The Database¡¯s Advantage ? <country>, <population>, and similar tags have the role of identification because they label content ¨C instance, entity, object ? <demogData> tag has the role of implementing affinity because it groups an entity¡¯s properties Wednesday, April 9, 14
  • 9. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley XML: Extensible Markup Language ? XML is a Meta-language: a language for creating new markup languages ¨C Use XML to create custom Tag sets ? All markup languages are self-describing languages Wednesday, April 9, 14
  • 10. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Rules for Writing XML Wednesday, April 9, 14
  • 11. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley XML Example ? Scenario: ¨C Create a database for the Windward Islands archipelago in the South Pacific ¨C Plan what information will be stored ¨C Develop those tags: <archipelago> <island> <iName> Tahiti </iName> <area>1048</area> </island> ? </archipelago> Wednesday, April 9, 14
  • 12. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley XML Example ? Scenario: ¨C Create a database for the Windward Islands archipelago in the South Pacific ¨C Plan what information will be stored ¨C Develop those tags: <archipelago> <island> <iName> Tahiti </iName> <area>1048</area> </island> ? </archipelago> Affinity role Wednesday, April 9, 14
  • 13. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley New root element Wednesday, April 9, 14
  • 14. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Attributes in XML ? XML tags can have attributes ¨C Tag attribute values can be enclosed either in paired single or paired double quotes ¨C Use attributes for metadata, not for content Wednesday, April 9, 14
  • 15. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Identity Rule: Label Data with Tags Consistently ¨C You can choose whatever tag names you wish to name data, but once you¡¯ve decided on a tag for a particular kind of data, you must always surround that kind of data with that tag. Wednesday, April 9, 14
  • 16. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Affinity Rule: Group Related Data ¨C Enclose in a pair of tags all tagged data referring to the same entity, instance, object ¨C Grouping makes an association of the tagged data items as being related => properties of the entity. Wednesday, April 9, 14
  • 17. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Collection Rule: Group Related Instances ¨C When you have several instances of the same kind of data, enclose them in tags Wednesday, April 9, 14
  • 18. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Effective Design with XML Tags ? Group Related Instances ¨C A group of five islands were grouped inside an <archipelago> tag ¨C A group of two archipelagos were grouped inside a <geo_feature> tag Wednesday, April 9, 14
  • 19. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Wednesday, April 9, 14
  • 20. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Wednesday, April 9, 14
  • 21. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Relational Data & RDBMS ? Relational databases ¨C data are stored in tables (relations) in a db. ¨C A table stores information in rows (records) and columns (fields, properties). ¨C Relational databases include a Structured Query Language (SQL) => application programming interface (API) Wednesday, April 9, 14
  • 22. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Relational Data & RDBMS ? Developed in the 1970s, relational databases are based on an underlying mathematical model, have great expressive power and are a core computer science technology that has migrated to all fields. ? The RDBMS market is dominated by Oracle, IBM DB2, and Microsoft SQL Server, which account for 85% of worldwide RDBMS revenue. Wednesday, April 9, 14
  • 23. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Relational Data & RDBMS ? RDBMS Advantages: Power, speed, reliability, concurrency, abstraction, scalable. ? RDBMS in the CIT curriculum: CIT 281: MySQL intro; NoSQL intro. CIT 381: Database Design CIT 382: Data-APIs & Cloud Computing CIT 383: Networked Databases & Infrastructure Security Wednesday, April 9, 14
  • 24. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Entities ? An entity is anything that can be identified by a fixed number of its characteristics (attributes) ¨C The attributes have names and values ¨C The values are the data that is stored in the table ? In relational databases, an attribute is a ¡°column of a table¡± Wednesday, April 9, 14
  • 25. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Entities ? The tag used in affinity is the entity¡¯s name ? The tags within are its attributes ¨C ¡°island¡± is an entity ¨C ¡°name¡±, ¡°area¡±, and ¡°elevation are the attributes ¨C ¡°archipelago¡± is also an entity Wednesday, April 9, 14
  • 26. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Table Instance for Island Entity Wednesday, April 9, 14
  • 27. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Database Schemas ? Tags are a cumbersome way to define a table ? Database systems specify a table as a database scheme or database schema ? The schema is a collection of table definitions that gives the name of the table, lists the attributes, their data types, and identifies the primary key Wednesday, April 9, 14
  • 28. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Database Schema for Island Table Wednesday, April 9, 14
  • 29. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Database Schema for Nations Table Wednesday, April 9, 14
  • 30. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Operations on Tables ? Select ? Project ? Union ? Difference ? Product These are generic operations that SQL implements with slightly different commands Wednesday, April 9, 14
  • 31. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Case Study: MySQL Open-Source RDBMS ? MySQL is a widely used open source RDBMS ? It is named after co-founder Michael Widenius' daughter, My. ? SQL stands for Structured Query Language Wednesday, April 9, 14
  • 32. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Connecting to MySQL ? Creating MySQL databases and tables requires communicating with a MySQL server ? CIS 110 uses SQL Fiddle to run MySQL in the browser ? CIT 281: install MySQL on your own virtual machine (AWS EC2) Wednesday, April 9, 14
  • 33. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Connecting to MySQL ? MySQL Terminal ¨C command-line interface for interacting with the MySQL server ¨C Demo: MySQL Terminal on shell.uoregon.edu Wednesday, April 9, 14
  • 34. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Select Operation ? SELECT * FROM Nations; ? SELECT Name, Capital FROM Nations; ? SELECT Name FROM Nations WHERE Interest=¡®Beach¡¯; Wednesday, April 9, 14
  • 35. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Operations on Tables in MySQL Ch. 15 describes five general operations on tables: Select, Project, Union, Difference, and Product In MySQL, the first two are implented using the SELECT command. The second two use commands that operate on two tables and will not be covered in CIS 110 Wednesday, April 9, 14
  • 36. Copyright ? 2013 Pearson Education, Inc. Publishing as Pearson Addison-Wesley Learning MySQL You will learn how to use MySQL in your Week 8 lab. Blackboard > Weekly Labs > Lab Week 8: LM: MySQL Wednesday, April 9, 14