Frequently Asked Questions

Getting Started

• How can I find out about MIS jobs, internships and scholarships?
• Do I need to take MIS 304 before MIS 325?
• I'm registered for the 9:30 section. Can I attend the 11:00 lectures?
• I misplaced one of the handouts. Are there any extras?
• Are we responsible for the FYIs?
• How about the FAQs? Are we responsible for them?
• Can I use a laptop during your lectures?
• I don't have an account to use the computers in the McCombs' labs. How do I get one?
• How many homework points will there be this semester?
• What is the policy on late homeworks?
• Do you throw out any low homework grades?
• What should I do if I think a homework or exam has been misgraded?
• I forgot my Class ID. Can you reset it so I can check my grades?
• Is it actually possible to escape from Yesterworld?

Database Concepts

• What is the difference between a primary and foreign key?
• Should every table have a primary key?
• Should every table have a foreign key?
• Should every foreign key have a primary key?
• Should every primary key have a foreign key?
• Can a table have more than one primary key?
• Can a table have more than one foreign key?
• Can a field be a primary and a foreign key at the same time?
• What is a composite key?
• What is a secondary index?
• How are secondary indices stored?
• What is referential integrity?
• What is the difference between 1:1, 1:N and M:N relationships?
• How are 1:1 relationships stored?
• How are 1:N relationships stored?
• How are M:N relationships stored?
• Can a bridge table be related to only one table?
• Can a bridge table be related to more than two tables?
• Can a bridge table be used to represent a 1:N relationship or is it only used for M:N relationships?
• What is an optional relationship?
• What is a unary relationship?
• The unary relationship in the previous FAQ was shown as optional in BOTH directions. Is that a correct?
• Is a unary relationship the same as a one-to-one relationship?
• Can a unary relationship be many-to-many?
• Does a unary relationship have to be many-to-many?
• Can a table have more than one unary relationship?
• What is normalization?
• What is de-normalization?
• What is 1NF?
• What is 2NF?
• What is 3NF?
• Can a database be in 2NF and 3NF, but not in 1NF?

Visio

• I'm having problems building relationships in Visio. Any suggestions?
• How do I convert Visio's arrowhead style of relationship into the crow's foot style?
• How do I create the FK in Visio's crow's foot diagram?
• My primary keys show up at the top of each table above a horizontal dividing line. How do I display them in their proper position without the dividing line?
• How do I make relationships optional in Visio?
• How do I change a 1:N relationship to 1:1?
• How do I draw unary relationships in Visio?
• What is the difference between Visio's identifying (solid) and non-identifying (dotted) relationships?
• Do I need to worry about the difference between identifying and non-identifying relationships?
• Can Visio draw relationships as straight lines instead of 90 degree lines?
• Can Visio draw relationships as curved lines, too?
• How do I adjust the size of the tables?
• How do I add my name to the ER diagram?
• How do I print in landscape orientation?
• I created a Visio diagram on a lab computer. Can I print it on my own computer if I don't have Visio?
• I heard you briefly mention reverse engineering. What is it?
• Can Visio create drawings with shapes from more than one template/stencil?
• Can I install Visio on my own computer?

Assignment One

• Is the horizontal dividing line between the PK and non-PK fields acceptable for this assignment?
• Do I need to specify the data type for each field?
• Your spousal FK is in the table of Men, but mine is in the table of Women. Why?
• Does the current MarriageDate need to be stored in the table of Men and Women or just in the table of Men?
• Is the relationship for former marriages a M:N relationship?
• How do I split a M:N relationship into a bridge table and a set of 1:N relationships?
• Can the M:N bridge table for former marriages replace the 1:1 relationship for current marriages?
• Does the marriage bridge table need a primary key?

Assignment Two

• Should the ER diagram have six tables and eleven relationships?
• Can the foreign key fields in a bridge table be defined as optional (null) or should they be defined as required (non-blank)?
• I created a bridge for the M:N relationship between the Staff and Appointment tables. Should I use StaffID and AppointmentID as a composite primary key or should I make up a new field as the primary key of the bridge?
• Does condition 10 mean that I have to keep track of the actual quantities used?
• Can non-key data fields in different tables have the same name? For instance, can LastName be in the Patient table and in the Staff table?
• Don't forget to check the FAQs for Visio (above)

Assignment Three

• Can a child's FK field have a different name than the parent's PK field?
• How did you display the Mom/Dad relationship names in the previous FAQ?
• Can I create extra fields like CustomerID or should I only use the ones in the initial table?
• Should rental pick ups and returns be represented by one table or two?
• What is a pickup and return location? Is it the same thing as a customer address?
• Are dates compound fields?
• Can CurrentOdometer be computed?
• Can CurrentLocation be computed?
• Is a Mustang always a Ford?
• Is CarID a compound field that should be split into four individual fields?
• Should the street address be split into a street number and a street name?
• Should the street and apartment be split into two fields?
• I understand that the city and state abbreviation cannot be stored in the customer table because they depend on the zip code. What about the street and apartment?
• Don't forget to check the FAQs for Visio (above)

Exam One

• Why did you make the child side optional for the 1:N relationship between tblFlight and tblFlightCrewBridge in the Normalization Practice Problem? Planes can't fly without a crew.
• Why did you make the child side mandatory for the 1:N relationship between tblPassenger and tblFlightPassengerBridge?
• Why didn't you have a Sponsor table in your solution to question 3 of the sample exam?
• Could you use JerseyNumber as the PK of the Players table for question 3 of the sample exam?
• I don't understand why you included CurrentOfficeID and CurrentOdometer in your solution to Assignment Three. Don't they violate 3NF?
• Why did you have the tblManufacturer table in your solution to Assignment Three? Is it really necessary?
• Do I need to know BCNF, 4NF, 5NF, etc?
• Don't forget to check the other FAQs… particularly for Database Concepts (above)

SQL Server's Management Studio

• How do I change my SQL Server password?
• What do I do when Management Studio says I'm not the owner or administrator?
• What do I do when Management Studio says saving changes is not permitted?
• What do I do when Management Studio says it is unable to start the Transact-SQL debugger?
• What do I do when Management Studio says it cannot find CurrentSettings-2009-##-##.vssettings?
• What is a Transport-Level Error?
• What do I do when Management Studio says I am not associated with a trusted SQL Server connection?
• What do I do when Management Studio says it does not allow remote connections?
• What is a VPN connection?
• What do I do when Management Studio says it cannot find my file?
• Can I install SQL Server on my own computer?
• I created a sorted view, but the records aren't sorted when I re-open them. What happened?

SQL DDL

• How do I create a new table?
• How do I modify an existing table?
• How do I modify an existing table? (Part Two)
• How do I create a simple primary key?
• How do I create a composite primary key?
• How do I create an identity/autonumber primary key?
• How do I create a secondary index?
• How do I create a validation rule or check constraint?
• Is it possible to validate that a SSN has the proper ###-##-#### format?
• Is it possible to validate that a zip code has the proper format?
• How do I define a 1:N relationship between tables?
• How do I enable cascading for a relationship?
• How do I discard a table?
• Why can't a view include an Order By clause?

SQL DML

• How do I insert data into a table?
• How do I copy records from one table to another?
• How do I update data in a table?
• How do I delete records from a table?
• Why does 'Smith' have quotes, but 123.45 does not?
• What is the difference between SELECT, SELECT ALL and SELECT TOP 100 PERCENT?
• What is the difference between SELECT and SELECT DISTINCT?
• I can find people from Austin, Tx. How do I find the ones not from Austin, Tx?
• What does LIKE mean in a WHERE clause?
• What is the difference between NULL, '' and ' '?
• How is the ROUND function used?
• What is the difference between WHERE and HAVING?
• Why does 'SELECT * FROM tblA GROUP BY X' generate an error message?
• Is 'GROUP BY X, Y, Z' the same as 'GROUP BY Z, Y, X'?
• What is the difference between COUNT(X) and COUNT(DISTINCT X)?
• I know how to count records and I know how to count non-null values, but how can I count null values?
• Can I use the COUNT function without using a GROUP BY clause?
• How can I identify the largest/smallest value in a table?
• How can I identify the five largest/smallest values in a table?
• What does the 'ORDER BY items must appear in the select list if SELECT DISTINCT is specified' error message mean?
• Is a join the same thing as a relationship?
• What is the difference between an inner and an outer join?
• What is the difference between a left, right and full outer join?
• Is 'X inner Y' the same as 'Y inner X'?
• Is 'X left Y' the same as 'Y right X'?
• Is 'X inner Y inner Z' the same as 'Z inner Y inner X'?
• Is 'X left Y inner Z' the same as 'Z inner Y right X'?
• How can I identify the records in the crescent moon?
• What is the difference between an inner and a cross join?
• How do I join three (or more) tables?
• Can you explain how a self join is implemented?
• Is there such a thing as an outer self join?
• What is a subquery?
• Can subqueries be re-written as joins and vice versa?
• How can I create a combined list of all employees and customers?
• What is the difference between UNION and UNION ALL?
• Can the individual sections of a union be sorted?
• But what if I need to sort the individual sections of a union?
• How do you sort concatenated names in a union?
• Can a union display X columns from one table and Y columns from another table or do the number of columns need to be the same?
• What is the difference between UNION, INTERSECT and EXCEPT?

Assignment Four

• Should I use GUI or SQL to create my tables?
• How do I force the zip code to have exactly five digits?
• How do I force the phone number to include 14 to 25 characters?
• How do I allow ReturnDateTime to either be null or sometime after PickupDateTime?
• Why does my ReturnDateTime validation generate an error saying that it is referencing another column?
• Does 'between 2003 and 2009' include the endpoints?
• Is it OK if the ContractIDs are numbered 3, 4, 5, etc instead of 1, 2, 3, etc?
• Does tblCar have two simple indices or one composite index? Are they the same?
• I created all of the secondary indices, but the records still show up in their original order when I open the tables. How do I fix this?
• Should the secondary index on tblPostalLocation be City/State or State/City?
• I created the secondary indices. How do I know if they actually work?
• Why do I get a 'conversion of char to int' error for my State and Zip values?
• All of my records are being rejected when I try to enter them into tblRentalContract. Why?
• I created some bad records in my tables. Is there an easy way to get rid of them?
• Don't forget to check the FAQs for SQL Server and DDL (above)

Assignment Five

• Should this assignment be done in 20 separate steps or in one big batch?
• What is mis325.dbo.tblEmployeeHW5 is step 5?
• How am I supposed to save the 20 SQL commands?
• OK, if I don't save my SQL commands, how will the TAs know that I did the assignment?
• Oops, I skipped a step. What do I do now?
• So, what are the answers to the various questions you posed?

Assignment Six

• How do I check the syntax of my SQL commands?
• I named my views A6T1, A6T2, etc instead of qryA6T1, qryA6T2, etc. Is that OK?
• Management Studio added TOP 100 PERCENT to my view. Is that OK?
• What does 'The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.' error message mean?
• Should all concatenated names be separated with blanks?
• Can I concatenate the names as LastName_FirstName instead of FirstName_LastName? It sure would make my sorting easier.
• Are Tasks 1, 2, 3, 5, 6, 7 and 10 for current employees or all employees?
• Why does WHERE LastName BETWEEN 6 AND 8 generate an error for Task 5?
• How do I find the longest name?
• Why doesn't WHERE Phone LIKE '(512)' work?
• OK, should I use LEFT, SUBSTRING, BETWEEN or LIKE for the 512 area codes?
• You said this assignment could either be done with the CREATE VIEW command or with the GUI New View interface. Which way is best?
• I created views for each task, but the records aren't sorted when I examine them. What happened?
• How is this assignment going to be graded?
• Don't forget to check the FAQs for SQL Server and DML (above)

Assignment Seven

• Do I need to add four extra tblEmployee records like I did for Assignment Seven?
• How do I use Management Studio to create an aggregation query (view)?
• What does QBE mean in the previous FAQ?
• What is STDEV?
• Why doesn't "WHERE Gender='F' AND Gender='M'" find the JobTitles that have both genders?
• Can I break a task into multiple steps/views?
• Don't forget to check the FAQs for SQL Server and DML (above)

Assignment Eight

• How do I join tblDiscipline to tblFaculty?
• How do I join tblDiscipline to tblStudent based on their major?
• How do I join tblDiscipline to tblStudent based on majoring OR minoring?
• I'm confused by your answer to the previous question. Why is it 'ON ... OR ...' instead of 'ON ... AND ...'?
• Management Studio says 'ON ... AND ...' but you say 'ON ... OR ...'. Which one is correct?
• Why doesn't FROM tblStudent, tblCourse work for Task 2?
• How can three tables be joined for Task 2?
• Can three tables be joined even if one of them is not a bridge as in the previous question?
• What does 'Ambiguous column name SID' mean?
• Why are some of the SIDs missing/blank/null for Task 3?
• I am confused by the meaning of 'Total Number of Sections Taught' in Task 8. Is it the number taught by the rank or by each person?
• Why do COUNT(Instructor) and COUNT(CID) give me the same thing? Shouldn't one give me the number of Instructors and the other give me the number of sections?
• Why does my Task 8 average of 5/3 round down to 1?
• Can I add additional fields to tblFaculty?
• Don't forget to check the FAQs for SQL Server and DML (above)

Assignment Nine

• Some of these tasks look like multi-step problems. Can I use the 'cheating' approach and hardwire the step 1 results into the step 2 code?
• If I decompose a task into multiple steps, then should I use the multi-view approach or the subquery approach?
• If I use the multi-view approach, then what should I name the views for each of the steps?
• Can I use qryA9T4 as the first step of my solution to Task 5?
• I don't want to use qryA9T4 as the first step of my solution to Task 5. How can I sort the concatenated names?
• Don't forget to check the FAQs for SQL Server and DML (above)

Exam Two

• How should I prepare for the exam?
• Sample exam? What sample exam?
• Can you explain the relationships in the ER diagram for the sample exam?
• The suggested solution to question 1 on the sample exam uses OR. Shouldn't it be AND?
• The suggested solution to question 6 on the sample exam uses two steps connected with a join. Can the two steps be connected by a subquery instead of a join?
• The suggested solution to question 6 on the sample exam uses two steps. Why didn't you do it in one step?
• How do I save the INSERT and UPDATE commands for questions 8 and 9 on the sample exam?
• How can questions 8 and 9 be combined into a single INSERT command?
• You asked the class to determine how to fix the 'employment gap' problem with the simplified longevity pay update example. Can you show us how?
• Don't forget to check the other FAQs… particularly for SQL DDL and SQL DML (above)

ASP and HTML

• What is ASP?
• You mentioned "flat files" a few times this semester (including in the previous FAQ). What are they?
• How do I connect to Classweb2 from a lab computer?
• How do I connect to Classweb2 from my home computer?
• OK, how do I get to my own files once I've connected to Classweb2?
• How do I examine the sample ASP files that you put in my Demo subdirectory?
• I tried to use the sample ASP files, but they blow up. Why do I get the error message: "Cannot open database MIS325_msbaa001 requested by the login"
• I tried to use the sample ASP files, but they blow up. Why do I get the error message: "Invalid object name: tblEmployee"
• Single quotes, double quotes or no quotes. What gives?
• What do <% and %> mean?
• What is Response.Write?
• What is Request.Form?
• What are BackEnd and conn?
• What is rst?
• How do I process (i.e., loop through) the records in a recordset?
• What are rst.EOF and rst.MoveNext?
• Why do I get the error message: "Object Required"?
• Why do I get the error message: "Item cannot be found in the collection corresponding to the requested name or ordinal"
• Why do I get the error message: "Overflow"
• Why do I get the error message: "Buffer Overflow"
• Why do I get the error message: "Script Timed Out"
• How do I concatenate names in a recordset?
• How do I aggregate records in a recordset?
• Can I join multiple tables in a single recordset?
• Can ASP access a SQL Server view/query instead of a table?
• How do I display information in a grid?
• How do I display information in a drop-down list?
• How do I create a functional button?
• How do control the size of my buttons?
• How do control the size of my input text boxes?
• How do control the size of my drop-down lists?
• What is the difference between Size and MaxLength for an input text box?
• What are Replace and Trim?
• What are LCase and UCase?
• What are Left, Right and Mid?
• What is Len?
• What is If-Then?
• How do I determine if the user entered a value in a text box, or a drop-down list, or a radio button or a check box?
• What are Errors and ErrorMessage?
• How do I determine if the user entered a numeric value?
• How do I determine if the user entered a valid date?
• How do I determine if the user entered a valid (i.e., unique) primary key?
• How do I determine if the user entered a valid foreign key?
• How do I determine if the user entered a valid state abbreviation?
• How do I execute an action command?
• How do I execute a number of action commands in one batch?
• How do I control the display format for currency values?
• How do I control the display format for numeric values?
• How do I control the display format for date/time values?
• I tried to use Word instead of Notepad to edit my ASP files. Unfortunately, Word gives me curly quotes instead of straight quotes. How do I change this?
• Are there any web sites for learning ASP?
• Is there an easy way to debug an ASP file?
• Internet Explorer says I have an error on line 200 of my ASP file. Counting lines will take forever. Is there an easy way to find the line?
• Internet Explorer says 'The page cannot be displayed. (HTTP Error 500)' How do I fix this?

Assignment Ten

• How do I view my pages with the browser?
• Where should I store my photograph?
• I can see my photograph, but my study partner can't. Why?
• My photograph is very, very slow to display. Why?
• How do I reduce the size of my photograph?
• Why do I get the error message: "Cannot open database MIS325_msbaa001 requested by the login"
• Why do I get the error message: "Not associated with a trusted SQL Server connection"
• Why do I get the error message: "Invalid object name tblEmployee"
• Why do I get the error message: "Permission denied on object tblEmployee"
• Why do I get the error message: "Login failed for user MCCOMBS\mis325_msbaz123"
• Why do I get the error message: "Violation of PRIMARY KEY constraint PK__tblEmployee"
• How do I eliminate the digits after the decimal point in the paycheck amounts?
• Don't forget to check the FAQs for Active Server Pages (above)

Assignment Eleven

• How do I view my pages with the browser?
• I'm a little lost. How should I begin this assignment?
• Do I need to worry about case sensitivity?
• Don't forget to check the FAQs for Active Server Pages (above)

Assignment Twelve

• How can I change the discipline's abbreviation (ACC) into the name (Accounting) for Task 1?
• Should I use SQL or VB to convert a first name into an initial for Task 2?
• Should I use a join or a subquery for the Fall 2009 filter in Task 2?
• How do I create a stepped report for Task 3?
• Can I use a second recordset for the bonus subtotal rows?
• Don't forget to check the FAQs for Active Server Pages (above)

Assignment Thirteen

• Should I use Trim or Replace to sanitize Dept?
• Do I need to validate that the user's input data is not too long?
• Why do I get the message 'Conversion failed when converting varchar value to data type int' when I run Task 1?
• How can I determine if the FID value entered by the user is actually a number?
• How can I determine if the user entered a legal Dept for Task 1?
• Why do I get the message 'INSERT statement conflicted with the FOREIGN KEY constraint' when I run Task 1?
• Why do I get the message 'String or binary data would be truncated' when I run Task 1?
• Why does my Task 1 script tell me that I have a syntax error near (512) 471-9436?
• Can I use Management Studio to create a view for the Task 2 aggregation query?
• Do I have to create a Management Studio view for Task 2?
• I'm probably making this harder than it should be, but I don't understand how to use a view. Can you give me an example?
• OK, OK. Should I create a Management Studio view for Task 2?
• Your Task 2 report shows Fine Arts, but mine does not. Why?
• I inserted a bunch of tblFaculty records while doing this assignment. Do I need to delete them for grading?
• Don't forget to check the FAQs for Active Server Pages (above)

Exam Three

• I've heard some pretty scary stories about the third exam. Are they true?
• What is the policy on skipping the third exam?