Database01 :: TOPIC01

DBMS(DataBase Management System) 

*Purpose of a Database

The purpose of a database is : 

to store data

to provide an organizational structure for data

to provide a mechanism for querying, creating, modifying, and deleting data 

 

☆A database can store information and relationships that are more complicated than a simple list.

 

* 4 basic ways of interacting with data

Create, Read, Update, Delete

hierarchical relationships amog data.

 

*Problems with Lists : Redundancy 

the same information may be entered several times. 

For example manager's name, ID, and phone extension 

 

*Problems with Lists: Multiple Themes

In a list, each row may contain inforamtion on more than one theme or business concept.

 

*List Modification issues

Redundancy and multiple  themes in lists create modification problems

Deletion problems

Update problems

Insertion problems 
These are called Anomalies

 

 

* Addressing information Complexities.

- Relational databases are designed to address many of the information complexity issues that arise in business.

 

As a result, relational databases as we will see not only solve the problems that are associated with lists,

but alsoo allow us to model these natural relationships among business data.

 

* Relational Databases

- A relational database stores information in tables. Each informational theme (business concept) is stored in its own table.

 

* Putting the Pieces Back Together

In our relational database example, we broke apart our list into several tables.

Somehow the tables must be joined back together.

 

In a relational database, tables are joined together using matched pairs of data values.

 

*Sounds like More Work, Not Less

- A relational database is more complicated than a list

- However, a relational database minimizes data redundancy, preserves complex relationships among topics, and

allows for partial data( null values)

- Furthermore, a relational database provides a solid foundation for creating user interface form and reports 

 

* The Structured Query Language(SQL)

- The Structured Query Language(SQL) is an international standard language for creating, processing, and querying databases and their tables.

- The vast majority of data-driven applications and websites use SQL to retrieve, format, repost, insert, delete, and/or modify for users.

 

* Database Systems

- The four components of a database system are : 

Users

Database Application(s)

Database Management System(DBMS)

Database

 

 

*Users

A user of a database system will:

User a database application to keep track of information

Use different user interface forms to enter, read, delete, and query data

Produce reports

 

*The Database

A database is a self-describing collection of related records

Self-describing:

The database itself contains the definities if its structure.

Metadata are data describing the structure of the data in the database

Tables within a relational database are related to each other in some way.

 

 

*Database Management System (DBMS)

A database management system(DBMS) serves as an intermediary between database applications and the database 

The DBMS manages and controls database activites.

The DBMS creates, processes, and administers the databases it controls 

it is not the database.(itself)

 

*Functions of a DBMS

- Create databases

- Create tables

- Create supporting structures

- Read database data

- Modify database data(insert, update, delete)

- Maintain database structures

- Enforce rules

- Control concurrency

- Provide security

- Perform data backup and recovery

 

 

*Referential Integrity Constraints.

A DBMS can enforce many constraints...

Referential integrity constraints ensure that the values of a column in one table are valid based on the values in another table.

For example, if a 5 was entered as a Customer ID in the PROJECT table, a Customer having a CustomerID value of 5 must exist in the CUSTOMER table.

*Database Applications
A database application is a set of one or more computer programs or websites that serve as an intermediary between the user and the DBMS
- they must go through the DBMS 

 

A typical ERP implementation will have thousands of tables, all related to each other in some way.

An Enterprise-Level database management system can handle this level of complexity with relative ease.

 

watch?v=4Z9KEBexzcM