Week 1 - Database.ppt

Database Systems Topic 1: Introduction to Database Systems 1 Learning Objectives Learning Objectives: Understand the role of data as a resource in an organisation Provide definitions for: Data, Database and DBMS Describe the database system environment U...

Database Systems Topic 1: Introduction to Database Systems 1 Learning Objectives Learning Objectives: Understand the role of data as a resource in an organisation Provide definitions for: Data, Database and DBMS Describe the database system environment Understand the motivation for the database approach State the objectives of the database approach Explain what data independence means Be aware of a database transaction Be aware of database backup and recovery Explain how a database improves the security of data Understand how a database inter-relates data through a Data Model Describe the major data models being: Relational, Hierarchical and Network References: Rob, P. & Coronel, C., Database Systems, 8th Edition, Chpt 1, Chpt 2 - Sections 2.1 - 2.5 2 Activity - Introduction to databases For discussion: (5 minutes) What is data? What is information? What is a database? 3  Data Data refers to raw facts. Facts concerning people, places, events, or other objects/concepts Data by itself is useless unless it is somehow processed Data that is processed to reveal meaning becomes information A lack of data leads to inadequate information and thus ill-informed decisions Data represents a valuable corporate resource which needs adequate integrity and security controls Data management is a discipline that focuses on the proper generation, storage and retrieval of data 4 Activity In pairs, discuss the difference between data and information and think of 4 examples of each. (5 minutes) We will talk about this using the board 5 Data vs. Information 6 Traditional File Systems Databases are often contrasted to traditional file systems though the latter are now rarely used. But as the problems that existed were the impetus for the development of the “Database Concept” it is worth noting some of these. Problems: Requires extensive programming in third- generation language (3GL) Data and structural dependence Data redundancy 7 Traditional File Systems Requests for information (reports) required a DP specialist to write programs for the department that required the report File systems developed to address needs Data was organized in the files according to expected use - led to islands of information To retrieve data required extensive programming in third-generation language (3GL), this was time consuming and made ad hoc queries impossible Often the same data was stored in many different locations, eg. agent details occurred in both the CUSTOMER and AGENT files 8 Traditional File Systems Example of an insurance company file – can you see the problems here? 9 Traditional File Systems Example of storing student records in different departments in a University, can you see the problems here? 10  Traditional File Systems Data Dependence – Changes in file’s data characteristics requires modification of data access programs – Must tell program what to do and how – Makes file systems cumbersome from a programming and data management views Structural Dependence – Change in file structure requires modification of related programs Data Redundancy – Different and conflicting versions of same data – Results of uncontrolled data redundancy > Data anomalies – Modification, Insertion, Deletion 11  Traditional File Systems Data Redundancy (cont) – Data inconsistency > Lack of data integrity File Terminology – Field > group of characters with specific meaning – Record > logically connected fields that describe a person, place, or thing – File > collection of related records 12 Database System Consists of related data stored in a single repository Provides advantages over file system management approach – Eliminates inconsistency, data anomalies, data dependency, and structural dependency problems – Stores data structures, relationships, and access paths The centralised control of data means that for many applications the data already exists The data is no longer related by application programs, but by the structure defined in the database 13 Database vs. File Systems 14 Objectives of Database Technology Data Independence Minimal Data Redundancy Increased Data Sharing Improved Data Quality Improved Security of Data Improved Access to Data Reduced Program Maintenance Inter-relate data thru the model 15 Data Independence Is the property of being able to change the logical or physical structure of data without requiring changes to application programs that manipulate that data Results in a more complex structure Data is stored independently of the programs Data descriptions are not embedded in application programs. Can the database structure be changed with no impact on application programs? Role of the database catalog or dictionary (the metadata) 16 Logical vs Physical Data Independence Application Global Logical Physical Program / Database Files Local Views Description Logical Data Physical Data Independence Independence 17 Minimal Data Redundancy Returning to slide 8: Minimise the duplication of data, separate into two 'piles' of data CUSTOMER data: c_no, c_name, c_address, c_zip, tp, amt, ren, a_code, AGENT data: a_no, a_name, a_phone Note agent details now only appears once for each agent 18 Sharing of Data – the problem The DBMS should: support multiple concurrent users of the same data and ensure that the data remains consistent at all times To avoid concurrency problems, transactions must be made logically serial One common technique used is record locking That is, a transaction can lock a record, preventing update by another transaction, until the update has completed. 19 Data Integrity Validation or integrity rules should be defined and automatically invoked at run time by the DBMS Significant variation exists among DBMSs in the level of support for data integrity. ANSI/ISO suggest that 100% of all enterprise rules should be held in the database, and specifically none in application programs. An area of significant development during the 1990s. 20 Security Protecting data against accidental or intentional use by unauthorised users Each user requires identification with a user-id and password Users can be restricted in the data they can see and what actions they can perform on that data. The DBMS encrypts and decrypts data as it is stored and retrieved. Views are often used to limit users access to data 21 Database and DBMS So…a database is a shared integrated computer structure that houses a collection of: – end user data eg. product details, orders placed for these products – metadata, or data about data, through which the data is integrated and managed A database management system (DBMS) is ─ a collection of programs that manages the database structure and controls access to the data stored in the database ─ includes a query language 22 DBMS Functions Data dictionary management stores the definitions of data and their relationships (metadata) in a data dictionary; any changes made are automatically recorded in the data dictionary. Data storage management creates and manages the complex structures required for data storage. Data transformation and presentation transforms entered data to conform to the data structures that are required to store the data 23 DBMS Functions Security management creates a security system and enforces security within that system. Multi-user access control creates complex structures that allow multiple‑user access to the data. Backup and recovery management performs backup and data recovery procedures to ensure data safety. 24 DBMS Functions Data integrity management promotes and enforces integrity rules to eliminate data integrity problems Database language and application programming interfaces provides access to the data via utility programs and from programming languages interfaces. Database communication interfaces provides end-user access to data within a computer network environment. 25 Database Models Collection of logical constructs used to represent data structure and relationships within the database Conceptual models: logical nature of data representation Implementation/Physical models: emphasis on how the data are represented in the database Conceptual Models consists of Context Model Key Model Fully attributed Model Implementation/Physical Models consists of Hierarchical Network Relational 26 Implementation Models Hierarchical Logically represented by an upside down tree  Each parent can have many children  Each child has only one parent 27 Implementation Models Network Each record can have multiple parents Composed of sets  Each set has owner record and member record  Member may have several owners 28 Relational Database Model  Perceived by user as a collection of tables for data storage  A table is a series of row/column intersections  Tables related by sharing common entity characteristics This semester we will be concentrating on the Relational database model (the basis for most current DBMS) 29 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 Disadvantages Substantial hardware and system software overhead Conceptual simplicity allows untrained people to potentially generate poor designs Ease of development may promote 'islands of information' as departments 'go their own way' 30  Relational Database Terminology A relational database represents a collection of relations EMPLOYEE relation: EMPLOYEE (emp_num, emp_lname, emp_fname, emp_initial, emp_hiredate, job_code) Row or TUPLE Column or ATTRIBUTE Tables, as above, are used as pictorial representations of relations although there are some problems with this approach – see next week 31 Summary Information is derived from data, which are usually stored in a database A DBMS is software that implements and manages a database Databases were developed to address the weaknesses of file systems A DBMS presents to the user a single data repository that promotes data sharing Enforces data integrity, eliminates redundancy and promotes data security 32