Parcial 1: Chapters 1 and 2

Who or what is responsible for mapping between conceptual, internal and external schemas?

The DBMS

What is "Paradigm shift"?

to think of the data first and the application second.

What does the conceptual/internal mapping do?

transforms requests and results between the conceptual and internal levels

What is Client-server Architecture?

where the backend is the server and the frontend are the clients

How many conceptual and internal schemas are there per database?

1

14 DBMS Advantages

1. Balance conflicting requirements 2.Improved data accessibility and responsiveness 3. Increased productivity 4. Improved maintenance through data independence 5. Increased concurrency 6. Improved backup and recovery services 7. Control of data redundancy 8. Data consistency 9. More Information from the same amount of data 10. Sharing of data 11. Improved data integrity 12. Improved security 13. Enforcement of standards 14. Economy of scale

7 DBMS disadvantages

1. Complexity 2. Size 3. Cost of DBMS 4. Additional hardware costs 5. Cost of conversion 6. Performance 7. Higher impact of failure

5 roles in Database

1. Data Administrator (DA) 2. Database Administrator (DBA) 3. Database designer 4. Programmers (Application developers) 5. Final Users/end users (naive and sofisticated)

9 File Systems Limitations

1. Duplicated data (Redundancy) 2. Inconsistency 3. Lack of concurrency control, no multiuser 4. Lack of security 5. No integrity rules 6. Data dependency 7. File incompatibility (cobol, c pascal) 8. Lack of transactions 9. No automatic recovery in case of failures

What are the 4 stages in DBMS History and their models?

1. First Generation (Hierarchical and Network model) 2. Second Generation (Relational Model) 3. Third Generation (Object-Relational and Object Oriented) 4. Emerging Technologies in DB

What are the 2 types of database designers?

1. Logical database designer 2. Physical database designer

6 DBMSs examples

1. MySQL 2. PostgreSQL 3. Microsoft SQL Server 4. Oracle 5. Sybase 6. IBM DB2

What are the 2 types of End-users?

1. Naïve users: are typically unaware of the DBMS. For example, the checkout assistant at the local supermarket uses a bar code reader to find out the price of the item. 2. Sophisticated users. is familiar with the structure of the database and the facilities offered by the DBMS. Sophisticated end-users may use a high-level query language such as SQL to perform the required operations.

What are the 2 types of DML?

1. Procedural: specify how the output of a DML statement is to be obtained 2. Non-procedural: describe only what output is to be obtained.

What 3 benefits do "Views" provide?

1. Security :Views can be set up to exclude data that some users should not see. 2. Appearance customization of the database: the Contracts Department may wish to call the monthly rent field (rent) by the more obvious name, Monthly Rent. 3. Consistency: unchanging picture of the structure of the database, even if the underlying database is changed

5 examples of database applications

1. Supermarket (POS) 2. E-commerce 3. Travel reservation 4. Video rental 5. University Applications

2 factors to which all limitations of file-based approach can be attributed to?

1. The definition of the data is embedded in the application programs, rather than being stored separately and independently 2. There is no control over the access and manipulation of data beyond that imposed by the application programs

What is file system (file-based system)?

A collection of application programs that perform services for the end-users such as the production of reports. Each program defines and manages its own data.

What is Non-procedural DML?

A language that allows the user to state what data is needed rather than how it is to be retrieved. Also called declarative-languages.

What is Procedural DML?

A language that allows the user to tell the system what data is needed and exactly how to retrieve the data

What is the benefit of CONCURRENCY CONTROL?

Allows shared access of the database

Why does the database provide program-data independence?

Because it has a self-describing nature

What is the conceptual level?

Conceptual level: The community view of the database. This level describes what data is stored in the database and the relationships among the data. It is concerned with: all entities, their attributes, and their relationships; the constraints on the data; semantic information about the data; security and integrity information.

What is the DA?

Data Administrator. Is the responsible for the management of the data resource including database planning, development and maintenance of standards, policies and procedures, and conceptual/logical database design

What are the 2 parts of a data sublanguage?

Data Definition Language (DDL) and Data Manipulation Language (DML)

What is a DDL?

Data Definition language. allows the DBA or user to describe and name the entities, attributes, and relationships required for the application, together with any associated integrity and security constraints. The DDL is used to define a schema or to modify an existing one. It cannot be used to manipulate data.

What is DML and what is it for?

Data Manipulation Language. Allows users to insert, update, delete, and retrieve data from the database.

What is DML?

Data Manipulation language. used to both read and update the database

What is Database independence?

Data independence means that upper levels are unaffected by changes to lower levels. There are two kinds of data independence: logical and physical.

What is data consistency and how does it relate to control of data redundancy?

Data redundancy is having stored an item more than once. If a data item is stored only once in the database, any update to its value has to be performed only once and the new value is available immediately to all users, this provides data consistency

DBMS

Database Management system. Computer software application that interacts with the user, other applications, and the database itself to capture and analyze data

DBMS allows:

Definition, creation, querying, update, and administration of databases

What is "System catalog" or "Data dictionary" or "Metadata"?

Description of the data

What do Object-based data models include?

Entity-Relationship, semantic, functional, and object-oriented models

Which has better performance, file-based system or DBMS?

File-based system

What are "Views"?

It is a mechanism that allows each user to have his or her own view of the database. For example, we could set up a view that allows the Contracts Department to see only the data that they want to see for rental properties.

What is Database abstraction?

It means that the DEFINITION of data is separated into INTERNAL and EXTERNAL. The users only see the external definition and do not know how the object works. The internal definition can be changed without affecting the external definition.

Is a database portable across different DBMSs?

NO

If new data structures are added to a database or existing ones are modified will the application programs be affected?

No because of data abstraction

What is a Database?

Organized collection of data. A shared collection of logically related data, and a description of this data, designed to meet the information needs of an organization. Also known as a self-describing collection of integrated records.

What is Database application?

Program that interacts with the database

What is physical data independence?

Refers to the immunity of the conceptual schema to changes in the internal schema. Using different file organizations or storage structures, using different storage devices, modifying indexes, or hashing algorithms, should be possible without having to change the conceptual or external schemas.

What MODEL do the most popular database systems support since 1980s?

Relational model (RM)

What is Database Management System (DBMS)?

Software that manages and controls access to database

What is the most common query language?

Structured Query Language (SQL)

What is database instance?

The data in the database at any particular point in time

What is query language?

The part of a DML that involves data retrieval

What is the internal level?

The physical representation of the database on the computer. This level describes how the data is stored in the database. It is concerned with: storage space allocation for data and indexes; record descriptions for storage (with stored sizes for data items); record placement; data compression and data encryption techniques.

What are application developers?

The responsible of implementing the application programs that provide functionality for the end-users

What is schema?

The structure of the database

What is the external level?

The users' view of the database.

How do users interact with the Database?

Through the application programms

Can a single application work with more than one DBMS?

YES. by using standards as SQL, ODBC, JDBC

What is the result of the compilation of the DDL statements?

a set of tables stored in special files collectively called the system catalog

What is a data model?

collection of concepts that can be used to describe a set of data, the operations to manipulate the data, and a set of integrity constraints for the data.

What are the functions and services of multi-user DBMS?

data storage, retrieval, and update; a user-accessible catalog; transaction support; concurrency control and recovery services; authorization services; support for data communication; integrity services; services to promote data independence; utility services

What is metadata and what does it contain?

data that describes objects in the database and makes it easier for those objects to be accessed or manipulated. The metadata contains definitions of records, data items, and other objects that are of interest to users or are required by the DBMS.

What is ANSI-SPARC Architecture? what are its levels?

database architecture that uses three levels of abstraction: external, conceptual, and internal.Its the data abstraction model. The way users perceive the data is called the external level. The way the DBMS and the operating system perceive the data is the internal level, where the data is actually stored. The conceptual level provides both the mapping and the desired independence between the external and internal levels

What is a Transaction Processing (TP) Monitor program?

is a program that controls data transfer between clients and servers in order to provide a consistent environment, particularly for online transaction processing (OLTP).

What is the DBA?

is responsible for the physical realization of the database, including physical database design and implementation, security and integrity control, maintenance of the operational system, and ensuring satisfactory performance of the applications for users.

What does the external/conceptual mapping do?

mapping transforms requests and results between the external and conceptual levels.

What are the three categories of the data models?

object-based data models, record-based data models, and physical data models. The first two are used to describe data at the conceptual and external levels; the latter is used to describe data at the internal level.

What is logical data independence?

refers to the immunity of the external schemas to changes in the conceptual schema. The addition or removal of new entities, attributes, or relationships, should be possible without having to change existing external schemas or having to rewrite application programs. Clearly, the users for whom the changes have been made need to be aware of them, but what is important is that other users should not be.

What are procedures?

the instructions and rules that govern the design and use of the data-base

What is conceptual modeling?

the process of constructing a detailed architecture for a database that is independent of implementation details, such as the target DBMS, application programs, programming languages, or any other physical considerations

What do Record-based data models include?

the relational, network, and hierarchical models.