Chapter 1 Databases and Database Users CS 6360 Database Systems Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 Outline 1 1 Introduction 1 2 An Example 1 3 Characteristics of the Database Approach 1 4 Actors on the Scene 1 5 Workers behind the Scene 1 6 Advantages of Using the DBMS Approach 1 7 A Brief History of Database Applications 1 8 When Not to Use a DBMS 2 1 1 Introduction What is a Database Database Collection of related data Known facts that can be recorded and that have implicit meaning Miniworld or Universe of Discourse UoD Represents some aspect of the real world Logically coherent collection of data with inherent meaning semantics Built for a specific purpose 4 Examples Traditional database applications Store textual and or numeric information such as the Medicare database Multimedia databases Store images audio clips and video streams digitally Most modern DBMSs can do this Geographic information systems GIS Store and analyze maps weather data and satellite images 5 Examples eCommerce eBay Amazon etc Data warehouses and online analytical processing OLAP systems Extract and analyze useful business information from very large databases Support decision making Real time and active database technology Control industrial and manufacturing processes 6 Database Management System DBMS A suite of programs that allows a user to create and maintain a database You can specify the types of data relationships between various data elements and constraints on what can be stored Metadata describes the data and is stored in the database Also called a data dictionary A schema is the structure of the database 7 Database Management System cont d DBMSs allow you to manipulate the database both the schema and the actual data You can query the database to retrieve information based upon criteria You can update the database which includes adding new information changing existing information and removing records DBMSs are generally multi user they support requests from large numbers of users 8 Database Management System cont d DBMSs generally provide security both in terms of encrypting sensitive data and limiting access to authorized users DBMSs provide a programming interface API to allow you to write programs to interact with the database 9 Practical Stu There are two readily available DBMSs Microsoft SQL Server The professional version is free to you as a UTD student while the express edition is free to everyone MySQL from Oracle There is a free version of this and that s what I ll be using for most of my examples You can download Java libraries for either one I suggest you do this soon 10 Implementation Terminology Table A set of data records of the same format divided into columns that all contain the same kind of data and rows of related records View A composite of various related tables Stored Procedure A set of SQL statements that can be parameterized and executed 11 Terminology Relational Algebra Database Relation Table Field Column Tuple Row 12 Data Types Each column field data element is of a specific type Data types can include Fixed length strings Variable length strings Dates Integers Floating point numbers BLOBs binary large objects 13 1 2 An Example An Example UNIVERSITY database Information concerning students courses and grades in a university environment Data records tables STUDENT COURSE SECTION GRADE REPORT PREREQUISITE 15 An Example cont d Construct UNIVERSITY database Store data to represent each student course section grade report and prerequisite as a record in appropriate file Relationships among the records Manipulation involves querying and updating 16 An Example cont d A good question to ask when designing a database is What will you want to know from it For example Retrieve the transcript List the names of students who took the section of the Database course offered in fall 2013 and their grades in that section List the prerequisites of the Database course 17 An Example cont d Examples of updates Change the class of Smith to sophomore Create a new section for the Database course for this semester Enter a grade of A for Smith in the Database section of last semester 18 University Database 19 Phases for Designing a Database Requirements specification and analysis Does this sound like software engineering Conceptual design ER Model Chapter 7 Logical design Relational Model Chapter 3 Physical design 20 1 3 Characteristics of the Database Approach The Traditional File Approach In traditional file processing Each user defines and implements the files needed for a specific software application This is not strictly true usually we designed suites of applications that used common files However the layout of the files was defined in the code not in the file e g preferences files configuration files 22 The Database Approach Single repository maintains data that is defined once and then accessed by various users and the database contains the layout Self describing nature of a database system Insulation between programs and data and data abstraction Support of multiple views of the data Sharing of data and multiuser transaction processing 23 Self Describing Database Database system contains complete definition of structure and constraints Meta data Database Catalog Describes structure of the database Database catalog used by DBMS software Database users who need information about database structure 24 Database Catalog i e Meta data Example 25 Insulation Between Programs and Data Program data independence Structure of data files is stored in DBMS catalog separately from access programs Program operation independence Operations specified in two parts Interface includes operation name and data types of its arguments Implementation can be changed without affecting the interface 26 Support of Multiple Views of the Data View Subset of the database Contains virtual data derived from the database files but is not explicitly stored Multiuser DBMS Users have a variety of distinct applications Must provide facilities for defining multiple views 27 Sharing of Data and Multiuser Transaction Processing Allow multiple users to access the database at the same time Concurrency control software Ensure that several users trying to update the same data do so in a controlled manner Result of the updates is correct Online transaction processing OLTP application e g filling in forms 28 Sharing of Data and Multiuser Transaction Processing Transaction
View Full Document
Unlocking...