Designing Relational Databases: Schema, Entities, and Controls

1. Chapter 12 Systems Analysis and Design in a Changing World, 6th Edition

2. Databases, Controls, and Security Chapter 12 Systems Analysis and Design in a Changing World 6th Ed Satzinger, Jackson & Burd

3. Some Database Concepts • Database (DB) -- an integrated collection of stored data that is centrally managed and controlled • Database management system (DBMS) -- a system software component that manages and controls one or more databases • Schema -- contains descriptive information about the data • what it represents • Logical design Systems Analysis and Design in a Changing World, 6th Edition

4. Database Schema • Contains Represents • Tables Entity/Class • Fields Attribute • Rows Instance • Relationships Associations (relationships) • Data types, lengths, controls • Access Controls (ie authentication, authorization) Systems Analysis and Design in a Changing World, 6th Edition

5. Database and DBMS Components Systems Analysis and Design in a Changing World, 6th Edition

6. Partial Display of a Relational Database Table Systems Analysis and Design in a Changing World, 6th Edition

7. How a relationship is implemented Each foreign key MUST reference A valid primary key, Or else the record is Said to be “orphaned”! Systems Analysis and Design in a Changing World, 6th Edition

8. Designing Relational DatabasesBased on the Domain Model Class Diagram • Create a table for each class • Choose a primary key for each table (invent one, if necessary) • Add foreign keys to represent one-to-many associations • Create new tables to represent many-to-many associations • Represent classification hierarchies • Define referential integrity constraints • Evaluate schema quality and make necessary improvements • Choose appropriate data types • Incorporate integrity and security controls Systems Analysis and Design in a Changing World, 6th Edition

9. 1: Create a Table for each class Systems Analysis and Design in a Changing World, 6th Edition

10. 2: Choose primary key for each table Primary Key field may either be CREATED (brand new) or ASSIGNED (from an existing unique attribute). Systems Analysis and Design in a Changing World, 6th Edition

11. Step 3: Add Foreign Keys • Foreign Keys required for relationships between entities. • One to many: add a foreign key to the “many” table that corresponds to the primary key from the “one” table • Many to many: Create a table in between the two “many” tables (if no assoc. table exists). Primary key will be a composite of two key attributes from the first two tables. • For each relationship, a “constraint” is created in the DB

12. Step 3: Add Foreign Keys(in italics) CartItem InventoryItem OnlineCart ProductComment Sale SaleItem SaleTransaction Systems Analysis and Design in a Changing World, 6th Edition

13. M:N added • AccessoryPackageContents • New • PromoOffering • Existing Systems Analysis and Design in a Changing World, 6th Edition

14. Classification Hierarchies? • Can implement as one table • Attributes that don’t apply to a subclass are left to be NULL (or empty) • Or several with 1:N relationships • One table for the superclass • One table each for the subclasses • Primary key for superclass is primary key for subclass, ALSO a foreign key to the superclass. Systems Analysis and Design in a Changing World, 6th Edition

15. Referential Integrity and Schema Quality • Referential integrity -- a consistent state among foreign key and primary key values • Referential integrity constraint -- a constraint, stored in the schema, that the DBMS uses to automatically enforce referential integrity • A high-quality relational database schema has these features: • Flexibility or ease of implementing future data model changes • Lack of redundant data • Normalization -- a formal technique for evaluating and improving the quality of a relational database schema Systems Analysis and Design in a Changing World, 6th Edition

16. Example of Integrity Violations Systems Analysis and Design in a Changing World, 6th Edition

17. Database Architectures • Single database server architecture -- one or more databases are hosted by a single DBMS running on a single server • Replicated database server architecture -- complete database copies are hosted by cooperating DBMSs running on multiple servers • Partitioned database server architecture -- multiple distributed database servers are used and the database schema is partitioned • Cloud-based database server architecture -- use of a cloud computing service provider to provide some or all database services Systems Analysis and Design in a Changing World, 6th Edition

18. Partitioning Database SchemaInto Client Access Subsets Systems Analysis and Design in a Changing World, 6th Edition

19. The Need for Database Controls • Threats to the integrity of the database • People performing operations that they are not authorized to • Inputting false, empty, or repeated data • Orphaned records • Security breaches on sensitive data • Etc • This compels the developer to create “integrity controls” in the system, to ensure a high quality of data Systems Analysis and Design in a Changing World, 6th Edition

20. Designing System Controls • Controls -- mechanisms and procedures that are built into a system to safeguard the system and the information within it • Integrity control -- a control that rejects invalid data inputs, prevents unauthorized data outputs, and protects data and programs against accidental or malicious tampering • Security controls -- are part of the operating system and the network and tend to be less application specific. Systems Analysis and Design in a Changing World, 6th Edition

21. Integrity ControlsInput Controls • Can be implemented in the DBMS, or elsewhere • Input control -- prevents invalid or erroneous data from entering the system • value limit control -- checks numeric data input to ensure that the value is reasonable • completeness control -- ensures that all required data values describing an object or transaction are present • data validation control -- ensures that numeric fields that contain codes or identifiers are correct (ie postal code) • field combination control -- a control that reviews combinations of data inputs to ensure that the correct data are entered (ie “Feb 31st”) Systems Analysis and Design in a Changing World, 6th Edition

22. Integrity ControlsInput Controls - Examples • value limit control • can’t enter numbers below zero • can’t enter number over a certain amt • completeness control • Postal code cant be missing • Must provide email address • data validation control • Postal code in right format • field combination control • Month can’t = February if Day = 31 Systems Analysis and Design in a Changing World, 6th Edition

23. Integrity ControlsAccess controls, Transaction logging, Complex update controls, Output controls • Access control -- a control that restricts which persons or programs can add, modify, or view information resources • Transaction logging -- any update to the database is logged with such audit information as user ID, date, time, input data, and type of update • Complex update control -- prevents errors that can occur when multiple programs try to update the same data at the same time or when recording a single transaction requires multiple related database updates • Output control -- ensures that output arrives at the proper destination and is accurate, current, and complete Systems Analysis and Design in a Changing World, 6th Edition

24. Integrity ControlsRedundancy, Backup, and Recovery • Designed to protect data from hardware failure and catastrophes • Redundancy – continuous access to data through redundant databases, servers, and sites • Backup – procedures make partial or full copies of a database to removable storage media, such as magnetic tape, or to data storage devices or servers at another site • Recovery – procedures read the off-site copies and replicate their contents to a database server that can then provide access to programs and users. Systems Analysis and Design in a Changing World, 6th Edition

25. Designing Security Controls • Security control -- a control that protects the assets of an organization from all threats, with a primary focus on external threats • Two Objectives • Maintain a stable, functioning operating environment for users and application systems (usually 24 hours a day, 7 days a week). • Firewalls to protect from hackers, viruses, works, and denial of service attacks • Protect information and transactions during transmission across the Internet and other insecure environments • Information could be intercepted, destroyed or modified Systems Analysis and Design in a Changing World, 6th Edition

26. Security ControlsAccess Controls • Authentication -- the process of identifying users who request access to sensitive resources • Authorization -- the process of allowing or restricting a specific authenticated user’s access to a specific resource based on an access control list • Multifactor authentication -- using multiple authentication methods for increased reliability • Unauthorized user -- a person who isn’t allowed access to any part or functions of the system • Registered user -- a person who is authorized to access • Privileged user -- a person who has access to the source code, executable program, and database structure of the system Systems Analysis and Design in a Changing World, 6th Edition

27. Security ControlsData Encryption • Encryption -- the process of altering data so unauthorized users can’t view them • Decryption -- the process of converting encrypted data back to their original state Systems Analysis and Design in a Changing World, 6th Edition