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.