Midterm+Study+Guide.docx

IOE 373 Winter 2010 Midterm Study Guide The midterm exam counts for 12 points. It is scheduled for March 31 at 3:40 PM (3:30 Michigan Time). Room assignments will be posted in announcements. Midterm scores will be awarded points based on the percentage score. Percentages will be rounded to the nearest whole number, and then converted to points by the following scale: Most of the exam will come from the topics listed below. You will have 80 minutes to complete it. The exam is closed everything (book, computer, phone, IPod, IPad, calculator, etc.). Warning: The categories below may not exactly match the categories on the exam. For example, if there is a definition in one of the online quizzes, you may see it included in the True/False, Multiple Choice, Short Answer, or Definition section of the exam. Similarly, one of the definitions below may be tested in any section of the exam. Definitions You should be familiar with the following definitions: Variable: Named location in memory. Boolean: Refers to a True/False variable or value. Arguments: Variables/values passed to functions and subroutines. Also called parameters. Parameters: Variables/values passed to functions and subroutines. Also called arguments. Function: A procedure which returns a value. Functions should not produce side effects aside from returning a value. Subroutine: A procedure which does not return a value. Procedure: General term for some code lumped together and given a name, generally intended to perform a defined task. Includes subroutines and functions. DBMS: Database Management System. The software that organizes and retrieves data. Common DBMS?s are Oracle, Microsoft SQL Server, DB2, MySQL, and Access. Database: A collection of interrelated data items that are managed as a single unit. Entity: Person, place, thing, event, or concept about which data is collected. Attribute: A unit fact that characterizes or describes an entity. Primary Key: A column or set of columns that uniquely identifies each row in a table. Candidate Key: Within a given table, a column or set of columns that uniquely identifies each row, and therefore could serve as the primary key. Some tables may have several candidate keys. Uniqname is an example of a candidate key in tables of student records that use StudentID as the primary key. Foreign Key: A column or set of columns in a child table that is the primary key of another table (the parent table). The foreign key is used to associate records in the child table with records in the parent table. SQL: Structured Query Language; the language used in relational databases to communicate with the database. Field: A column in a database table. Record: A row in a database table. Query: A question or request to a database. Normalization: Creating or modifying a database so that it stores data efficiently and accurately, and avoids the three anomalies (insert, update, and delete). Insert anomaly: A situation wherein one cannot insert a new row into a table because of an artificial dependency. Delete anomaly: A situation wherein a deletion of data about one particular entity causes unintended loss of data that characterizes another entity. Update anomaly: A situation where an update of a single data value requires multiple rows of data to be updated. Third Normal Form (3NF): The property of a table which has a proper primary key, no multivalued attributes, all non-key attributes functionally dependent on the entire primary key, and no transitive dependencies. The generally-accepted standard for normalization in OLTP databases. OLTP: Online Transaction Processing. The term for databases which experience frequent INSERT, UPDATE, and DELETE commands, for example, a bank or a retail store. OLTP databases should generally be normalized to 3NF. OLAP: Online Analytical Processing. The term for databases which are used for analyzing patterns and trends, and which do not experience frequent INSERT, UPDATE, and DELETE commands. To speed up analysis, these databases are frequently not normalized. Database Connection: The object through which a VB program can interact with a database. DataTable: An in-memory representation of the results of a SELECT query. OOP: Object-oriented programming. A programming approach which incorporates encapsulation, modularity, polymorphism, and inheritance. Class: The basic structural element of object-oriented programs; contains the code used to create and store objects, and the procedures and properties that those objects will possess. Object: An instance of a class. Property: A combination of a subroutine and a function using the same name which appears to the rest of the program as a variable, but allows for data validation or other code to run whenever the property is accessed. Constructor: A subroutine which runs whenever a new object is created. In VB, a constructor is always called ?New?. Overloaded Procedures: Procedures in a class having the same name but parameter lists which differ in type and/or number. Inheritance: Creating new classes from an existing class using the keyword ?Inherits?. Stack: A specialized VB collection type which incorporates last in, first out (LIFO) behavior. Queue: A specialized VB collection type which incorporates first in, first out (FIFO) behavior. Dictionary: A specialized VB collection type which pairs a key of one data type with data of another. FIFO: First in, first out; describes the behavior of a queue. LIFO: Last in, first out; describes the behavior of a stack. General Knowledge There will be some True-False and Multiple Choice questions based on your general knowledge of the course topics. These were covered in lecture, but you should know them anyway if you have understood what you were doing in the assignments. If you haven?t understood what you were doing in the assignments, reviewing the lectures may help to improve your chances to get these questions right. Online Quizzes 1 through 6 Word versions of online quizzes 1, 3, 4, 5 and 6 have been posted in Resources/Midterm Materials. These include all questions and answers from these quizzes. Any question from these quizzes may be used in the midterm. The format of the questions may be changed; for example, questions that were fill-in-the-blank in the online quizzes may be converted to multiple choice or True/False. 3NF: A copy of online quiz 2 (Normalization) is available in the Test Center for practice (?Normalization Practice for the Midterm?). Scores on the practice quiz do not affect your grade. Expect to see several 3NF questions like these on the midterm. Database Design There will be one simple database design problem, similar to, but easier than, the problems in assignment 1. You will be given a table design that is not in 3NF, and asked to redesign it as multiple tables in 3NF with appropriate relationships. Example 1: Here is a table of college majors which is not in 3NF: Draw the relationship diagram of a database which has this data properly organized in tables in 3NF. You do not need to show any data. You also do not need to add additional fields beyond those that appear above, EXCEPT for surrogate primary keys where needed. Example 1 Solution: Note: SchoolID is the added surrogate key in this example. Database Design Example 2: Note that players can only play for one team in this league. Draw the relationship diagram of a database which has this data properly organized in tables in 3NF. You do not need to show any data. You also do not need to add additional fields beyond those that appear above, EXCEPT for surrogate primary keys where needed. Example 2 Solution: Note: TeamID is the added surrogate key in this example. Reminder: There will be only ONE of these database design problems on the midterm. It may involve a many-to-many relationship like example 1, or an one-to-many relationship like example 2. All that you need to show on the exam is the relationship diagram as shown above. SQL Study the SQL Practice Quiz available in Resources/Midterm Materials. All SQL questions on the midterm will be similar to the questions in the SQL Practice Quiz. VB Code You will not be expected to write more than one line of code for any question. You may be asked to find and correct an error in a section of code. You will also be expected to be able to follow VB code and determine the values of variables. These problems will be similar to those in Online Quiz 7. 52-56: 1 56-60: 2 60-64: 3 64-68: 4 68-72: 5 72-76: 6 76-80: 7 80-84: 8 84-88: 9 88-92: 10 92-96: 11 96-100: 12