Frequently Asked Questions
Getting Started
Database Concepts
Visio
Exam One
SQL Server
SQL DDL
SQL DML
Exam Two
Active Server Pages
Assignment One
(Visio Basics)
Assignment Two
(DB Design)
Assignment Three
(Normalization)
Assignment Four
(DDL)
Assignment Five
(DML)
Assignment Six
(Single Table Selects)
Assignment Seven
(Single Table Aggregation Selects)
Assignment Eight
(Multi-Table Selects)
Assignment Nine
(Special Selects)
Assignment Ten
(HTML & ASP)
Assignment Eleven
(ASP)
Assignment Twelve
(ASP)
Assignment Thirteen
(ASP)
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?