Transcript Database
DATABASE TECHNOLOGIES (Week 3, Tuesday 9/4/2007) BUS3500 - Abdou Illia, Fall 2007 1 LEARNING GOALS Explain basic concepts of data management. Describe file systems and identify their problems. Define database management systems and describe their various functions. Explain how the relational database model works. Describe how databases are developed. 2 Basic Concepts of Data Management Table 1 Table 2 Form 1 Acc #:_______ Name:_______ Table 3 Report Database: Collection of data organized in different containers 3 Basic Concepts of Data Management Accounts table AccountID Customer Type Balance 660001 John Smith Checking $120.00 660002 Linda Martin Saving $9450.00 660003 Paul Graham Checking $3400.00 Each table has: Fields Records 1 Primary key Table Two-dimensional structure composed of rows and columns Like a column in a spreadsheet Like a column name in a spreadsheet Examples: AccountID, Customer, Type, Balance Actual data for the field Set of fields that describe an entity (a person, an account, etc.) A field, or group of fields, that uniquely identifies a record Field Field name Field values Record Primary key 4 Basic Concepts in Data Management A Primary key could be a single field like in this table A Primary key could be a composite key, i.e. multiple fields 5 Traditional File Systems System of files that store groups of records used by a particular software application Simple but with a cost Inability to share data Inadequate security Difficulties in maintenance and expansion Allows data duplication (e.g. redundancy) Application 1 Application 2 Program 1 Program 2 Program 1 Program 2 File 1 File 1 File 1 File 1 File 2 File 2 File 2 File 2 File 3 File 3 File 3 File 3 6 File System Anomalies Insertion anomaly Modification anomaly Data needs to be entered more than once if located in multiple file systems Redundant data in separate file systems Inconsistent data in your system Deletion anomaly Failure to simultaneously delete all copies of redundant data Deletion of critical data 7 Database Management System (DBMS) Combination of software and data for Collecting, storing and managing data in a database environment. A DBMS includes: Database Database engine (for accessing and modifying the DB content) Data Manipulation Language Application 1 Program-1 Application 2 Program-2 Program-1 DBMS Program-2 8 DBMS Functions Store data (in tables) on secondary storage Transform data into information (reports, ..) Provide user with different logical views of actual database content Provide security DBMSs control who can add, view, change, or delete data in the database Physical view ID Name Amt 01 John 23.00 02 Linda 3.00 03 Paul 53.00 ID 02 Name Paul Logical views Name Linda ID Name Amt 01 John 23.00 02 Linda 3.00 Amt 53.00 9 More DBMS Functions Allow multi-user access Control concurrency of access to data Prevent one user from accessing data that has not been completely updated When selling tickets online, Ticketmaster allows you to hold a ticket for only 2 minutes to make your purchase decision, then the ticket is released to sell to someone else – that is concurrency control 10 Types of DBMSs Desktop Used by individuals or small groups Requires little or no formal training Does not have all the capabilities of larger DBMSs 11 Types of DBMSs (Continued) Enterprise Serve multiple locations and store large amounts of data Either centralized or distributed Centralized – all data on one server Easy to maintain Prone to run slowly when many simultaneous users No access if the one server goes down Distributed – each location has part of the database Very complex database administration Usually faster than centralized If one server crashes, others can still continue to operate. 12 Database Models Database model = a representation of the relationship between structures (e.g. tables) in a database Four common database models Flat file model Hierarchical, or tree structure, model Network model Relational model (this one is the most common) 13 Flat File Database Stores data in basic table structures No relationship between tables Used on PDAs for address book 14 Hierarchical Database Model Resembles an inverted tree, with the root at the top Limited to storing data in one-to-many relationships One parent segment to many child segments Very fast when searching large amounts of data in a pre-specified order Not very flexible 15 Network Model Many-to-many relationships between tables Any record may be linked to any other record Highly flexible but also highly complex Rarely used 16 Relational Model Multiple tables related by common fields Uses controlled redundancy to create fields that provide linkage relationships between tables in the database These fields are called foreign keys – the secret to a relational database A foreign key is a field, or group of fields, in one table that is the primary key of another table Somewhat slower than hierarchical and network DBMSs 17 Database Development Analyze data needs and use Develop conceptual model Develop physical model Implement database Administer database 1) Analysis • • Develop a clear understanding of how the organization works and what data is used Determine data needs of each functional area 18 Database Development Process (Continued) 2) Develop a conceptual model – • • • Show how data are grouped together and related to each other Entity-Relationship diagrams (ERDs) are used Less expensive to correct an ERD than to redesign an already constructed database 19 Database Development Process (Continued) 3) Develop a physical model – • • • Physical model provides specific details about each table and field in the database Normalization used to remove redundant data and therefore minimize any anomalies Optimize the database for performance 20 Database Development Process (Continued) 4) Database implementation • • • Install the DBMS software Build the database Test 21 Database Development Process (Continued) 5) Database administration • • • Ensures database efficiency Manages backup and restoration Sets up user accounts and security 22 Summary Questions Notes 1) What is a table, a field, a record, a primary key, a composite key? 4-5 2) What are the problems with traditional file systems? 6,7 3) What are the major components of a DBMS? 8 4) (a) Name some Desktop DBMSs. (b) Name some Enterprise DBMSs. 5) What are the differences between Flat File and Relational database models? 6) What are the steps for Database development? 14,17 18- 23