1.) Several years ago, the college developed an "in-house" enrollment system; it was developed for client-server and had all of the database control and logic built into the application. When it was designed the college had 1/5 of its current student population. During student enrollment periods (October, August), when there are several people entering records the system simply "locks up", and no one can enter any additional information. Eventually, the system will release and most of the users get a cryptic "Your changes were not saved; another users' changes have overwritten yours." The situation has become critical, and the college President and the VP of MIS wants to understand what is happening. They have hired you to assess the situation, what do you think is happening?
2.) You have been hired as a database administrator for a local financial services company. There are several analysts who have written queries against the transaction table for you company. It has millions of records. The analysts are complaining that their queries have gotten progressively slower over the past year. Please provide me with three suggestions on what you could do to help them improve their query performance?
3.) You have two tables with identical structure, CUSTOMER and CUSTOMER_2. (Field names: custID, CFName, CLName, CAddr1, CAddr2, CCity, CST, CZip). Create the SQL statements that would 1)list the combined rows from both tables, 2)list the matching rows in both tables, 3)list the rows in CUSTOMER that do not exist in CUSTOMER_2, and 4)list the rows in CUSTOMER_2 that do not exist in CUSTOMER. Only list the SQL, you do not need to include any results.
4.) Thomas works for a newspaper in the MIS department, the newspaper has a CUSTOMER table (Field names: custID, CFName, CLName, CAddr1, CAddr2, CCity, CST, CZip). Currently, when a new customer is added to the CUSTOMER table, a record is manually created in the BILLING table. The database is SQL Server 2008. How would you advise Thomas so that this occurs automatically each time a new record is added to the CUSTOMER table (I am not looking for any SQL code, just a verbal description will be adequate)
5.) For Gwen's Beauty Consortium, you have the logical design complete, and are ready to build the physical model. She is still unsure which DBMS system to purchase, she wants database level security, as well as column-level security (she does not want her stylists to be able to easily see what each other earns). Her company is small (<25 employees), and the transaction volume is low. She is looking to you for direction and advice, what would you recommend? (You do not need to make a specific product suggestion, I am looking for a formal approach to analyze her specific situation.)