Databases terminologie

Declarative DMLs

(also referred to as nonprocedural DMLs) require a user to specify what data are needed without specifying how to get those data.

Database systems implement integrity constraints that can be tested with minimal overhead:

(because arbitrary predicates, which a constraint can be to a database, may be costly to test.) 1. Domain constraints 2. Referential Integrity 3. Assertion 4. Authorization

Representative Applications of Datases

- enterprise information - banking and finance, - universities - airlines - telecommunication

Most common types of Authorization (= Integrity constraint)

- read authorization - which allows reading, but not modification, of data; - insert authorization - which allows insertion of new data, but not modification of existing data; - update authorization - which allows modification, but not deletion, of data; - delete authorization - which allows deletion of data. We may assign the users all, none, or a combination of these types of authorization.

disadvantages of file-processing systems (1) Data redundancy and inconsistency

1. Data redundancy and inconsistency - files are likely to have different structures - programs may be written in several languages - same information may be duplicated in different files - it can lead to <<data inconsistency>>: various copies may no longer agree (e.g. double major student being in both departments, address change only in Music department, not the Mathematics one)

disadvantages of file-processing systems

1. Data redundancy and inconsistency 2. Difficulty in accessing data 3. Data isolation 4. Integrity problems 5. Atomicity problems 6. Concurrent-access anomalies 7. Security problems

The two types of DMLs

1. Procedural DMLs 2. Declarative DMLs

The data models can be classified into four different categories:

1. Relational Model 2. Entity-Relationship Model 3. Object-Based Data Model 4. Semistructured Data Model

The types of access of DML are:

1. Retrieval of information stored in database 2. Insertion of new information in the database 3. Deletion of information in the database 4. Modification of information stored in the database. Keyword: x. of INFORMATION STORED IN THE DATABASE (dus niet het wijzigen van de structuur)

How can you see the importance of databases in today's society?

1. There are endless examples of organizations using Web interfaces that made a variety of services and information online. 2. Database vendors like Oracle are among the largest software companies in the world, and database systems form an important part of the product in line of Microsoft and IBM.

disadvantages of file-processing systems (2) Difficulty in accessing data

2. Difficulty in accessing data - conventional file-processing environments do not allow needed data to be retreived in a convenient and efficient manner. More responsive data-retrieval systems are required for general use. (e.g. university clerk needs to find the names of students / that live within a particular postal-code area / who have taken at least 60 credit hours. Programs do not exist, either the clerk asks the programmaers to write the exact program to extract that information, or do it manually from the list that can be generated of all the students.

disadvantages of file-processing systems (3) Data isolation

3. Data isolation Because data are scattered in various files, and files may be in different formats, writing new application programs to retrieve the appropriate data is difficult.

disadvantages of file-processing systems (4) Integrity problems

4. Integrity Problems The data values stored in the database must satisfy certain types of consistency constaints. e.g. the university maintains bank accounts for different departments, - university (then) requires that the balance of a department may never be below zero. - programmers enforce these constraints in the system by adding appropriate code in the various application programs. - when new constraints are added, it is difficult to change the programs to enforce them. Problem is compounded when constraints involve several data items from different files.

disadvantages of file-processing systems (5) Atomicity problems

5. Atomicity problems. A computer system is subject to failure. In many applications it is crucial that if a failure occurs the data be restored to the consistent state that existed prior to the failure. e.g. transferring 500 euros to another bank account - either the transaction happened - or it did not. The funds transfer must be <<atomic>> - it must happen in its entirety or not at all. it is difficult to ensure atomicity in a conventional file-processing system.

disadvantages of file-processing systems (6) Concurrent-access anomalies

6. Concurrent-access anomalies For the sake of overall performance of the system and faster response, many systems allow multiple users to update the data simultaneously. To guard against the possibility of concurrent-access anomalies, the system must maintain some form of supervision. But supervision is difficult to provide because data may be accessed by many different application programs that have not been coordinated previously. e.g. Store clerks that debit the account balance of the department simultaneously. Depending on which program executing on behalf of each withdrawal writes the value last, the account balance could contain that (incorrect) value.

disadvantages of file-processing systems (7) Security problems

7. Security problems Not every user of the database system should be able to access all the data. But, since application programs are added to the file-processing system in an ad-hoc manner, enforcing such security constraints is difficult.

Integrity constraints that Database systems implement (1) - Domain Constraints

A domain of possible values must be associated with every attribute (for example, integer types, character types, date/time types)

Data Abstraction

A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored or maintained. Developers hide the complexity from users through several levels of abstraction, to simplify users´ interaction with the system: - Physical level - Logical level - View level

Relational Database

A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data. It also includes a DML and DDL. Most commercial relational database systems employ the SQL language. It is an example of the record-based model. Each table contains records of a particular type. It is possible to create schema's in the relational model that have problems such as unnecessarily duplicated information. For example: suppose we store the department budget as an attribute of the instructor record. Then, whenever the value of a particular budget (say that of the Physics department) changes, that change must to be reflected in the records of all instructors.associated with the Physics department. (= bad schema design)

Physical data independence

Although implementation of the simple structures at the logical level may involve complex physical-level structures, the user of the logical level does not need to be aware of this complexity. This is referred to as physical data independence. Application programs are said to exhibit physical data independence if they do not depend on the physical schema, and thus need not be rewritten if the physical schema changes.

Integrity constraints that Database systems implement (3) - Assertions

An assertion is any condition that the database must always satisfy. Domain constraints and referential-integrity constraints are special forms of assertions. However, there are many constraints that we cannot express by using only these special forms. For example., 'Every department must have at least five courses offered every semester' must be expressed as an assertion. When an assertion is created, the system tests it for validity. If the assertion is valid, then any future modification to the database is allowed only if it does not cause that assertion to be violated.

Application programs (1) what they are for

Because SQL can't execute some computations that are possible using a general-purpose programming language, and does also not support actions such as input from users, output to displays, or communication over the network, such computations and actions must be written in a host language, such as C, C++ or Java, with embedded SQL queries that access the data in the database. Application programs are programs that are used to interact with the database in this fashion.

DBMS

Database Management System << A collection of interrelated data >> and << a set of programs to access those data. >> A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored or maintained.

Application Programs (2) examples + how-to

Examples in a university system: - programs that allow students to register for courses - generate class rosters - calculate student GPA - generate payroll checks This can be done in two ways: by providing an application program interface (set of procedures) e.g. Open Database Connectivity for C-language, Java Database Connectivity for Java. Or by extending the host language syntac to embed DML calls within the host language program using a DML precompiler to convert the DML statements to normal procedure calls in the host language.

specification of functional requirement

In a specification of functional requirement, users describe the kinds of operations (or transactions) that will be p

Object-Based Data model (Category of data models 3)

Object-oriented programming (especially in Java, C++, or C#) has become the dominant software-development methodology. This led to the development of an object-oriented data model that can be seen as extending the E-R model with notions of encapsulation, methods (functions) and object identity. The object-relational data model combines features of the object-oriented data model and relational data model.

SQL

Structured Query Language - the most widely used query language.

Entity-Relationship Model (Category of data models 2)

The entity-relationship (E-R) data model uses a collection of basic objects, called entities and relationship among these objects. An entity is a 'thing' or 'object in the real world that is distinguishable from other objects. The entity-relationship model is widely used in database design.

During database design there is a problem regarding how to group the attributes. There are two ways to solve them. What are these?

The first one is to use the entity-relationship model; the other is to employ a set of algorithms (collectively known as normalization) that takes as input the set of attributes and generates a set of tables.

Data Abstraction Level (3) - View level

The highest level of abstraction describes only part of the entire database. The view level of abstraction exists to simplify the interaction of the many users of the database system that do not need all this information and need to only access a part of the database. The system may provide many views for the same database.

What sharply increased direct user access to databases?

The internet revolution of the 1990's. This led organisations to convert many of their phone interfaces (3) to databases into Web interfaces, and made a variety of services and information available online (e.g. online bookstore, ordering something, bank web site, advertisements collecting information, data about your web accesses) ------------------------------------------------------- 1. Early days -> interacting indirectly with databases (e.g. printed reports such as credit card statements, through agents such as bank tellers and airlines reservation agents) 2. Then -> automated teller machines 3. Also -> Phone interfaces (interactive voice-response system) (e.g. to find flight arrival/departure times or to register for university courses)

Data Abstraction Level (1) - Physical level

The lowest level of abstraction described how the data are actually stored. The physical level describes complex low-level data structures in detail.

Data Abstraction Level (2) - Logical level

The next-higher level of abstraction described what data are stored in the database, and what relationships exists among those data

data dictionary

The output of DDL is placed in the data dictionary, which contains metadata- that is, data about data. The data dictionary is considered to be a special type of table that can only be accessed and updated by the database system itself (not a regular user). The database system consults the data dictionary before reading or modifying actual data. the DDL statement updates the data dictionary, which contains metadata.

Relational Model (Category of data models 1)

The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns, and each column has a unique name. Tables are also known as relations. The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types. Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes. THe columns of the table corresponds to the attributes of the record type. The relational data model is the most widely used data model, and a vast majority of current database systems are based on the relational model. The network data model and the hierarchical data model preceded the relational data model.

Semistructured Data Model (Category of data models 4)

The semistructured data model permits the specification of data where individual data items of the same type may have different sets of attributes. This is in contrast to the data models mentioned earlier, where every data item of a particular type must have the same set of attributes. The Extensible Markup Language (XML) is widely used to represent semistructured data.

Integrity constraints that Database systems implement (2) - Referential Integrity

There are cases where we wish to ensure that a value that appears in one relation for a given set of attributes also appears in a certain set of attributes in another relation (referential integrity). For example, the department listed for each course must be one that actually exists. More precisely, the dept_name value in a course record must appear in the dept_name attribute of some record of the department relation. Database modifications can cause violations of referential integrity. When a referential-integrity constraint is violated, the normal procedure is to reject the action that caused the violation.

What is the primary goal of a DBMS?

To provide a way to store and retrieve database information that is both convenient and efficient. A major purpose of a database system is to provide users with an abstract view of the data. That is, the system hides certain details of how the data are stored or maintained.

Integrity constraints that Database systems implement (4) - Authorization

We may want to differentiate among the user as far as the type of access they are permitted on various data values in the database. These differentiations are expressed in terms of authorization. The most common being: - read authorization - which allows reading, but not modification, of data; - insert authorization - which allows insertion of new data, but not modification of existing data; - update authorization - which allows modification, but not deletion, of data; - delete authorization - which allows deletion of data. We may assign the users all, none, or a combination of these types of authorization.

data model

a collection of conceptual tools for describing data, data relationships, data semantics and consistency constraints A data model provides a way to describe the design of a database at the physical, logical and view levels.

query

a statement requesting the retrieval of information.

logical schema

by far the most important schema (among the physical schema, logical schema and several schemas at view level often called subschemas) the logical schema describes the database design at the logical level. the physical schema ... at the physical level a database may also have several schemas at the view level, sometimes called subschemas, that describe different views of the database.

Database

collection of interrelated data that contains information relevant to an enterprise

The data values stored in the database must satisfy certain

consistency constraints. For example, suppose the university requires that the account balance of a department must never be negative.

metadata

data about data, metadata is the output of a DDL, and can be found in the data dictionary. The schema of a table is an example of metadata

We specify the storage structure and access methods used by the database system by a set of statements in a special type of DDL called a

data storage and definition language. These statements define the implementation details of the database schemas, which are usually hidden by users.

DDL

data-definition language is used to specify the data-schema. We specify a database schema by a set of definitions expressed by a special language called a data-definition language. The DDL is also used to specify additional properties of the data. In practice, DDL and DML are not two separate languages: instead they simply form parts of a single database language, such as the widely used SQL language.

DML

data-manipulation language is used to express database queries and updates. It is language that enables users to access or manipulate data as organized by the appropriate data model. In practice, DDL and DML are not two separate languages: instead they simply form parts of a single database language, such as the widely used SQL language.

file-processing system

operating system files, that have a(n increasing) number of application programs that manipulate the files in order to allow users to manipulate the information. (e.g. a university organisation that among other data keeps information about all instructors, students, departments and course offerings) A file-processing system is usually supported by a conventional operating system that stores permanent records in various files, and needs different application programs to extract from, and add records to, the appropriate files. Such systems were usually the ones organisations used to store their information before DBMSs were introduced.

Procedural DMLs

require a user to specify what data areneeded and how to get those data.

query language

the portion of a DML that involves information retrieval