Text fields store 255 characters
and are used for a short collection of text, codes such as phone
numbers, email addresses, and zip codes . Memo fields store up to
1GB of data and display up to 64,000 characters and are used for
formated text, and to accumulate logs in append mode.
Currency fields are number fields
with a currency sign in front of them. Currency fields also default
to 2 decimal places.
You should never store calculated
fields in your database. For instance, if you know someone is 32
years old, you don't store their age, you store their date of birth.
That way you can generate a report now or 5 years from now that
includes the age of that person and it will be correct in both cases
without having to go in to the database annually and recalculate
that persons age.
When you have a field in a form
that has a small set of non changing possible values you create a
drop down box to allow people to quickly set the value in that
field. This prevents things like someone entering a state name
that doesn't exist. You could have drop downs for ice cream at an
ice cream shop or for picking the name of someone to assign a bug to
in a bug tracking database.
What are the three general rules
about naming objects in a database? Names have to be less than 64
characters, but should be less than this. Names cannot include a
period, exclamation point, accent grave, or brackets. Names cannot
include spaces.
The four main database objects
are Tables, Queries, Forms, and Reports. Tables hold data, queries
ask questions about the data, forms allow you to enter and display
data and to act as a switchboard to your program, and reports allow
you to retrieve and format data from the database in an attractive
way.
Select, Action and Crosstab
queries are the three types. Select queries ask questions about the
data in the tables in the database and display a dataset. Action
queries change the data in the database. Crosstab queries calculate
data from a table and display it in a dataset.
Redundant data entry, Error prone
and difficult to update when information changes. If the
information does change and you don't change all the occurrences
then you have introduced data inconsistencies to the data.
A primary key is a field or set of
fields that uniquely identifies a record. If this key is included
in another table it is called a foreign key. By linking tables
together this way you create a relation between the tables that
links the records in one table to the records in another table.
A is one to many. Any one
customer will have one or more orders. B is one to one. States
only have a single capital. C is many to many. There are many
college students in a class and each of the students take multiple
classes.
Entity integrity is done by using
a primary key, which requires that there be only one record in the
database with that key and that key is not null. Referential
integrity is when the value in the foreign keys in a table match the
table where they are primary keys. Yes, you should enforce
referential integrity in a database so that you don't get records
with values that don't relate to the other tables correctly.
Deletion anomalies occur when you
delete a record and the cascading delete removes data from related
tables because that was the records last matching record set.
Update anomalies occur when there is duplicate data in the database
and an update only changes some of the data. Insertion anomalies
occur when you can't insert a record into a table unless you enter
data into another table first.
Normalizing databases result in a
smaller database, reduces the occurrence of inconsistencies and
reduces the occurrence of all three types of anomalies. The first
normal form reduces repeating groups. The second normal form
removes functional dependencies. The third normal form requires
that every field in a record be at least partially determinant on
the key.
A determinant is a field, or set
of fields whose value determines the value in another field. A
partial determinant is where the value depends on a subset of a key.
A transitive dependency is related to another field that has a
partial or full determinant dependency.
No comments:
Post a Comment