Summary INF2603 - Databases I systems.

INF2603 - Databases I systems. Chapter 1: Database Systems Data: Raw facts Information: The result of processing raw data to reveal its meaning. Knowledge: The body of information and facts about a specific subject. Data Management: A discipline that focuses on the proper generation, storage and retrieval of data. Database: A shared, integrated computer structure that stores a collection of: • End-user data or raw facts of interest to the end user. • Metadata or data about data, through which the end-user data are integrated and managed. DBMS (Database management system): A collection of programs that manage the database structure and controls access to the data stored in the DB. Data Inconsistency: Exists when different versions of the same data appear in different places. Query: A specific request for data manipulation. Done through the DBMS. Types of databases Single User DB: Supports only one user at a time. It runs on a PC and is called a desktop DB. Multiuser DB: Supports multiple users at the same time, when it has a small user base it is called a workgroup DB, if it supports more than 50 users it is called an enterprise DB. Centralised DB: A DB that supports data located at a single site. Distributed DB: A DB that supports data distributed across several different sites. Operational DB: A DB that is designed primarily to supports a company’s day to day operations. (AKA Transactional DB, Production DB) Data Warehouse: Focuses on storing data used to generate information required to make tactical or strategic decisions. Why DB design is important DB Design: Refers to the activities that focus on the design of DB structure that will be used to store and manage end user data. Historical Roots: Files and File Systems Basic file terminology: Field: A character or group of characters that has a specific meaning. A field is used to define and store data. Record: A logically connected set of one or more fields that describe a person, place or thing. File: A collection of related records. Limitations of file system data management: • Requires extensive programming. • There are no ad hoc query capabilities. • System admin can be more complex and difficult. • It’s difficult to make changes to existing structures. • Security features are likely to be inadequate. Structural and Data dependence: Structural Dependence: Access to a file is dependent on its structure. Structural Independence: Exists when it’s possible to make changes in the file structure without affecting the application program’s ability to access the data. Data Dependence: Because all data access programs are subject to change when any of the file’s data storage characteristics change the file system is said to exhibit data dependence. Data Independence: Exists when it is possible to make changes in the data storage characteristics without affecting the application program’s ability to access the data. The practical significance of data dependence is the difference between the logical data format (how the human views the data) and the physical data format (how the computer sees the data). Data dependence makes the file system extremely cumbersome from a programming and data management point of view. Data Redundancy Islands Of Information: The storage of the same basic data in different locations. This means some variations of the same data can also be saved. Data Integrity: The condition in which all of the data in the DB are consistent with real world events and conditions. Data Redundancy: Exists when the same data are stored unnecessarily at different places. Uncontrolled data redundancy sets the stage for: • Data Inconsistency: Exists when the same data are stored unnecessarily at different places. • Data Anomalies: Occurs when all of the required changes in the redundant data are not made successfully. Types of anomalies: Update, Insert, Delete. Database Systems The database system environment Fig 1.6 P21 – Contrasting database and file systems. Database system: Refers to an organization of components that define and regulate the collection, storage, management and use of data within the DB environment. It is comprised of 5 parts: • Hardware • Software • People • Procedures • Data DBMS Functions A DBMS performs several important functions that guarantee the integrity and consistency of the data in the DB: • Data Dictionary Management: The DBMS stores definitions of the data element and their relationships (metadata) in a data dictionary. • Data Storage Management: The DBMS creates and manages the complex structures required for data storage, thus relieving you of the difficult task of defining and programming the physical data characteristics. • Data Transformation and Presentation: The DBMS transforms entered data to conform to required data structures. • Security Management: The DBMS creates a security system that enforces user security and data privacy. • Multiuser Access Control: The DBMS uses sophisticated algorithms to ensure that multiple users can access the database concurrently without compromising the integrity of the DB • Backup and Recovery Management: The DBMS provides backup and data recovery to ensure data safety and integrity. • Data Integrity Management: The DBMS promotes and enforces integrity rules, thus minimizing data redundancy and maximizing data consistency. • Database Access Languages and Application Programming Interface: The DBMS provides data access through a query language. • Database Communication interface: The DBMS provides access to its data via the internet, networks, etc. Managing the DB System Although the DB system yields considerable advantages over previous data management approaches, DB systems do impose significant overheads: • Increased cost. • Management complexity. • System maintenance. • Vendor dependence. Chapter 2: Data Models The Importance of Data Models Data Model: Relatively simple representations, usually graphical, of more complex real world data structures. Within a DB environment, a data model represents data structures and their characteristics, relationships constraints, and transformations. (aka Database model) Data Model Basic Building Blocks Entity: Anything about which data are to be collected and stored. (ex. Person) Attribute: A characteristic of an entity. (ex. Surname) Relationship: Describes an association among entities. Constraint: A restriction placed on data. Constraints are important because they help to ensure data integrity. They are normally expressed as rules. (ex. Salary has to be between 10000 and 30000) Business Rules Business Rule: A brief, precise and unambiguous description of a policy, procedure or principle within a specific organization. Discovering Business Rules The main sources of business rules are company managers, policy makers, department managers and written documentation, such as procedures, standards or operations manuals. The Evolution of Data Models The Hierarchical Model The hierarchical model was developed in the 60’s to manage large amounts of data for complex manufacturing projects such as the Apollo rocket. The Network Model The network model was created to represent complex data relationships more effectively than the hierarchical model, to improve DB performance and to impose a DB standard. • Network Schema: The conceptual organization of the entire DB as viewed by the DB admin. • Network Subschema: Defines the portion of the DB seen by the programs that actually produce the desired info from the data contained within the DB. • Data Management Language (DML): Defines the environment in which data can be managed. The Relational Model The relational model was introduced by E. F. Codd (IBM) in 1970. The relational DB model is implemented through a sophisticated Relational Database Management System (RDBMS). The most important advantage of the RDBMS is its ability to hide the complexities of the relational model from the user. • Table: A matrix, consisting of a series of row/column intersections. Tables, also called relations, are related to each other through the sharing of a field which is common to both entities. • Relational Diagram: A representation of the relational database’s entities, the attributes within those entities, and the relationships between those entities. From the end-user perspective, any SQL-based relational database application involves three parts: • The end-user interface • A collection of tables stored in the DB • SQL Engine The Entity Relationship Model This model has become a widely accepted standard for data modelling. Entity relationship diagram (ERD): Uses graphical representations to model DB components. It clearly makes a distinction between entities and the relationships between them. The ER model is based on the following components: • Entity: Anything about which data are to be collected and stored. Each row in the relational table is known as an entity instance or entity occurrence in the ER model. • Entity Set: A collection of entities. • Relationship: Relationships describe associations among data. ERD modellers use the term connectivity to label the types of relationships. Fig 2.5 P 45 The basic UML ERD The Object-Orientated (OO) model In the object-orientated data model (OODM), both data and their relationships are contained in a single structure known as an object. In turn, the ODM is the basis for the object-orientated database management system (OODBMS). The OO data model is based on the following components: • An object is an abstraction of a real-world entity. An object represents only one individual occurrence of an entity. • Attributes describe the properties of an object. • Objects that share similar characteristics are grouped in classes. A class is a collection of similar objects with shared structure (attributes) and behaviour (methods). • Classes are organized in a class hierarchy. The class hierarchy resembles an upside-down tree in which each class has only one parent. • Inheritance is the ability of an object within the class hierarchy to inherit the attributes and methods of the classes above it. Fig 2.7 P48 A comparison of the OO model and the ER model. Degrees of data abstraction External Model Conceptual Model Internal Model Physical Model The external model is the end users’ view of the data environment As data is being modelled, ER diagrams will be used to represent external views. A specific representation of an external view is known as an external schema. The conceptual model represents a global view of the entire database. It is a representation of data as viewed by the entire organization. The internal model is the representation of the database as seen by the DBMS The physical model operates at the lowest level of abstraction, describing the way data is saved on storage media such as disks or tapes Chapter 3: The Relational Database Model Based on the predicate logic and set theory, the relational model has three well-defined components: • A logical data structure represented by the relational table, where data are stored. • A set of integrity rules to enforce that the data are and remain consistent over time. • A set of operations that define how data are manipulated. A logical view of data The relational model enables you to view data logically rather than physically. Tables and their characteristics The logical view of the relational database is facilitated by the creation of data relationships based on a logical construct known as a table. A table is perceived as a two dimensional structure composed of rows and columns. A table contains a group of related entities. A table is also called a relation. Within the relational model, columns of tables are referred to as attributes and rows of a table are known as tuples.