Database Management

TAL Distributors

TAL Distributors

• Wholesaler of wooden toys, games, puzzles

• Uses spreadsheet software to maintain important data

• Recent growth has made spreadsheet approach problematic
  
  
  

Problems using spreadsheet

• Redundancy

• Difficulty accessing related data

• Limited security features

• Size limitations

Redundancy

Duplication of data or the storing of the same data in more than one place

Information TAL Distributors needs to maintain

• Sales Reps

• Customers

• Items Inventory

Sales Rep

Sales rep number, last name, first name, address, total commission, commission rate

Customers

Customer number, name, address, current balance, credit limit, # of customer’s sales rep

Items Inventory

Item #, description, # of units on hand, item category, storehouse #, unit price

Order

Order #, order date, customer #

Order line

Order #, item #, # of units ordered, quoted price

Overall order total

Not stored because it can be calculated

Items for each customer’s order

• Order

• Order line

• Overall order total

Database

Structure that can store information (data)

Entity

• Person, place, object, event, or idea

• Entities of TAL Distributors: sales reps, customers, orders, and items

Attribute

• Characteristic or property of an entity

• May also be called a field or column

• Example: Customer has name, street, city, etc

Relationship

Association between entities

One-to-many relationship

Type of association in database design where a single record in one table (the "one" side) can be associated with multiple records in another table (the "many" side).

Data file

• File used to store data

• Computer counterpart to paper file

Entity-relationship (E-R) diagram

• Visual way to represent a database

• Rectangles represent entities

• Lines represent relationships between connected entities

Database Management System (DBMS)

A program, or collection of programs, through which users interact with a database

Popular DBMS’s

Access, Oracle, DB2, MYSQL, and SQL Server

Database Design

Determining the structure of the required database

Forms

Screen objects used to maintain, view, and print data from a database

Reports

Created by DBMS for TAL distributors that’s based on user’s desired content and appearance

Advantages of Database Processing

• Getting more info from the same amount of data

• Sharing Data

• Balancing conflicting requirements

• Controlling redundancy

• Facilitating consistency

• Improving integrity

• Expanding security

• Increasing productivity

• Providing data independence

Database Administrator/Database Administration (DBA)

Person or group in charge of the database

Integrity Constraint

a rule that data must follow in the database

Security

prevention of unauthorized access

Data Independence

can change the structure of a database without changing the programs that access the database

Disadvantages of Database Processing

• Larger file size

• Increased complexity

• Greater impact of failure

• More difficult recovery

Big Data

• Large volume of data

• Produced by every digital process, system, sensor, mobile device, and social
  media exchange

Dr Edgar F. Codd

researcher and pioneer of the Relational Model of
database systems.

Codd’s Rules

Twelve rules a database must obey in order to be regarded as a true relational database.

Codd’s 12 Commandments

Another name for Codd’s Rules

Rule 0: The Foundation Rule

For any system advertised or claimed to be a Relational Database Management System (RDBMS), the system must be able to manage data entirely through its relational capabilities.

Rule 1: Information Rule

The data stored in a database, may it be user data or metadata, must be a value of some table cell. Everything in a database must be stored in a table format

Rule 2: Guaranteed Access Rule

Every single data element (value) is guaranteed to be accessible logically with a combination of table-name, primary-key (row value), and attribute-name (column value). No other means, such as pointers, can be used to access data

Rule 3: Systematic Treatment of NULL Values

The NULL values in a database must be supported and given a systematic and uniform treatment. This is a very important rule because a NULL can be interpreted as one the following − data is missing, data is not known, or data is not applicable.

Rule 4: Active Online Catalog

The structure description of the entire database must be stored in an online catalog, known as data dictionary. Authorized users can use the same query
language to access the catalog as that used to access the database itself

Rule 5: Comprehensive Data Sub-Language Rule

There must be at least one language having a well defined syntax that supports data definition, view definition, data manipulation, integrity constraints, authorization and transaction management operations. This language can be used directly or by means of some application. If the database allows access to data without any help of this language, then it is considered as a violation.

Rule 6: View Updating Rule

All the views of a database, which can theoretically be updated, must also be updatable by the system.

Rule 7: High-Level Insert, Update, and Delete Rule

A database must support high-level insertion, updation, and deletion in additional to retrieval. This must not be limited to a single row, that is, it must also support union, intersection and minus operations to yield sets of data records.

Rule 8: Physical Data Independence

The data stored in a database must be independent of the applications that access the database. Any change in the physical structure of a database must not have any impact on how the data is being accessed by external applications.

Rule 9: Logical Data Independence

The logical data in a database must be independent of its user’s view (application). Any change in logical data must not affect the applications using it. For example, if two tables are merged or one is split into two different tables, there should be no impact or change on the user application. This is one of the most difficult rule to apply

Rule 10: Integrity Independence

Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs. This rule makes a database independent of the front-end application and its interface.

Rule 11: Distribution Independence

The end-user must not be able to see that the data is distributed over various locations. Users should always get the impression that the data is located at one site only. This rule has been regarded as the foundation of distributed database systems

Rule 12: Non-Subversion Rule

If a system has an interface that provides access to low-level records, then the interface must not be able to subvert the system and bypass security and integrity constraints.

Relation

two-dimensional table

Traits of a Relation

• Entries are single-valued

• Each column has a distinct name (attribute name)

• All Values in a column are values of the same attribute

• Order of columns is immaterial

• Each row is distinct

• Order of rows is immaterial

Relational Database

collection of relations

Unnormalized relation

A structure that satisfies all properties of a relation except for the first item