Introduction to Database

1. Introduction to Database • Database system is a computer based record keeping system. • It is a system whose overall purpose is to record and maintain information. The information concerned can be anything that is deemed important to the organization. • In another words, database is collection of stored operational data which can be used and shared by different applications and users of any enterprise. This enterprise could be scientific, commercail, educational, etc.

2. Why Database • Database system provides the enterprise with centralized control of its operational data, which is one of its most valuable assets. • This is totally oposite of the situation that is happening in many organizations today, where typically each application has its own private files and a lot of times its own private tapes and disk packs too. This makes the operational data widely dispered and difficult to control.

3. Advantage of centralized database • Redundancy can be reduced • Inconsistency can be avoided • Data can be shared • Standards can be enfored • Security restrictions can be applied • Integrity can be maintained • Conflicting requirements can be balanced

4. Data independence • Data indepencence is an objective in database development • Most present day applications are data dependent. This is the way in which the data is organized in secondary storage and is accessed. Both are dictated by the requirements of the application. This is undesirable because: • Different applications will need defferent view of the same data. • Data Base Adminstrator (DBA) must have the freedom to change the storage structure or access strategy or both in response to changing requirements without the necessity of modifying existing application.

5. Data independence • Data independence could be defined as that, applications concerned with data, not depend on storage structure or access strategy of data. • With data independence database is able to grow without affecting existing applications (another major reason).

6. . . . ANSI database system architecture External Level External Level (Individual user view) Conceptual Level Cmmunity user view Internal Level (phisical storage view

7. . . . mapping External/conceptual mapping conceptual/internal Data Dictionary ANSI database system architecture External Level A3 A2 An A1 External Level (Individual user view) External Schema External Schema Database Management System (DBMS) Conceptual Level Cmmunity user view Conceptual Schema DBA Internal Level (phisical storage view Internal schema

8. Database architecture-terms • Schema: defines a database and sub-schema defines a portion of the database. • Schema and mapping built and maintained by the database adminstrator. • Conceptual schema is known as data model • Internal level is concerned with the in which the data is actually stored. • Users are: • application programmers • ad hoc users • external users

9. Database architecture-terms • Each user communicates with the database via a language referred as data sub-language (DSL), which is the subset of the language concerned with the retrieval and storage of information in the database. • Mapping defines the correspondence between data models and external/internal schema. • DBMS is the software that handles all access to the database and also is responsible for applying the authorization checks and validation procedures. • DBMS is a bridge between users and data.

10. Data Base Adminstrator • DBA is responsible for supervising the overall DBS. • DBA’s responsibilities: • Deceiding the information content of the DBS • Decide about the storage structure and access strategy • Interface between users and DBS: • Insure the availability of data for users • Assist users for their inquiries • In cooperation with users set up the external schema • Define authorization check and validation procedures. • Define the strategy for back up and recovry process. • monitoring the performance and respond to changing requirements.

11. Data Dictionary • Data dictionary is the most important tool for DBA. • Data dictionary is a database on its own right. • It defines all data and their relationship, “ data anot data” or information about meta data.

12. Disadvantage of Database Systems • Database is more vulnerble to destruction thru: • machine malfunction • personal error • Deliberate human tampring • Cost: the cost of required hardware, DB development, and DB maintenance is high. • Complexity: Due to its complexity, the user should understand it well enough to use it effecienty and effectively.

13. Database models • Almost all of the database systems developed over the past two decades are relational. • Almost all of the database research is also based on relational related ideas. • Relational database is a system in which: • the data is perseived by the user as tables • the operators at user’s disposal are operators which generate a new table from old • We can categorize systems by the data structures and operators they present to the users.

14. Database Models - Product - Vendor • The four major data model categories are: • Relational DB2 IBMSQL/DS Ingress Relational Technology Oracle Oracle corp. Access Microsoft • Network DMS100 Unysis IDMS Cullinet • Heirarchical IMS IBM System 2000 Intel • Object oriented Postgress Relational Technology Starburst IBM Gemstone Orion

15. Relational Database • Relational database is a collection of tables • Formally a table is called a relation • Database is a structure that can hold information about tables, rows, and columns

16. Relational Database Relational Relational Traditional ModelDBMSFile System Relation Table File Tuple Row Record Attribute Column Field Primary Key (PK) Primary Key (PK) Search Key Relationship (FK) Relationship (FK) Not Used

17. Relational Database • Primary Key (PK): An attribute which can uniquely identify each record (tuple) of a relation (table). • Foreign Key (FK): An attribute which is a regular attribute in one table but a primary key in another table.

18. Sale SalesNONameRate City Dept# 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Example of a Relational Database Relation Name Attribute Primary Key (PK) Tuple (record)

19. Sale SalesNONameRate City Dept# 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Example of a Relational Database Customer CustIDNameBalance CitySaleNo 132 Black 2000.00Dallas 10 135 Tom 129.89 Denver 12 198 Tom (132.90) Dallas 10 SalesNO is PK in Sales table

20. Sale SalesNONameRate City Dept# 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Example of a Relational Database Customer CustIDNameBalance CitySaleNo 132 Black 2000.00Dallas 10 135 Tom 129.89 Denver 12 198 Tom (132.90) Dallas 10 SalesNO is PK in Sales table and FK in Customer table

21. ONOOline# Part# Qty Part# 102 112.00 10 EX454 102 2 129.89 1 DE012 199 1 32.90 3 DC810 Sale SalesNONameRate City Dept# 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Example: Order Entry Database OrderLine Order ONODATE CustID SalesNO 102 11/2/94132 10 199 2/15/95 135 12 92 10/4/94 102 53 Customer CustIDNameBalance City SaleNo 132 Black 2000.00Dallas 10 135 Tom 129.89 Denver 12 198 Tom (132.90) Dallas 10