L02-Background305.pdf

© Ahmad Esmaili , Robert F. Kelly 2009-2014 1 Introduction to Database Management Reading: Chapters 1 & 2 (read lightly) Some material from Business Database Systems by Connolly, Begg, and Holowczak  © Ahmad Esmaili , Robe...

© Ahmad Esmaili , Robert F. Kelly 2009-2014 1 Introduction to Database Management Reading: Chapters 1 & 2 (read lightly) Some material from Business Database Systems by Connolly, Begg, and Holowczak  © Ahmad Esmaili , Robert F. Kelly 2009-2014 2 Objectives Understand the: Common uses of database systems. Meaning of the term database. Meaning of the term Database Management System (DBMS). Components of the DBMS environment. Typical functions of a DBMS. Advantages/disadvantages of DBMSs Build your first DB Your overall goal for this session is to develop a good intuitive idea of a database  © Ahmad Esmaili , Robert F. Kelly 2009-2014 3 Examples of Database Systems Purchases using your credit card Your iTunes library What is your intuitive sense Your navigation (maps) system of entities in these databases? Your academic record (Solar) Your health history (maybe) All the records of all mobile phone calls in the past 5 years The access history of all users that accessed your company’s Web site  © Ahmad Esmaili , Robert F. Kelly 2009-2014 4 Definitions Database - collection of logically related data (and a description of this data) DBMS - A software system that enables users to define, create, and maintain the database and that provides controlled access to this database  © Ahmad Esmaili , Robert F. Kelly 2009-2014 5 Database Application/System Database Application A computer program that interacts with the database by issuing an appropriate request (typically using SQL statements) to the DBMS Database System The collection of database applications that interact with the database along with the DBMS and the database itself.  © Ahmad Esmaili , Robert F. Kelly 2009-2014 6 DB Access Users can be in differing locations  © Ahmad Esmaili , Robert F. Kelly 2009-2014 7 Functions of a DBMS Data storage, retrieval, and update Transaction support Concurrency control services Recovery services Authorization services Support for data communication Integrity services  © Ahmad Esmaili , Robert F. Kelly 2009-2014 8 Tables properties ID Product Price 1 Pocket knife - Nile 4.50 2 Compass 10.00 3 Geo positioning system 500.00 4 Map measure 4.90 A relational table Each table has a primary key corresponds to an entity that uniquely defines the corresponding row  © Ahmad Esmaili , Robert F. Kelly 2009-2014 9 Views Allows each user to have his or her own view of the database. A view is essentially some subset of the database. Benefits include: Provides a level of security Provides a mechanism to customize the appearance of the database Presents a consistent, unchanging picture of the structure of the database, even if the underlying database is changed  © Ahmad Esmaili , Robert F. Kelly 2009-2014 10 DBMS Components 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  © Ahmad Esmaili , Robert F. Kelly 2009-2014 11 Database Design The structure of the database is determined during the database design The design process is a translation of an understanding of the application (e.g., written statement) into the design Many different designs are possible Design rules restrict the number of possible designs  © Ahmad Esmaili , Robert F. Kelly 2009-2014 12 MS Access DBMS Intended as a simple way to create and use a DB Limited number of simultaneous users GUI interface Access Provides a datasheet view Non-standard SQL and a design view Access DB consists of Database name Collection of tables Collection of fields within each table (think of a table as an entity with properties) Type for each field  © Ahmad Esmaili , Robert F. Kelly 2009-2014 13 Advantages of DBMSs Control of data redundancy Data consistency Sharing of data Improved data integrity Improved maintenance through data independence.  © Ahmad Esmaili , Robert F. Kelly 2009-2014 14 Disadvantages of DBMSs Complexity Cost Cost of conversion Performance Higher impact of a failure  © Ahmad Esmaili , Robert F. Kelly 2009-2014 15 DBMS Systems Timeline  © Ahmad Esmaili , Robert F. Kelly 2009-2014 16 Nonprocedural Access Query: request for data to answer a question Indicate what parts of database to retrieve not the procedural details Improve productivity and improve accessibility SQL SELECT statement and graphical tools Your work in the course will use the graphical GUI in MS Access as well as SQL  © Ahmad Esmaili , Robert F. Kelly 2009-2014 17 Graphical Tool Relational Data Model (RDM)  © Ahmad Esmaili , Robert F. Kelly 2009-2014 18 Are We on Track? Goal: (use MS Access) Download Excel file on Blackboard: SBU_ActiveProjects.xlsx Open Access Create a blank database and import the Excel file Let Access add the primary key Name the table: Awards Do not select Analyzer Wizard Save the DB (named SBU_Awards) (not import steps) Make the table more readable (e.g., adjust column widths) View the DB in Access design view  © Ahmad Esmaili , Robert F. Kelly 2009-2014 19 Did You Achieve the Objectives? Understand the: Common uses of database systems. Meaning of the term database. Meaning of the term Database Management System (DBMS). Components of the DBMS environment. Typical functions of a DBMS. Advantages/disadvantages of DBMSs Build your first DB