Wednesday, April 25, 2012

Database Design - homework

  1. The four steps of database design are discovery phase, plan the tables, normalize, and test the database using sample data.
  1. Data duplication is entering two or more records in the database about the same entity with a slight variation. You have to delete or merge duplicate records by hand. Data redundancy is the same data in the database repeatedly. You remove this redundancy by normalizing the database. The reason you want to try to only store data a minimum number of times is to assist in keeping the data up to date and consistent.
  1. Scope creep is when new features and requirements are added to a project after the project has begun.
  2. When you are assigning a data type and size to a field you have to know the storage requirements for that field. You need to know the size and range of data will be stored in the field.
  1. 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.

  2. Currency fields are number fields with a currency sign in front of them. Currency fields also default to 2 decimal places.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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.

  14. 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