Database Systems

External schema

(view level) Describes part of the database that a particular user group is interested in.

Referential integrity constraint

- Specified between two relations and is used to maintain the consistency among the tuples in the two relations. - It states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation.

What are the various reasons that lead to the occurrence of NULL values in relations.

- The value of an attribute for a particular tuple is not known or not defined. - It is known but does not apply to the tuple specifically. - Values can be not available presently. - It is possible to devise different codes for different meanings of NULL values.

SuperKey

A combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

Client/server architecture

A computer architecture in which multiple computers (clients) are connected to other computers (servers) that store and distribute large amounts of shared data.

Transaction-processing application.

A management information system designed to handle large volumes of routine, recurring transactions.

Why do we designate one of the candidate keys of a relation to be the primary key?

A particular candidate key is called as a primary key over the other candidate keys. This primary key is generally a single attribute or a smaller number of attributes. It becomes easier to deal with a database when we can have a single distinct key for a particular relation instead of having more than one key. (When there is more than one key in a relation schema of a database, all keys are referred to as candidate keys.)

End user

A person or system that directly interacts with the solution.

Foreign key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

Nested queries

A query (SELECT statement) inside a query that can appear as part of a condition in the WHERE or HAVING clauses as well as in the FROM clause.

Why are tuples in a relation not ordered?

A relation is defined as a set of tuples. Mathematically, elements of a set have no order among them; hence, tuples in a relation do not have any particular order.

Database

A shared, integrated computer structure that houses a collection of related data. Contains two types of data: end-user data (raw facts) and metadata

Key

A single or combination of multiple fields that retrieve data rows from the table according to the requirement. -Defined in tables to access or sequence the stored data quickly and smoothly and are used to create links between different tables.

Relation State

A state of relations between a set of n-tuples.

Aggregate functions and grouping

Aggregate functions -Used to summarize information from multiple tuples into a single-tuple summary Grouping - Create subgroups of tuples before summarizing

Database System

An organization of components that defines and regulates the collection, storage, management, and use of data in a database environment.

What are the different types of database end users?

Casual, Naïve/parametric, Sophisticated, Standalone

Update operation

Changes the value of an object's attributes in the tuple of some relation, which could result in the change in the objects state. It is necessary to specify a condition on the attributes of the relation to select the tuple to be modified

Three-tier architecture

Client server, Application server, Database server

Data Model

Collection of concepts that describe the structure of a database and provides means to achieve data abstraction

DDL

Data Definition Language -Allows a database administrator to define the database structure, schema, and subschema.

DML

Data Manipulation Language - Allows retrieval, insertion, deletion, modification

Meta-data

Data associated with an object, supplementing the object's file system data.

Transaction

Deals with operations like insert, delete and update (modify). After transactions, it is mandatory for the database to be in a consistent state and this state should obey all the laws of constraints.

Internal schema

Describes physical storage structure of the database

Conceptual schema

Describes the structure of the whole database for a community of users

How are NULLs treated in comparison operators in SQL?

Each individual NULL value considered to be different from every other NULL value

Sophisticated end users

Engineers, scientists, etc who are experts at using the DB, and develop applications on top of a databases.

Query language

High-level DML used in a standalone interactive manner

Attribute

In a relation table, each column is called by its column name or attribute name.

What is the additional functionality incorporated in n-tier architecture (n> 3)?

It is able to divide the layers between the user and the stored data further into finer components

Degree of a relation

It is the number of attributes in a relation schema.

Is it harder to achieve logical data independence and physical data independence?

Its harder to achieve logical data independence as it maps to multiple user interfaces therefore changes in logical level may affect sometime external

Database Administrator (DBA)

Job title focused on directing, performing, or overseeing activities associated with a database or set of databases.

Standalone end users

Maintain personal databases by using ready-made program packages that provide easy-to-use interfaces.

DBMS - (Database Management System)

Manages the data, provides security, a means of accessing data from in the database.

Casual end users

People who use a database query language to access a database, but do not develop applications.

Database utility

Represents the interface between the ABAP Dictionary and the relational database that is the foundation of the SAP System.

Canned transaction

Standard types of queries and updates that have been carefully programmed and tested. Mostly used by Naive users to perform actions on the database related to their needs.

SDL

Storage Definition Language - Specifies the internal schema

Data Sublanguage

The DML in a general purpose programming language

Domain

The range of values of an attribute. It has a logical definition and a data type or a format defined for it.

Data independence

The separation of data descriptions from the application programs that use the data.

How are NULLs treated if they exist in grouping attributes?

They are separated into a group created for all tuples with a NULL value in grouping attribute.

Persistent object

a specialized object that has the property of continuous state, which means it is available at all times

tuples

rows of attribute values

What are the four main types of database actions?

selection,updating,deleting,inserting.

What is the difference between a database schema and a database state?

• When we define a new database, we specify its database schema only to the DBMS. • We get the initial state of the database when the database is first loaded with the initial data. • Every time an update operation is applied to the database, we get another database state. • The DBMS is partly responsible for ensuring that every state of the database satisfies the structure and constraints specified in the schema.

What are the six clauses of an SQL retrieval query?

1. SELECT - column name 2. FROM - table or views 3. *WHERE - conditions or predicates are met 4. *GROUP BY - subsets of rows 5. *HAVING - a common condition as a group 6. *ORDER BY - a sorting method *=Optional

Why are duplicate tuples not allowed in a relation?

Because it violates the specifications of the relational integrity constraints, particularly the key constraint which states that no two tuples can have the same values for their attributes at any relation state of a database.

Database Catalog

Consists of metadata in which definitions of database objects are stored -such as base tables, views (virtual tables), synonyms, value ranges, indexes, users, and user groups.

Naïve/parametric end users

Constantly querying and updating the database, using standard types of queries and updates that have been carefully programmed and tested.

Database state

Current state of the database including all of its data. *Think of of it as a snapshot.

What are the main characteristics of the database approach and how it differs from traditional file systems.

DATABASE: • Self-describing nature of a database system • Insulation between programs and data, and data abstraction • Support of multiple views of the data • Sharing of data and multi-user transaction processing FILE SYSTEM: • Approach to the file system, each user is defined as necessary to implement the file, to perform a specific application

Database schema

Description of a database

Entity integrity constraint

No primary key value can be NULL because the primary key value is used to identify individual tuples in a relation. Having NULL values for the primary key implies that we cannot identify some tuples.

What are the characteristics of relations that make them different from ordinary tables and files?

Ordering of tuples in a Relation -The tuples are not considered to be ordered, even though they appear to be in the tabular form. Ordering of attributes in a relation schema R and of values within each tuples - We consider the attributes in R(A1, A2, .., An) and the values in t=<v1, v2, .., vn> to be ordered. Values in a tuple - All values are considered to be indivisible(atomic). A special null value is used to represent values that are unknown or inapplicable to certain tuples.

Joined tables and outer joins

Permits users to specify a table resulting from a join operation in the FROM clause of a query Inner join - Default type of join in a joined table - Tuple is included in the result only if a matching tuple exists in the other relation Outer join - Every tuple in right/left table must appear in result

Deductive database system

Provides capabilities for defining deduction rules; inferencing new information from the stored database facts

Data

Raw facts, or facts that have not been processed to reveal their meaning to the end user.

Relational Database State

Represented as DB = {r1, r2, .., rn} where r1, r2, .., rn are different relation states in the database. Each ri satisfies the integrity constraints specified by the IC.

Relational database schema

Represented as S = {R1, R2, .., Rn} where R1, R2, .., Rn are the relation schemas. It also consists of a set of Integrity Constraints(IC).

Relation schema

Represented by R(A1, A2, .., An) where R is called the name of the relation and (A1, A2, .., An) are the attributes of this schema,

User View

Subset of the database that contains virtual data derived from the database files but is not explicitly stored.

How are NULLs treated when aggregate functions are applied in an SQL query?

The NULL values are discarded when aggregate functions are applied to a particular column Aggregate function example: FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value

What is the difference between logical data independence and physical data independence?

The logical data independence is the ability to modify a logical schema without making external view or application program change. The physical data independence is the ability to modify a physical schema without making external view or application program change.

Program-data Independence

The separation of the logical (conceptually organizes and understands data) and physical views (how and where the data are physically arranged and stored on a disk, tape, or other media) of data

What is the difference between the two-tier and three-tier client/server architectures?

Two-tier (client/server) : Client(has task request) ->server (performs task), Server has the data. Three-tier/multi tier architecture (client/server/database) : Client request ->server (sends request) -> database (sends back the data required to the server which sends it back to the client)

VDL

View Definition Language - Specifies user views/mappings to conceptual schema

Host languages

Whenever DML commands, whether high or low level, are embedded in a general-purpose programming language.

Discuss the differences between database systems and information retrieval systems.

• Database systems work with relational databases(well structured data) - use relational tables with well defined values for each row and column. • Information retrieval system works on unstructured data(raw textfiles/documents). IR system uses an Inverted index which is the index of {term,docIDs} entries. For each term there is a corresponding list of documents in which the term is present.

What are the capabilities of a Database Management System?

• Provides a way to organize data as records, tables, or objects • Accepts data input • Provides query languages - for searching, sorting, reporting...etc. • Provides multi-user access to data - with security features that prevent some users from viewing/changing info • Provides data integrity features - prevents more than one user from accessing and changing the same info • Provides a data dictionary (metadata) - that describes the structure of the database, related files, and record information