CS3600.01 Database Management System (On line, Fall 2020)


  • My home page
  • Email me any time between 8 a.m. and 10 p.m.: zshen@plymouth.edu
  • Meet me "in person": Zoom meeting by appointment through the above email address

    Catalog Description

    Covers the principles and practice of relational database design and analysis, including topics of entity-relationship modeling, functional dependencies, normalization, relational algebra, as well as their SQL correspondents. Other related issues are discussed such as other data base models, object-oriented database scheme, concurrent data access, recovery and security. One or more projects form a significant part of this course. Falls. Prerequisite(s): CS 2370 and (MA 2200 or MA 3200 or MA2250). 4 Credits

    General Information

    This course is to provide students with a solid background for both theory and practice of computer based information management. To achieve this objective, we study various aspects of (relational) database management systems, particularly, how to design and set up normalized (well organized) databases, how to query (ask questions about) those databases based on relational algebra, how to maintain the integrity of those databases, as well as a detailed discussion of the related conceptual background.

    Since database related programming plays an increasingly crucial part in every walk of our lives and works, we also provide an introduction to database programming based on MariaDB, PhP and HTML. If you are interested in more advanced database concepts and programming practice, you may take such subsequent courses as Web programming, Advanced web programming, Mobile applications, HCI, Cybersecurity, Computer Security, Big Data Analytics, and System Administration.

    Lectures or other class meetings for this course may be recorded by the university using USNH media platforms. Such recordings may be available for educational use by other students enrolled in the class (including both for instruction and as a review tool), the course instructor(s), and other university officials who support course instruction. Your voice or image may be captured on the recordings, and by enrolling in this course you are consenting to such recording for these purposes.

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

    Most of the communication regarding this class will be done through email to your plymouth address, with a subject starting with "CS3600:...", so you have to regularly check your email while taking this course.

    Several assignments of homework will be made. Although you should not hand in any homework unless and until you are reasonably sure about their correctness; all the homework must be submitted by the deadline, usually 9 p.m., on the first business day in the following week, once a chapter is completed. A student should definitely not wait until the last day to do the homework, but get the homework assignment completed right after the relevant subject is discussed.

    If you have a solid reason for a delay ("excused absences", as defined in the Plymouth State University attendence policy) of the aforementioned homework and/or project, please let me know before the deadline, or as soon as possible afterwards. The work(s) invovled will not be included in calculating your grade for this course.

    To reach a balance of theory and practice, a lab session will be held approximately once a week, usually on Friday from 10:00 a.m. to 10:50 a.m. in Memorial 213. Students are expected to spend a significant amount of time outside the class to go through the labnotes, complete the labworks as assigned within these notes, and send them in by their respective deadline, usually by 10 p.m. on the day it is to be assigned. It is strongly recommended that students read the relevant part(s) before a lab session is to start.

    Due to the complexity and magnitude of database programming, teamwork is often the normal format of database related programming. This allows us to further our soft skills such as teamwork, verbal and written communication, time management, problem solving, and flexibility; and contribute our personal attributes, including risk tolerance, collegiality, patience, work ethic, identification of opportunity, sense of social responsibility, and appreciation for diversity. We thus have also included a semester-long team project with a user friendly interface (UI) and enriching user experience (UX), the outcome of which will be presented at the end of the semester.

    If a student finds any part of the assignment, including homework, lab assignment, project assignment, midterm and/or final exam, is not clear, she should ask the instructor for clarification as soon as possible. The instructor will share with the whole class such a clarification, anonymously.

    Anything and everything, once submitted, will be considered final. If you send it in prematurely by an accident, please send me an email right away to cancel your submission.

    Anything and everything handed in for this course, including homework assignments and the project report, must be readable. You should use, e.g., Notepad++, to type up a text file; and Microsoft Visio for drawing Entity-Relationship charts.

    As a constructive feedback, an anonymous sample solution for all the assignments, including homeworks, programming labs, various phases of the team project, and midterm test, will be posted after the event. As a further, and important, step of the learning process, students are urged to study such samplers carefully, and compare them with her own works with the goal of learning what is expected. Please do come to talk to the instructor with any further questions.

    Anything and everything handed in for this course must 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 leader of the Computer Science and Technology discipline. For relevant definitions and procedural details regarding academic integrity related issues, please refer to the current University's policy on Academic Integrity.

    The instructor will try his utmost to be fair in grading students' work as a measurement of the quality of the submitted work in terms of its degree of meeting a clearly stated expectation. 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.

    Plymouth State University is committed to providing students with documented disabilities equal access to all university programs and facilities. If you think you have a disability requiring accommodations, you should immediately contact the Campus Accessibility Services, located in Speare 210, to determine whether you are eligible for such accommodations. Academic accommodations will only be considered for students who have registered with the Accessibility Service. If you have a Letter of Accommodation for this course from the Campus Accessibility Services Office, please provide the instructor with that information privately so that you and the instructor can review those accommodations.

    The PASS (Plymouth Academic Support Services) Office, located in Speare 209, provides tutoring and study skills information.

    Student Support Foundation (SSF) provides short-term emergency financial assistance and long-term student support. Click here for more information. Student Support Foundation also runs a food pantry, located in Belknap Hall. To learn more about SSF or access the food pantry, either via open hours or a private appointment, contact the SSF advisor, at psu-ssf@plymouth.edu.

    You might want to click here for additional information regarding other basic needs.



    Textbooks and other teaching material:



    Topics

    1. An overview of this course
    2. Opening up!
    3. RDB Basics via a Case Study: A preview
      • Fun reading: A little history of the RDB model
      • Deeper reading: What is in relational algebra?

      • Lab 2: Associative arrays. Friday, Sept. 11, 2020 from 10:00 a.m. to 10:50 a.m..
      • Project(I): Integrity constraint construction.
        • Form your group with three students and (s)elect a leader by Wednesday, September 9, 2020. The leaders send me an email about the composition of their teams by 9 p.m., Wedneday, September 7, 2020. I will group those left out into teams.
        • Get together and choose a project among those proposed or suggest your own. In the latter case, come up with a project proposal "equivalent" to those contained in the suggested projects.
        • Write at least five business rule based integrity constraints for your chosen project. Half of them should be intra-relational, and the rest inter-relational. None should be about the keys only.
        • The project leader sends the above ICs to me in an email by 9 p.m., Monday, September 14, 2020.
      • Homework for this chapter is due by 9 p.m., Monday, September 14, 2020.
    4. The Relational Model: How to integrate tables?
      • Fun reading: What does ANSI do?
      • Fun reading: Another perspective of the ANSI/SPARK DB architecture
      • Fun reading: What is the Y2K problem?
      • Fun reading: Various views of Mt. Washington
      • Further reading: MariaDB does not allow user defined domain yet.
      • Further reading: What data types have been defined in MariaDB?
      • Further reading: Use foreign key in MariaDB
      • Further reading: How to alter a table in MariaDB?
      • Further reading: subquery and exists in MariaDB
      • Further reading: Trigger in MariaDB
      • Further reading: Create and use view in MariaDB
      • Further reading: Grant privileges in MariaDB
      • Further reading: How about heredoc in PhP?

      • Lab 3: Strings and functions. Friday, Sept. 21, 2020 from 10:00 a.m. to 10:50 a.m..
      • Lab 4: Variable scope and recursion (On your own)
      • Lab 5: Table creation and population. Friday, Septemer 25, 2020, from 10:00 a.m. to 10:50 a.m..
        • Before coming to the lab read Sections 1 of A Gentler Introduction to MariaDB Database Programming, and know how to use all the commands as contained within.
        • Set up the registration database and populate the tables using the data as shown in Section 2 of the above lab notes.
        • Check the queries as given in pp. 12 through pp. 16 in the notes of Unit 3, namely, RBA Basics via a Case Study, with the registration database. Notice that the student IDs are no longer just 4 digit long with this real instance.
        • Complete Labwork 2.2, and 2.3, the whole nine yards.
        • Send me an email, "I am done.", together with a) the name of your database; b) the Create Table statements for the SupplyPart database in MariaDB; and c) the query results as you should have got for the Registration database, by 9 p.m. Friday, September 25, 2020.
      • Homework for this chapter is due by 9 p.m., Monday, September 28, 2020. You have to type it up.
    5. Conceptual Modeling: The very first step
      • Review: Inheritance in Java
      • Project(II): E/R diagrams and a preliminary design of the database:
        1. Come up with an E/R chart for your project, that you have chosen for you team project, which should be "typed up" using a computer based tool such as Microsoft Visio 2019, available in the labs; If you don't have access to it, use Insert/Shape in MS Word instead. Send in a draft by 9 p.m., Wednesday, Septemer 30, 2020.
        2. Convert the E/R chart to a preliminary design of the database in terms of tables, keys (primary and foreign), and other constraints. You need to attach a detailed description/justification as why you come up with this design.
        3. Set up the databases in your team account and populate the tables with at least ten tuples for each table, by 9 p.m., Monday, October 12, 2020.
      • Lab 6: Data passing with PhP. We have worked with a bunch of single PhP pages, and explored the nature of local, global, and static variables in Lab 5. But even a global variable is only defined within the page where it is declared. This lab shows us how to pass data between the pages. Friday, October 2, 2020 from 10:00 a.m. to 10:50 a.m..
      • Homework for this chapter is due by 9 p.m., Monday, October 5, 2020. You have to type it up.
      • Midterm: Wednesday, October 7, 2020, from 10:00 to 10:50 a.m..
    6. Relational Algebra and SQL: Get the right stuff out
      • Further reading: What else should you know about join in MariaDB?
      • Further reading: What else should you know about Union in MariaDB?
      • Further reading: What else should you know about Intersect in MariaDB?
      • Further reading: What else should you know about Except in MariaDB?
      • Further reading: What else should you know about All in MariaDB?
      • Further reading: What else should you know about Exist in MariaDB?
      • Further reading: ROUND is handy.
      • Further reading: What else should you know about Update in MariaDB?
      • Further reading: What aggregation functions can you use in MariaDB?
      • Lab 7: More data structures, Friday, October 9, 2020 from 10:00 a.m. to 10:50 a.m..
      • Lab 8: Simple queries, Friday, October 16, 2020.
        • Before coming to the lab, please read Section 3.1 of A Gentler Introduction to MariaDB database Programming, really understand the sample queries, and test out all the MariaDB codes as I have done.
        • Complete Labwork 3.1.
        • Send in the answers, lots of stuff, to the questions in the required format to me via the moodle page by 9 p.m., Friday, October 16, 2020.
      • Lab 9: Set operations
        • Before starting to do the lab, please read Section 3.2 of A Gentler Introduction to MariaDB database Programming, really understand all the examples, and test out all the codes.
        • Complete Labwork 3.2.
        • Send in the answers to questions as specified in the above Labwork assignment, fifteen in total, to me via the moodle page by 9 p.m., Monday, October 19, 2020.
      • Lab 10: Nested queries, Wednesday, October 21, 2020
        • Read Sections 3.3 of A Gentler Introduction to MariaDB database Programming, really understand the stuff; and test out all the codes.
        • Complete Labwork 3.3.
        • Send in the answers to the questions in the required format to me via the moodle page by 9 p.m., Wednesday, October 21, 2020.
      • Lab 11: Aggregation, Friday, October 23, 2020.
        • Read Section 3.4 of A Gentler Introduction to MariaDB database Programming, really understand the stuff; and test out all the codes.
        • Revise the registration database, as instructed at the beginning of Section 3.4 of the MariaDB notes.
        • Complete Labwork 3.4.
        • Send in the answers to the questions in the required format to me via the moodle page by 9 p.m., Friday, October 23, 2020.
      • Lab 12: View, Monday, October 26, 2020.
        • Read Section 4 on View of A Gentler Introduction to MariaDB database Programming,, really understand the stuff; and test out all the codes.
        • Complete Labwork 4. Lots of work, so start early.
        • Send in your work, as specified in the assignments, via the moodle page by 9 p.m., Monday, October 26, 2020.
      • Project(III): Query design
        • The project teams come up with five "non-trivial and useful" queries for the database, test them out with a non-trivial database instance, as completed in Project (II).
        • Send me a report, containing the database structure, with desc command, those queries, and the associated sample results via the moodle page by 9 p.m., Wednesday, Octber 28, 2020.
    7. Normalization: Make it lean
      • Further reading: Additional information on 3NF
      • Homework for this chapter is due by 9 m., Monday, November 2, 2020.
      • Project (IV) : Normalize your database
        • Have a good look at the tables that you have come up in Project(II), and, for each of them, identify the FDs, based on the ICs that you have developed in Part(I) and/or recently.
        • For each of the tables, answer the following questions, and use what you have learned in this chapter to justify your answers:
          • What are the IC's for this table? Turn them over to FDs.
          • Where does the table sit in the normalization hierarchy by applying the checking as we went through in class and described in the textbook/notes?
          • If this table is not in BCNF, follow the procedure which we just went through in the class, 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 added and/or dropped?
        • Based on the results, revise your database design, populate those tables, and revise the queries that you have come up in Project III, and run them in terms of the revised database.
        • Send me the typed up answers to the above questions via email by 9 p.m., Wednesday, November 4, 20120.
    8. A User Friendly Product That Meets Users' Needs
      • Further reading: Additional information on hashing in PhP
      • Further reading: How to change access rights in unix?
      • Lab 13: PhP and MariaDB(1), Friday, November 6, 2020 (This lab work should be done by the respective project team.)
      • Lab 14: PhP and MariaDB(2), Monday, November 9, 2020 (This lab work should be done by the respective project team.)
        • Go through Sections 5.5 of A Gentler Introduction to PhP and Its Application in Programming,, and really understand the stuff, especially those context sensitive error messages.
        • Complete Labwork 5.5.1 through Labwork 5.5.3. When they are done, it should look like this.
        • Complete Lab 5.5.4.
        • Send in the URL of your revised cript to me via email with a subject being "We're done with Lab 14" by 9 p.m., Monday, November 9, 2020.
      • Lab 15: PhP and MariaDB(3) Wednesday, November 11, 2020 (This lab work should be done by the project team.)
      • Lab 16: PhP and MariaDB(4) (This lab work should be done by the respective project team.)
      • Lab 17: PhP and MariaDB(5) (This lab work should be done by the respective project team.)
      • Project(V): Close in
        • In light of Labs 13, 14, 15, 16, 17, and the queries that you came up in Project III, come up with a preliminary user interface of your project, which allows your users to enter data, and get out information via queries associated with buttons, by 9 p.m., Monday, November 16, 2020.
      • Project(VI): Wrap up
        • Database design: A well developed database, as described in a well written and readable document. (8) Check out Section 4: Grading Criteria of project for details
        • Queries (5): Develop a collection of queries and/or triggers, addressing users's needs, and a correct implementation in MariaDB.
        • Interface (2): A "user-friendly" interface to your DB app.
        • Send the above work, scripts and any other supporting material in a well written, complete and readable manner, in .doc(x); including a link to your project interface, to me via email by 9 p.m., Wednesday, Novemeber 18, 2020.
    9. Triggers: What is it? (Self study)
    10. Recovery: What happens if we lose it?
    11. Concurrency: Make it run faster, but....
      • Further checking: How many cars are going through this Chinese toll booth?
    12. Object Databases: Beyond 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 mid-term test (Unit 2--Unit 5) counts (20%).
    5. A final exam: 8:00 a.m. - 10:30 a.m., Friday, November 20, 2020 (Unit 7--Unit 12), counts (25%)

    A calculated grade will then be converted to a letter grade by the following procedure: If a student gets at least 92.5, s/he will get 'A', if s/he gets between 87.5, and 92.4, s/he gets 'A-', so on and so forth, until s/he gets a grade below 42.5, when s/he will get 'F'.

    To correct any unintentional inconsistency during the grading process, if your grade is within 0.5 of a letter grade, you will get that higher grade. Thus, if your final grade is above 92.0, you will get A, instead of A-.

    As constructive feedbacks for the above items, sample solutions for all the homeworks, projects, and tests, will be posted right after the submission deadline. Besides providing an example of a correct solution to help you achieve the expected learning objectives, such a constructive feedback also serves the purpose of justifying your grade, thus implementing the fair grading policy.

    As a critical step of the learning process, each and every student must go through these feedbacks, and ask the instructor for further clarification of any issue(s) s/he might have regarding an assignment, its solution, and the grade that granted for that assignment.

    Your final grade for this course can be found here.