Transcript Database Concepts - E-Portfolio: Robert Power

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
uniform format 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
CNA-Q ID Number
Last Name
First Name
Email
Address
City
Program
Sponsor ID Number
Sponsor
Information
Sponsor ID Number
Company Name
Contact Last Name
Contact First Name
Email
Telephone
Fax Number
Address
City
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
Instructor ID Number
Course Information
Course ID Number
PeopleSoft ID Number
Course Name
Instructor 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
Sponsor 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
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 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
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?
Pass/Fail?
Pass/Fail?
Pass/Fail?
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
can generate reports
• Reports We
number of purposes:
Simple Form Creation
Class Lists
Custom Form Creation
Candidate Report Cards