Chapter 5: Database Processing

What is a Database?

Most of us will consider a spreadsheet to be the same as a database, bot depending on what task and responsibilities you have, you may not want to solely use a spreadsheet. For example, as a mom I use Microsoft Excel to keep track of my finances and I also have another spreadsheet that keeps track of my son's grades. If I  want to start giving my son weekly allowances, depending on his grades, using these two separate spreadsheets will become a pain. It will be concise and easier to follow progress, if I had one database with all the information required. A spreadsheet is more beneficial for single themes, but a database is best for multiple themes.

The purpose of a database is to keep track of multiple things. Here is the basic information to understand what a database is.

• Database is a self-describing collection of integrated records.
• Byte: a character data
• Column/Fields: a group of bytes
• Rows/Records: the collection group of columns/fields
• Key/Primary Key: a column or group of columns that identifies a unique row in a table.
• Foreign Keys: keys of a different region (foreign) table than the one in which they reside.
• Relational  Databases: databases that carry their data in the form of tables and that represent relationships using foreign keys.
• Table/File: a group of similar rows/records
• Metadata: describes the structure of the data, always a part of a database, and it depends on software product that is processing the database.

Databases that we currently use

Therefore, a collection of tables + relationships among the rows in those tables + special data "metadata"= Database.

What is a Database Management System (DBMS)?

DBMS- is a program used to create, process, and administer a database.

5 Popular DBMS

1. DB2-IBM
2. Access-Microsoft
3. SQL Server-Microsoft
4. Oracle Database-Oracle Corporation
5. MySQL-Open Source, Free license for most products.

3 Functions why Database developers use DBMS

• Creating the Database and its structures
• Processing the Database
• Structured Query Language (SQL)- is an international standard language for processing a database.
• They can be issued directly to the DBMS by an application program.
• They can be used to create database and database structures.
• Administering the Database
• Database administration- involves a wide variety of activities.
• Used for its security function, variety of permissions to use database.
• Used for backing up database data.
• Used for adding structures to improve the performance of database application.
• Used for removing data that are no longer wanted or needed, and similar tasks.

How Do Database Applications Make Databases More Useful?

Database application- is a collection of forms, reports, queries, and application programs that serves as an intermediary between users and database data. According to the textbook, Using MIS, "Database applications came into prominence in the 1990's and were based on technology available at that time. Current database applications are based on newer technology that employs browsers, the Web, and related standards" (pg 173). We are able to create, modify, transfer, and share data, that is exciting!

Traditional Database

• Mostly shared among many users.
• Most cases a network not the Internet is used to transmit traffic back and forth between the users' computers and the DBMS server computer.
• Most run within a corporate network protected from threats common on the Internet.
• Single-user database like MS Access, are an exception.
• MS Access the application, the DBMS, and the database all reside on the user's computer.
• Programs are written in object-oriented languages.
• C++
• VisualBasic
• COBOL (earlier versions)
• Thick-client Applications that do need to be installed on user's computers.
• All of the application logic is contained in a program users' computers and the server does nothing except run the DBMS and serve up data.
• Some application code is placed on both the user's computers and the database server computer.

Database in Browser-based Applications

• Nearly always shared among many users.
• Thin-client applications that do not need to be preinstalled on the users' computer.
• All of the code for generating and processing the application elements is shared between the users' computer and the servers.
• JavaScript-standard language for user-side processing.
• C# and Java- used for server-side code
• JavaScript-will be used on the server with an open source product named Node.js.
• Forms, reports, and queries are displayed and processed using html and, most recently, using html5, css3, and JavaScript.
• Can support traditional queries
• Graphical Queries- query criteria are created when the user clicks on a graphic.
• Security requirements are more stringent, since they are open source and are more vulnerable to Internet threats.

Multi-user Processing

• Most traditional and Browser-based applications involve multiple users processing the same database.
• Lost-update problem- when multiple users attempt to purchased a similar item on a Web site, each person uses their own browser, and someone will be disappointed to know that the item was already purchased because someone did not checked out, while the other person did check out.

How Are Data Models Used for Database Development?

According the textbook Using MIS, Database developers know that users need a database to perform their tasks, but do not what specifically to include in the database, since every user has unique needs depending on the business environment. Developers create data models before they start designing the database. Developers also use entity-relationship data models to describe the content of a data model by the things (entities) that will be stored in the database and the relationships among those entities (pg 178). It is crucial for your job and the business that you work with to all the database developer to create something that in the data model contradicts your business structure and most important its strategy.

Terms to know

• Data model- describes the data and relationships that will be stored in the database.
• Entity-Relationship (E-R) data model- a tool for constructing data models.
• Entity-Relationship (E-R) diagram- database designers use these diagrams to make E-R data models easier to present to others.
• Unified Modeling Language (UML)- a second, but less popular tool for data modeling.
• Entity- some thing that the users want to track, they are always singular.
• Have relationships to each other.
• Lines are used to represent a relationship between two entities.
• Angled lines that are shorthand for the multiple lines between two entities are called crow's feet.
• Crow's-foot diagram- shows the maximum number of entities that can be involved a relationship.
• Commonly called "Maximum cardinality" Examples: 1:N, N:M, 1:1
• Constraints on minimum requirements are called "Minimum cardinalities"
• 1:N "one-to-many relationships"- 1 department can have many advisers, but an adviser has at most one department.
• Example: San Jose State Business department has more than 2 advisers, but each adviser has at least the business department as employment.
• N:M "many-to-many relationships"- 1 adviser can have many students and one student can have many advisers.
• Example: As an undergrad student and enrolled in the EOP program, I had two advisers that I could go to, and each of the advisers had many students to support as well.
• Attributes- describe the characteristics of the entity.
• Identifier- an attribute (or group of attributes) whose value is associated with one and only one entity instance.

How Is a Data Model Transformed into a Database Design?

According to the textbook Using MIS, "a database design is the process of converting a data model into tables, relationships, and data constraints. The database design team transforms entities into tables and expresses relationships by defining foreign keys" (pg 181). The best design will depend on the user's processing requirements. 

Normalization-  is the process of converting a poorly structured table into two or more well-structured tables to eliminate data integrity problems, which occurs from duplicating data. This process is very slow and a database developer and user thinking about a database design must take the slower process into consideration. 

• Normal Form- database with tables that have a single topic or theme.
• Normalizing the table- transforming a table into a normal form to remove duplicated data and other problems.
• Non-normalized- tables that have a format that could cause data integrity problems.
• Representing Relationships
• The database designer creates a table for each entity.
• The resulting tables are normalized so that each table has a single theme.
• Represent the relationship
• 1:N- "relational model" a foreign key is added to one of the two tables.
• N:M- foreign keys cannot be added, so a third table must be created
• Wrong data model = Wrong database design 
• User's Role in the Development of Databases
• Reviewing the data is crucial! 
• Easier to change the database structure during the data modeling stage.
• Only the user knows what data the database should contain.
• Only the user knows how the records in that database should be related to one another.
• "Entities must contain all of the data you and your employees need to do your jobs, and relationships must accurately reflect your view of the business" (pg 185-186).

How Can Falcon Security Benefit from a Database System?

In the textbook Using MIS, "Falcon Security wants to be able to find videos by querying their characteristics. The Director of IT, Toshio and the Operations Manager, Cam have two database architectures to choose from" (pg 187).

1. Store the video footage on a file server and keep metadata about each video in a relational database that it can query. The metadata will include the address of the video footage on the file server.

2. Utilize one of the new NoSQL DBMS products.

 For example, MongoDB- is an open source document-oriented DBMS--to store the video footage in the same database as the metadata. Instead of choosing the second alternative, both decide to use Access to store the metadata, since both are skilled and have more knowledge than using MongoDB. Finally Toshio creates an E-R diagram, then both create the database and related applications.

2026?

Traditional relational DBMS products devote considerable code and processing power to support ACID (Atomic, Consistent, Isolated, Durable) transactions. 

Three New Categories of DBMS

1. NoSQL DBMS- NotRelational DBMS, supports very high transaction rates processing relatively simple data structures, replicated on many servers in the cloud without ACID transactions.
2. NewSQL DBMS- Process very high levels of transactions, may or may not support the relational model, provides ACID support.
3. In-Memory DBMS- Process databases in main memory, usually is able to support or extend the relational model.