Review of Major DB Concepts cid 127 Data and Information Data raw facts Information processed data cid 127 Database cid 127 Metadata cid 127 DBMS Database management system cid 127 Database system 1 An Example of Data cid 127 Sales per employee for each of X company s two divisions 2 Data and Its Structure cid 127 Data is actually stored as bits but it is difficult to work with data at this level cid 127 It is convenient to view data at different levels of abstraction cid 127 Schema Description of data at some level Each level has its own schema cid 127 Three schemas physical conceptual and external 3 Physical Data Schema cid 127 Describes details of how data is stored tracks cylinders indices etc cid 127 Early applications worked at this level explicitly dealt with details cid 127 Problem Routines hard coded to deal with physical representation Changes to data structure difficult to make Application code becomes complex since it must deal with details Rapid implementation of new features impossible 4 Conceptual Data Level cid 127 Hides details In the relational model the conceptual schema presents data as a set of tables cid 127 DBMS maps from conceptual to physical schema automatically cid 127 Physical schema can be changed without changing application DBMS must change mapping from conceptual to physical cid 127 Referred to as physical data independence 5 Conceptual Data Level con t Application Conceptual view of data DBMS Physical view of data 6 External Data Level cid 127 In the relational model the external schema also presents data as a set of relations cid 127 An external schema specifies a view of the data in terms of the conceptual level It is tailored to the needs of a particular category of users Portions of stored data should not be seen by some users cid 127 Students should not see faculty salaries cid 127 Faculty should not see billing data Information that can be derived from stored data might be viewed as if it were stored cid 127 GPA not stored calculated when needed 7 External Data Level con t cid 127 Application is written in terms of an external schema cid 127 A view is computed when accessed not stored cid 127 Different external schemas can be provided to different categories of users cid 127 Translation from external to conceptual done automatically by DBMS at run time cid 127 Conceptual schema can be changed without changing application Mapping from external to conceptual must be changed cid 127 Referred to as conceptual data independence 8 Levels of Abstraction payroll billing records View 1 View 2 View 3 External schemas Conceptual schema Physical schema 9 Data Independence cid 127 Logical Data Independence The capacity to change the conceptual schema without having to change the external schemas and their application programs cid 127 Physical Data Independence The capacity to change the internal schema without having to change the conceptual schema 10 11 Data indep is accomplished changing a schema at a lower level of DB only the mappings between this schema and higher level schemas need to be changed in a DBMS that fully supports data independence The higher level schemas themselves are unchanged Hence the application programs need not be changed since they refer to the external schemas 12 A Database System 13 Historical Roots cid 127 File systems Provides historical perspective Avoid pitfalls of data mgmt Simple characteristics Knowledge of converting a file system to a DB system 14 Contents of the CUSTOMER File 15 Basic File Terminology 16 Contents of the AGENT File 17 A Simple File System 18 File System Critique cid 127 File System Data Management Require extensive programming in a 3GL System admini becomes difficult as the of file expands Difficult and important to make changes in existing file structure Omit security feature to safeguard data Island of data information 19 File System Critique cid 127 Structural and Data Dependence Structural Dependence Data Dependence cid 127 Data dependence makes file system cumbersome 20 File System Critique cid 127 Field Definitions and Name Conventions A flexible good record definition anticipates reporting requirements by breaking up fileds into their components Example 21 File System Critique Selecting proper filed names is very important cid 127 descriptive within restrictions cid 127 Reflect documentation cid 127 Data Redundancy Data inconsistency lack of data integrity Data anomalies cid 127 Modification anomalies cid 127 Insertion anomalies cid 127 Deletion anomalies 22 DB System Vs File System 23 DBMS Languages cid 127 Data Definition Language DDL Used by the DBA and database designers to specify the conceptual schema of a database In many DBMSs the DDL is also used to define internal and external schemas views In some DBMSs separate storage definition language SDL and view definition language VDL are used to define internal and external schemas Result of compiling DDL is catalog a set of tables stored in a file cid 127 Data Manipulation Language DML Used to specify database retrievals and updates High level nonprocedural declarative DML Describe what data is needed w o specifying how to get it DML commands data sublanguage can be embedded in a general purpose programming language host language such as COBOL PL 1 or PASCAL Low level procedural DML Describe what how stand alone DML commands can be applied directly query language 24 Data Model cid 127 Schema description of data at some level e g tables attributes constraints domains cid 127 Model tools and language for describing Conceptual and external schema cid 127 Data definition language DDL Integrity constraints domains DDL Operations on data cid 127 Data manipulation language DML Directives that influence the physical schema affects performance not semantics cid 127 Storage definition language SDL 25 DBMS Interfaces cid 127 Stand alone query language interfaces cid 127 Programmer interfaces for embedding DML in programming languages Pre compiler Approach Procedure Subroutine Call Approach cid 127 User friendly interfaces Menu based Graphics based Point and Click Drag and Drop etc Forms based Natural language Combinations of the above Web Browser as an interface 26 DBMS Interfaces Cont cid 127 Parametric interfaces using function keys cid 127 Report generation languages cid 127 Interfaces for the DBA Creating accounts granting authorizations Setting system parameters Changing schemas or access path 27 Database System Environment 28 Database System
View Full Document
Unlocking...