ICDL Software Applications Database Concepts Unit 6 Data

ICDL Software Applications - Database Concepts

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

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 uniformat to store data for each person or thing in the file

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.

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

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

File and Database Concepts Possible Tables in a CNA-Q Database: Student Information Sponsor Information Course Information CNA-Q ID Number Last Name First Name Email Address City Program Sponsor ID Number Company Name Contact Last Name Contact First Name Email Telephone Fax Number Address City Course ID Number People. Soft ID Number Course Name Instructor ID Number Financial Accounts CNA-Q Student ID Number Term Number Cost Per Course Number of Courses Amount for Term Amount Paid Amount Owing Faculty Advisors CNA-Q Student ID Number Instructors Instructor ID Number Last Name First Name Faculty Email Office Telephone

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

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

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

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.

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

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

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

Section A File and Database Concepts In a Relational Database, records in two or more tables may be related by common fields: Student Information CNA-Q ID Number Last Name First Name Email Address City Program Sponsor ID Number Many records can have the same Sponsor ID Number Sponsor Information One-to-Many Relationship Sponsor ID Number Company Name Contact Last Name Contact First Name Email Telephone Fax Number Address City Only one record can have each Sponsor ID Number

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

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

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

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

Database Design • Some file and database management systems provide additional data types such as date and logical • The 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

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

Database Design Module 1 Module 2 Module 3 Module 4 Module 5 Module 6 Module 7 80 100 76 94 42 100 90 Pass/Fail? Pass/Fail? Computed Fields Student’s Average Pass/Fail Term? ? ? ? ? Pass Grade: 80

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

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

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

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

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