CSCD 327 — RELATIONAL DATABASE SYSTEMS and RELATIONAL DATABASE SYSTEMS LAB
Learning Objectives & Matches
a.Demonstrate use of the relational algebra operations from mathematical set theory and the relational algebra operations developed specifically for relational databases.
Document and communicate database schemas, using accepted notations.
Design and implement warehouse database structures.
Develop data models and databases.
Write computer programs or scripts to be used in querying databases.
Develop new principles and new relationships between existing mathematical principles to advance mathematical science.
Conduct logical analyses of business, scientific, engineering, and other technical problems, formulating mathematical models of problems for solution by computers.
Demonstrate database technical functionality, such as performance, security and reliability.
Compute, record, and proofread data and other information, such as records or reports.
Design database applications, such as interfaces, data transfer mechanisms, global temporary tables, data partitions, and function-based indexes to enable efficient access of the generic database structure.
Develop and document database architectures.
f.Write a stored procedure that deals with parameters to provide a given functionality.
Record and process results of procedures.
Select methods, techniques, or criteria for data warehousing evaluative procedures.
Record operational or production data on specified forms.
Establish and calculate optimum values for database parameters, using manuals and calculators.
Record production and operational data on specified forms.
Instruct others in program methods, procedures, or functions.
Design and direct the testing or control of processing procedures.
Document the performance, interpretation, or outcomes of all procedures performed.
Record production or operational data on specified forms.
Record data from operations, testing, and production on specified forms.
h.Create relational schemas from an entity-relationship diagram.
Create supporting documentation, such as metadata and diagrams of entity relationships, business processes, and process flow.
Lay out and draw schematic, orthographic, or angle views to depict functional relationships of components, assemblies, systems, and machines.
Document and communicate database schemas, using accepted notations.
Diagram supply chain models to help facilitate discussions with customers.
Develop database architectural strategies at the modeling, design and implementation stages to address business or industry requirements.
Create diagrams to communicate environmental remediation planning, using geographic information systems (GIS), computer-aided design (CAD), or other mapping or diagramming software.
Create Web models or prototypes that include physical, interface, logical, or data models.
Create Web models or prototypes that include physical, interface, logical, or data models.
Develop system interaction or sequence diagrams.
Develop system interaction or sequence diagrams.
i.Determine whether a relation is in 2NF, 3NF or BCNF.
O*NET-aligned topic inventory
59 tags extracted from this course-term's Canvas content, organized by O*NET 30.2 dimension. Depth reflects what students do with each topic over the term: mastered, practiced, introduced.
The course's named central deliverable is relational database systems: students build relational schemas, normalize them, query them in SQL across 9 labs and a JDBC project, and design ER models that compile to relations.
files/syllabus.pdf;modules.json
Students load five provided schema scripts (salesDB.sql, booksDB.sql, universityDB.sql, productsDB.sql, crimeDB.sql) into a relational DBMS and run lab queries against the populated database.
assignments/7042397-Lab 1.html;assignments/7042407-Project.html
The Project ships mysql-connector-java-8.0.19.jar as required JDBC driver and crimeDB.sql as the schema students load into MySQL to run their MyQuery.java implementation against.
assignments/7042407-Project.html;my_submission_files/7042407_Jessica_Doner.zip
Project provides mysql-connector-java-8.0.19.jar (a JDBC driver) and a TestMyQuery.java harness; students implement queries through JDBC connection / Statement / ResultSet APIs to satisfy the project rubric.
assignments/7042407-Project.html
Module 9 (Accessing DB in Java) and the 35-pt Project require students to complete MyQuery.java and TestMyQuery.java; Jessica submitted a 7444-byte MyQuery.java via turnin/.
assignments/7042407-Project.html;my_submission_files/7042407_Jessica_Doner.zip
Labs 1-8 require students to write CREATE TABLE / SELECT / JOIN / GROUP BY / subquery / UPDATE / INSERT / DELETE statements against provided salesDB, booksDB, universityDB, productsDB, and crimeDB schemas.
assignments/7042397-Lab 1.html;assignments/7042403-Lab 8.html;modules.json
Students apply each module's new SQL feature (joins, aggregates, subqueries, updates, integrity constraints) on the corresponding lab the same week, then carry the technique forward into the JDBC project.
modules.json
Syllabus mandates attendance at M-F 10am lectures and counts in-class participation toward 10% of the final grade; Lab 1 page includes a recorded lecture link supplementing in-person delivery.
files/syllabus.pdf;assignments/7042397-Lab 1.html
The 35-pt Project requires students to translate English questions about a crime database into JDBC-driven SQL methods that pass the TestMyQuery.java harness, integrating schema reasoning, query design, and Java-side data marshaling.
assignments/7042407-Project.html
Lab 6 (subqueries, 26 pts) and Lab 8 (normalization, 16 pts) ask students to choose among multiple valid query formulations and decomposition strategies and justify the choice.
assignments/7042402-Lab 6.html;assignments/7042403-Lab 8.html
Subquery labs (Lab 6, 26 pts) and integrity-constraint labs (Lab 7) require choosing among nested-vs-join, EXISTS-vs-IN, ON DELETE CASCADE-vs-RESTRICT, and similar design tradeoffs.
assignments/7042402-Lab 6.html;assignments/7150184-Lab 7.html
Module 3 Relational Algebra and Lab 2 require students to compute selection, projection, join, and set-operation expressions; normalization (Lab 8) requires functional-dependency reasoning. MATH 301 is a prerequisite.
assignments/7042398-Lab 2.html;assignments/7042403-Lab 8.html;files/syllabus.pdf
Module 11 ER modeling and Lab 9 require students to elicit data requirements from a problem description and translate them into entity sets, attributes, and relationships before mapping to a schema.
assignments/7042404-Lab 9.html;modules.json
Project requires students to implement query methods in MyQuery.java and validate them with TestMyQuery.java against a MySQL crimeDB; submission ships compiled Java source.
assignments/7042407-Project.html;my_submission_files/7042407_Jessica_Doner.zip
TestMyQuery.java is a test harness students run on their own MyQuery.java implementation; lab results document tabular outputs to compare against expected answer sets.
assignments/7042407-Project.html;my_submission_files/7042407_Jessica_Doner.zip
Recommended textbooks (Database System Concepts 7e; A First Course in Database Systems 3e) and weekly module pages require students to read database theory and translate prose questions into formal queries.
files/syllabus.pdf;modules.json
Lab 8 (DB normalization) requires students to analyze functional dependencies in an existing relational design and decompose it to BCNF/3NF; Lab 9 (ER modeling) analyzes whole-system data requirements.
assignments/7042403-Lab 8.html;assignments/7042404-Lab 9.html
Lab 8 normalization compares candidate decompositions on dependency-preservation and lossless-join criteria; Lab 9 ERD asks students to evaluate whether their model captures the stated business rules.
assignments/7042403-Lab 8.html;assignments/7042404-Lab 9.html
Lab 1 / Module 2 require students to write CREATE TABLE DDL with primary keys, foreign keys, and types; Lab 9 ER modeling requires a designed entity-relationship diagram before schema generation.
assignments/7042397-Lab 1.html;assignments/7042404-Lab 9.html
Students debug SQL queries that return wrong row sets and Java code that fails the TestMyQuery.java harness; iterative SQL refinement against expected outputs is the lab feedback loop.
assignments/7042407-Project.html
Students submit lab reports in DOCX/PDF documenting their relational designs, ER diagrams, and normalization decompositions; Jessica's archive contains 9 lab DOCX/PDF deliverables.
my_submission_files/
Course covers DBMS architecture, data definition, data manipulation, and integration with a Java client via JDBC; students operate a MySQL server and a Java client across a 35-pt project.
files/syllabus.pdf;assignments/7042407-Project.html
Modules 10-11 cover database design: ER modeling, mapping ER to relational schema, and normalizing to BCNF/3NF; Lab 9 requires an original ER diagram for a stated problem.
assignments/7042404-Lab 9.html;modules.json
Course teaches the engineering of database systems: schema design, normalization to reduce redundancy, integrity constraints, and application integration via JDBC.
files/syllabus.pdf;modules.json
All lab deliverables are submitted as DOCX/PDF reports; students write English explanations of their schemas, query strategies, and normalization decisions alongside SQL artifacts.
my_submission_files/
Relational algebra (Module 3, Lab 2) is set-theoretic; functional dependency theory (Module 10, Lab 8) requires reasoning about closures and minimal covers. MATH 301 is a stated prerequisite.
files/syllabus.pdf;assignments/7042398-Lab 2.html;assignments/7042403-Lab 8.html
ER modeling (Lab 9) requires choosing among entity, weak entity, attribute, and relationship classifications to fit observed real-world objects into the relational model.
assignments/7042404-Lab 9.html
Functional-dependency reasoning in Lab 8 (BCNF/3NF decomposition) and relational-algebra equivalence in Lab 2 require deriving consequences from given axioms and definitions.
assignments/7042398-Lab 2.html;assignments/7042403-Lab 8.html
Subquery labs (Lab 6) and the JDBC project require students to test sample inputs against expected row sets, then generalize a query that satisfies the broader specification.
assignments/7042402-Lab 6.html;assignments/7042407-Project.html
GROUP BY / ORDER BY / aggregate construction in Lab 5 and JOIN ordering in Lab 4 require arranging operations and tuples by specified rules.
assignments/7042400-Lab 4.html;assignments/7042401-Lab 5.html
Relational algebra exercises (Lab 2, Module 3) and normalization theory (Lab 8) require formal manipulation of sets, predicates, and functional dependencies.
assignments/7042398-Lab 2.html;assignments/7042403-Lab 8.html
Two midterm exams (40% of grade) test SQL syntax and relational-algebra notation; quizzes after each module add closed-form recall of operators, normal-form definitions, and DDL keywords.
files/syllabus.pdf;assignments/7042408-Quiz 1.json;assignments/7042405-Midterm 1.json
Aggregate-query lab (Lab 5: COUNT, SUM, AVG, MIN, MAX with HAVING) requires students to predict numeric outputs and reason about NULL-handling in summary computations.
assignments/7042401-Lab 5.html
Daily M-F 10am lectures + recorded lecture videos linked from Lab 1 require students to follow spoken explanations of relational algebra and SQL semantics.
files/syllabus.pdf;assignments/7042397-Lab 1.html
Integrity-constraint lab (Lab 7) and normalization lab (Lab 8) train students to spot anomalies (insertion, update, deletion) and constraint violations in proposed schemas.
assignments/7150184-Lab 7.html;assignments/7042403-Lab 8.html
Subquery and join labs require careful tracking of correlated columns, alias scopes, and table-qualified attribute names across nested SQL blocks.
assignments/7042400-Lab 4.html;assignments/7042402-Lab 6.html
Lab 9 requires students to draw and submit ER diagrams (rectangles, diamonds, ellipses, lines with cardinality marks) representing the data model graphically.
assignments/7042404-Lab 9.html
Students read multi-page lab PDFs, schema diagrams, and textbook chapters (Database System Concepts 7e) to understand what each query is supposed to compute.
files/syllabus.pdf;assignments/7042403-Lab 8.html
Lab reports are written in DOCX/PDF; students articulate query strategies, normalization decisions, and ER design rationales in English alongside their SQL.
my_submission_files/
Subquery (Lab 6, 26 pts) and aggregate (Lab 5) labs require students to identify principles, reasons, and facts behind the data by writing analytic queries against the schemas.
assignments/7042401-Lab 5.html;assignments/7042402-Lab 6.html
Each lab is submitted as a DOCX or PDF report capturing schemas, queries, and result sets; Jessica's archive contains 9 lab document submissions plus a project zip with result.docx.
my_submission_files/
Lab 1 (Relational Model, 14 pts) and Lab 9 (ERD) require students to draft schema specifications and ER diagrams that document the technical layout of a database before implementation.
assignments/7042397-Lab 1.html;assignments/7042404-Lab 9.html
Students extract data from provided relational schemas via SELECT statements across labs 3-7 to answer business-style questions about sales, books, university, and crime data.
assignments/7042399-Lab 3.html;assignments/7042400-Lab 4.html;assignments/7042402-Lab 6.html
ER modeling (Lab 9) and relational-model lab (Lab 1) require students to identify entities, attributes, and relationships from a textual description of a real-world domain.
assignments/7042397-Lab 1.html;assignments/7042404-Lab 9.html
Every lab requires students to operate a SQL client against a loaded DBMS instance; the project requires running a Java client over JDBC.
assignments/7042397-Lab 1.html;assignments/7042407-Project.html
Normalization (Lab 8) and integrity-constraint design (Lab 7) require choosing among decomposition strategies and constraint actions and committing to one in writing.
assignments/7150184-Lab 7.html;assignments/7042403-Lab 8.html
Lab 5 aggregate queries and Lab 7 update / integrity-constraint labs require students to compile, categorize, calculate, and verify rows in the database.
assignments/7042401-Lab 5.html;assignments/7150184-Lab 7.html
Lab 9 ER modeling and the JDBC project ask students to design an original schema and an original Java method set that satisfy a stated specification.
assignments/7042404-Lab 9.html;assignments/7042407-Project.html
Each weekly module introduces a new SQL or modeling concept that the corresponding lab applies; students must integrate the prior week's techniques into the next deliverable.
modules.json
Syllabus mandates daily attendance and counts in-class participation toward the grade; office hours M-F 9-9:50 are the named channel for asking questions.
files/syllabus.pdf
Aggregate queries (Lab 5) and summary modules introduce students to estimating row counts and aggregate measures (COUNT, SUM, AVG) over relational data.
assignments/7042401-Lab 5.html
Lab 1 + Lab 9 (ER) + Lab 8 (normalization) walk students through the full design loop: ER diagram -> relational schema -> normalized decomposition for a business-style domain.
assignments/7042397-Lab 1.html;assignments/7042403-Lab 8.html;assignments/7042404-Lab 9.html
Lab 9 ERD and Module 11 instruction require submission of entity-relationship diagrams that document the relational model graphically.
assignments/7042404-Lab 9.html
Project requires students to author MyQuery.java and ship a working Java client that compiles and runs against MySQL via JDBC; Jessica submitted 7444 bytes of MyQuery.java.
assignments/7042407-Project.html;my_submission_files/7042407_Jessica_Doner.zip
Course teaches the principles (relational model, integrity constraints, normalization) underpinning DBMS development without requiring students to implement a DBMS engine.
files/syllabus.pdf;modules.json
Students load, query, update (Lab 7), and maintain integrity constraints over five provided relational schemas through a 10-week sequence of labs.
assignments/7150184-Lab 7.html;assignments/7042397-Lab 1.html
TestMyQuery.java in the Project is an explicit test driver students run against their MyQuery.java; failures require students to correct SQL or Java code and re-test.
assignments/7042407-Project.html
Project's MyQuery.java retrieves and stores crime-database information through JDBC calls; this is the canonical 'storing or retrieving data' task for a Software Developer.
assignments/7042407-Project.html;my_submission_files/7042407_Jessica_Doner.zip
Module 8 (DB Updates and Integrity Constraints) introduces GRANT/REVOKE-style permissions and constraint design as part of the data-integrity discussion, though graded labs focus on constraints over access control.
assignments/7150184-Lab 7.html;modules.json