CS 331 - Database System Design & Management - Dr. Canan Eren - Open Pedagogy - Research Guides at New Jersey Institute of Technology

CS 331 - Database System Design & Management

What is this course about?

The course teaches students how to design, create, query, and update a database through a small project. They get hands-on experience with modern database management systems using the standard database language SQL.

Term Project

Analyze, design, and implement a database through a small project.

Choose Your Group: Form a group of two people.
Choose Your Application Area/Subject:
- Rent a Car
- Bank
- Materials Management/Inventory
- Other (Discuss with the professor for approval)

Prerequisite: CS 114 or CS 116 or IT 114 with a grade C or better.

Initial Assignment

Phase I: Database Modeling

This assignment guides you through the three core stages of database design: defining rules, creating a conceptual model, and developing a logical schema.

Step 1: List the business rules requirements of the system that you have chosen.
- Example: Bank System Rules
- Branch: Has a unique Branch-ID, Name, Address, and Total Assets.
- Employee: Has a unique SSN, Name, Address, Phone, and Start Date.
- Relationships: Each employee works for one branch. Each branch is managed by one employee. Each employee reports to one supervisor.
Step 2: Create a conceptual ER diagram that accurately models the business rules, using any tool you prefer and clearly documenting your assumptions.
- Example: Bank System ERD
Step 3: Convert your conceptual schema into a logical model that can be implemented in a relational DBMS. Document your design in Database Schema format.
- Example: Bank System Schema

Phase II: Database Design

Implement the database in a Database Management System (DBMS).
- DBMS Options: Oracle (request an account from NJIT IST), PostgreSQL, MySQL.
Write SQL statements to create the tables using the conceptual design from Phase I.
Use appropriate naming conventions for all your tables, columns, primary keys, and foreign keys.

Example SQL:

CREATE TABLE BRANCH (
    BRANCH_ID INT PRIMARY KEY,
    B_NAME VARCHAR(200),
    B_ADDRESS VARCHAR(100),
    B_PHONE VARCHAR(200),
    ASSETS INT,
    MANAGER INT
);

CREATE TABLE EMPLOYEE (
    SSN INT PRIMARY KEY,
    E_NAME VARCHAR(200),
    E_ADDRESS VARCHAR(100),
    E_PHONE VARCHAR(200),
    STARTDATE DATE,
    SUPERVISOR INT,
    BRANCH_ID INT,
    FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH (BRANCH_ID) ON DELETE SET NULL
);

ALTER TABLE BRANCH
ADD CONSTRAINT BRN_MNG
FOREIGN KEY (MANAGER) REFERENCES EMPLOYEE (SSN) ON DELETE SET NULL;

ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMP_SPR
FOREIGN KEY (SUPERVISOR) REFERENCES EMPLOYEE (SSN) ON DELETE SET NULL;

Phase III: Final Implementation & Presentation

Focus Areas: Normalization, Testing, Application Program Design (User Interface), Presentation.

Complete the database and upload the final project to the NJIT Oracle server.
Submit all necessary documentation, including:
- ER diagram
- Relational schema
- SQL scripts
- Application Program Design (UI) codes
The application program design should have web pages to connect to the database.
Projects will not be publicly visible and are not used beyond the class.

Open and Renewable Version of the Assignment

Phase I: Database Modeling

This assignment guides you through the three core stages of database design: defining rules, creating a conceptual model, and developing a logical schema.

Step 1: List the business rules requirements of the system that you have chosen.
- Example: Bank System Rules
- Branch: Has a unique Branch-ID, Name, Address, and Total Assets.
- Employee: Has a unique SSN, Name, Address, Phone, and Start Date.
- Relationships: Each employee works for one branch. Each branch is managed by one employee. Each employee reports to one supervisor.
Step 2: Create a conceptual ER diagram that accurately models the business rules, using any tool you prefer and clearly documenting your assumptions.
- Example: Bank System ERD
Step 3: Convert your conceptual schema into a logical model that can be implemented in a relational DBMS. Document your design in Database Schema format.
- Example: Bank System Schema

Phase II: Database Design

Implement the database in a Database Management System (DBMS).
- DBMS Options: Oracle (request an account from NJIT IST), PostgreSQL, MySQL.
Write SQL statements to create the tables using the conceptual design from Phase I.
Use appropriate naming conventions for all your tables, columns, primary keys, and foreign keys.

Example SQL:

CREATE TABLE BRANCH (
    BRANCH_ID INT PRIMARY KEY,
    B_NAME VARCHAR(200),
    B_ADDRESS VARCHAR(100),
    B_PHONE VARCHAR(200),
    ASSETS INT,
    MANAGER INT
);

CREATE TABLE EMPLOYEE (
    SSN INT PRIMARY KEY,
    E_NAME VARCHAR(200),
    E_ADDRESS VARCHAR(100),
    E_PHONE VARCHAR(200),
    STARTDATE DATE,
    SUPERVISOR INT,
    BRANCH_ID INT,
    FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH (BRANCH_ID) ON DELETE SET NULL
);

ALTER TABLE BRANCH
ADD CONSTRAINT BRN_MNG
FOREIGN KEY (MANAGER) REFERENCES EMPLOYEE (SSN) ON DELETE SET NULL;

ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMP_SPR
FOREIGN KEY (SUPERVISOR) REFERENCES EMPLOYEE (SSN) ON DELETE SET NULL;

Phase III: Open and Renewable Implementation

Part 1: In-Class Session
Students must attend a 45-minute session in class covering:

AI Literacy
Open Educational Resources (OERs)
Open Access and Open Pedagogy

Part 2: User Interface Development (The "Renewable" Assignment)
Instead of a disposable assignment seen only by the instructor, students will create work that contributes to the community.

Create a user interface on GitHub: Hosting the code publicly makes the assignment open and renewable.
The UI should connect to your database.
Collaboration: Students are encouraged to review other projects and improve their own work based on peer feedback.

Class GitHub Repository:
https://github.com/NJIT-YWCC-CS331