1 / 40

C HAPTERS 15-16

C HAPTERS 15-16. Application Software: Databases. Databases. A database is a collection of data Database software helps you find, organize, update, and report information stored in a database. Database Concepts.

macha
Download Presentation

C HAPTERS 15-16

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. CHAPTERS 15-16 Application Software: Databases

  2. Databases • A database is a collection of data • Database software helps you find, organize, update, and report information stored in a database

  3. Database Concepts • Database Management System Software is used to create, maintain, and access information in an electronic database. • Common Database Management Systems • Microsoft Access • Borland dBase • mySQL • Oracle

  4. Data Organization “Hierarchy” • Databases: a collection of files with the connections between them • Files : Information dealing with different types of data are stored in different files (i.e. addresses, transcripts, and schedules) • Records : Each instance in a file is called a record (i.e. all information about one student) • Fields : Each part of a record is called a field (i.e. Zip Code for a student)

  5. Database Terminology Field Record Table Database A database is made up of one or more tables Individual fields Individual tables in a database Records 6

  6. Tables in a Database • Each DB file has a table structure for its records. • The table structure is created first and given a table name as well as a set of fields.

  7. Table Design Considerations Just as you first create a blueprint to build a house, you should first sketch or outline the design of a database table Careful pre-planning will save you much time in the future

  8. Tables in a Database • Each Field in the table has… • name • data type • field width • After the table structure is complete, data can be entered into the table’s fields. • default values • required? • primary key?

  9. Data Types for the Fields • Text • alphanumeric, alpha, string – holds any character • Numeric • byte, short, long, integer • floating point (decimal) , currencies • Date • Memo • Logical (binary or boolean or yes/no) • Hyperlink • Object

  10. Table Design Considerations – Field Size Property Set the field size in Table Design View Always anticipate the current field size, may one day need to be larger Set field size in the Field Properties grid of Table Design View

  11. Data Validation • Make sure the data in the computer matches the real-world information it represents • Data Type Enforcement • Default Values • Ranges • Required Fields • Unique Keys • Non-Null Keys

  12. Data Validation • Make a field numeric types ONLY when you plan to do arithmetic with the field and it makes sense to do it! • Social security numbers, for example, should be TEXT not Long integers – you do not want someone to total them by accident, it would be meaningless

  13. Table Design Consideration – Sarbanes Oxley Act (SOX) Protects the general public and companies shareholders against fraudulent practices and accounting errors. Requires that all business records be saved for a period of five years and be made available to the SEC upon request SOX RECORDS Do Not Discard

  14. Table Design Considerations - PNPI • Short for Personal, Non-Public, Information • PNPI laws govern the safeguarding of data such as: • social security numbers • credit card numbers • Educational records (FERPA) • bank account numbers • medical records (HPAA)

  15. Table Design Considerations – Store Data in its Smallest part For greater flexibility, store data in its smallest part Instead of one field for an address, use many Instead of one field for a name, two or three Like this Not like this

  16. Table Design Consideration - Plan for Date Arithmetic Using a data type of date/time for all date fields allows the use of date arithmetic Fields declared as a data type of Date/Time

  17. Work with Table Views Design View Datasheet View 19 Datasheet (Table) View – used to add, modify, delete and view records Design View – used to create and modify the fields in a table

  18. Work with Properties Field Properties can be used to specify characteristics for individual fields Located in the lower pane of Table Design View Field Size property Caption property

  19. Objects Tables Queries Reports Forms Macros Modules Objects 21

  20. Reports • Reportsare created when a more formal printout is desired. • Use a wizard to create a report • Executes one or more queries and presents the results in a more organized fashion • Can sort the data, can do simple arithmetic (totals)

  21. Queries • Queries are questions used to retrieve information from a database. • Contain criteria to specify the records and fields to be included in the query results. • Can contain wild cards or logical operators to make more complex queries • * and ? • <, >, <=, >=, <>, =, AND, OR, etc… • Named and saved so they can be run again at a later time.

  22. Query Languages • A query language such as SQL (Structured Query Language) provides a set of commands for locating and manipulating data • LIST ALL FOR STATE = “KY” • Not user friendly • Query By Example is another query language • Fill out a form that describes your query • More friendly

  23. Queries Queries allow us to ask questions about data This record set that answers our question is called a dataset Dataset resulting from querying table for only employees who are Sales Representatives Employees table

  24. Using Query Design View Query Design grid has two panes – the table pane and the design pane Striking the F6 key will toggle you between sections Tables pane Design pane

  25. Specifying Criteria in a Select Query Field row – displays the field name Sort row – enables you to sort the dataset Show row – controls whether or not you see a field in the dataset Criteria row – determines the records that will be selected for display Fields in design grid allow us to specify criteria for the dataset

  26. Specifying Criteria – Currency and Operands Specify criteria with currency Without the dollar sign With or without the decimal point Use operands such as: Less than and greater than Equal to or not equal to Currency amount entered without dollar sign Greater than (>) operand

  27. Specifying Criteria – Wildcards Asterisk - searches for a pattern that includes any number of characters in the position of the asterisk Question mark - searches for a pattern that includes a single character in the position of the question mark Query with question mark and asterisk wildcard and resulting dataset to specify criteria for the dataset Query with asterisk wildcard and resulting dataset

  28. Specifying Criteria – And and Or OR finds records that can match one or more conditions AND finds records that must match all criteria specified Or Criterion and resulting dataset And criterion and resulting dataset

  29. Run a Query Running, or executing, a query is done by clicking the Run command Run command

  30. Add a Total Row in a Query • The total row can be added to the design grid by clicking the Totals Icon Totals Icon Total row added to the query

  31. Calculated Queries • Will find averages, max, min, sum, std • Work with ONE field – NOT more! • Add Totals row to query • Open “Group By” menu • Choose appropriate function • Run query

  32. Sorting Records • Can sort records in a database by field values • Single Field Sort • Sort all students in the DB by home state • Multi-Field Sort (Field A within Field B) • Sort all students in the DB by home state then by alphabetical order of last name (Name within State) State is primary sort field, Name is secondary sort field • Access sorts multiple fields left to right

  33. Sorting Table Data Last Name field sorted descending Last Name field sorted ascending 35 Lists records in ascending or descending order according to one or more fields

  34. Concurrent Processing • Large databases allow more than one person to access a file at one time • Have to be careful to control access - can lose data that way - who writes LAST? • MS Access uses a .ldb file to control this • .ldb file created when a mdb file is opened, says that it is "locked" so no one else can use it • .ldb file goes away when mdb file is closed • DO NOT submit it INSTEAD of your mdb!

  35. Compact and Repair Compact and Repair is located under the Manage menu 37 • Fixes problems due to inefficient file storage and growth of a database • Should be performed everyday • Often decreases the file size by 50% or more

  36. Access or Excel? Use Excel when: Use Access when: • Your data is of a manageable data size • There is no need for relationships between data • You are primarily creating calculations and statistics 38 You are working with large amounts of data You need to create relationships between your data

  37. Understand Large Database Differences Most large companies separate their database into front and back ends Front end – contains the objects needed to interact with data, but not the tables where the record values reside Back end – contains the tables where the data resides

  38. Data Mining – a question for society • the process of extracting hidden patterns from data (from Wikipedia) • Businesses and governments do it all the time (finding prospects, detecting terrorism) • For Knowledge Discovery and Prediction • Privacy concerns – identifying specific individuals

More Related