CS3600.01 Database Management System (Fall 2009)

  • Office: Memorial 215 (X2534, zshen@mail, http://oz.plymouth.edu/~zshen)
  • Office Hour: M: 11:30 a.m. - 12:30 p.m., 1:30: p.m.-2:30 p.m.; W, F: 1:00 p.m.-2:00 p.m.; and whenever my door is open

    General Information

    In this course, we study various aspects of (relational) database management systems, particularly, how to set up well organized databases, how to ask questions about those databases, how to maintain those databases, as well as the related conceptual background; and, provide an introduction to database programming with MySQL, PhP and HTML.

    Students are strongly advised to attend every class. For those who have to miss some classes or have some class notes to make up, I have included an extended abstract of my teaching notes within this WEB based syllabus.

    Although you should not hand in any homework unless and until you are reasonably sure about their correctness; all the homework must be handed in by the end of the first business day in the following week, once a chapter is completed. There will be no exception.

    Anything and everything handed in for this course, including homework assignments and the project report, has to be readable. You should either use a word processor, or clearly spell out your work.

    Besides a lab, which will be held roughly once a week, usually on Friday from 11:15 a.m. to 12:05 p.m. in Memorial 312, students are expected to spend a significant amount of time outside the class to go through the labnotes and complete the labwork as assigned within these notes. It is strongly recommended that students read the relevant part(s) before walking into the lab.

    Anything and everything handed in for this course has to be done by the signed individual, or a group in case of the team project. If enough evidence shows that someone is involved with ``cheating'', the case(s) will be immediately reported to the Chair of the Computer Science Department. For a more detailed and specific description of University's policy on Academic Integrity, please refer to the relevant section(s) in the most recent version of the Academic Catalog.

    The instructor will try his utmost to be fair in grading students' work. On the other hand, if a student believes that s/he is not given a fair grade, s/he should follow the procedure as specified in the University Fairgrading Policy to appeal the grade.

    Textbook:

    An overview of this course

    1. An Introduction: Open things up!
    2. RBA Basics via a Case Study: Get to know it
    3. The Relational Model: Stay in fashion
      • Lab 3: Strings and functions.
      • Lab 4: Variable scope, Friday, Sept. 26, 2009 from 11:15 a.m. to 12:05 p.m.. (Memorial 312)
      • Lab 5: Recursion
      • Lab 6: Table creation and population.
        • Read Sections 1 through 2 of A Gentler Introduction to MySQl Database Programming,.
        • Complete Labwork 2.
        • Check the queries as given in pp. 12 through pp. 13 in Chapter 2 notes with the registration database. Notice that the student IDs are no longer just 4 digit long with this real instance.
        • Send me an email, telling me that you are done, together with the query results as you will get for the last piece, by 23:59:59 Wednesday, September 30, 2009.
      • Project(I): Integrety constraint construction.
        • Get together your group.
        • Check out again the details of your chosen and assigned proposal.
        • Write at least four integrity constraints for your project.
        • The project leader sends the above ICs to me in an email by 23:59:39, Wednesday, September 30, 2009.
      • Lab 7: Data passing with PhP. We have worked with a bunch of single PhP pages. We know start to work with a collection of pages and, in particular, learn how to pass data between the pages. Friday, October 2, 2009 from 11:15 a.m. to 12:05 p.m.. (Memorial 312)
      • Homework for this chapter is due by midnight, Monday, October 5, 2009.
    4. Conceptual Modeling: The big picture
      • Lab 8: PhP applications, Friday, October 9, 2009
      • Project(II): E/R diagrams and the database draft:
        • The project team gets together and discuss the project you will work with.
        • Apply the stuff we learn from this chapter to come up with the E/R diagrams for the database, which should be typed up using a computer based tool such as Visio.
        • Apply what we have learned in this chapter to convert the E/R chart to a preliminary design of the database in terms of tables, keys (primary and foreign), and other constraint. You need to attach a detailed description as why you come up with this design.
        • Send the above to me by 23:59:59, Thursday, October 15, 2009
        • Here is a sample.
      • Lab 9: More data structures, Friday, October 16, 2009
      • Homework for this chapter is due by midnight, Monday, October 19, 2009.
    5. Relational Algebra and SQL: The tough stuff
      • The classes on Friday, October 23, 2009 will be held in Memorial 213.
      • Lab 8: Simple queries, Monday, October 26, 2009 from 10:10 a.m. to 11:00 a.m.., Memorial 312.
        • Before coming to the lab, please read Sections 3.1 of A Gentler Introduction to MySQL database Programming,, really understand the stuff, and test out all the codes as I have done.
        • Complete Labwork 3.1.
        • Send in the answers to the questions in the required format to me via email by 23:59:59, Monday, October 26, 2009.
        • Here is a sample solution to some of the work in Labwork 3.1.
      • Lab 9: Set operations, Wednesday, October 28, 2009 from 10:10 a.m. to 11:00 a.m.., Memorial 312.
        • Read Sections 3.2 of A Gentler Introduction to MySQL database Programming,, really understand the stuff; and test out all the codes.
        • Complete Labwork 3.2.
        • Send in the answers to the questions in the required format to me via email by 23:59:59, Wednesday, October 28, 2009 from 10:10 a.m. to 11:00 a.m.., Memorial 312.
        • Here is a sample solution to some of the work in Labwork 3.2.
      • Lab 10: Aggregation, Monday, November 2, 2009 from 10:10 a.m. to 11:00 a.m.., Memorial 312.
      • Lab 11: View, Wednesday, November 4, 2009 from 10:10 a.m. to 11:00 a.m.., Memorial 312.
      • Midterm: From 10:10 to 11 a.m., Friday, November 6, 2009.
      • Project(III): Query design, Friday, November 6, 2009 from 11:15 a.m. to 12:05 p.m.., Memorial 312.
        • The project teams, as well as the individuals, improves the database design, sets up and populate a sample database.
        • Add in as much IC as possible into the database design.
        • Come up with a bunch of queries for the database and check them out for their correctness and appropriateness.
        • Send me a report in email that you have done the above by 23:59:59, Monday, November 9, 2009.
      • You dont' need to do any homework for this chapter, as given in the notes.
    6. Normalization: The name of the game
      • Project (IV) : How good are they?
        • Have a good look at the tables that you have come up in III, and, for each of them, identify the FDs, based on the ICs that you have developped in Part(III).
        • For each of the tables, answer the following question, and use what you have learned in this chapter to back up your answer:
          • Where does the table sit in the normalization hierarchy?
          • If this table is not in BCNF, follow the procedure which we just went through, to decompose it into a collection of tables that are in BCNF.
          • When you turn the table into BCNF, does it preserve all the FD's? If not, which ones are dropped?
        • Based on the results, revise your database design, populate those tables, and revise the queries, and check them out.
        • Send me the typed up answers to the above questions via email by 23:59:59, Wednesday, November 18, 2009.
      • Homework for this chapter is due by midnight, Monday, November 23, 2008.
    7. Using SQL in Application: Get real
      • Lab 12: PhP and MySQL(1) (This lab work should be done by the respective project teams.) Monday, November 23, 2009 from 10:10 a.m. to 11:00 a.m.., Memorial 312.
        • Read Section 5 of A Gentler Introduction to MySQL database Programming,, and really understand the stuff.
        • Complete Labwork 5. Once it is done, it should look like this, with which you can check out queries for your database.
        • Send in the URL of your script with which I can check your queries against your group database to me via email by 23:59:59, Monday, November 23, 2009.
    8. Recovery: What happens if we lose it?
    9. Concurrency: Make it run faster, but....
    10. Object Databases: Beyong the horizon

    Evaluation

    1. Solutions to some of the assigned homework (20%)
    2. General information and additional scripts related to lab work (20%)
    3. A database programming project (15%)
    4. A solution to a mid-term test (Chapter 1--Chapter 5) counts (20%).
    5. A final exam, to be held between 8 a.m. to 10:30 a.m., Monday, Dec. 14, 2009, counts (25%).
    Your final grade for this course can be found here.