Learning outcomes:
Learning the concept of database
Understanding the IT terms
To use IT language to express your ideas.

File vs. Databases

In our daily life, information is organizated in documentation based. For example, we have so many kinds of forms such as add-drop form, enrollment form, bank-in form......however, these documentaional approach is not an efficiency way to store and process data. I give it an example:


The best way to handle documentational data is grouping the fields together in a systemetical method. This method is database.





Everything can be an object.
In Database term, Entity is a generalized term of object.
- Example of Entity:
- "Student" is an object, "Mary" is an object, "Bus" is an object, "Bus route 101" is an object.
- However, only Student and Bus are Entity. Because "Mary" & "Bus route 101" are specified term of "Girl" and "Bus" respectively.

We use fields (<-IS/business people term) to describe an object's attributes (<-IT language)
- Example of fields:
- "Student ID", "Student Name", "Phone Number", Birth Date" are fields of student (entity)

Fields containing data about one entity form a record.
- Example of record:
- "007", "Mary", "2765 4321", "4 June 1989" form a record of student (entity)

A set of all related records forms a file.

A set of interrelated, centrally coordinated files forms a database.

In summary,
- Database contains many entities.
- Each entity is a file,
- File consists many records
- Record contains many fields

Database System:

Proliferation of master files is a traditional information problem. It refers two cases:

1) In the past, data are stored in individual master file and they work independently.
- Example of Proliferation of master files:
- -Master file in Finance Dept (Staff ID, Staff name, Salary)
- -Master file in HR Dept (Staff ID, Staff name, Job Title, Department)
-- Master file in IT Dept (Staff ID, Staff name, User right)
For each amendment, we have to update 3 master files separately .
This is inefficient method. The solution is use database.
Database is a set of inter-related centrally coordinated files.

2) In the past, application program is supported by its own master file format only.
---(such as, Powerpoint file is for MS Powerpoint application program, not for MS words.)
When we change the application program, we have to change all data files.
The solution is use database management system (DBMS).
DBMS serves as the interface between the database in various programs

The combination of the database, DBMS and application programs is reffered to as the database system.
The person responsible for the database is the database administrator.
A very large database is called data warehouses.

The importance and advantages of database systems:

We need to learn database, because:
- Database is everywhere.
- We have to working with database.

The benefits of database are include:
- data integration
- data sharing
- reporting flexibility
- minimal data redundancy and inconsistencies
- data independence
- central management of data.
- cross-functional analysis

In traditional file-oriented system, programmers must know the physcial location and layout of records used by program. This is time consuming and compliated for data processing.

To overcome this problem, database system separating the storage and use of data in two views:
- Logical view (how the user conceptually organizes and understands the data)
- Physical view (how and where the data are physically arranged and stored)

Separating these views facilitates application development. (refer powerpoint notes)

The important is DBMS handles the link between the physical and logical views of the data.

Separating the views of data also means user can change their conceptualizaton of the data relationships without making changes in the physical storage.

The database adminstrator can also change the physical storage of the data without affecting users or application programs.

Schemas:

A schema describes the logical structure of a database.
Three level of schema are:
- conceptual level (big picture of entire database)
- external level (a set of individual user views of database)
- internal level (how the data are actually stored and accessed)

Data dictionary:

It contains information about the structure of the database.
- Examples of information are listed in Powerpoint notes.
For each data element, there is a corresponding record in the data dictionary describing that element.

DBMS languages:

Data definition language (DDL) - creating a database structure
Data manipulation language (DML) - changing a database data
Data query language (DQL) - querying a database.

Relational Database:

The relational database is most common data model of DBMS.
Relational database model represents everthing in the database as being stored in the forms of tables. (relation)
This model only describes how the data appear in the conceptual and external level schemas.
The data are physically stored according to the description in the internal-level schema.
In an relation, the column is call attribute, while each row is call tuple.
A primary key is the attribute or combination of attributes that uniquely identifies a specific row (tuple) in a table.
A foreign key is an attribute in one table that is a primary key in another table. We use foreign key to link tables together.
Other non-key attributes are the information about the entity.

Basic requirements of a relational database:

- Every column in a row must be single value
- Primary key cannot be null.
- Foreign key must either be null or correspond to the value of a primary key in another table.
- All non-key attributes in a table should describe a characteristic of the object identified by the primary key.