ICT Database Lesson 3 Creating and Managing Database

ICT Database Lesson 3 Creating and Managing Database 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

Exporting Records • Preparing data to be used in another application • Run a query to create a table with the appropriate information • Export function as (. txt, . csv, or. xlsx)

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

Query A question that you ask a database using a set of criteria and in return the database provides its answer Returns information in the form of a new table 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