Introduction to Database Design and Implementation With Professor

Introduction to Database Design and Implementation With Professor Ramon A. Mata-Toledo 2 -1

Week No. 1 Lecture 2 2 -2

Unit 1 Objectives At the end of this unit the student should be able to: • Define what a data model is and differentiate between a schema and an instance of a database. • Define and interrelate the components of the three-schema architecture of a DBMS. • Define the concepts of data Independence and data Dependency. • Define and interrelate the different languages that a DBMS support • Define the types of interfaces provided by a DBMS. 2 -3

A Data Model • is used to conceptually describe the structure of a database • hides from the user the implementation details of the database • describes the data types, relationships and constraints that should hold for the database • may provide basic operations for updates and retrievals and some mechanisms to specify user-defined operations 2 -4

A Data Model (Continuation) Based upon the types of concepts used to describe databases, data models can be classified as: • High level provide concepts that are close to the user’s perception of data. • Low level provide concepts that allow the description of how data is stored in the computer. • Implementation - midway between the high level and low level models. - provide concepts that can be understood by the end user and yet directly implemented on a computer. 2 -5

Schemas and Instances • The data stored in a database at a particular point in time is called an instance or snapshot of the database. • The instances of a database will change many times during the lifetime of the database. 2 -6

Schemas and Instances The description of a database is called the schema. The schema is • specified during the design phase of the database • stored in the data dictionary • The schema of a well-designed database should not change frequently unless there are unusual circumstances to do so. 2 -7

Oracle Schemas In Oracle™ the term schema is used slightly differently to the general definition given before. Here a schema is A collection of of logical data or schema objects that can be manipulated by SQL commands. A schema, owned by each database user, has the same name as the user Some of the objects in a user’s schema are: • • • Tables Views Indexes Links Synonyms 2 -8

Oracle Schema (Continuation) Oracle schemas are: • Not in a 1 -1 correspondence to the physical files on disk • Contained in a tablespace of the database The data of each object may be contained in one or more of the tablespace’s datafiles. A tablespace may contain objects from different schemas and objects of the schema may be contained in more than one database Notes taken form the Oracle Corporation website. All rights reserved. 2 -9

Relationships between a databases, tablespaces and datafields Figure taken from the Oracle Website 2 -10

The Three-Schema Architecture • Provides a framework for describing general database concepts • It separates the user applications from the physical database ( it provides logical data independence from physical data independence) • DBMSs are not required to meet all details of the three-level-schema architecture • Developed by the American National Standard Institute, Standard Planning and Requirement Committee (ANSI/SPARC) study group on DBMS 2 -11

The Three-Schema Architecture 1) External Level individual user’s logical view 2) Conceptual Level describes for the community how the data is stored and logically interrelated 3) Internal Level describes how at the physical level the data is stored and represented Figure taken from http: //www. slideshare. net/sabeeh 1991/a-n-s-i-s-p-a-r-c-architecture 2 -12

External Level • Includes a number of user views (external schemas ) of the data. • Describes that part of the database that is relevant to each user. That is. Different views may have different representation of same data. It is the closest to the user. • It uses a high-level data model or an implementation model 2 -13

Conceptual Level • Describes what data is stored and how it is interrelated in the database. • Does not provide a physical description of how the data is stored or represented internally. • Independent of software or hardware components. • Described by the DBA or Database Architecture. Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 2 -14

Internal Level • It is the closest to the physical storage • Describes the physical layer – Blocks, pages, I/O • Described by internal schema, DDL • Hardware dependent • Includes structures such as hash, heap, B-tree 2 -15

The Three-Schema Architecture (Continuation) • All three schemas are only descriptions of the data. • The only actual data that exists is at the physical level and is stored in data files. • The DBMS transforms the requests and formats the results that occur between the different schemas. • The processes of transforming requests and results between the different levels are called mappings. 2 -16

Mappings between the Three-Schema Levels • Conceptual/internal – Implementation of logical design at the physical level • External/conceptual – Overlapping subsets of views of the different users. • External/external – Allows some external views to be defined in terms of others if they are very similar; this may allow to improve the response time of the system. 2 -17

Limited Example of External Schema Human Resources Department Type • Employee = record • Employee. Name = string; • Employee. Address = string; • Employee. Marital. Status: Y/N • Employee. DOB = date; • Employee. Spouse. Name = string; • Employee. Spouse. DOB = date; End Payroll Department Type Employee = record Employee. Name = string Employee. Soc. Sec. No = integer unique; Employee. Address = string; Employee. Salary = real; Employee. Department = string; Employee. Address = string Employee. Title = string foreign key; Employee. Marital. Status = Boolean; Employee. DOB = date (less than 80); Employee. Spouse. Name = string; (Null if single) Employee. Spouse. DOB = date End Adapted from An Introduction to Database Systems by B. C. Desasi. West Publishing Company. 1990. 2 -18

Limited Example of Conceptual Schema (Continuation) Type Employee = record Employee. Name = string; Employee. Soc. Sec. No = integer primary key; Employee. Address = string; foreign key to Department Units; Employee. Salary = real; less than President; Employee. Department = string; Employee. Address = string Employee. Title = string foreign key fto Title of Department Unit; Employee. Salary = real; Employee. Marital. Status = Boolean; Employee. DOB = date (less than 80); End Copyright © 2004 Pearson Addison-Wesley. All rights reserved. 2 -19

Liimited Example of Internal Schema (Continuation) Type Employee = record varchar 600; international code; Employee. Name = varchar 02 up to 132, Int. Code, Offset 0, NOT Null; Employee. Soc. Sec. No = positive integer, 9 char fixed, first ≠ digit 0, PK, Offset 25; Employee. Address. Street = varchar 02 up to 132, Int. Code, Offset 5, NOT Null; Employee. City = varchar 02 up to 132, Int. Code, NOT Null; Employee. Zip = varchar 5 to 10; NOT Null; Employee. Salary = real; NOT negative or NULL, less than President Employee. Department = var char 20, FK to Department Unit Employee. Title = varchar 20 FK to Title of Department Unit; Employee. Marital. Status = Boolean; Employee. DOB = date (less than 80); (less than 80), NOT Null; Employee. Spouse. Name = varchar 02 to 132; (Null if single) Employee. Spouse. DOB = date, Null if Employee. Marital. Status equal NO; End Adapted from An Introduction to Database Systems by B. C. Desasi. West Publishing Company. 1990. 2 -20

Data Independence This term refers to the ability to change any schema or view of the database without affecting any other schemas or views. There are two types of data independence • Logical Data Independence and • Physical Data Independence 2 -21

Data Independence (Continuation) Logical Data Independence • Ability to change the structure of the database without affecting the applications programs or internal mappings to the storage organization. Physical data Independence Ability to change the storage organization of the database without having to modify the external schemas 2 -22

Data Languages Several languages are used to specify the different schemas and mappings between them. If there are no separations between the different architectural levels: • A Data Definition Language (DDL) is used by the DBA and the application programmers to define the external and internal schemas and mappings between them • All DDL statements are processed by a DDL compiler. 2 -23

Data Languages (Continuation) If there are separations between the different architectural levels: • The DDL is used to describe the conceptual schema only. • A Storage Definition Language (SLD) is used to specify the internal structure. • The mappings between the architectural levels can be specified in either language 2 -24

Data Language (Continuation) The Data Manipulation Language (DML) allows the following operations on the individual rows of a table: • Retrieve • Insert • Update • Alter • Delete 2 -25

Data Languages (Continuation) • If the DML is part of a general purpose language such as C or COBOL(the host language ) all language operations concerned with the db and its objects are called a Data Sublanguage (DSL) • The industry de facto DSL is SQL • When the host language and the DSL are easily distinguished they are said to be tightly coupled otherwise they are said to be loosely coupled 2 -26

Data Language (Continuation) • In some systems a language called a View Definition Language (VDL) is used to specify the external schema and its mapping to the conceptual schema 2 -27

Classification of DMLs Based on the numbers of records that they can retrieve and manipulate as a result of a single DML statement, DMLs are classified as: • Set-oriented DML (retrievemanyrecordsata time ) • record-at-a-time DMLs (retrieve only one record at a time ) 2 -28

Classification of DMLs (Continuation) • DMLs which, in a query, specify what data needs to be retrieved instead of how to retrieve it are called declarative; otherwise they are called procedural • An English-like DML is called a Query Language • SQL is a Query Language 2 -29

Classification of DMLs (Continuation) • The term “query language” is a misnomer because a query language does more than retrieving data. It also includes operations such as UPDATE, DELETE, INSERT, and ALTER which operate on the database objects such tables, views, indexes, etc. • DDLs and DMLs commands may need a compiler to be executed. 2 -30

Classification of DMLs (Continuation) • The term “query language” is a misnomer because a query language does more than retrieving data. It also includes operations such as UPDATE, DELETE, INSERT, and ALTER which operate on the database objects such tables, views, indexes, etc. • DDLs and DMLs commands may need a compiler to be executed. 2 -31

Image taken from Fundamentals of Database Systems by Elmasri and Navathe. Pearson Publishers. 2010 2 -32

Database Interfaces Provide a mechanism to interact with the user. The main types of interfaces are: • Menu-based Users choose from a list of options (menus) or look through the content of the database for specific objects using a browser. • Graphical Users select options or specify queries by pressing a key or pointing to different parts of the screen. 2 -33

Database Interfaces (Continuation) • Parametric – Designed to be used by the widest possible audience, allow users to perform a limited number of operations with minimum user participation. – Are generally found at ATMs or similar types of devices. • DBA Users select options or specify queries to perform privileged commands than can only be used by the DBA’s staff. 2 -34

Questions? 2 -35