Database Terms

1. Database Terms Hernandez, Chapter 3

2. Data/Information • The values you store in the database are data. Pieces of Data in and of themselves is not particularly useful • Information is processed data. It is data organized and returned in ways that give it meaning and significance

3. Null • A null represents a missing value. • It is not a 0 or an empty string “”. It is unknown. • How a DBMS treats nulls can be significant. • Do you count them when you are counting rows • Do you include them somehow in an average by converting them to 0’s? (but this will give you a false average) • Do you exclude them? (but this too may give a false average because the count could be wrong)

4. More on Nulls • Nulls can result from error • A field can be null because it is not know yet (for instance your quarter grade is null in my gradebook) • A field can be null because the field does not apply to the particular record • Most databases ignore nulls in aggregate functions such as sum or avg • In other math functions a null +-*/ by any number is still a null

5. Table • A two dimensional structure consisting of columns (fields) and rows (records)

6. Types of Tables • Data: a table that stores relevant data • Lookup: a table that stores look up data, such as zip codes or state names. Helps insure data integrity and consistency • Link tables: tables that link together two other tables that have a many to many relationship (Much more on this later)

7. Field • The smallest structure in a database. A single datum. (also called an attribute or column.) • In a properly designed database every field consists of one and only one value • Common field problems in a poorly designed database • Multipart fields (more than one distinct value) • Multivalued field (multiple instances of the same type of value) • Calculated fields

8. View • A view is a virtual table composed of the fields of one or more tables • A view is a way of looking at the data in the database • In a good design, every database user will have an appropriate view • You can use view to bring information together that is stored in separate tables • You can also use views to hide information that particular users do not need to see

9. Keys • Keys are special fields that are used to tie tables together, to relate them • Primary Keys uniquely identify a row in a table (They must be unique, they cannot repeat in the table in which they are the key) • Primary keys represent the table in relations • A primary key can be composite (meaning it can consist of more than one field—but it is still a single key)

10. Foreign Keys • A primary key repeated in another table to create a relationship is a foreign key. • Unlike the primary key, a foreign key can repeat several times in the table. • A foreign key is almost never also the primary key of a table

11. Relationships • One to One—a single record in one table is related to one and only one record in a second table. (Each employee has one parking spot) • One to Many—One record in a table can relate to many records in a second table. (One customer can make many orders) • Many to Many—One student can register for many classes; each class contains many students

12. Integrity • Table integrity (table relates only to one thing, no duplicate records) • Field integrity (each field one thing, proper data type) • Relational Integrity (Sound relationships, enforce things like no order without a customer) • Business rules (limitations based on the business needs.)