DATABASE TECHNOLOGIESLEARNING GOALSBasic Concepts of Data ManagementSlide 4Basic Concepts in Data ManagementTraditional File SystemsFile System AnomaliesDatabase Management System (DBMS)DBMS FunctionsMore DBMS FunctionsTypes of DBMSsTypes of DBMSs (Continued)Database ModelsFlat File DatabaseHierarchical Database ModelNetwork ModelRelational ModelDatabase DevelopmentDatabase Development Process (Continued)Slide 20Slide 21Slide 22Summary Questions1DATABASETECHNOLOGIESBUS3500 - Abdou Illia, Fall 2007(Week 3, Tuesday 9/4/2007)2LEARNING 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.3Basic Concepts of Data ManagementDatabase: Collection of data organized in different containersTable 1 Table 2 Table 3ReportForm 1Acc #:_______Name:_______4Basic Concepts of Data ManagementTableTwo-dimensional structure composed of rows and columnsFieldLike a column in a spreadsheetField nameLike a column name in a spreadsheetExamples: AccountID, Customer, Type, BalanceField valuesActual data for the fieldRecordSet of fields that describe an entity (a person, an account, etc.)Primary key A field, or group of fields, that uniquely identifies a recordAccountID Customer Type Balance660001 John Smith Checking $120.00660002 Linda Martin Saving $9450.00660003 Paul Graham Checking $3400.00Accounts tableEach table has:FieldsRecords1 Primary key5Basic 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 fields6Traditional 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 1Program 1File 1File 2File 3Program 2File 1File 2File 3Application 2Program 1File 1File 2File 3Program 2File 1File 2File 37File System AnomaliesInsertion anomalyData needs to be entered more than once if located in multiple file systemsModification anomaly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 data8Database 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 LanguageApplication 1Program-1 Program-2Application 2Program-1 Program-2DBMS9DBMS 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 databaseID Name Amt01 John 23.0002 Linda 3.0003 Paul 53.00Physical viewID Name02 LindaName Amt Paul 53.00ID Name Amt01 John 23.0002 Linda 3.00Logical views10More 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 control11Types of DBMSsDesktopUsed by individuals or small groupsRequires little or no formal trainingDoes not have all the capabilities of larger DBMSs12Types 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.13Database 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)14Flat File DatabaseStores data in basic table structuresNo relationship between tablesUsed on PDAs for address book15Hierarchical 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 flexible16Network ModelMany-to-many relationships between tablesAny record may be linked to any other recordHighly flexible but also highly complexRarely used17Relational 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 DBMSs18Database Development1) Analysis •Develop a clear understanding of how the organization works and what data is used•Determine data needs of each functional areaAnalyze data needs and useDevelop conceptual model Develop physical model Implement database Administer database19Database 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 database20Database 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 performance21Database Development Process (Continued)4) Database implementation•Install the DBMS