Chapter 1 Database Concepts Chapter 1 2 in

Chapter 1 Database Concepts Chapter 1 & 2 in Textbook

Database Concepts Data is a meaningless static value. e. g. Ali, 3421. What does 3421 means? Information is the data you process in a manner that makes it meaningful. Information can be provided only if proper data exists. e. g. “Ali ID number is 0987”. A database (DB) is a collection of a logically related persistent data, designed to meet the information needs of an organization. Can be generated & maintained manually or automatically. Data is what you store in database. Information is what you retrieve from a database. Database Concepts 2

Database Concepts A database application is a collection of data and the programs that allow the manipulation of these data to meet the information needs of an enterprise. Database Management System (DBMS): A software system that facilitates the creation and maintenance of a database, i. e. implementing database application. Application Program (of a Database): a computer program that interacts with database by issuing an appropriate request (SQL statement) to the DBMS. Database System (DBS): is database and Software (DBMS + Application Program). Database Concepts 3

DBS Environment Users/Programmers DBS Application Programs DBMS Query Process Data Access Meta-Database Concepts Database 4

Examples of Database Applications • Edugate and ERegister. • KSU Library. • Supermarket system. • Credit card system. • Saudi Airlines Reservations. Database Concepts 5

File-Based Systems (FBS) FBS is a collection of application programs that perform tasks (e. g. reports) where each program defines and manages its own data. User 1 (Sales) Data Entry & Reports File handling File Def Sales Application Program User 2 (HR) Data Entry & Reports File handling File Def HR Application Program Database Concepts Sales File HR File 6

File-Based Systems (FBS) Major limitations of FBS: • Data Redundancy and Inconsistency. • Data isolation and separation. • Data dependence. • Incompatible file formats. • Fixed Queries/Proliferation of application programs. Database Concepts 7

File-Based Systems (FBS) These limitations of the FBS approach attributed to two factors: • The definition of data is embedded in the application programs, rather than being stored separately and independently. • There is no control over the access & manipulation of data beyond the imposed by the application program. Database Concepts 8

Database Systems (DBS) DBS is a single large repository of data, defined once and managed using DBMS while used by many application programs. DBS User 1 (Sales) Data Entry & Reports Sales Application Program DBMS User 2 (HR) Data Entry & Reports Database HR Application Program Database Concepts 9

Database Systems (DBS) Major characteristics of DBS: • Self-describing nature of a database system. • Program-data independence. • Sharing of data and multi-user transactions processing (guarantee Concurrency Control). • Support of multiple views of the data. Database Concepts 10

Database Systems (DBS) Disadvantages of DBS: • Complexity. • Size. • Cost (DBMS, Hardware, Staff, Training). • Performance. • Higher impact of a failure. Database Concepts 11

Database Management System (DBMS) A DBMS is a collection of software that facilitates the implementation and management of database applications. DBMS facilitates the following: • Create and Define a DB. • Construct and Load a DB contents. • Manipulate a DB (query & update the DB). • Control access to data. Database Concepts 12

Database Management System (DBMS) Major Relational DBMS vendors/products: • Paradox/Corel. • d. Base/Clipper. • Fox. Pro, Access & SQL Server/MS. • My. SQL. • DB 2/IBM. • Oracle, Sybase, Informix. Database Concepts 13

DBMS Languages 1. Data Definition Language (DDL): – DDL is a descriptive language for defining and constructing the database. – Allows users to specify the data types and structures and the constraints on the data to be stored in the DB. – DDL compiler generates the meta-data that is stored in the data dictionary. Database Concepts 14

DBMS Languages 2. Data Manipulation Language (DML): – DML is a language for retrieving and updating (insert, delete, & modify) the data in the DB. – Types of DML: – Low level or Procedural Language: user specifies what data is required and how to get those data. – High level or Nonprocedural Language: user specifies what data is required without specifying how to get those data. Database Concepts 15

DBMS Languages Both DDL and DML are usually not considered distinct languages. Rather, they are included in a comprehensive integrated language. For example, SQL relational database language is a comprehensive DB language which represents a combination of DDL and DML. Database Concepts 16

DBMS Languages DBMS have a facility for embedding DDL & DML (sub-languages) in a High-Level Language (COBOL, C, C++ or Java), which in this case is considered a host language. C, C++, Lisp, . . Application Program Call to DB DBMS Local Vars (memory) Database Concepts 17

Roles in the Database Environment Communicate Procedure User Requirement Write DB Designer Application programmer Use App Program System Analyst Design Program Write DBA Manage DBMS Naïve End User DB Use Sophisticated End User H/W 18

Roles in the Database Environment System Analyst: Determine the user requirements and develop the system specifications. Database Designer: Identify the data and choose the appropriate structure to represent and store the data. Application Programmer: Implement the application program based on the system specification. Database administrator (DBA): Administrates the DB, DBMS and related software. Database Concepts 19

Roles in the Database Environment Database End-users: They use the data for queries, reports and some of them update the database content (data). End-users can be categorized into: – Naïve users: Invokes one of the permanent application programs that have been written previously. – Sophisticated users: form requests in a database query language. Additional Roles: – DBMS designer and implementer. – Tool Developer. – Operators and maintenance Personnel. Database Concepts 20

Schemas versus Instances Database Schema: The description of the database. It rarely changes. – Includes descriptions of the database structure, data types, and the constraints on the database. Database Instance (snapshot): The actual data stored in a database at a particular moment in time. Changes rapidly. The concepts of Schema & Instances corresponds to Types & Values in programming languages, respectively. Database Concepts 21

Example Schema Database Concepts Instance 22

Three-Schema Architecture External Level Conceptual Level Internal Level User 1 User 2 View 1 View 2 User n View n Conceptual Schema Internal Schema DB Database Concepts 23

Three-Schema Architecture Defines DBMS schemas at three levels: – External Level • Describes that part of database that is relevant to a particular user. • Users’ view of the database. The way perceived by end users. – Conceptual Level • Describes what data is stored in database and relationships among the data. • The way perceived by the DBA & programmers. – Internal Level • Describes how the data is stored in the database. • The way perceived by the DBMS & OS. Database Concepts 24

Three-Schema Architecture External Level Staff_No Conceptual Level Internal Level View 1 FName Staff_No View 2 LName FName Struct STAFF { int Staff_No; char FName [15]; char LName [15]; date DOB; float Salary; int Bno; struct STAFF * next; }; Age Salary LName DOB Staff_No LName Salary Bno 25

Three-Schema Architecture Proposed to support DBMS characteristics of: – Program-data independence. – Support of multiple views of the data. Database Concepts 26

Mapping is the process of transforming requests and results between the Internal, Conceptual & External levels. • Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution. • Data extracted from the internal DBMS level is reformatted to match the user’s external view. Two types of mapping: – External / Conceptual mapping. – Conceptual / Internal mapping. Database Concepts 27

Data Independence is the ability to modify a schema definition in one level without affecting a schema definition in the next higher level. Two levels of data independence: – Logical data independence. – Physical data independence. Data Independent is similar to the concept of ADT in programming language where both hide the implementation details from the user. Database Concepts 28

Data Independence in Three-Schema Architecture External Schema External/Conceptual Mapping External Schema Logical data Independence Conceptual Schema Conceptual/Internal Mapping Physical data Independence Internal Schema Database Concepts 29

Data Models Data Model: A set of concepts to describe the structure of a database, the operations for manipulating these structures, and certain constraints that the database should obey. Purpose of data modelling is to represent data in an understandable way. Categories of data models include: – Conceptual data models (Object-based). – Logical data models (Record-based). – Physical data models. Database Concepts 30

Categories of Data Models Conceptual data models: is the construction of an enterprise’s information that is independent of implementation details. • Also called entity-based or object-based data models. Logical data models: is the logical description of an enterprise’s information with high level description of the implementation. • Also called record-based data models. Physical data models: is the physical description of how data is stored in the computer. Database Concepts 31

Categories of Data Models Hardware independent Software independent Conceptual model Hardware independent Software dependent Logical model Hardware dependent Software dependent Database Concepts Physical model 32

Data Models Conceptual Data Models (Object-Based): – – Entity-Relationship. Semantic. Functional. Object-Oriented. Logical Data Models (Record-Based): – Relational Data Model. – Network Data Model. – Hierarchical Data Model. Physical Data Models. Database Concepts 33

Data Models Staff Branch Staff_No LName Relational Data Model Network Data Model Database Concepts St 1 St 2 St 3 St 4 Name 1 Name 2 Name 3 Name 4 Salary Brn_No 7000 4000 6500 3000 1 1 2 3 Brn_No Street Tel_No 1 2 3 Olayaa 4630000 Malaz 4780000 Rawda 2310000 St 1 Name 1 7000 Olayaa 4630000 St 2 Name 2 4000 Malaz 4780000 St 3 Name 3 6500 Rawda 2310000 St 4 Name 4 3000 34

Data Models Malaz 4780000 St 3 Name 3 St 1 Olayaa 4630000 St 4 6500 Name 1 Rawda 2310000 7000 St 2 Name 4 3000 4000 Hierarchical Data Model Database Concepts 35

Summary of DB Concepts • Database (DB). • Database Application. • Database Management System (DBMS). • Database System (DBS) - Centralized approach. • File-Based System(FBS) - Decentralized approach. • Program-data dependence & independence. • Meta-data & data dictionary. • Concurrency Control. • Data Definition Language (DDL). • Data Manipulation Language (DML). Database Concepts 36

Summary of DB Concepts • Schemas & Instances. • Three-schema Architecture (internal, conceptual, external levels). • Mapping (external/conceptual, conceptual/internal). • Data Independence (logical & physical). • Query language & host language. • Conceptual model, Logical model, & Physical model. • Data model (Entity-Relationship, Object-Oriented, Relational). Database Concepts 37