1 / 42

CSC100

CSC100. Databases Marni Ferner. A Few Well-Known Examples. Itunes & Ipods Amazon.com Ebay LL Bean Wal-Mart. Components of a Database System. Components of a Database System. Why not just use a spreadsheet?. Problems with Flat Tables: Redundancy and Multiple Themes.

aradia
Download Presentation

CSC100

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSC100 Databases Marni Ferner

  2. A Few Well-Known Examples • Itunes & Ipods • Amazon.com • Ebay • LL Bean • Wal-Mart

  3. Components of a Database System

  4. Components of a Database System

  5. Why not just use a spreadsheet?

  6. Problems with Flat Tables:Redundancy and Multiple Themes

  7. Modification Issues • Redundancy and multiple themes create modification problems • Deletion problems • Update problems • Insertion problems

  8. Potential Problems: DeletionWhat if Ariel Johnson is deleted?

  9. Potential Problems: DeletionIntermediate Pastels is gone

  10. Potential Problems: UpdatesWhat if the fee for Advanced Pastels changes?

  11. Potential Problems: UpdatesDoes it change for all offerings?Is the 11/15/2006 date an error or another offering?Did we make ALL of the required changes?

  12. Potential Problems: InsertionHow do we add a new course? Lots of empty space

  13. A Better Solution

  14. Relational Databases • A relational database stores information in tables. Each informational topic is stored in its own table • In essence, a relational database will break-up a list into several parts. One part for each theme in the list • An Enrollment List would be divided into a CUSTOMER Table, a CLASSES Table, and an ENROLLMENT Table

  15. But, how do we re-create the original data?

  16. Structured Query Language (SQL) • Structured Query Language (SQL) is an international standard for creating, processing and querying database and their tables • Most database applications use SQL to retrieve, format, report, insert, delete, and/or modify data for users

  17. SQL Example SELECTCUSTOMER.CustomerName, CUSTOMER.Phone,COURSE.CourseDate, ENROLLMENT.AmountPaid,COURSE.Course, COURSE.Fee FROM CUSTOMER, ENROLLMENT, COURSE WHERE CUSTOMER.CustomerNumber = ENROLLMENT.CustomerNumber AND COURSE.CourseNumber = ENROLLMENT.CourseNumber;

  18. A Relational Database Solves the Problems of Lists and adds THE POWER OF QUERIES

  19. Query Tools Database Management System Software

  20. Query Tools Query tools help users narrow down information needed to be searched. Database Management System Software

  21. Query Tools Query tools help users narrow down information needed to be searched. A query allows users to ask questions designed to retrieve information. Database Management System Software

  22. Query Tools Query tools help users narrow down information needed to be searched. A query allows users to ask questions designed to retrieve information. Aselect command asks a database to return records that match specific criteria. Database Management System Software

  23. Query Tools Query tools help users narrow down information needed to be searched. A query allows users to ask questions designed to retrieve information. A select command asks a database to return records that match specific criteria. Joining matches data from fields in various tables. Database Management System Software

  24. Structured Query Language (SQL) This SQL statement could search for top students in a college, with “top” defined as those students with a grade point average (GPA) above 3.49. Database Management System Software

  25. What is a database and how does it organize information? Database Basics

  26. What is a database and how does it organize information? A databaseis a computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files. Database Basics

  27. What is a database and how does it organize information? A databaseis a computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files. Database Basics

  28. What is a database and how does it organize information? A databaseis a computerized system for storing information in an organized manner so that it can be searched for and retrieved when needed. Information is stored in the form of fields, records, and files. Unlike a list or spreadsheet, a database may store information that is more complicated than a simple list. Database Basics

  29. Levels of Data within a Database Database Basics

  30. Levels of Data within a Database Entity – a person, place, thing, or event Database Basics

  31. Levels of Data within a Database Entity – a person, place, thing, or event Field– a single value, such as a name, address, or dollar amount Database Basics • data type: usually numeric or text (numbers and text) • name: assigned by person developing the database • size: number of characters that can be entered

  32. Levels of Data within a Database Entity – a person, place, thing, or event Field – a single value, such as a name, address, or dollar amount Database Basics • data type: usually numbers, text, dates, but can also be other files (BLOBs), URL’s, etc. • name: assigned by person developing the database • size: number of characters that can be entered • Record – related fields describing an event or situation

  33. Levels of Data within a Database Entity – a person, place, thing, or event Field – a single value, such as a name, address, or dollar amount Database Basics • data type: usually numeric or text (numbers and text) • name: assigned by person developing the database • size: number of characters that can be entered • Record – related fields describing an event or situation • File – collection of records of the same type

  34. Traditional databaseorganizes data in a hierarchy. A field is the smallest element. Fields are within a record. Records are organized in a data file. Database is a collection of data files. Database Basics

  35. Sounds likeMore Work, Not Less • A relational database is more complicated than a list • However, a relational database minimizes data redundancy, preserves complex relationships among topics, and allows for partial data • Furthermore, a relational database provides a solid foundation for user forms and reports and allows the data to be examined in a myriad of different ways.

  36. Current and Future Applications THE WEB Sponges of the Caribbean

  37. Current and Future Applications Data Warehousing and Data Mining

  38. Wal-Mart “When the world's largest retailer struggles with a database issue, the numbers can be a bit daunting. It's data warehouse, for example, is larger than 4 Petabytes. That's more than 4,096 TBytes, give or take a few million bits. The chain has more than 6,000 stores, with some having almost a half-million SKUs each. You think your Excel spreadsheets are bad? Wal-Mart's database tables have literally 100 billion rows. The retailer's POS systems have to ring up some 276 million items—a day.” Source: StorefrontBacktalk.com

  39. Current and Future Applications Biometrics

  40. Ethics and Privacy Issues Discussion Topic: Sharing Criminal Dataand Megan’s Law

  41. California was the first state to employ a controversial database. Based on Megan’s Law – the statute named for a seven-year-old girl who was violated and killed by a paroled felon – the database listed the names and addresses of people convicted of crimes against children. Today, all states employ similar databases and are required to share the information with a national database. Most states now allow you to use the databases to find out if these felons live near you. In some communities, when an offender moves in, the police inform the local school system, which in turn sends parents a notification that includes a history, address, and picture of the wrongdoer. Some states share information with each other regarding almost all criminals, and some allow citizens to search for these offenders by name. Touted as a valuable tool in crime prevention, some feel that publishing this information makes it impossible for an offender to lead a normal life and can result in vigilantism – one paroled lawbreaker’s car was firebombed only days after his name was released. Should a database of people paroled or released after the commission of sex crimes be made public? Why or why not? What about those who have committed other types of crimes? What about those awaiting trial? Who should have access to the database, and why? Should there be a cost to accessing this type of data? • http://sexoffender.ncdoj.gov/search.aspx • http://www1.aoc.state.nc.us/www/calendars/DWIQuery.html

  42. The End

More Related