Module 1: Introduction to Databases and Transactions Flashcards
(136 cards)
1
Q
Is shared collection of logically related data (and a description of this data), designed to
meet the information needs of an organization
A
Database
2
Q
Database is composed of:
A
Entities and Relations
3
Q
Is a collection of application programs that perform services for the end users
such as the production of reports
A
File-based System
4
Q
5 Limitations of the File-based Approach
A
- Separation and Isolation of Data
- Duplication of Data
- Data Dependence
- Incompatible File Formats
- Fixed Queries/Proliferation of Application Programs
5
Q
The Limitations of the File-based Approach can be attributed to two factors:
A
- Definition of data embedded in the application programs, rather than being stored separately
and independently - There is no control over the access and manipulation of data beyond that imposed by the
application programs
6
Q
- Is software designed to assist in maintaining and
utilizing large collections of data - A general-purpose software system that facilitates the processes of defining,
constructing, & manipulating databases for various applications
A
Database Management System or DBMS
7
Q
A database involves specifying the data types, structures, and constraints for the
data to be stored in the database
A
Defining
8
Q
The database is the process of storing data itself on some storage medium that
is controlled by the DBMS
A
Constructing
9
Q
A database includes such functions as querying the database to retrieve specific
data, updating the database to reflect changes in the miniworld, & generating reports from
the data
A
Manipulating
10
Q
Is a software that enables user to define, create and maintain that database and which
provides controlled access to the database
A
DBMS
11
Q
DBMS provides the following facilities:
A
- Allows users to define the database through DDL (Data Definition Language)
- Allows user to insert, update, delete and retrieve data through DML (Data Manipulation
Language) - Provides controlled access to the database.
12
Q
Provides controlled access to the database
A
DBMS
13
Q
DBMS may provide:
A
- A security system, which prevents unauthorized users from accessing the database
- An integrity system, which maintains the consistency of stored data
- A concurrency control system, which allows shared access of the database
- A recovery control system, which restores the database to a previous consistent state following a hardware or software failure
14
Q
What are we going to cover?
- How do we represent the world with a database?
A
Database Design and Application Development
15
Q
What are we going to cover?
- How can we answer questions about enterprising this data?
A
Data Analysis
16
Q
What are we going to cover?
- How does a DBMS allow many users to access data concurrently, and
how does it protect against failures?
A
Concurrency and Robustness
17
Q
What are we going to cover?
- How does the database cope with large amounts of data?
A
Efficiency and Scalability
18
Q
Who created the first general purpose DBMS Integrated Data Store, where and when?
A
Charles Bachman; at GE; early 1960s
19
Q
It creates the basis for the network model (standardized by CODASYL)
A
DBMS Integrated Data Store
20
Q
Develops the Information Management System (IMS)
A
IBM
21
Q
_____ uses and alternate model, called the _____.
A
Information Management System (IMS); Hierarchical Data Model
22
Q
Is created around IMS
A
SABRE
23
Q
Who from IBM creates the relational data model and when?
A
Edgar Codd; 1970
24
Q
In _____, Codd receives the _____
for his contributions to database theory.
A
1981; Turing Award
25
In _____, _____ developed by IBM, becomes the standard query language for databases
1980s; SQL
26
SQL is
| standardized by
ISO
27
In _____ and _____, IBM, Oracle, Informix and others develop powerful _____.
1980s and 1990s; DBMS
28
In the _____, DBMS are showing how useful they can be.
Internet Age
29
4 Forms of Data
1. Text
2. Image
3. Audio
4. Video
30
4 Data Sources
1. PC
2. Web
3. Phone
4. Bar Code
31
- Are data that describe the properties of data (but do not include that data)
- Allow database designers & users to understand what data exist, what the data mean
and what the fine distinctions are between seemingly similar data items
Metadata
32
12 Advantages of Why we use DBMS
1. Control of data redundancy
2. Data consistency
3. Sharing of data
4. Data integrity and security
5. Enforcement of Standards
6. Economy of scale
7. Balanced conflicting requirements
8. Data independence
9. Efficient data access
10. Data administration
11. Concurrent access and crash recovery
12. Reduced application development time
33
7 Disadvantages of Why we use DBMS
1. Complexity
2. Size
3. Cost of DBMS
4. Additional hardware costs
5. Cost of conversion
6. Performance
7. Higher impact of a failure
34
- Is responsible for the management of the data resource including database planning, development
and maintenance of the standards, policies and procedures and logical database design
- Consults with and advises senior managers, ensuring that the direction of database development will
ultimately support corporate objectives
Data Administrators
35
Is responsible for the physical realization of the database system, including physical database design
and implementation, security and integrity control, maintenance of the operational system and
ensuring satisfactory performance for the applications and users.
Database Administrators
36
Two Types of Designers
1. Logical Database Designer
| 2. Physical Database Designer
37
- Is concerned with identifying the data, the relationships between data and the constraints on
the data that is to be stored in the database.
- Must have a thorough and complete understanding of the organization’s data andof the
business rules.
Logical Database Designer
38
Takes the logical data model and decides how it is to be physically realized
Physical Database Designer
39
Physical database designer involves:
- Mapping the logical data model into a set of tables and integrity constraints;
- Selecting specific storage structures and access methods for the data to achieve
good performance for the database activities;
- Designing any security measure required on the data.
40
- Responsible in the implementation of the application programs that provide the required
functionality for the end users.
- Work from the specification produced by systems analyst.
Application Programmers
41
- Are the clients for the database – the database has been designed and implemented, and is being maintained to server their information needs.
End Users
42
End users can be classified according to the way they use the system:
1. Naive Users
| 2. Sophisticated Users
43
- Are typically unaware of the DBMS.
- They invoke database operations by entering simple commands or choosing options
form a menu.
Naive Users
44
- Familiar with the structure of the database and the facilities offered by the DBMS.
- May use a high-level query language such as SQL to perform the required operations.
Sophisticated Users
45
Is a collection of high level description constructs that hide many low-level
storage details
Data Model
46
Most current DBMS use the _____.
Relational Data Model
47
The central data description in this model is the _____ (_____ - same as in set theory mathematics).
Relation; A Set of Tuples
48
For convenience, we refer to each tuple as a _____.
Row
49
Is a description of the data in terms of the data model
Schema
50
In the relational model the
| schema looks like:
RelationName(field1 : type1, ..., fieldn : typen)
51
- Is the abstract graphical representation of an enterprise
| - Usually the beginning of database development
Enterprise Data Model
52
Is language-independent. It can be drawn using a pen and a paper.
Data Modeling
53
Purpose of Enterprise Data Model
It creates an overall picture or explanation of organizational data NOT the design of the database
54
4 Data Modeling Tools
1. Entity Relationship Diagram
2. Unified Modeling Language
3. Data Flow Diagram
4. Data Dictionary
55
6 Components of Information System Architecture (ISA)
1. Data
2. Processes
3. Network
4. People
5. Events and Point in Time
6. Reasons
56
Component of Information System Architecture that may be represented as ERD
Data
57
Component of Information System Architecture that manipulate data; represented by DFD, object-models with methods or other notations
Processes
58
Component of Information System Architecture that transports data around & between organizations and business partners
Network
59
Component of Information System Architecture that the source & receiver of data / information and performs processes
People
60
Component of Information System Architecture that shows when processes are performed (e.g State-Transition
Diagrams)
Events and Point in Time
61
Component of Information System Architecture that shows events & rules governing the processing of data. Some diagramming
tools exist for rules (e.g decision tables)
Reasons
62
It is a formal methodology in creating and maintaining information system
Information Engineering
63
2 Classifications of Information Engineering
1. Bottom-up Planning
| 2. Top-down Planning
64
Projects are requested by IS users who need certain info to do their job
Bottom-up
65
Generic IS planning methodology that attempts to gain a broad understanding of the IS needs of
the entire org
Top-down
66
4 Steps of Top-down
1. Planning
2. Analysis
3. Design
4. Implementation
67
Results in an IS architecture, including an enterprise model (INFO. SYSTEM PLANNING)
Planning
68
Is the road map setting the direction of systems development
Information System Planning (ISP)
69
The Goal of Information Systems Planning
To align IT with the business strategies of the organization
70
Goal: Business Alignment Steps:
1. Identify strategic planning factors
2. Identify corporate planning objects
3. Developing an enterprise model
71
Goal: Business Alignment Steps:
- Goals
- Critical Success Factors
- Problem Areas
Identify Strategic Planning Factors
72
Goal: Business Alignment Steps:
- Identifying corporate planning objects is also called as enterprise analysis.
- It requires the analysis of organization-wide information requirements in terms of organizational units, functions and processes and information requirements
Identify Corporate Planning Objects
73
Are various departments, branches or program areas in the
| organization
Organizational Units
74
Geographic locations of business units where business
| operations occur
Organizational Locations
75
Related groups of business processes that support the mission of the organization
Business Functions
76
Major categories of data about people, places, events & things managed by the organization. Key attributes are also discussed in this section
Entity Types
77
Application software & supporting procedures for handling sets of
data
Information System
78
Goal: Business Alignment Steps:
Contains:
- Functional decomposition
- Entity Relationship Diagram
- Planning matrix
Developing an Enterprise Model
79
Process of breaking down the functions of an organization into progressively greater level of details
Functional Decomposition
80
Used to describe Enterprise model by depicting the entity types, relationships between entities & how business operates. (BUSINESS RULES - govern the validity of data)
Entity Relationship Diagram
81
Shows Inter-relationships between planning objects
Planning Matrix
82
8 Alternative Systems Building Approaches
1. Traditional Systems Development Lifecycle
2. Rapid Application Development (RAD)
3. Prototyping
4. Computer-Aided Software Engineering Tools
5. Integrated-CASE (I-CASE) Tools
6. Application Software Packages
7. Outsourcing
8. End User Development
83
– Also known as the SDLC
– Methodological, Highly Structured
– Includes many checks & balance
Traditional Systems Development Lifecycle
84
– Iterative process of rapidly repeating analysis, design & implementation steps until desired system is acquired
Rapid Application Development (RAD)
85
RAD works best when:
- Most of the database needed already exists
| - Systems that primary retrieve data
86
– Process of building experimental system quickly and inexpensively for demonstration &
evaluation
– Iterative
– May be used as an alternative to the systems development life cycle
Prototyping
87
Software that provides automated support for some portion of the systems development process
Computer-Aided Software Engineering Tools
88
3 Features of Computer-Aided Software Engineering Tools
1. Ability to draw data models using entity relationships & other notations (ensures consistency
across diagrams)
2. Ability to generate codes (database definition commands to be given to DBMS)
3. Information Repository
89
- Provides support across whole life-cycle but such tools are rarely used since they tend to be strong in supporting certain phases but weak in supporting other phases of the system
- Created as case tool that has the ability to share the metadata that are developed during each stage of the process
Integrated-CASE (I-CASE) Tools
90
Set of prewritten, precoded application software programs commercially available for sale or lease.
Application Software Packages
91
Application Software Packages requires _____.
customization
92
Contracting computer center operations, telecommunications networks, or applications development to external vendors
Outsourcing
93
- Development of information systems by end users with little or no formal assistance from technical specialists
- Allows users to specify their own business needs
End User Development
94
Describes the data stored in the database
Conceptual Schema
95
Creating a good conceptual schema is not a simple task, and it is called _____.
Conceptual Database Design
96
Conceptual Database Design involves:
- Determining the different relations needed
- The number of fields per relation
- The type of each field
97
Specifies how the relations are actually stored in secondary storage devices
Physical Schema
98
Physical Schema also specifies _____ used to speed up the access to the relations
Auxiliary Data Structures (Indexes)
99
Decisions about the physical schema depend upon:
- Understanding how the data is going to be accessed
| - The facilities provided by the DBMS
100
2 Examples of Physical Schema
1. Store all relations in unsorted files of records
| 2. Create indexes in the first column of every relation, and in the sal column of faculty
101
Is a refinement of the conceptual schema
External Schema
102
Every database has _____ conceptual and _____ physical schema, but it can have _____ external schemas
one; one; many
103
Each external schema:
Users
104
External Schema is tailored to a particular group of users consists of _____ of the conceptual schema
one or more views and relations
105
Is conceptually a relation, but its records are not stored in the database; instead, they are computed from other relations.
View
106
Means that programs are isolated from changes in the way the data is structured and stored.
Data Independence
107
Users are shielded from the logical structure of the data (e.g. a relation is split into 2 or more)
Logical Data Independence
108
As long as the conceptual schema remains the same, we can change the storage details of the application without affecting the user
Physical Data Independence
109
Example of External Schema
CourseInfo(cid: string, fname: string, enrollment: integer)
110
Data for an IS may reside in _____ to balance various organizational and technical factors
multiple locations or tiers of computers
111
Three-tier Database Architecture:
- Desktop or laptop computer which concentrates on managing the user-system interface and localized data
Client-tier
112
Three-tier Database Architecture:
- Performs calculations and provides access to data shared within the workgroup
Department (Workgroup) Minicomputer Server Tier
113
Three-tier Database Architecture:
- Performs sophisticated calculations and manages the merging of data from multiple sources across the organization
Enterprise Server (Minicomputer or Mainframe) Tier
114
_____ in which database SW on a server (called _____) performs database commands sent to it from client workstations and application programs on each client concentrate on user interface functions
LAN-based Environment; Database Server or Database Engine
115
5 Advantages of LAN-based Environment
1. Allows for simultaneous processing on multiple processors for the same application.
2. Possible to take advantage of the best data processing features of each computer platform.
3. Can mix client tech (kiosks, networked PCs) while sharing common data.
4. Processing can be performed close to source of processed data thus improving response times & reducing network traffic.
5. Allows open-system standard.
116
Why do we need a DBMS?
To answer queries
117
A _____ provides a specialized language, called _____ to ask questions to the DBMS
DBMS; Query Language
118
When several users access (and possibly modify) a database concurrently, the DBMS must order their request carefully to avoid _____
conflicts
119
DBMS should also protect users from _____.
system failures
120
DBMS should deal with crashes in the _____ of a _____.
middle; transaction
121
Is a conceptually indivisible group of operations that a user wants to perform (for example, getting transferring money from one account to another)
Transaction
122
An important task of a DBMS
Is to schedule concurrent accesses in a way that every user can ignore the fact that others are accessing the data at the same time
123
A DBMS allows user to think that their programs are executed in _____
isolation
124
Has to be implemented to allow transactions to be interleaved
Locking
125
Allow several transactions to hold (and access) an object at the same time
Shared Locks
126
Only one transaction can hold the object
Exclusive Locks
127
The DBMS maintains a _____ of everything it writes
log
128
The log is created _____ the operation is done: _____
before; write-ahead log
129
Example of a Conceptual Schema
Students(sid:string, name: string, login: string, age: integer, gpa: integer,gpa: real)
Faculty(fid: string, fname:string, sal: real)
Courses(cid: string, cname: string, credits: integer) Teaches(fid: string, cid: string)
Enrolled(sid: string, cid: string, grade: string)
130
Relational Data Model Example (A Relation of Students)
Students(sid : string, name : string, age : integer, gpa : real)
131
File-based Sample for Sales Files
Property_for_Rent (Property Number, Street, Area, City, Post Code, Property Type, Number of Rooms, Monthly Rent, Owner Number)
Owner (Owner Number, FirstName, LastName, Address, Telephone Number)
Renter (Renter Number, FirstName, LastName, Address, Telephone Number, Preferred Type, Maximum Rent)
132
File-based Sample for Contracts Files
Lease (Lease Number, Property Number, Renter Number, Monthly Rent, Payment Method, Deposit, Paid, Rent StartDate, Rent Finish Date, Duration)
Property_for_Rent (Property Number, Street, Area, City, Post Code, Property Type, Number of Rooms, Monthly Rent, Owner Number)
Renter (Renter Number, FirstName, LastName, Address, Telephone Number, Preferred Type,
Maximum Rent)
133
File-based Sample for The Personnel Department also Stores Staff Details, namely:
Staff(Staff Number, FirstName, LastName, Address, Telephone Number, Position, Sex, Date of Birth, Salary, National Insurance Number, Branch Number)
134
Can present a consistent, unchanging picture of the structure of the database, even if the underlying database is changed (for example, fields added or removed, relationships changed,
files split, restructured or renamed).
View
135
If fields are added or removed from a file, and these fields
| are not required by the view, the view is _____.
not affected by this change
136
A view helps provide
| the _____.
Program-data Independence
