y23-25 Database

Flat-file databases

• stores its data in one table
• organised by rows and columns
• Each column in the table contains a field (with field name)
• each cell in that column has the same, predefined data type

Relational databases

• stores data in more than one linked table (in a file)
• designed so that the same data is not stored many times
• tables within a relational database are linked with relationships

Key Field

• Each table within a relational database will have a key field
• The relationships linking the tables use these key fields.
• There are two types of key field:
• Primary
• Foreign

Primary Key Field

• Most tables will have a primary key
• holds unique data (no two of the same data)
• used to identify that record
• Examples:
• Student ID
• IC number
• Passport number

Foreign Key Field

• Some tables will have one or more foreign key fields
• A foreign key field in one data table stores values from a primary key field in another table

From Flat File to Relational

Example:

the names of each student taught by each teacher using a flat-file database

From Flat File to Relational

• Now split into two tables
• one for the teachers and one for the students
• link them together
• it can be stored and retrieved more efficiently

From Flat File to Relational

• Example:

Teachers table

From Flat File to Relational

• Example:

Students' table

From Flat File to Relational

• These two tables are linked with a ‘one-to-many’ relationship
• because one teacher’s record is linked to many students’ records
• The primary key fields are the Student_ID and Teacher_ID.
• The foreign key fields are English, Maths and Science

Advantages of using relational databases

• storage space is saved by not storing data more than once
• each change to an item of data or to a record has to be made only once
• easier for users to produce reports

Advantages of using flat-file databases

• Although people often think that it is quicker to search using relational rather than flat-file databases, it is not always the case.

Alphanumeric

• store alpha characters (text)
• and numeric data (numbers) that will not be used for calculations
• Other names:
• Text, short text, long text
• String
• Varchar

Alphanumeric examples:

• Sophia
• Alex324
• 173@Tyler
• Michelle Lee
• Hi, have a great day!
• 0171234567 (phone numbers and IC numbers are usually stored as alphanumeric because we do not use them for calculation)

Integer

Decimal

Currency

Date

and

Time

Boolean

• or logical
• stores data as 0/ -1
• but can display it as:
• Yes/ No
• True/ False
• 0/ -1

Data Capture

• Before setting up a database, the record structure must be decided to make best use of the memory and backing store, and to make searching and report creation easier.

• For example, a car showroom wants to record details of the cars it sells. Before setting up the database, the following questions need to be answered:
• What information is needed?
• What validation could there be?

Data Capture

• With these questions answered, informed decisions can be made about the record structure. This is how it might begin:

Best practice when designing a Database

• Ask the correct questions
• Choose correct field type
• Field name must be concise and precise about the data it contains
• Avoid space in field name
• Choose a correct primary key

Observation

Questionnaire

Interview

Paperwork

Data Capture Form

• Before setting up a database the data must be collected. This can be done using a data capture form.

Data Capture Form

• Data capture forms often use boxes or a set amount of spaces and occasionally provide examples too.

• This is to make sure each field is completed correctly.

How to create data capture form?

• In Microsoft Access, data capture form or data entry form can be created using Form Wizard

What is Wizard?

• Also called as setup assistant

• is a user interface type that presents a user with a sequence of dialog boxes that lead the user through a series of well-defined steps.

• Tasks that are complex, infrequently performed, or unfamiliar may be easier to perform using a wizard.

Example of wizard in Access

Key-features of Well Designed Form

• simple, with clear questions, using closed questions where possible
• has similar fields grouped together
• with white space between each data entry box
• has a title
• there are instructions on filling in the form
• field has appropriate space for the data that will be added
• Radio buttons (or drop-down menus) are used where possible
• there are navigation buttons
• key fields can be highlighted to show that this data must be completed before the record can be saved