Database systems design - Oswego

Database systems design - Oswego

File Systems Introduction to Databases 8/27/2012 ISC 329 Isabelle Bichindaritz 1 Learning Objectives

Introduction to class. Introduction to databases. Files and file structures. Critique of the file system. Database systems. Database models. Evolution of database models. 8/27/2012 ISC 329 Isabelle Bichindaritz 2 Introduction to Class

Syllabus Schedule Web-site http://www.oswego.edu/angel Assignments Project (can be team-based) 8/27/2012 ISC 329 Isabelle Bichindaritz 3

Introduction to Class ISC 329 is a Database Applications course. Some of the objectives for this course include: 1. Understand fundamental database concepts and the different database systems. 2. Understand methodologies to conceptualize and design database systems. 3. Identify the key issues in developing database systems and applications. 4. Develop applications involving database systems with fourthgeneration programming tools in MS Access. 5. Develop Web database applications with MySQL and PHP. 8/27/2012 ISC 329 Isabelle Bichindaritz 4

Introduction to Class 1. 2. 3. 4. 5. 6. 7. 8. Fundamental concepts of files and databases The different database models hierarchical, relational, network Relational databases Conceptual data models Entity-Relationship model, UML model Normalization Database system development methodology SQL language commands and queries, query optimization

Development of database applications using fourth-generation programming techniques 9. Development of Web-based database systems 10. Database security 11. Ethical concerns 12. Transaction management and concurrency control 8/27/2012 ISC 329 Isabelle Bichindaritz 5 Acknowledgments Some of these slides have been adapted from Thomas Connolly and Carolyn Begg 8/27/2012

ISC 329 Isabelle Bichindaritz 6 Examples of Database Applications Purchases from the supermarket

Purchases using your credit card Booking a holiday at the travel agents Using the local library Taking out insurance Using the Internet Studying at university 8/27/2012 ISC 329 Isabelle Bichindaritz 7 File-Based Systems Collection of application programs that perform services for the end users (e.g. reports). Each program defines and manages its

own data. 8/27/2012 ISC 329 Isabelle Bichindaritz 8 File-Based Processing 8/27/2012 ISC 329 Isabelle Bichindaritz 9 Limitations of File-Based

Approach Separation and isolation of data Each program maintains its own set of data. Users of one program may be unaware of potentially useful data held by other programs. Duplication of data Same data is held by different programs. Wasted space and potentially different values and/or different formats for the same item. 8/27/2012 ISC 329 Isabelle Bichindaritz 10

Limitations of File-Based Approach Data dependence File structure is defined in the program code. Incompatible file formats Programs are written in different languages, and so cannot easily access each others files. Fixed Queries/Proliferation of application programs Programs are written to satisfy particular functions. 8/27/2012 ISC 329 Isabelle Bichindaritz

11 Any new requirement needs a new program. Database Approach Arose because: Definition of data was embedded in application programs, rather than being stored separately and independently. No control over access and manipulation of data beyond that imposed by application programs. Result: the database and Database Management System (DBMS). 8/27/2012

ISC 329 Isabelle Bichindaritz 12 Database Shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization. System catalog (metadata) provides description of data to enable programdata independence. Logically related data comprises entities, attributes, and relationships of an organizations information. 8/27/2012 ISC 329 Isabelle Bichindaritz

13 Database Management System (DBMS) A software system that enables users to define, create, and maintain the database and that provides controlled access to this database. 8/27/2012 ISC 329 Isabelle Bichindaritz 14 Database Management System (DBMS)

8/27/2012 ISC 329 Isabelle Bichindaritz 15 Database Approach Data definition language (DDL). Permits specification of data types, structures and any data constraints. All specifications are stored in the database. Data manipulation language (DML). General enquiry facility (query language) of the data.

8/27/2012 ISC 329 Isabelle Bichindaritz 16 Database Approach Controlled access to database may include: A security system. An integrity system.

A concurrency control system. A recovery control system. A user-accessible catalog. A view mechanism. Provides users with only the data they want or need to use. 8/27/2012 ISC 329 Isabelle Bichindaritz 17 Views Allows each user to have his or her own view of the database.

A view is essentially some subset of the database. 8/27/2012 ISC 329 Isabelle Bichindaritz 18 Views Benefits include: Reduce complexity; Provide a level of security; Provide a mechanism to customize the appearance of the database; Present a consistent, unchanging picture of the structure of the database, even if the underlying

database is changed. 8/27/2012 ISC 329 Isabelle Bichindaritz 19 Components of DBMS Environment 8/27/2012 ISC 329 Isabelle Bichindaritz 20

Components of DBMS Environment Hardware Can range from a PC to a network of computers. Software DBMS, operating system, network software (if necessary) and also the application programs. Data Used by the organization and a description of this data called the schema. 8/27/2012

ISC 329 Isabelle Bichindaritz 21 Components of DBMS Environment Procedures Instructions and rules that should be applied to the design and use of the database and DBMS. People 8/27/2012 ISC 329 Isabelle Bichindaritz

22 Roles in the Database Environment Data Administrator (DA) Database Administrator (DBA) Database Designers (Logical and Physical) Application Programmers End Users (naive and sophisticated) 8/27/2012 ISC 329 Isabelle Bichindaritz

23 History of Database Systems First-generation Hierarchical and Network Second generation Relational Third generation Object Relational Object-Oriented 8/27/2012 ISC 329 Isabelle Bichindaritz 24

The DBMS Marketplace Relational DBMS companies Oracle, Sybase are among the largest software companies in the world. IBM offers its relational DB2 system. With IMS, a nonrelational system, IBM is by some accounts the largest DBMS vendor in the world. Microsoft offers SQL-Server, plus Microsoft Access for the cheap DBMS on the desktop, answered by lite systems from other competitors. Relational companies also challenged by object-oriented DB companies. But countered with object-relational systems, which retain the relational core while allowing type extension as in OO systems. 8/27/2012

ISC 329 Isabelle Bichindaritz 25 Hierarchical Database Model History: North American Rockwell developed GUAM (Generalized Update Access Method) Mid 1960s Rockwell partner with IBM to create Information Management System (IMS) IMS DB/DC lead the mainframe database market in 70s and early 80s Represents well how components are decomposed into parts 8/27/2012

ISC 329 Isabelle Bichindaritz 26 Hierarchical Database Model Logically represented by an upside down tree Each parent can have many children Each child has only one parent 8/27/2012 ISC 329 Isabelle Bichindaritz 27 Hierarchical Database Model

Advantages Conceptual simplicity Database security and integrity Data independence Efficiency Disadvantages

8/27/2012 Complex implementation Difficult to manage and lack of standards Lacks structural independence Applications programming and use complexity Implementation limitations (no M:N relationship) ISC 329 Isabelle Bichindaritz 28 Network Database Model History: CODASYL (Conference on Data Systems Languages) created a group to work on standardization of databases: Database Task Group (DBTG)

Identified 3 database component: Network schema (database organization) Subschema (views of database per user) Data management language 8/27/2012 ISC 329 Isabelle Bichindaritz 29 Network Database Model Each record can have multiple parents Composed of sets - relationships Each set has owner record and member record Member may have several owners A set represents a 1:M relationship between the owner

and the member 8/27/2012 ISC 329 Isabelle Bichindaritz Figure 1.10 30 Network Database Model Advantages

Conceptual simplicity Handles more relationship types Data access flexibility Promotes database integrity Data independence Conformance to standards Disadvantages System complexity Lack of structural independence 8/27/2012 ISC 329 Isabelle Bichindaritz

31 Relational Database Model First developed by E.F. Codd (IBM) in 1970 First deployed on mainframe computers (DB2), then also personal computers Oracle, Informix, SQL server, DB2 8/27/2012 ISC 329 Isabelle Bichindaritz 32 Relational Database Model Perceived by user as a collection of tables

for data storage Tables are a series of row/column intersections (a row corresponds to a record, a column to a field) Tables related by sharing common entity characteristic(s) RDBMS 8/27/2012 ISC 329 Isabelle Bichindaritz 33 Relational Database Model (cont.) Figure 1.11

8/27/2012 ISC 329 Isabelle Bichindaritz 34 Relational Database Model Advantages Structural independence Improved conceptual simplicity Easier database design, implementation, management, and use Ad hoc query capability with SQL Powerful database management system 8/27/2012

ISC 329 Isabelle Bichindaritz 35 Relational Database Model Disadvantages Substantial hardware and system software overhead Poor design and implementation is made easy May promote islands of information problems 8/27/2012 ISC 329 Isabelle Bichindaritz

36 Advantages of DBMSs Control of data redundancy Data consistency More information from the same amount of data Sharing of data Improved data integrity Improved security Enforcement of standards Economy of scale 8/27/2012 ISC 329 Isabelle Bichindaritz 37

Advantages of DBMSs Balanced conflicting requirements Improved data accessibility and responsiveness Increased productivity Improved maintenance through data independence Increased concurrency Improved backup and recovery services 8/27/2012 ISC 329 Isabelle Bichindaritz 38 Disadvantages of DBMSs

Complexity Size Cost of DBMS Additional hardware costs Cost of conversion Performance Higher impact of a failure 8/27/2012

ISC 329 Isabelle Bichindaritz 39 Database Design Database design deals with how to design a database Importance of good design Poor design results in unwanted data redundancy Poor design generates errors leading to bad decisions Practical approach Focus on principles and concepts of database design Importance of logical design 8/27/2012 ISC 329 Isabelle Bichindaritz

40

Recently Viewed Presentations

  • By Richard C. Wilson

    By Richard C. Wilson

    The EIF conducted an in-depth due diligence process, which included in-person meetings both in Luxembourg and Tel Aviv, on-site due diligence on the iAngels team and our processes, and meetings with CEOs from our portfoliocompanies ... By Richard C. Wilson...
  • 21st Century Civic Literacy Rebecca Tinder Bowles Rice

    21st Century Civic Literacy Rebecca Tinder Bowles Rice

    No Child Left Behind identifies civics as one of the core academic subject areas essential to a good education. The focus on core subjects must expand beyond minimal mastery to high levels of understanding and skill. Civics is not just...
  • 10.1: Scatter Plots & Trend Lines Essential Question:

    10.1: Scatter Plots & Trend Lines Essential Question:

    Two-variable data: two data points, one individual/object. For example: weight and height of a 9th grader or MYA score and Math % grade of an Algebra 1 student. Scatterplot: Visual representation of two-variable data. One value is plotted on the...
  • NPQH Final Assessment Aims of the assessment process

    NPQH Final Assessment Aims of the assessment process

    NPQH assessment description and tasks overview. NPQH Final Assessment Guidance. Submission Form for both task 1 and 2 . General submission advice. Task 2 submission advice. How to book your submission window. All these documents are available on the Inspiring...
  • 8 Indo Global summit and Expo on th

    8 Indo Global summit and Expo on th

    Long . lasting . immunity. Safe. Stable in field conditions. Combined. Single dose. Affordable (and accessible) to all. Vaccine development . Historically, there has been a close and longstanding association between microbiology and vaccine development and the use of laboratory...
  • Corporate Presentation Tutorial

    Corporate Presentation Tutorial

    Services Ontology Development An Overview from HDTF December 2007 Ken Rubin EDS Co-Chair, OMG Healthcare Domain Task Force Co-Chair, HL7 Services-oriented Architecture SIG
  • 中国科技政策 - Embassy of China, London

    中国科技政策 - Embassy of China, London

    Annual rate 7.2% 2000 2020 ¥ 40 tn GDP ¥ 10 tn per capita GDP energy consumption reduces 4% each year main pollutants cut 40% by 2020 Domestic Oil supply and demand Domestic Production Total Consumption 200 100 1980 1985...
  • Migrations in Development Neural Crest Migration  The peripheral

    Migrations in Development Neural Crest Migration The peripheral

    Steps In NC Migration Initiation of migration Migration Homing Differentiation Initiation of Migration Transition from tissue ball to migration Loss of cadherins Acquisition of Integrins Acquisition of motile machinery Stimulus to migrate Migrations in Development Neural Crest Migration The peripheral...