General Details

Due Date: 2019-09-18, Wednesday of week 04

The following guidelines are expected for all homework submissions:

• All homework must be typed. Homework which is not typed will be returned ungraded and will be subject to the late homework guidelines as set out on the syllabus page. PLEASE DO NOT scribble something on lined paper and rip it out of your spiral notebook — hanging chads went out with the presidential election in 2000.
• I don't care too much about what font you use or how large your margins are; however, you might want to check out a monospaced font for typing code, as it will be easy to see the indentations.
• Speaking of indenting, PLEASE DON'T USE TABS TO INDENT YOUR CODE. Tabs can often get interpreted differently by different computers and applications, and could make code that is nicely formatted on your computer look "all over the map" on my computer or printer. USE SPACES INSTEAD. You can set up almost every modern text editor to insert spaces whenever you press the TAB key, or you can simply pound the spacebar.
• Work with a partner. This mimics an industry code development model called "pair programming" which is part of the Extreme Programming software development method. Feel free to collaborate in your pairs as much as you want, preferably doing the entire assignment together. If you would like to have the same homework partner that you have for your database semester project, that is a convenient way to handle things. HOWEVER……
• DO NOT share your work between groups. Doing so will count as plagiarism. If you wish to discuss solutions with another group over coffee in the Lair, that's fine as long as it is kept at the conceptual level, but each group needs to turn in its own version of the solutions.
• In addition, DO NOT DIVIDE UP THE WORK on your homework assignments. You MUST work on the entire assignment together. Failure to do so will cost you a letter grade on the assignment, so it is enlightened self-interest to do things this way.
• You only need to turn in one copy of the assignment per group.
• If you'd like to use the sqlfiddle website to do your programs, that is great. Simply save the program in jsfiddle as a shared fiddle, then copy and embed a link to the fiddle into your homework where the answer goes for that problem. I'll use the link to go to YOUR fiddle and run the code. To save your fiddles you'll need to sign up for an account…
• You should submit your homework in a GitHub repository. MAKE SURE YOUR GITHUB REPO IS PRIVATE [for the reasons explained on the syllabus page].

Problems From Chapter 1

• Ch 1, #13: Give examples of systems in which it may make sense to use traditional file processing instead of a database approach.

• Ch 1, #14: Consider Figure 1.2 [see page 8 of the Elmasri book].
  
  1. If the name of the 'CS' [Computer Science] Department changes to 'CSSE' [Computer Science and Software Engineering] Department and the correspondingg prefix for the course number also changes, identify the columns in the database that would need to be updated.
  2. Can you restructure the columns in the COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated?

Problems From Chapter 2

• Ch 2, #3: What is the difference between a database schema and a database state?

• Ch 2, #7: Discuss the different types of user-friendly interfaces and the types of users who typically use each.

• Ch 2, #14: If you were designing a Web-based system to make airline reservations and sell airline tickets, which DBMS architecture would you choose from Section 2.5? Why? Why would the other architectures not be a good choice?

Problems From Chapter 3

• Ch 3, #2: Why are tuples in a relation not ordered?

• Ch 3, #5: Why do we designate one of the candidate keys of a relation to be the primary key?

• Ch 3, #9: Define foreign key. What is this concept used for?

• Ch 3, #13: Consider the relation CLASS(Course#, Univ_Section#, Instructor_name, Semester, Building_code, Room#, Time_period, Weekdays, Credit_hours). This represents classes taught in a university, with unique Univ_section#s. Identify what you think should be various candidate keys, and write in your own words the conditions or assumptions under which each candidate key would be valid.

• Ch 3, #20 [part c only]: Recent changes in privacy laws have disallowed organizations from using Social Security numbers to identify individuals unless certain restrictions are satisfied. As a result, most U.S. universities cannot use SSNs as primary keys [except for financial data]. In practice, Sutend_id, a unique identifier assigned to every student, is likely to be used as the primary key rather than SSN since Student_id can be used throughout the system.
  c. What are the advantages and disadvantages of using generated [surrogate] keys?