Chapter 11 Databases

1. Chapter 11Databases

2. Chapter Contents • Section A: File and Database Concepts • Section B: Data Management Tools • Section C: Database Design • Section D: SQL and Queries • Section E: Database Security Chapter 11: Databases

3. Section A: File and Database Concepts • Database Basics • Database Models Chapter 11: Databases

4. Database Basics • A database is a collection of information • Typically stored as computer files • The tasks associated with creating, maintaining, and accessing the information in databases are referred to as data management, file management, or database management Chapter 11: Databases

5. Database Basics • Databases can be used in a variety of ways • Collect and store data • Update data • Organize and output data • Distribute data • Find data • Analyze data Chapter 11: Databases

6. Databases • A database is a collection of data • Database software helps you enter, find, organize, update, and report information stored in a database Chapter 3: Software

7. Databases Chapter 3: Software

8. Databases • A query language such as SQL (Structured Query Language) provides a set of commands for locating and manipulating data • Query by example is the other way of querying a database for answers Chapter 3: Software

9. Database Basics • Data mining refers to the process of analyzing existing database information to discover previously unknown and potentially useful information, including relationships and patterns • Data warehouse • Predictive analytics • Big data Chapter 11: Databases

10. Database Models • An unstructured file has a unique structure and contains different kinds of data • A structured file uses a uniform format to store data • The underlying structure of a database is referred to as a database model Chapter 11: Databases

11. Database Models • The simplest model for storing data is a flat file that consists of a single, two-dimensional table of data elements Chapter 11: Databases

12. Database Models • A field contains the smallest unit of meaningful information • Each field has a unique field name • Variable-length field vs. fixed-length field Chapter 11: Databases

13. Database Models • A record is a collection of data fields • The template for a record is referred to as a record type • A record that contains data is referred to as a record occurrence Chapter 11: Databases

14. 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

15. 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

16. Database Models • A relationship is an association between data that is stored in different record types • Cardinality • One-to-many relationship • Many-to-many relationship • One-to-one relationship Chapter 11: Databases

17. Database Models • Cardinality refers to the number of associations that can exist between two record types • The relationship between record types can be depicted graphically with an entity-relationship diagram Chapter 11: Databases

18. Database Models • A relational database stores data in a collection of related tables Chapter 11: Databases

19. Database Models • An object databasestores data as objects, which can be grouped into classes and defined by attributes and methods Chapter 11: Databases

20. Section B: Data Management Tools • Data Management Software • Database Management Systems Chapter 11: Databases

21. Data Management Software • Originally data was managed by custom written software • Each file had its own program which sorted it, searched it, made reports from it • If you changed the structure of the data (new fields, longer fields, etc.) you had to change the program • This was called data DEPENDENCE Chapter 11: Databases

22. Data Dependence / Independence • Programmers realized that they were reinventing the wheel • Wrote programs to deal with any structured file (has a description of its fields in it) • These were Database management systems • Encouraged data INDEPENDENCE • Data file structure could change without changing program Chapter 11: Databases

23. Data Management Software • For small amounts of data and simple tasks you can adapt other software applications to do the job • MS Word (word processing apps in general) will allow you to create tables and manipulate them in some ways • MS Excel (spreadsheet apps in general) are able to do more with data in tables – they can’t handle as much data as a DBMS nor do as much with the data Chapter 11: Databases

24. Data Management Software Chapter 11: Databases

25. Data Management Software • Data dependence vs. data independence Chapter 11: Databases

26. Database Management Systems Chapter 11: Databases

27. Database Management Systems • Database client software allows any remote computer or network workstation to access data in a database Chapter 11: Databases

28. Database Management Systems • Multiple users can interact with the same database Chapter 11: Databases

29. 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 • The user who writes LAST is the change that “sticks”

30. Concurrent Processing (cont’d) • MS Access uses a lock file to control this • .laccdb file created when an .accdb file is opened, says that it is "locked" so no one else can use it • The lock file goes away when database file is closed • DO NOT submit it INSTEAD of your database file on a Lab Test!

31. Section C: Database Design • Defining Fields • Normalization • Organizing Records Chapter 11: Databases

32. Defining Fields • The term database structure refers to the arrangement of fields, tables, and relationships in a database • Break data into fields just by using common sense and considering how people might want to access the data • Use a primary key field to make each record unique • Use appropriate data types for each field Chapter 11: Databases

33. 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

34. Defining Fields Chapter 11: Databases

35. Defining Fields • A computed field is a calculation that a DBMS performs during processing and temporarily stores in a memory location • Uppercase and lowercase are not always treated the same • Case sensitive database • Use field formats to show what the data is supposed to look like when it’s entered • Use field validation rules to filter data Chapter 11: Databases

36. Normalization • Process that helps save storage space and increase processing efficiency • Minimizes data redundancy Chapter 11: Databases

37. Organizing Records • Records can be organized in different ways, depending on use • Sorting • A table’s sort order refers to the order in which records are stored on disk • Sort key • Database index • Similar to the index in a book Chapter 11: Databases

38. Organizing Records Chapter 11: Databases

39. Designing Report Templates • Report generators are used to specify the content and format for a database report • A report template contains the outline or general specifications for a report Chapter 11: Databases

40. Section D: SQL • SQL Basics • Structured Query Language • Early DBMS used only SQL • Now we have QBE (Query by Example) • SQL statements look like “Select Album from Albums where Artist = ‘Cher’ or Artist = “Elton John” • Not as friendly, but powerful Chapter 11: Databases

41. SQL Basics • Intermediary between the database client software and the database itself Chapter 11: Databases

42. 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

43. 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

44. 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

45. 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

46. 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

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

48. Section E: Database Security • Database Vulnerabilities • Database Security Measures • Database Security Regulations • What Individuals Can Do Chapter 11: Databases

49. Database Vulnerabilities • Databases are vulnerable to physical theft, hacking, and unauthorized access • Databases can be stolen without going missing • There is hardly a person in America who is not in at least one computer database • Privacy is viewed as an inherent right • Importance of data accuracy • Data should be kept secure Chapter 11: Databases

50. Database Security Measures • Today’s computers are under assault from hackers and natural disasters • Security measures include encryption, access controls, data security policies, and intrusion monitoring • Encryption can make data in a database unintelligible to a hacker • The process of decrypting database information in response to a query typically increases the amount of time necessary to process each query Chapter 11: Databases