ICDL

1. ICDL Software Applications - Database Concepts

2. Data and Data Representation Unit 6 • Database Concepts • File Structure • Relationships • Database Design • Data Types • Design Considerations

3. File and Database Concepts • A database is a collection of information • Databases are typically stored as computer files • A structured file is similar to a card file or Rolodex because it uses a uniform format to store data for each person or thing in the file

4. File and Database Concepts • A field contains the smallest unit of meaningful information • Each field has a unique field name that describes its contents • A field can be either variable length or fixed length.

5. Section A File and Database Concepts • A record refers to a collection of data fields

6. Section A File and Database Concepts • Each kind of record is referred to as a record type • A record type is similar to a blank form, • it is usually shown without any data in the fields

7. File and Database Concepts Possible Tables in a CNA-Q Database:

8. Data and Data Representation • Database Concepts • File Structure • Relationship and Cardinality • Database Design • Data Types • Design Considerations

9. Section A File and Database Concepts • A data file that contains only one record type is often referred to as a flat file • In contrast, a database can contain a variety of different record types

10. Section A File and Database Concepts • In database jargon, a relationship is an association between data stored in different record types • Cardinality refers to the number of associations that can exist between two record types

11. Section A File and Database Concepts • A one-to-one relationship means that a record in one record type is related to only one record in another record type • Example: • A single grade report is related to only one student.

12. File and Database Concepts • When one record is related to many records in another table, the relationship is referred to as a one-to-many relationship • Example: • Instructor records to student advisees

13. File and Database Concepts • A many-to-many relationship means that one record in a particular record type can be related to many records in another record type, and vice versa • Example: • Student records to course enrollment

14. File and Database Concepts • A relational database stores data in a collection of related tables • Each table (also called a “relation”) is a sequence, or list, of records. • All of the records in a table are of the same record type

15. Section A File and Database Concepts In a Relational Database, records in two or more tables may be related by common fields: One-to-Many Relationship Many records can have the same Sponsor ID Number Only one record can have each Sponsor ID Number

16. Data and Data Representation • Database Concepts • File Structure • Relationship and Cardinality • Database Design • Data Types • Design Considerations

17. Database Design • The data that can be entered into a field depends on the field’s data type • From a technical perspective, a data type specifies the way data is represented on the disk and in RAM • From a user perspective, the data type determines the way that data can be manipulated

18. Database Design • The two most common data types are numeric and character • A database designer can assign a numeric data type to fields containing numbers that will be manipulated mathematically by adding, averaging, multiplying, and so forth

19. Database Design • For fields that contain data that would not be used for calculations, a database designer can specify a character (or string) data type. • In Access, this is referred to as “Text” type. • Text fields sometimes hold data that looks like numbers, but doesn’t need to be mathematically manipulated

20. Database Design • Some file and database management systems provide additional data types such as date and logical • The date data type is used to store dates in a format that allows them to be manipulated • The logical data type is used to store true/false or yes/no data using minimal storage space

21. Database Design • A computed field is a calculation that a DBMS performs during processing, and then temporarily stores in a memory location. • An efficiently designed database uses computed fields whenever possible because they do not require disk storage space

22. Database Design Computed Fields

23. Data and Data Representation • Database Concepts • File Structure • Relationship and Cardinality • Database Design • Data Types • Design Considerations

24. Database planning considerations • Database purpose • Number of tables and type of information • Fields in each table • Information to extract • How to enter data • Reports

25. Database Purpose • To store and organize information for the CNA-Q ICDL Training and Testing Centre.

26. Number of Tables and Type of Information • Instructors • Class Sections • Candidates/Students • Module Tests Instructor ID Number Last Name First Name Email Section ID Number Instructor ID Number Days of the Week Time Start Date End Date Student ID Number Last Name First Name Email Section ID Number Student ID Number Pass Module 1 Pass Module 2 Pass Module 3 Pass Module 4 Pass Module 5 Pass Module 6 Pass Module 7

27. Database planning considerations • Database purpose • Number of tables and type of information • Fields in each table • Information to extract • How to enter data • Reports • Extracting information is called Querying a database: • Single Table Queries • Multi-Table Queries • Conditional Queries • Data can be entered into tables directly, or by using Forms: • Simple Form Creation • Custom Form Creation • We can generate reports for a number of purposes: • Class Lists • Candidate Report Cards