Due date and time: Friday, August 4, 11:59pm

---

Introduction

Thus far, we've focused our energies on the task of querying an existing database, which is a centrally important skill in the field of data management. However, when it comes to real-world database applications, being able to write queries is only a relatively small part of the battle. One of several other necessary skills is the ability to design a new database.

Database design is a complex undertaking, requiring both technical prowess and an understanding of the rules and constraints governing the underlying enterprise. This assignment will begin to cultivate your understanding of database design, by asking you to engage in a few of the tasks involved in the design of a database application, assuming that it has already been decided what tables will be needed and what columns they'll require. A future assignment will allow you to explore how to decompose the system requirements into a set of necessary tables and columns.

---

The application

In a single-person software development effort, it's often unnecessary to formally keep track of defects and problems; often, it's enough to write things down on sticky notes or keep a "to do" list in a text file. When a development effort involves a larger number of people — be it an in-house development project at a single company site, or a large open-source project with contributors spread throughout the world — it becomes much more important to centrally track bugs and feature requests, so that one person can report a defect or feature request, the task can be assigned to the appropriate person who will make the necessary changes, and it is evident to all involved who the responsible party is. Also of use is a discussion thread included with each defect or feature request, so that developers responsible for fixing bugs and the people who reported them can clarify any issues as the fixes are made and tested.

For this assignment, we'll consider some aspects of the design of a hypothetical Web-based defect-tracking system called Stinkbug. Stinkbug's primary goal is to track defects in software systems, where a defect is defined as something at least one user believes is "wrong" with the system.

The data requirements of the system follow.

• For each user, the system knows the user's email address, a password, and name. Each user may have only one email address specified, and no two users may have the same email address. (Passwords are stored in the database unencrypted; this is an obvious security problem, but not something we should be concerned with in this assignment.)
  • Users will log into the system by specifying their email address and password.
  • Users are never removed from the database once they are added, though the email address of a user can be changed at any time, so long as the new email address does not already belong to another user.
• A defect is comprised of a unique defect ID, a brief description, a status, a severity, an indication of what user reported it, and the date and time it was reported.
  • The defect ID is a positive integer.
  • The brief description is no longer than 100 characters.
  • The status of a defect is indicated by an integer code. The integer code has a brief description associated with it, such as "New" or "Closed".
    • The description associated with a status code can be changed over time, but the status code itself cannot be removed from the database so long as any defect has that status code.
    • No two status codes are permitted to have identical descriptions.
    • The description associated with a status code must not be longer than 20 characters.
  • The severity of a defect is indicated by an integer code. The integer code has a brief description associated with it, such as "Critical" or "Feature Request".
    • The description associated with a severity code can be changed over time, but the severity code itself cannot be removed from the database so long as any defect has that severity code.
    • No two severity codes are permitted to have identical descriptions.
    • The description associated with a severity code must not be longer than 20 characters.
  • Defects are never removed from the database once they are added.
• Each defect has zero or more comments associated with it. Comments are used as a means for users to discuss defects. A comment is a (potentially long) string of text posted by a user. Each comment is attached to a particular defect, and has a sequence number that is unique among the comments attached to that defect, but may be the same as the sequence number for a comment attached to a different defect. (For example, the first comment associated with each defect might have the sequence number 1, the second comment might have the sequence number 2, and so on.)
  • Sequence numbers must be positive integers, though they are not required to be consecutive.
  • A comment can be removed by the user who posted it.
  • Stored with each comment is the date/time that the comment was posted.
• Each defect can be assigned to any number of users. When a defect is assigned to a user, the presumption is that the assigned user needs to play some role in fixing the defect; that role is described by a brief string of text in the database.
• Each defect is related to some component of the overall system for which Stinkbug is managing defects.
  • Each component has a unique integer ID and a name.
  • Component ID's are positive integers.
  • No two components are permitted to have the same name.
  • Components are never removed from the database. The name of a component can be changed, but its ID will never change.
• No attributes in the database can ever be NULL.

With the requirements above in mind, the database will require the following tables. Columns that are part of the primary key for a table are underlined.

• user(email_address, password, user_real_name)
• defect(defect_id, description, status, severity, reporting_user, reported_date_time, component_id)
• comment(defect_id, sequence_number, posting_user, comment_text, posted_date_time)
• component(component_id, component_name)
• defect_assignment(defect_id, assigned_user, role)
• status_code(status, status_description)
• severity_code(severity, severity_description)

The database design is required to include whatever mechanisms will help ensure the integrity of the data, according to the requirements above; particularly because the system is not intended to handle a high volume of transactions, the cost of ensuring data integrity is well worth it, relative to the cost of having incorrect data in the database.

---

Starting point

I have provided an SQL script that creates (or re-creates) the Stinkbug database. In its provided form, it creates the database and its tables, without specifying any integrity constraints on the tables, and without including any of the other necessary parts of the design. You are required to use this script as a starting point for your work, which is described in the next section.

---

Your tasks

Part 1: Defining integrity constraints (30 points)

Augment the CREATE TABLE statements in the provided SQL script so that the integrity constraints (including primary key constraints) discussed or implied by the data requirements above will be verified by the database engine whenever possible. The changes you make should be supported by MySQL; include CHECK constraints, even though MySQL will ignore them. None of the constraints will require defining an assertion; avoid the use of assertions, since MySQL does not even parse, much less support, assertions. (In fact, I have yet to find an industrial-strength database product that does. Anyone know otherwise? I'm curious.)

You are not permitted to change the names of the tables, nor are you permitted to change the names or types of any columns, though you may find it necessary to create the tables in a different order than the one given in the provided SQL script; if so, you can rearrange the order of the table creations.

Part 2: Defining views (20 points)

Add SQL statements that add each of the following views to the Stinkbug database.

• assigned_component_defects(component_id, component_name, assigned_defect_count)
  • assigned_defect_count is defined as the number of defects associated with a particular component whose status descriptions are 'ASSIGNED'.
• defect_summary(defect_id, description, status_description, severity_description, reporting_user_email, reporting_user_real_name, reported_date_time, component_name, initial_comment)
  • This view gives a summary of information for each defect, by pulling related information together from various tables.
  • initial_comment is defined as the comment that has the minimum sequence number among all of the comments associated with the defect. (You may assume that every defect has at least one comment associated with it.)

Part 3: Defining and using stored procedures and functions (30 points)

Write SQL statements that add the following stored procedures and functions to your database. You are permitted to make use of the views you defined in Part 2, if you wish, though it is not required.

• A function called defect_count_by_status, which takes a status description and returns the number of defects in the database that have that status description. For example:

      SELECT defect_count_by_status('CLOSED');

would return the number of bugs with the status description 'CLOSED'.
• A procedure called add_defect, which takes six parameters:
  • The email address of the user who reported the defect.
  • The brief description of the defect.
  • The description (not the status code) of the status of the defect.
  • The description (not the severity code) of the severity of the defect.
  • The name of the component that exhibits the defect.
  • The text of the posting user's initial comment about the defect, which presumably contains a more detailed description of the defect. This parameter may be NULL, in which case the posting user has not included an initial comment.
  The procedure adds a new defect to the database, using the information provided in the parameters, along with the following rules:
  • The defect_id for the new defect is chosen to be one greater than the maximum existing defect_id. (For example, if the maximum existing defect_id is 1267, the new defect_id would be chosen as 1268.
  • The reported_date_time is chosen to be the current time. (Note that the NOW( ) function in MySQL, which takes no parameters, returns the current date/time. In a stored procedure/function, it returns the time that the procedure/function began its execution, so that multiple calls to NOW( ) within the same execution of a procedure/function will always return the same date/time.)
  • If the initial comment text is non-NULL, a row is added to the comment table, with the posting_user being the user who reported the defect, the posted_date_time being the current time, and the sequence_number being 1.
  Once defined, the following statement will add a new defect into the database.

      CALL add_defect('alex@thornton.com', 'Stinkbug needs stored procedures',
          'NEW', 'FEATURE REQUEST', 'Database',
          'Stinkbug needs stored procedures, so that it will be easier to work with the database!');
  

In lecture, we've covered a fair amount of syntax for stored procedures and functions in MySQL, but you'll almost inevitably have questions as you work. We'll gladly answer questions, though you might be able to get a quicker answer by checking out the MySQL 5.0 documentation regarding stored procedures and functions, or other parts of the MySQL 5.0 Reference Manual, as necessary.

Part 4: Considering the effect of database design decisions (20 points)

One of the challenges of database design is making the necessary trade-offs, striking the appropriate balance between, say, clarity and efficiency, or between avoiding duplication of information in the database and allowing useful queries to be written without requiring joins. I made several of these kinds of decisions while designing the tables for this assignment, and I'd like you to consider a few of them by answering the following questions.

1. I selected each user's email address as the primary key for the user table, since each user is only allowed to have one email address, and no two users may have the same email address. This choice will work, since email addresses uniquely identify users, but there are performance implications. If this system were used in practical circumstances, users would need to be allowed to change their email addresses. What changes must be made in the database whenever a user changes his/her email address? How could the database have been designed differently to allow an email address change to be implemented more efficiently?
2. The view assigned_component_defects could also be implemented as a table, with the information physically stored in the database. What are the trade-offs involved with the decision to implement this as a view or as a table? What are the advantages of implementing assigned_component_defects as a table? What are the advantages of implementing it as a view?

---

Documentation

As you'll notice in the provided SQL script, it is possible to include comments in an SQL script by beginning a line with a '#' character. While you are not required to write a lot of documentation for this assignment, I would like you, at the least, to point out which portions of the script address which of the tasks you've been assigned. This will make it easier for us to grade your work accurately. It's especially important to include your answers to the questions posed in Part 4 as comments in your SQL script, so that your entire solution to this assignment will be contained in one file for ease of grading.

---

Some MySQL odds and ends

Using the DATETIME type

The DATETIME data type in MySQL is similar to the DATE type that we used in the previous assignment. The key difference is that DATETIME stores both a date and a time, while DATE stores only a date. MySQL accepts a number of formats for DATETIME values, but I suggest using the 'YYYY-DD-MM HH:MM:SS' format. For example, to insert a new defect into the database reported at February 20, 2006 at 3:24:55pm, you might execute this INSERT statement:

    INSERT INTO defect VALUES(101, 'Stinkbug needs integrity constraints',
        5, 3, 'alex@thornton.com', '2006-02-20 15:24:55')

Using the TEXT type

The TEXT data type in MySQL represents a string of text that could potentially be quite long. Syntactically, the TEXT type behaves no differently from the other character-string types, such as CHAR and VARCHAR; TEXT just has a longer length limit, and is treated differently internally. The exact length limit depends on the platform and MySQL version, but suffice it to say, for our purposes, that TEXT columns will be plenty long enough whenever we've used them.

The need for the DELIMITER statement when declaring a stored procedure or function using the mysql command-line utility

When using the mysql command-line utility, semicolons are a way to indicate that you've supplied all of the input you plan to supply for one SQL statement. (This is the only way for mysql's parser to know when a statement has ended.) Unfortunately, the syntax for stored procedures and functions also uses semicolons, as a way of terminating statements within a stored procedure/function. So, when you create a procedure or function within the mysql utility using the CREATE PROCEDURE or CREATE FUNCTION statements, semicolons within the procedure/function will fool mysql into thinking that you've finished the entire CREATE PROCEDURE command, even though you haven't.

The solution to this problem is to temporarily tell mysql to use something other than a semicolon as a delimiter, the indicator that specifies when a complete SQL statement has been entered. Then, use the new delimiter to terminate the CREATE PROCEDURE or CREATE FUNCTION statement, and, finally, change the delimiter back to semicolon. An example follows.

    DELIMITER //
    
    CREATE FUNCTION always_zero()
    BEGIN
        RETURN 0;
    END;
    //
    
    DELIMITER ;

You'll need to do this in your SQL script, because SQL scripts are executed as though they had been typed into the mysql command-line utility.

---

Deliverables

Submit a single SQL script that (re-)creates your Stinkbug database, built using the provided SQL script as a starting point. At the bottom of this script, include (as comments) your answers to the questions asked in Part 4 of the assignment.

Please do not include statements that populate your database with test data. We'll supply our own test data if we need it.

Follow this link for a discussion of how to submit your assignment. Be aware that I'll be holding you to all of the rules specified in that document, including the one that says that you're responsible for submitting the version of the assignment that you want graded. We won't regrade your assignment simply because you submitted the wrong version by accident.