Overview of Database Management Systems • Goals: – DBMS basic concepts – Introduce underlying managerial issues – Prepare for discussion of uses of DBMS, such as OLAP and database mining misnotes-dbms-19980121. ppt Steven O. Kimbrough 1 Overview of Database Management • What is a database system? – Acomputerized record-keeping system • Record? – Think of it as a row in a table, consisting of fields of data – Note: Role of metaphors • System? A general software module that provides all sorts of services: – Create new files and add them to the database – Insert new data into an existing file – Retrieve data from existing files – Update (change) data in existing files – Delete data in existing files – Delete (remove) existing files from the database – And much else: system management features, security, integrity, etc. misnotes-dbms-19980121. ppt Steven O. Kimbrough 2 Overview of Database Management • Important and fundamental distinctions – System software vs application software – Logical vs physical » See MIS
Notes, chapter 1 • System software, e. g. , – – – – Operating system Language compilers Communications DBMS • Idea: everybody needs it, so write it once and standardly for all – Reading/writing files (OS) – Storing/retrieving records (DBMS) misnotes-dbms-19980121. ppt Steven O. Kimbrough 3 Overview of Database Management • Where do database systems come from? – Commercial software vendors » Oracle, DB2, RDB, Ingres, Focus, etc. for mainframes » For Unix, Oracle, Ingres, Informix, etc. » Macs: Mainly Oracle » PCs: Access, Paradox, Oracle, etc. Is there more than one kind? Why? – Yes: flat file systems, network systems, hierarchical systems, relational systems, object-oriented systems – And different brands for each type – Why? History, needs and requirements • Is there a standard? Yes, 2 really – Relational DBMS – SQL as the access language » DDL, DML, etc. » Note: Can have SQL w/o relational and relational w/o SQL – Note: Access is relational & has SQL access misnotes-dbms-19980121. ppt Steven O. Kimbrough 4 Overview of Database Management • Who uses database systems? – – – Everyone, high, middle, and low Pervasive in business and commerce Difficult to overestimate importance Quite possible to earn your way through school doing database work • Are databases here to stay? Are they stable? – Here to stay? Absolutely! – Stable? Yes, but… » Relational DBMS with SQL: commercial de facto standard; client-server uses » Object-oriented DBMS: thought to be the next big thing • • • A few years away Specialized uses SQL will stay—probably misnotes-dbms-19980121. ppt Steven O. Kimbrough 5
More Comments on Database and Information Systems Generallly • Important concepts/themes • Persistence – Why we need data bases • Metaphors and computing generally and database specifically – Relational model, relational databases: » Tables, tables, tables • Indirection – e. g. external, conceptual, internal views of database misnotes-dbms-19980121. ppt Steven O. Kimbrough 6 Overview of Database Management • • • Why database? Alternatives are unattractive Positive reasons for DBMSs • • • • • • • Data independence Reduction of redundancy Avoidance of inconsistency Enforcement of standards Security Integrity Use of markets isnotes-dbms-19980121. ppt Steven O. Kimbrough 7 Database Architecture • • • • These comments apply broadly to all types of DBMSs … but, will focus on SQL, “big guys” systems ANSI/SPARC (broadly accepted) framework Three levels: • External • Conceptual • Internal “Relational” refers to how the data appears to users, so to the external and conceptual levels – How it appears (as tables) and – How you operate on it – Recall: metaphors • misnotes-dbms-19980121. ppt Steven O. Kimbrough 8 The External Level The individual user level (application programmer, someone interacting online with the database, or an application, or a user via a nice front end) Users (typically) interact with a DBMS using a language of some sort. • e. g. , SQL, either embedded in a programming language or interactively • e. g. , QBE for users Data sublanguage vs host language Data sublanguage: • • • Data definition language (DDL) Data manipulation language (DML) • • • The point of external views: to give different users different views of the data – Views: concept, advantages isnotes-dbms-19980121. ppt Steven O. Kimbrough 9 The Conceptual Level • A “representation of the entire information content of the database” • … from the point of view of the underlying data model (here, relational) Conceptual schema Conceptual schema is to be “pure”; no refernce to the physical • • misnotes-dbms-19980121. ppt Steven O. Kimbrough 10 The Internal Level • • Described by the internal schema Refers to details of the physical representation – E. g. , how records are laid out on a hard drive • But NOT to device-specific physical details isnotes-dbms-19980121. ppt Steven O. Kimbrough 11 The Database Administrator • Data administrator—makes strategic and policy decisions Database administrator—technical support for implementing policy decisions • • • Defines internal schema Liaison with users Oversees and defines security and integrity checks Manages backup and recovery Performance monitoring, database tuning • • • misnotes-dbms-19980121. ppt Steven O. Kimbrough 12 The Parts-Supplier Database • Three tables: S (suppliers), P (parts), SP
S S# SNAME STATUS CITY ====================================== S1 S2 S3 S4 S5 • • • • • • • • Smith Jones Blake Clark Adams 20 10 30 20 30 London Paris Paris London Athens Table (and relation) Tuple (or record) Field Attribute Domain Data types Data atomicity Key, key field misnotes-dbms-19980121. ppt Steven O. Kimbrough 13 The Parts-Supplier Database (continued) P P# PNAME COLOR WEIGHT CITY ====================================== P1 P2 P3 P4 P5 P6 • • • • • Nut Bolt Screw Screw Cam Cog Red Green Blue Red Blue Red 12 17 17 14 12 19 London Paris Rome London Paris London Ordering on the rows?
Does CITY in P mean the same as CITY in S? 17 what? Pounds? Ounces? Tons? Kilograms? Why more than one table? How do we answer queries that rely on data in more than one table? misnotes-dbms-19980121. ppt Steven O. Kimbrough 14 The Parts-Supplier Database (continued) SP S# P# QTY ====================================== S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 • • • • P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 300 200 400 200 100 100 300 400 200 200 300 400 S5? S6? Double key, S#-P# Why more than one table? How do we answer queries that rely on data in more than one table? misnotes-dbms-19980121. ppt Steven O. Kimbrough 15