Databases

Databases are collections of related data. They can be on paper, on computers, on cell phones, and even on napkins. Any collection of related data is a database. Several examples of databases include your cell phone's contact list, an address book, and the yellow pages listings.

Databases software is used to manage data, such as customers, products, vendors, employees, and students. In this class the focus will be computer databases management systems (DBMS), using Microsoft Access and MySQL (for Web Page Design 2).

There are many advantages to using computer databases. For example:

• Faster entry of data
• Faster data retrieval
• Multiple views of data
• Improved data security
• Improved data distribution
• Minimized data duplication

Using a database is not difficult. The challenge comes in creating and updating databases. In order to create (develop) databases, you must first learn some basic terminology:

Database: A collection of related data usually organized in tables. For example, the Aeries database at CHS manages all student data, such as your student id and class schedule.

Tables: Collection of records about entities. Tables are composed of fields that describe entities. Entities can be students, classes, and teachers.

Fields: Attributes are characteristics of an entity (Person, Place, Thing). These characteristics describe the entity. For example, the following characteristics describe the student entity. All students in the database will have a studentID, FirstName, LastName, Birthday, Grade, and Gender.

	StudentID
	FirstName
	LastName
	Birthday
	Grade
	Gender

Records: A set of related fields, such as the characteristics of one student. A record does not include the field name, only the data. The following characteristics apply to the student named David Hanson.

Relational database management systems (RDBMS), such as Access and MySQL, store data in tables. These tables are associated (related) in some manner and are, therefore, linked. We will explore table relationships later. For now we will focus on Access as our starting point.

Access

Microsoft Access is used for small department databases. It is a good start for learning about relational databases. What you learn with Access can be applied to Oracle, SQL Server, MySQL and many other relational database management systems.

Access utilizes four objects to manage data. These objects include tables, (composed of fields and rows), forms, queries, and reports.

Table: A collection of records about entities displayed in spreadsheet format
Form: A computer screen for data entry or data display
Query: A "view" of table data used to answer questions regarding the data
Report: An enhanced view of data with headers, footers, and calculations

The main object will always be your table. Forms, queries, and reports are tools for utilizing and updating data in your tables.

Tables are the most important objects in your database. They store all the raw data, the individual pieces of information stored in the fields of the database. When designing a table you select the fields the table will contain. You also select the data type for each field. For example, the FirstName field uses the text data type, the Birthday field uses the date/time data type, and the Grade field uses the Number data type. When designing tables you:

Determine the purpose of the database.
What will the database store?

Determine the output (reports) of the database.
What information do we need from the database?

Determine the input (source data) of the database.
Where will you get the data and in what format?

Determine the structure of each table.
Which data types will be used for each field?

You also need to determine how tables are related so you can insert foreign keys. Again, we will discuss how tables have relationships and are, therefore, linked.

There are several data types to choose from including:

• text
• memo
• number
• datetime
• currency
• yes/no
• autonumber

Forms
Forms are objects that allow users to input data or view data quickly. Forms allow you to find, enter, edit, and delete data from a table in a user-friendly way. They are often built to match source documents (paper forms) used to obtain data. Forms use controls such as buttons, checkboxes, textboxes, drop-down boxes, and list boxes to allow access to data.

Reports
Reports are objects that allow users to share data, usually in printed format. A report is similar to a form in the use of controls. However, report controls are only used to display data, not enter data. Reports allow users to create professionally format reports for management.

Queries
Queries are objects that allow different views of data within tables. They are what make databases so useful. Queries answer questions such as how many students passed the CAHSEE exam? How many of the students that passed were female? How many were seniors or juniors? How many were from Coalinga and how many were from Huron? These questions apply only to Coalinga High School, but queries can answer questions such as how many voters in the west coast voted for Mr. Chagoyan? How many voters in the nation were females younger than 27 and older than 23? Queries can return detail results that would take countless hours, weeks, and even years to tabulate in mere seconds. Queries can be used to select subset or records and subset of fields. They can even calculate sums, averages, and other statistics.

SQL
SQL stands for structured query language. It is a language derived from relational algebra. It is fairly easy to use once you understand key concepts. Queries are created via SQL statements. We will cover SQL in Web Page Design 2.

It is helpful to think of a database as a file cabinet. Each drawer in the cabinet is a table, which stores multiple files (records) and each file includes fields.

Databases can start off as paper systems. Use the following application to move data from paper to electronic database.

West Hills College Applicaton