DataBases Chapter 1

ER Entity-Relationships six entities the rectangles

Branch, staff, property for rent, client, private owner, and lease

more DBMS advantages

Economy of scale, balance of conflicting requirements, improved data accessibility and responsiveness, increased productivity, maintenance through data independence, concurrency, backup and recovery service

Application programs

a computer program that interacts with the database by issuing an appropriate request to the DBMS. SQL statements

A File is a

collection of records which contain logically related data.

Additional hardware costs

disk storage for DBMS

Entity is a

distinct object a person, place, thing, concept, event, that is to be represented in the database.

Limitations of file-based systems

-Separation and isolation of data -Duplication of data -Data dependence -Incompatible file formats -Fixed queries/proliferation of application programs

Cost of DBMSs

100 - 1,000,000 with recurrent annual maintenance cost.

DataBase Systems

A collection of application programs that interact with the database along with the DBMS and the database itself

File Base 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.

DataBase Application

A program that interacts with the database at some point in its execution

DataBase

A shared collection of LOGICALLY RELATED data and its description, designed to meet the information needs of an organization. A repository of data used by many departments and users.

DataBase Management Systems (DBMS)

A software system that enables users to define, create, maintain, and control access to the database. Interacts with the user's application programs and the database.

Disadvantages of DBMSs

Complexity, size, cost of DBMSs, additional hardware costs, cost of conversion performance, greater impact of a failure.

Balance of conflicting requirements

DBA makes decisions and design of use of database as whole.

Size

DBMS is large software, many megabytes and substantial amounts of memory to run efficiently.

Increased concurrency

DBMS manage concurrent database access and ensure problems won't occur

Increased productivity

DBMS provides all low-level file handling routines. This allows users not to worry about low-level details consisting of tools to simplify development.

Improved maintenance through data independence

DBMS separates the data descriptions from applications making applications immune to changes in data descriptions

CODASYL Conference on data systems languages renamed to

DBTG Data base task group. allows database creation and data manipulation.

seven ER relationships the names adjacent to the lines

Has, offers, oversees, views, owns, leased by, and holds

IMS Information management system

IBM joined NAA to develop GUAM.

End-users can be classified to the way they user the system

Naive users are unaware of DBMS. Sophisticated users are familiar with DBMS and user SQL and may write application programs.

DBTG 3 languages

Schema DDL enables DBA to define schema, subschema DDL allows app program to define parts of db, and a DML to manipulate data.

DBTG 3 components

Schema-logical oranization of entire db seen by dba. Subschema-part of db seen by user and data management language to define the data.

Schema is

The structure of the database.

Incompatible file formats

The structures are dependent on application program language and files are embedded. Time consuming to write software to convert files to common format for processing

Views benefits

Views provide a level of security, mechanism to customize the appearance of the database, and present a consistent unchanging picture of the structure of the database.

SQL provides controlled access to the database by

a security system prevents unauthorized users, an integrity system maintains consistency of data, a concurrency control system allows shared access to database, a recovery control system restores after failure, and a user-accessible catalog which contains descriptions of data in base.

Economy of scale

all operational data into one database creating a set of applications for cost savings.

Query language

alleviates the problems with file-based systems where the user has to work with fixed set of queries.

Data Manipulation Language DML

allows users to insert, update, delete, and retrieve data from the database.

DDL Data definition language

allows users to specify the data types and structures and the constraints on the data to be stored in the database.

Enterprise resource planning ERP system

an application layer built on to of a DBMS that integrates all the business functions of an organization, like manufacturing, finance, marketing, shipping, invoicing, and hr.

Legacy system

an older and usually inferior system.

Hierarchical structure

an upside-down tree structure

Performance

apps may not run fast and need better performance

Relationship is an

association between entities.

six ER attributes one for each entity

branch no, staff no, property no, client no, owner now, and lease no.

More information from the same amount of data

by all access to info derives more info from same amount of data.

Data consistency

by eliminating or controlling redundancy we reduce the risk of inconsistencies occurring. Data stored only once and update only once and available to all users keeps consistency.

Fields represent

characteristics of real world objects

object-oriented DBMS OODBMS and object-relational DBMS ORDBMS

complexity of database applications.

logical database designer 2 stages The WHATS

conceptual database design which is independent of implementing details, apps, languages, any physical considerations. Logical targets data modes, relational, network, hierarchical, or object-oriented.

logical database designer The WHATS

concerned with identifying the data the entities and attributes, the relationships between data, and constraints on data to be stored in database.

Business rules

constraints describing the characteristics of the data viewed.

Advantages of DBMS

control data redundancy, data consistency, more info from same amount of data, sharing data, improved data integrity, security, enforced standards.

Cost of conversion

cost of training staff and keep systems running.

Metadata

data about data.

Complexity

data and database administrators and end-users must understand functionality. bad design decisionsl

4 distinct types of people who participate in the DBMS environment

data and database administrators, database designers, application developers, and end-users.

Physical database designer The HOWS

decides how the logical database design is to be physically realized by mapping into set of tables, selecting storage structure, security.

Limitations of file-based approach can be attributed to two factors

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

Control of data redundancy

file systems waste space by storing info in more than one file. Db eliminates redundancy by integrating files so multi copies of same data aren't stored. It controls redundancy.

Schema consists of

files/tables, fields/attributes, system catalog.

Enforcement of standards

format and exchange data between systems, naming conventions documentation, standards, update and access rules

5 major components in the DBMS environment are

hardware, software, data, procedures, and people.

Data abstraction is

internal definition of an object without affecting the users of the object, provided that the external definition remains the same.

Structured Query Language SQL

is now both the formal and de facto standard language for relational DBMSs.f

Duplication of data

is wasteful, it takes up storage, can be avoided by sharing data files, and leads to less data integrity

Separation and isolation of data is

isolated in separate files, it is more difficult to access data.

2 types of database designers

logical and physical.

Improved backup and recovery services

might include a nightly backup . DBMS provide processing that minimize failure.

IDS Integrated data store led to

network DBMS, data relationships of hierarchical structures.

Sharing of data

new applications can build on existing data and add only data that is not currently stored.

Fixed queries/proliferation of application programs

no provision for security or integrity, access was restricted to one user.

Database administrator DBA is

physical realization of database, security, integrity control, maintenance of operational system, and ensuring satisfactory performance of applications for users.

Data dependence

physical storage of data files and records defined in application code. Changes to existing structure are difficult to make. Very time consuming and subject to error known as program-data dependence.

Attribute is a

property that describes some aspect of the object that we wish to record.

Application developers

provide the required functionality for the end-users.

Improved data accessibility and responsiveness

provides ad hoc questions to get info immediately. SELECT* FROM PropertyForRent WHERE type = 'Flat' AND rent > 400;

Data administrator DA is

responsible for the management of the data resource, database planning, development, maintenance, and conceptual/logical database design.

Improved security

retrieval, insert, update, delete. get access to all data but not sensitive data.

GUAM Generalized update access method

smaller components come together as parts of larger components until final product is assembled.

A view is

some subset of the database. We can set up a view that allows a department to see only the data that they want to see.

Data warehouses

store data drawn from several data sources, maintained by different operating units of an organization.

Database is also defined as

system catalog or data dictionary or metadata--the "data about data." Provides program data independence.

Semantic data modeling is

the attempt to provide a data model that represents the "real world" more closely.

Client-server

the backend is the server, and the frontends are the clients.

Frontend

the part of the DBMS that interfaces with the user.

Backend

the part of the DBMS that manages and controls access to the database.

Database design

the structure of the database is determined during it.

Paradigm shift is

to think of the data first and the application second.

Improved data integrity

validity and consistency of stored data. Integrity constraints are consistency rules.

DBMS provides another facility known as a

view mechanism which allows each user to have his or her own view of the database.

Greater impact of a failure

vulnerability of system. Because DBMS, the failure of certain components can bring operations to a halt.

Logically related is when

we analyze the information needs of an organization, we attempt to identify entities, attributes, and relationships.