ICT Database Lesson 1 What is a Database

Objectives • 9. 1. 1: Describe the functions of a relational database. • 9. 1. 2: Distinguish between databases and spreadsheets. • 9. 1. 3: Identify advantages of using a database instead of alternatives (e. g. , spreadsheets, electronic documents, paper). • 9. 1. 4: Describe real-world uses for databases, including search engines, schools, hospitals, retail.

Database A file that stores data in an organized fashion so that information can be retrieved from it. Examples: • i. Pod playlist • Netflix movie list • Contacts in cell phone

Table – Flatfile Database • • A collection of data organized in rows and columns that can be used to store and manage information Work great with small lists of data (information) that is all related. A simple way to create a flat file database is to use a spreadsheet. Store information in cells created by using columns and rows of data

Spreadsheets - Advantages • • Great for analyzing and sorting related data. Easy to learn and use Suitable for storing and "crunching" relatively small volumes of numerical data. Able to present numerical data in the graphical form to quickly analyze data.

Spreadsheets - Disadvantages • • Require that you enter the same information in multiple places Have simplistic sorting and querying capabilities Contain only a finite number of records Changes to data in the computer memory and are not complete until the file is saved

Data Integrity The validity of the data “Garbage in, garbage out. ” Arizona / ARIZONA / Ariz / ARIZ / Az / AZ.

Creating Tables 1. Identify duplicate data 2. If duplicate data exists, create a separate table just for that data 3. Relate it back to the original table

Relational Database Consists of multiple tables of information related through common fields

Advantages 1. They can hold an unlimited number of records up to 1 gigabyte of storage. 2. A database allows for the simultaneous access and query of data by multiple individuals in multiple locations. 3. Data is protected against inadvertent corruption so you no longer need to keep redundant data. 4. We don’t have to manually enter the information reducing time, effort and mistakes. 5. Entering information is minimalized which reduces input time, resources and opportunities for human error. This reduces cost and increases data integrity. 6. Reduced processing time for large amounts of data.

Disadvantages 1. They are more complex and harder to learn and use than spreadsheets 2. Designing relational tables can be more difficult and timeconsuming 3. and software and hardware costs are higher than a spreadsheet.

Big Data A term that describes enormous volumes of data that are too huge for regular database programs • Unlike data in a database, big data is unstructured and unrelated. • Analysis of big data requires specialized tools.

ICT Database Lesson 2 Designing a Database

Objectives • 9. 2. 1: Identify the components of a database. • 9. 2. 2: Distinguish between fields and records in a database. • 9. 2. 3: Describe the basic data types and formats used in a database. • 9. 2. 4: Distinguish between a table and a query. • 9. 2. 5: Identify database keys, including primary and foreign. • 9. 2. 6: Identify the relationships between tables in a database (i. e. , oneto-one, one-to-many, many-to-many). • 9. 2. 7: Distinguish between a query and a report. • 9. 2. 8: Identify various report types.

Steps to Designing a Database • • • Who needs what information Organize data tables and fields Relate the information Identify data types Identify Queries, Forms and Reports

Fields • • Specific categories of information we need to track per type of information Variables organized as columns in a table

Record A group of fields in a table related to a single entity and is contained in a row within that table

Primary & Foreign Key Primary Key: uniquely identify each record in a table and help to link related records • Data contained in the primary key is unique — that is, no duplicate data can be contained in the field. Foreign Key: a field that refers back to a primary key in another table • Will reference a unique column in another table • This primary key – foreign key relationship is how you link the tables.

Primary & Foreign Key

Primary & Foreign Key Primary Key relates to the Foreign Key of the second table Foreign Key

Table Relationships Database Table Relationship Description One-to-one Each record in Table A can have only one matching record in Table B, and vice versa. The relationship is created only if both of the related fields are primary keys. One-to-many A record in Table A can have multiple matching records in Table B, but a record in Table B has only one matching record in Table A. The relationship is created only if the related field is the primary key in Table A and a foreign key in Table B. Many-to-many One record in Table A can relate to many matching records in Table B, and vice versa.

Table Relationships One-to-many relationship 8 1 8 Many-to-many relationship 1

Junction (Join)Tables • • A 3 rd unique table created to join two unrelated tables Contains the primary key fields from each of the other two tables in the relationship 1 Primary Fields 8 8 1 Junction Table Primary Fields

Data Types • Text – Alphanumeric values, examples are names, and titles. This field can hold between 1255 characters. • Integer – Numeric values, such as calculations and distance. This does not include currency. There is no decimal and precision is 10 places. • Float – Numeric values that have 2 decimal points and precision up to 16 places. • Currency – Numeric values that describe money. • Date/Time – Dates and Time. • Yes/No – Boolean values, use when the only options are true or false. • Memo – Long formatted text fields more than 255 characters. A memo is the right choice for a description of an item. • Attachment – Digital media such as pictures. A record can have more than one attachment. • Hyperlink – Links to other resources such as Web sites, and e-mail addresses. • Autonumber – Provides a unique numeric value that only appears once in a table. If you delete an autonumber, that number will never appear again in that table. Many databases use an Autonumber as the primary key in record.

Form Provides a simplified interface for entering, modifying, and viewing the database records

SQL – Structured Query Language A sub-language commonly used for developing and managing databases • SQL pronounced “sequel” • Used primarily in a database to retrieve, update, insert or remove information • Sufficiently powerful and can create tables, restructure tables and remove tables, among other very complex tasks

Database Management Systems A program used to store, access and manipulate database information • Microsoft Access is an end-user DBMS that you can use to create and manipulate fairly small and uncomplicated databases • Oracle, My. SQL, IBM’s DB 2 and Microsoft SQL Server are high-end DBMS programs used to create and manipulate large, complex databases used in large organizations

ODBC - Open Database Connectivity • An open standard application programming interface (API) • Allows us to use the MS Access interface tools to access the DBMS data. Using a simplified graphical user interface (GUI) like MS Access to build queries and reports is a flexible way for a relative novice to gain access to a wealth of company data.

ICT Database: Lesson 3 Creating and Managing Tables

Inserting Records • Adding information to a set of database fields • Manually enter in Datasheet View or Form – small amounts of data • Import data for a large number of records Usually a spreadsheet, another database, or as a text file (. txt, . csv) • Map corresponding fields

Updating Records • • • Updating or modifying information in an existing set of fields Datasheet View for limited amounts of data SQL routine for larger sets of data

Deleting Records • • • Information is permanently deleted from the database Datasheet View for small amounts of data SQL routine for larger amounts of data Must make sure to delete all related material in related tables May impact other database objects

Database Schema The skeleton structure that represents the logical view of the entire database. Lists: • The tables to be used in the database • each of the fields to be included in each of the tables • the data type of each of the fields • and the relationship among the various tables. It also includes all the constraints that are to be applied on the data.

Database Schema 1 8 8 1

Datasheet View • Displays fields in a table similar to how it would look in a spreadsheet • Rows and columns • Columns are fields • Rows are records • Can add or edit fields • Can add, modify or delete records

Design View • Displays and edit table and field properties ü Data type ü Default value ü Required

ICT Database: Lesson 4 Manipulating and Sharing Data

Common Errors • • Mistyping data or entering the wrong data type ü Data integrity can be improved using field properties: format, length, or a “mask” (date/time or (xxx) xxxxxxx) Referring to a non-existent table or field. ü The name of the table may have been changed or the table may have been deleted. Syntax errors – rules for wording or commands Checking the status of a table should be the first step in troubleshooting

Query A question that you ask a database using a set of criteria and in return the database provides its answer Will display only the particular fields and records from a database you want to see Data returned by a query can come from tables, other queries or reports Automatically create a join between Primary and Foreign Key

Query Example List all pool companies that have purchased BN-0609 -TGOLD bullnose and what is my price?

Types of Queries: Select • • • The simplest type of query and most common Used to select and display data from either a single table or a group of tables depending on what information is wanted It creates a "virtual" table where the data is displayed and kept. The data in a virtual table can only be changed 1 record at a time

Types of Queries: Action • Creates a specific action to be performed on the database • Examples: creating new tables, deleting rows from existing ones and updating records or creating entirely new ones. • Popular query to run because they allow for many records to be changed at one time • Five kinds of action queries are: 1. Insert Query – adds data to an existing table 2. Append Query – takes the set results of a query and "appends" (or adds) them to an existing table 3. Delete Query – deletes records from the table based on the results of the query 4. Make Table Query (Create) – creates a table based on the results of a query 5. Update Query – allows for one or more fields in a table to be updated

Importing / Exporting Data • Exporting data prepares the data to be used in other programs such as spreadsheet programs. • Importing prepares data to be used in the open program ü Import data for a large number of records. ü Usually a spreadsheet, another database, or as a text file (. txt, . csv) ü Map corresponding fields

Reports A way to display your data in a visually appealing and easy-to-read format. You can format the data for different types of reports • group and/or sort data, display subtotals, averages and other statistical data • highlight values by using conditional formatting • apply themes to enhance the "look and feel" of reports • add images to reports • preview and print reports

Reports • • Reflect current data only at the time the report is run Reports tool provides the fastest way for you to create a report ü Generates a report immediately without prompting you for information ü Displays all the fields from the underlying table or query

Reports: Controls - Themes • • Controls: display data, perform actions, and let you view and work with information that enhances the user interface, such as labels and images. Three types of controls: bound, unbound, and calculated Allow you to manipulate data such as sorting or summing a column Themes alter the “look and feel” of database reports