Databases Test #1

what is a database?

a shared collection of logically related data and descriptions of that data, designed to meet the needs of an organization

properties of a database

1. logically coherent and has some relevant meaning

2. designed, built, and populated with data for a specific purpose

3. represents some aspect of the real world

three main categories of models

1. user or conceptual models

2. logical models

3. physical models

user or conceptual models

how users perceive the world and/or the business

logical models

represent the logic of how a business operates, e.g. the relationship between entities and flow of data through the organization; based on user’s model

physical models

represent how the database is actually implemented on a computer system; based on logical model

database management system (DBMS)

a software system that enables users to define, create, and maintain the database and provides controlled access to the database

database system (DBS)

contains:

• the database +

• the DBMS +

• application programs (what users interact with)

file system

a collection of individual files accessed by applications programs

limitations of a file system

• separated and isolated data - makes coordinating, assimilating, and representing data difficult

• data duplication - wastes space and can lead to data integrity (inconsistency) problems

• application program dependencies - changes to a single file can require changes to numerous application programs

• incompatible files (for example number encoding differences)

• lack of data sharing - difficult to control access to files, especially to individual portions of files

advantages of a DBMS

• data consistency and integrity - by controlling access and minimizing data duplication

• application program independence - by storing data in a uniform fashion

• data sharing - by controlling access to data items, many users can access data concurrently

• backup and recovery

• security and privacy

• multiple views of data

history of database systems

• 1940s, 50s: initial use of computers as calculators; limited data, focus on algorithms; science, military applications

• 1960s: business uses; organizational data, customer data, sales, inventory, accounting, etc; file system based, high emphasis on applications programs to extract and assimilate data; larger amounts of data, relatively simple calculations

• 1970s: the relational model; data separated into individual tables; related by keys; initially required heavy system resources; examples: Oracle, Sybase, Informix, Digital RDB, IBM, DB2

• 1980s: microcomputers- the IBM PC, Apple Macintosh; database programs such as DBase (sort of), Paradox, FoxPro, MS Access

• Late 1980s: local area networks; workgroups sharing resources such as files, printers, email;

  • Client/Server: database resides on a central server, applications programs run on client PCs attached to the server over a LAN.

• 1990s: internet and world wide web make databases of all kinds available from a single type of client- the Web Browser; Data warehousing and Data Mining also emerge; other types of Databases:

  • object-oriented database systems: objects (data and methods) stored persistently.

  • distributed database systems: copies of data reside at different locations for redundancy or for performance issues

appropriate use for a database

• performance

• expendability, flexibility, scalability

• reduced application development times

• standards enforcement

when is DBMS not appropriate?

• database is small with a simple structure

• applications are simple, special purpose and relatively static

• applications have real-time requirements (e.g. traffic signal control, ECU patient monitoring

• concurrent, multi-user access to data is not required

disadvantages of a DBMS

• high initial cost (although falling)

• high overhead - requires powerful computers

• not special purpose software programs

contents of a database

• user data

• metadata

• indexes

• application metadata

user data

• data users work with directly by entering, updating, and viewing

• generally stored in tables with some relationships between tables

• each table has one or more columns; a set of columns forms a database record

metadata

• data about data

• describes how user data is stored in terms of table name, column name, data type, length, primary keys, etc.

• typically stored in system tables or system catalog, typically only accessible by the DBMS or by system administrator

indexes

• provide alternate means of accessing user data

• allow database to access a record without having to search through the entire table

• must be updated when updating data

applications metadata

• many DBMS have storage facilities for forms, reports, queries, and other application components

• accessed via database development programs

database design

activity of specifying the schema of a database in a given data model

database schema

structure of a database that:

• captures data types, relationships and constraints in data

• is independent of any application program

• changes infrequently

data model

• a set of primitives for defining the structure of a database

• a set of operations for specifying retrieval and updates on a database

• examples:

  • relational

  • hierarchical

  • networked

  • object-oriented

  • XML

database instance or state

the actual data contained in a database at a given time

two approaches of database development process

• top-down: design systems from an overall organizational perspective

• bottom-up: design systems from a specific perspective - one system at a time

steps of database development process

1. user needs assessment and requirements gathering: determine what the users are looking for, what functions should be supported, how the system should behave

2. data modeling: based on user requirements, form a logical model of the system, the logical model is then converted to a physical data model (tables, columns, relationships, etc.) that will be implemented

3. implementation: based on the data model, a database can be created; applications are then written to perform the required functions

4. testing: the system is tested using real data

5. deployment: the system is deployed to users; maintenance of the system begins

business rules

allow us to specify constraints on what data can appear in tables and what operations can be performed on data in tables (e.g. an account balance can never be negative, money can only be transferred from savings account to checking, etc.)

• enforced by

  • constraints on database

  • applications

information systems development life cycle (SDLC)

1. problem, opportunity and objective identification (system definition)

   • problems include shortcomings in present business practices that need to be corrected, opportunities include ways to improve existing practices, objectives identify the goal of an organization and for specific business processes

2. information requirements determination and requirements gathering

   • sampling existing data files, databases

   • examining existing reports and forms

   • user interviewing and questionnaires

   • prototyping

3. system needs analysis

   • system requirements that are based on data requirements and decision making processes, result is a system proposal that lists the recommended actions and cost/benefits

4. system design

   • logical design of the information system including identification of databases (tables, columns, keys, indexes) that will store required data and applications (forms, reports, menus) that will operate on the database

5. system development and documentation

   • system applications are developed including data entry forms, reports, menus, and queries; documentation for end users is also written

6. system testing and maintenance

   • system is tested on real data to pinpoint faults, any necessary changes are applied in this step before the system goes into production

7. implementation and evaluation

   • the system is put into production, end user training is performed, users and analysis evaluate effectiveness of system

- at each step of the cycle, there is opportunity to cycle back to prior step

- “waterfall” development approach

- Rapid Application Development (RAD) and Agile Development approaches run quickly through steps 1-5 for small portions of a complete application

Database Design Process

1. gather user and system requirements

2. create a conceptual model of the database using the Entity Relationship model that is based on the user requirements

3. choose a DBMS (e.g. MS Access)

4. convert conceptual model (E-R) to logical database model- we will use the Relational model (Data model mapping)

5. Normalize Relational model of the database

6. implement normalized relations as tables in a relational database - physical database design and implementation

computer aided software engineering (CASE)

can assist analysists in the SDLC

• provide repository to store information on each of the phases- excellent for comprehensive documentation of the development process

• diagramming tools assist in analysist/user communications, e.g. data flow diagrammer and E-R model diagrammer

• provide project management capabilities including maintenance management - impact analysis

upper CASE tools

typically focus on first 5 steps of the SDLC

lower CASE tools

focus on steps 4-6 of SDLC and include code generation capabilities

integrated CASE tools

cover all the steps of the SDLC (e.g. Oracle Designer, IBM System Architect, etc.)

data entry forms

a primary means to enter data into a database and to edit existing data, can also be used to query; typically arranged in groups of related data items

• would have fields that correspond to each of the database columns

• GIGO: garbage in, garbage out

• typically one for each table in a database application

*not* used for updating or creating new data, information-only forms

types of graphical user interfaces

• list boxes: provide a list of valid values for a user to choose from

• radio buttons: exclusive list of options (e.g. Gender M/F)

• check boxes - non exclusive list of options

four main types of forms

• single table (block) form

• single table (block) form with Lookup

• master/Detail form

• master/Detail form with Lookup