数据库原理介绍

1 .Chapters 1 & 2 (6e/5e ): Introduction to DB Prof. Steven A. Demurjian, Sr. Computer Science & Engineering Department The University of Connecticut 191 Auditorium Road, Box U-155 Storrs, CT 06269-3155 steve@engr.uconn.edu http://www.engr.uconn.edu/~steve (860) 486 - 4818 The majority of these slides are being used with the permission of Dr. Ling Lui, Associate Professor, College of Computing, Georgia Tech. Some slides have been adapted from the AWL web site for the textbook

2 .Motivating Database Management Manual Database Management Data are Not Stored Programmer Defines Both Logical Data Structure and Physical Structure (Storage)

3 .Motivating Database Management File Processing Data are Stored in Files with Interface Between Programs and Files. Various Access Methods Exist (E.G., Sequential, Indexed, Random) One File Corresponds to One or Several Programs.

4 .Problems with File Systems Data are Still Highly Redundant Sharing Limited and at the File Level Data is Unstructured “Flat” Files High Maintenance Costs Data Dependence Ensuring Data Consistency and Controlling Access to Data (Concurrent Access Problematic) Difficult to Understand by New Developers Difficulties in Developing New Applications Almost Impossible to Evolve with New Capabilities Risk of Inefficient Applications How have File Systems Changed today? Collaborative Editing

5 .Database vs. File System Coordinates Both Physical and Logical Access to the Data Data are Shared by All Programs Authorized to Have Access to It Flexible Access to Data (i.e., Queries) Multiple Users Accessing the Same Data at Same Time Coordinates Only the Physical Access to the Data Data Written by One Program May Not Be Readable by Another Program Pre-determined Access to Data (I.E., Compiled Programs) No Two Programs Can Concurrently Access the Same File

6 .The Role of DBMS in Computing

7 .What is a Database System? Web or PC app Mobile app REST API or Web Services

8 .What is the Role of Database System? Pervasive in Almost All Applications and Every Application Domain Norm rather than Exception Difficult to Imagine Application without Persistent Store Remember – Database is a Repository at Minimum Database Management for Mobile Computing Myriad of Architectures and Approaches: From: http://java.sun.com/javaone/javaone98/sessions/T400/index.html

9 .Database Concepts - Summary Schema vs. Data Database-Structured Collection of Data Describing Objects of Universe of Discourse being Modeling. A Database Consists of Schema and Data Schema: Describes the Intension (Type) of Objects Entity/Table/Relation: A portion of a Schema Data : Describes the Extension (Tuples) of Objects Data Definition vs. Data Manipulation Languages What is Metadata? Schema (metadata) Data DDL DML Operate on data according to the schema define Table

10 .What are Programming Analogs? Schema is Equivalent to a Class Library All of Different Types of Information Entity/Table/Relation Data Attributes and Types Akin to a Class Tuples Akin to Creating an Instance from Class Key Difference - Entity/Table is Two Abstractions Structure like a Class Also Represents a Set of all Tuples Meta-Data Akin to Java Reflection and Introspection Access to the Runtime Features of Objects Let’s See Example

11 .Classes for a Medical Application Data Types, Methods Patient Inherits from Person and Creates a Single Instance “John” Patient Ethnicity: String prefLang : String race:String Email: String gender: String getAllergies () get_clinical_notes () get_demographics () get_medications () get_immunizations () Observation Id:Integer statusCode : String name: String value: String Substance Id:Integer name: String statusCode : String effectiveTime:Date repeatNumber : Int Name family-name: String given-name: String prefix: String suffix : String Person Id: Integer name: name address: Address bday : String tel : String Address street: String locality: String region: String country: String deaNumber : String npiNumber:String Ethnicity: String race:String Email: String gender: String Provider hasMedicalObservations takesPrescribedMedication

12 .Database Entity Relationship Diagram Patient Entity represents Attributes of a set of Patients Defines Type and the Collection Patient Entity is a Database Table with Structure Like a Class However, Contains many Instances, e.g., Patients “John”, “George”, “Jane”, etc. Patient id Ethnicity prefLang race name address bday tel Observation id statusCode effectiveTime value Substance id name effectiveTime statusCode repeatNumber hasMedicalObservations takesPrescribedMedication

13 .Database Tables Patient( pid , name, address, tel , bday , etc .) Substance( sid , name, statusCode , etc .) Observation( oid , value, statusCode , etc .) PatientObservations ( pid , oid ) PatientMedications ( pid , sid )

14 .When is a Database System Needed? Traditional Examples Typical Environment Corporate Enterprise (Business Data vs. Bibliographies) Data With Large Homogenous Parts (e.g., Formatted Data) Data Relevant Over a Long Time Data Used by Many Simultaneous Users (Batch and On-line Users) for Retrieval & Update

15 .When is a Database System Needed? Emerging Examples Mobile Devices Fitness Devices Genetic, Genomic, and Phenotypic for Medical Research and Treatment Emerging Platforms Mobile: Store Locally in DB Format and Synchronize to Remote Location Fitness: Store on Fitness Device and Sync to both Mobile Device and Remote Location Genetic/Genomic: Data for a single Individual Database for “Targeted” Population for Medical, Drug, etc., Research

16 .An Example Database System An Integrated Telephone Customer Information System (Circa early 1980s) What are Examples Today? Has Scale Increased?

17 .The OpenMRS Sample Database Schema 99 Tables, Sample Database with 5000 patients and 500,000 observations

18 .What are World Largest DBs? (2010) * *http://www.comparebusinessproducts.com/fyi/10-largest-databases-in-the-world

19 .What is a DBMS? A Database Management System (DBMS) is the Generalized Tool that Facilitates the Management of and Access to the Database Main Functions: Defining a Database : Specifying Data Types, Structures, and Constraints Constructing a Database : the Process of Storing the Data Itself on Some Storage Medium Manipulating a Database : Function for Querying Specific Data in the Database and Updating the Database

20 .What is a DBMS? Additional Functions: Interaction with File Manager Data Storage and AccessIntegrity Enforcement Guarantee Correctness, Validity, Consistency Security Enforcement Prevent Data From Illegal Uses Concurrency Control Control the Interference Between Concurrent Programs Prevent “Lost Updates” Don’t Give Away Last Seat of CSE Class to 4 Students Recovery from Failure Query Processing and Optimization

21 .Components of a DBMS

22 .DBMS Architecture – High Level DBMS Languages Data Definition Language (DDL) Data Manipulation Language (DML) Host Language: DML Specification (e.g., SQL) is Embedded in a “Host” Programming Language (e.g., Java, C++) DBMS Interfaces Menu-Based Interface Graphical Interface Forms-Based Interface Interface for DBA (DB Administrator ) Abstract Layers RESTful APIs Hibernate Framework (OO to Relational Mapping

23 .DDL Defining Database Tables CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL , MINIT CHAR , LNAME VARCHAR(15) NOT NULL , SSN CHAR(9) NOT NULL , BDATE DATE ADDRESS VARCHAR(30) , SEX CHAR , SALARY DECIMAL(10,2) , SUPERSSN CHAR(9) , DNO INT NOT NULL , PRIMARY KEY (SSN) , FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN) , FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER) ) ;

24 .From Tables – Define Schema

25 . …and Corresponding DB Tables Which Represent Tuples/Instances of Each Relation 1 4 5 5 A S C null W B null null

26 . …and Corresponding DB Tables

27 .Data Manipulation via SQL Retrieve the Birthdate and Address of the Employee whose Name is John B. Smith. SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME=John AND MINIT=B’ AND LNAME=Smith’ Which Row(s) are Selected? B S C null W B null null

28 .Data Manipulation via SQL Retrieve Name and Address of all Employees who work for the Research Department SELECT FNAME, MINIT, LNAME, ADDRESS, DNAME FROM EMPLOYEE, DEPARTMENT WHERE DNAME=Research AND DNUMBER=DNO What Action is Being Performed?

29 .Simple SQL Queries - Result Called a Join on DNO=DNUMBER