ICT Database Lesson 2 Designing a Database Objectives

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