13 Database
Chapter Overview
- General introduction
- Usage
- Structure
- Data Type
- Flat files and Relational databases
- Search in database
What is a database
A structured collection of data that allows people to extract information in a way that meets their needs. The data can be varied in type.
KEY FEATURE
- Data is only stored once — no duplication
- The same data is used by everyone
Why we used it
USAGE
- Store information about people/things/events
E.g. Patients in a hospital, books in a library, hotel booking
ADVANTAGE (Compare electronic database to a manual database)
- Able to store more data
- Fast to find a specific record
- Fast to search for records meeting a specific criteria
- Less time is required for data entry if typist is quick
- Can be secured with a password
- Easier to make backups
Structure
Inside database, data is stored in tables, which consist of many records and each record has several fields.
- Table (file): A group of related records
E.g. Employees - Records (rows): A group of related fields
- Fields (columns): Pieces of data in a record
E.g. Last Name
Data Type
Data is categorized into types to make data processing easier, faster, safer and more accurate for the computer.
- Text (E.g. SCIE)
- Alphanumeric (E.g. 0478ComputerScience)
- Number (E.g. 1210))
- Boolean/Logical (E.g. True/False)
- Currency (E.g. $999)
- Date/Time (E.g. 21 Dec 2018)
Validation
Check if the data received is valid
- Range check — check if the number is in a certain range
- Length check — check if the length of the string is in a certain range
- Type check — check if the input is the required type
- Character check — check if the string contains forbidden characters
- Format check — check if the string follows the defined pattern
- Presence check — check if the there is an input
Flat files and Relational database
FLAT FILE
RELATIONAL DATABASE
- Contains two or more tables (file) of data, connected by links called relationships
- The relationship is built up by primary key and foreign key
- Primary key: A unique field within a table. Duplicates are not allowed
- Foreign key: A field that has a primary key in another table. Duplicates are allowed
How to search data in a database
By creating a QUERY (A request for data/information from a database)
- Field: search for the data in this field
- Table: search for the data in this table
- Criteria: select the data that meets specific criteria (e.g. =’Home’,