[igCS] Database - Intro, Structure and Data types

[igCS] Database

Objectives

• define a single-table database from given data storage requirements

• choose and specify suitable data types

What is Database?

• Collection of data that is stored and organized in a logical manner

• It can be paper-based or electronic

Physical Database: Records stored in paper and folders in a clinic

Open Ended

Name some physical database you have encountered before.

Before Internet, companies registered their contact number on yellow page

Electronic (Computer) Database

• Very common and appears in various scales

• e.g. The phonebook (or contacts) is a database usually with about 100 contacts

• The chat history of your instant messaging app may have 10000 records

Open Ended

Suggest one more database in your smartphone and give approx. how many total records in it.

In your smartphone

Probably there's tens or hundreds different database running in it...

Comparison of instant messaging users, in million. The scale of a database can be massive!

How companies support such a massive of database? - Data center

• https://www.youtube.com/watch?v=80aK2_iwMOs

Basic Functions provided by a database

• Storing and organization of data

• Search and retrieve of data (Query)

Database table structure

Table

• A table is a collection of same type of item

• e.g. for a school database system, we may have a student table which is storing student info.

• A database have multiple tables is called "relational database"

Database table (cont'd)

• Record is one entry in the table (row) - e.g. a particular student in our student table

• Field (column) one particular "property" of the record, e.g. First Name, Last Name, Gender are example of fields in student table

• 
  

Multiple Choice

How many records in this table?

• 5

• 6

• 7

• 9

Multiple Choice

How many fields in this table?

• 5

• 6

• 7

• 9

Indexing

• Make record searchable

• For example, in a dictionary the keyword is indexed (sorted);

• In yellow page numbers are grouped by type of business then by company name in alphabetical order

Open Ended

How is a typical supermarket index their products?

Open Ended

Library is not using ISBN but call number to index their books. Do some research, how does it work?

Indexing in db table

• Index in a database is called key

• Each table should have at least one key, which is called primary key

• Primary key is a unique (across the same table) value which is used to represent/identify a record in a table

Open Ended

Assumed that we are creating a database table for students in our class. List 5 most important fields in that table.

Multiple Select

Which of the followings are eligible to be primary key in our CS class student table?

• FirstName

• LastName

• StudentID

• Email

• ClassNumber

More about primary key

• Primary key must be unique to identify a record

• Primary key is unique in one table only, it can be repeated in another table.

• e.g. Student ID is Primary Key in Student table, but the same Value can be, for example, used in Report card table

• Primary key is also primary index, to help database retrieve a certain record

Database data types

• Each field in the database is assigned with a data type

• Usually database structure is fixed in size, so in data definitions the size of field is also defined

Case study

Take out text book and study p.171, Case study.

Multiple Choice

What is the data type for Name?

• short text

• integer

• decimal

• yes/no

Multiple Choice

What is the data type for Gender?

• short text

• integer

• decimal

• yes/no

Multiple Choice

What is the data type for Date of Birth?

• short text

• integer

• Date/Time

• Currency

Multiple Choice

What is the most suitable data type for Telephone Number?

• short text

• integer

• Date/Time

• Currency

Open Ended

Is there any field that is suitable to be a primary key? Explain why [2].

In case if no suitable primary key...

• In general each table must have a primary key

• In case if nothing suitable for it, we need to make up one

• One possible method is "Automatic ID", which either use computer algorithm to generate a code, or auto increment number