CHAPTER 10 Databases Databases and Structured 1 Fields

CHAPTER 10 Databases

Databases and Structured 1 Fields 0 ï A database is a collection of information – Typically stored as computer files ï A structured file uses a uniformat to store data in the file Chapter 10: Databases 2

1 Fields 0ïContain the smallest unit of meaningful information ïEach field has a unique field name ïVariable-length field vs. fixed-length field Chapter 10: Databases 3

1 Records 0ïCollections of data fields Records can be displayed as rows in a table or as forms. Chapter 10: Databases 4

1 Records 0ïEach kind of record is referred to as a record type Chapter 10: Databases 5

1 Records 0ïA record that contains data is referred to as a record occurrence Chapter 10: Databases 6

1 Relationships and Cardinality 0ïA flat file is a structured file that contains only one record type ïA relationship is an association between data that is stored in different record types Chapter 10: Databases 7

1 Relationships and Cardinality 0ïCardinality refers to the number of associations that can exist between two record types Chapter 10: Databases 8

1 Hierarchical, Network, Relational, and Object-Oriented Databases 0ïA hierarchical databases arranges record types in a hierarchy Chapter 10: Databases 9

1 Hierarchical, Network, Relational, and Object-Oriented Databases 0ïThe network database model allows many-tomany relationships in addition to one-to-many relationship Chapter 10: Databases 10

1 Hierarchical, Network, Relational, and Object-Oriented Databases 0ïA relational database stores data in a collection of related tables Chapter 10: Databases 11

1 Hierarchical, Network, Relational, and Object-Oriented Databases 0ïAn object-oriented database stores data as objects, which can be grouped into classes and defined by attributes and methods Chapter 10: Databases 12

1 Data Management Software 0 Microsoft Word allows you to create a table of information, such as a mailing list, which you can edit, sort, search, and print. In addition, you can merge data from the table with a template letter to create form letters, mailing labels, and envelopes. CLICK TO START Chapter 10: Databases 13

1 Data Management Software 0 Chapter 10: Databases 14

1 Data Management Software 0ïIt is possible to enter data as an ASCII text file ïCustom data management software ïData dependence vs. data independence Chapter 10: Databases 15

Database Management 1 Systems 0 Chapter 10: Databases 16

Database Management 1 Systems 0 ïDatabase client software allows any remote computer or networkstation to access data in a database Chapter 10: Databases 17

Database Management 1 Systems 0 ïMultiple users can interact with the same database Chapter 10: Databases 18

1 Databases and the Web 0ïThe Web provides opportunities and challenges for accessing databases – Static Web publishing – Dynamic Web publishing • Server-side program Chapter 10: Databases 19

1 Databases and the Web 0 Chapter 10: Databases 20

1 Databases and the Web 0ïForms can collect data, as well as specifications for a query – XForms Chapter 10: Databases 21

1 Databases and the Web 0ïSeveral tools are available to help create server-side programs Chapter 10: Databases 22

1 XML Documents 0ïMarkup language allowing field tags, data, and tables to be incorporated into a Web document Chapter 10: Databases 23

1 XML Documents 0ïUsed to specify a standard structure of fields and records ïData in an XML document is searchable ïXML is portable, but not optimized for many common database operations – Consider storing data in a relational database, managing it with RDBMS software, and generating XML documents for exchanging data over the Web Chapter 10: Databases 24

1 Defining Fields 0ïThe term database structure refers to the arrangement of fields, tables, and relationships in a database ïStore last names and first names in separate fields ïUse a primary key field to make each record unique ïUse appropriate data types for each field Chapter 10: Databases 25

1 Defining Fields 0 Chapter 10: Databases 26

1 Defining Fields 0ïA computed field is a calculation that a DBMS performs during processing and stores temporarily ï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 10: Databases 27

1 Normalization 0ïProcess that helps save storage space and increase processing efficiency – Reduces data redundancy Chapter 10: Databases 28

1 Organizing Records 0ï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 ïIndexing – Similar to the index in a book Chapter 10: Databases 29

1 Organizing Records 0 When indexed by date, the index file contains a list of keys and the record number (R#) that contains more information about the album released on that date. Chapter 10: Databases 30

1 Designing the Interface 0ïArrange fields in a logical order ïProvide visual clues to the entry areas ïEntry areas should appear in a consistent position relative to their labels ïProvide a quick way to move through the fields in order Chapter 10: Databases 31

1 Designing the Interface 0ïUse scrolling to create multiple screens, if necessary ïProvide buttons or other easy-to-use controls for navigating through records ïSupply on-screen instructions Chapter 10: Databases 32

1 Designing the Interface 0 Chapter 10: Databases 33

1 Designing Report Templates 0ï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 10: Databases 34

1 Designing Report Templates 0ïSupply only the information required ïPresent information in a usable format ïInformation should be timely ïInformation should be presented in a clear, unambiguous format ïPresent information in the format most appropriate for the audience Chapter 10: Databases 35

1 Loading Data 0ïData can be loaded into a database by: – Using generic data entry tools – Using a customized data entry module ïA conversion routine converts the data from its current format into a format that can be automatically incorporated into the new database Chapter 10: Databases 36

1 SQL Basics 0ïIntermediary between the database client software and the database itself Chapter 10: Databases 37

1 SQL Basics 0 Chapter 10: Databases 38

1 Adding Records 0 Chapter 10: Databases 39

1 Searching for Information 0 SELECT Album. Title, Album. Cover FROM Albums WHERE Artist. Name = ‘Jefferson Airplane’ ï The AND, OR, and NOT Boolean operators are used to perform complex SQL searches ï The use of parentheses indicates the part of the query to perform first Chapter 10: Databases 40

1 Updating Fields 0 UPDATE Albums SET In. Stock = In. Stock – 1 WHERE Album. Title = ‘G. I. Blues’ ïGlobal updates change the data in multiple records simultaneously – Works only for records with similar characteristics Chapter 10: Databases 41

1 Joining Tables 0ïJoining tables in SQL allows you to create relationships between tables Chapter 10: Databases 42

1 Lab Assignment-Create 3 Database Tables 0ïYou are a DBA at Wal-Mart Superstore and you need to create three new database tables for all the items you sell in Excel – Divide the store into 3 sections. Create 1 table per worksheet for each section. Name the worksheets after each section you create. – Create all the proper and logical field columns that will be used for each section. – Fill in the first 3 records for each table with random materials found in that section. Chapter 10: Databases 43