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