Database Design and Control

Database Design and Control: 

Database Design and Control Chapters AIS 9 and ITA 4

Flat-File Versus Database Environments: 

Flat-File Versus Database Environments RQ 9-4 2

Slide 3: 

Program 1 Program 2 Program 3 User 2 Transactions User 1 Transactions User 3 Transactions Database D B M S A, B, C, X, Y, L, M The Database Concept 3

Elements of the Database Environment: 

Elements of the Database Environment RQ 9-2 4

Internal Controls and DBMS: 

Internal Controls and DBMS Designing a database can help you understand how controls are implemented and work with data entry. The database management system stands between the user and the database per se. Thus, commercial DBMS’s (e.g., Access or Oracle) actually consist of a database plus… software to manage the database, especially controlling access and other internal controls software to generate reports, create data-entry forms, etc. 5

DBMS Features: 

DBMS Features Program Development - user created applications Backup and Recovery - copies database. Database Usage Reporting - captures statistics on database usage (who, when, etc.). Database Access - authorizes access to sections of the database. Also… User Programs - makes the presence of the DBMS transparent to the user. Direct Query - allows authorized users to access data without programming. 6

Functions of the DBA: 

Functions of the DBA 7

Database Conceptual Models: 

Database Conceptual Models Refers to the particular method used to organize records in a database. There are three main models: hierarchical (tree structure) network relational Most existing databases are relational We will work only with relational databases 8

The Relational Model: 

The Relational Model The relational model portrays data in the form of two dimensional ‘tables’. Its strength is the ease with which tables may be linked to one another. Relational model is based on the relational algebra functions of restrict, project, and join. 9

Slide 10: 

10 The Relational Algebra Functions Restrict, Project, and Join

Associations and Cardinality: 

Associations and Cardinality RQ 9-14 11

Slide 12: 

12 Examples of Entity Associations

Properly Designed Relational Tables: 

Properly Designed Relational Tables RQ 9-7, 9-8, 9-13 RQ 9-15 13

Database Terminology: 

Database Terminology Data Attribute/ Field: a single item of data Entity: database representation of an individual resource, event, or agent about which we choose to collect data Record Type: table or file Database: the set of record types that an organization needs to support its business processes 14

Six Phases in Designing Relational Databases: 

Six Phases in Designing Relational Databases Identify entities identify the primary entities of the organization RQ 9-24 construct a data model of their relationships Construct a data model showing entity associations determine the associations between entities 15

Six Phases in Designing Relational Databases: 

Six Phases in Designing Relational Databases Add primary keys and attributes assign primary keys to all entities in the model to uniquely identify records every attribute should appear in one or more user views Normalize and add foreign keys remove repeating groups, partial and transitive dependencies assign foreign keys to be able to link tables 16

Six Phases in Designing Relational Databases: 

Six Phases in Designing Relational Databases Construct the physical database create physical tables populate tables with data Prepare the user views normalized tables should support all required views of system users user views restrict users from having access to unauthorized data 17

The Normalization Process: 

The Normalization Process A process which systematically splits unnormalized complex tables into smaller tables that meet two conditions: all nonkey (secondary) attributes in the table are dependent on the primary key all nonkey attributes are independent of the other nonkey attributes When unnormalized tables are split and reduced to third normal form, they must then be linked together by foreign keys. 18

Steps in the Normalization Process: 

Steps in the Normalization Process 19

Database Management Controls: 

Database Management Controls Two crucial database control issues: Access controls Audit objectives: (1) those authorized to use databases are limited to data needed to perform their duties and (2) unauthorized individuals are denied access to data Backup controls Audit objectives: backup controls can adequately recover lost, destroyed, or corrupted data 20

Access Controls: 

Access Controls ITA RQ 4-25 ITA RQ 4-26 21

Database Authorization Table: 

Database Authorization Table 22

Access Controls: 

Access Controls Audit procedures : verify… responsibility for authority tables & subschemas granting appropriate access authority use or feasibility of biometric controls use of encryption 23

Backup Controls: 

Backup Controls ITA RQ 4-27 24

Backup Controls: 

Backup Controls Audit procedures : verify… that production databases are copied at regular intervals backup copies of the database are stored off site to support disaster recovery 25

Application homework problems: 

Application homework problems P 9-15 ITA P 4-6 26

In class activity: : 

In class activity: Normalization practice problems P 9-9, 9-11 27