CIS 430/530

Database Systems and Processing (3-0-3)

Course Content

  • Class Announcement and Post
  • Class Syllabus
  • Lab Assignments
  • Class Lecture Notes


  • Class Announcement and POST





    Semester Schedule for Final Exams, Holidays, Last Days to Add and Drop:
    See University's Official Academic Calendar for the Semester and the Final Exam Schedules




    Final Exam Info:

    Final Exam Will Be on Wed May 6 at 6:00PM - 8:00PM in Class!

    See the Final Lectures to Focus on for the Final Here !

    See the Links for the Final Subjects ONLY here !! (Scroll Down to the Lecture Note Section; All the links to the rest of the non-related lecture notes are disabled)


    The Subjects to Focus on for the Final Exam

    T/F Questions on:
    Aggregate Functions - Count, Sum, the Results of Count, SUM on Duplicates, Null values,
    Output of Group By and Aggregation Functions, Left Outer Join Result with Null values,
    How a View Is Updated When a Base table is changed, Difference Between View and Table,
    SQL Processing Algorithm with a Correlated Subquery 
      
    One Question on Each Subject
    The Third Normal Form Normalization to Transform a Multi-Valued and Composite Attributes to Correct Relational Table Scheme (The Same Question from the Midterm)
    Complex SQL with Left Outer Join, Aggregation with Group By and Having, Correlated Subquery
    Given 2 tables, waht is the result of Left Outer Join, and Right Outer Join?
    Two ways to Save a SQL Result in a SQL Server: View, Insert into Select
    Stored Procedure to Create and Polulate a Table from a Query result, Extended Embedded SQL, Dynamic SQL (Prepared Statement) with JDBC/ODBC


    NOT for THIS SEMESTER:
    Calculation of Query Processing Cost in the Number of Block I/Os, How much the Number of Block I/O Can Be Reduced with Primary Index
    Clustered Index, Secondary Index
    Transaction for Concurrency Control - Understanding Begin Transaction, Commit, Abort, Roll Back in the Example of Two Concurrent Users' Transactions


    One Page Note Each Side is Allowed to the Final !

    A Hand-Written (Should not Be Copy and Pasted from the Lecture Slides) Standard One Page Note Sheet (Front and Back) Is Allowed to the Final !
    Typed/Printed or Copy and Pasted Lecture Notes are NOT Allowed.








    18. Jan 12, 2026:

    The Midterm Will be Tentatively on Mach 18 in Class. It Will be Announced Ahead in Class.
    See the Topics to Focus on for the Midterm here:
    Midterm Links Only (All the links to the rest of the non-related lecture notes are disabled)

    One Page Note is NOT allowed to the Midterm !


    15. Jan 12, 2026:

    It is Required to Attend Every Class !
    Your Class Attedance Will be Checked in Each Class.
    There Will be Random Quizzes to Check the Attendance !
    Only 5 - 10 Mins Will Be Allowed for Each Quiz. Those Who Come in Class Late After 5 Mins, They Will NOT Be Given a Quiz !  


    14. Jan 12, 2026:

    The Lab Submission Link and the Deadline of Each Lab Will Be Posted on the Class BlackBoard !
    Wait For Lab Submission Links To Be Created on Blackboard with Each Deadline to Submit Each Lab


    13. Jan 12, 2026:

    Only the registered students can access the course blackboard.
    If you have a problem with your blackboard access, please contact the registar and CSU e-learning Tech Support to resolve the issue !

    Faculty do not control your registration in the CSU Campus Systems and Access to the Course Blackboard.

    CSU Tech Support



    9. Jan 12, 2026:

    TA Information:

    TA:
  • Lei Li

    • Email:l.li15@vikes.csuohio.edu

    Office Hours (In-person):
    Monday & Wednesday, 3:00 PM – 5:00 PM
    Location: FH311
    Send email to TA ahead to set up a time slot and to let TA know that you are coming

    Zoom ID (if needed for online meetings):824 2309 8929
    Zoom Link



    If you have questions in the Labs or Grading your Labs, Send an email to TA to See During the TA's Office Hours or Schedule a meeting in the TA's Zoom meeting






    Dr. Chung's Office Hours:

    Tues and Thursday 1:30PM - 3:00PM

    Email: s.chung@csuohio.edu
    (Send Me Email Ahead to Set Up a Meeting or a Zoom Meeting)

    Office: FH 222 or Zoom Meeting

    Meeting ID: 859 3867 6332
    Zoom Meeting Link




    8. Jan 12, 2026:

    For the Installation Guides of MS SQL Server in Detail, See the Lab0 Section below !

    To Install MS SQL Server on Window, a Mac or on Linux, Find the Instructions for Each OS in the Lab0 Section !

    Read CAREFULLY all the Installation Guides FIRST before starting downloading ! See the Lab0 Section for More Details !
    Read ALL the instructions regarding this in the Lab section including FAQs.

    Note that 2016, 2019 MS SQL Server Packages Do not Have the Client Software - SQL Server Management Studio (SSMS) Together.
    You have to Download it separately from the site specified and install it after Installing a SQL Server.
    Read the Instructions in the Lab0 Section First for This !

    For more guides on installation of SQL Server, see the Lab Section !

    If You Still Have Any Questions, Send Email to TA for Help


    7. Jan 12, 2026:

    If You Don't Own Your Own Computer to Install a SQL Server:
    Use a MS Azure Cloud Service to Use a SQL Server Instance ! Go to Azure Cloud to Create SQL Server Instance Service. See the Lab0 Section to Read What/How to Do in more detail !

    Read ALL the instructions regarding this in the Lab section including FAQs.
    If You Still Have Any Questions, Send Email to TA for Help

    6. Jan 12, 2026:

    The Output of each lab is your report in Doc file that shows your screen captures of your SQL Server Management Studio showing that you wrote each SQL/DDL/DML and the server returned the correct results.
    Each of your screen capture must show your query and the result returned by the server respond of the query in the SAME window in your System to prove that your lab is done correctly in YOUR Server !!

    Lab Submission:

    See the Lab Section for the Detail Instruction on the Requirements to Create Your Lab Report !! -- Very Important !!

    1) Submit your file in a Zip file that Includes all the SQL files (.sql) and Your Lab Report (.doc file) Showing each Screen Capture of Each Query Execution on Blackboard for a timestamp and as a proof.
    Please See the Lab1 Section for the detail instructions on How to Create Lab Report !


    5. Jan 12, 2026:

    The Lab Submission Link and the Deadline of Each Lab Will Be Posted on the Class Blackboard !
    You Have to Start Working on Labs Before the Submission Link Are Created on Blackboard for Each Lab Submission.

    If You did an Extra Credit Part, Mention about What Part is Done for Extra Credit at the Front(Cover) Page of Your Report in Bigger and Bold Font !

    Always Follow the Deadline of Each Lab Assigned on the Class Blackboard.
    The Deadlines mentioned on the Class Webpage Are Tentatively Scheduled at the Beginning of Each Semester.

    Please Mention Your Course Number When You Send Me an Email !


    4. Jan 12, 2026:

    Lab Assignment 0 : Prep for Lab Assignments

    Download Visual Studio, MS SQL Sever and its Client - MS SQL Management Studio (SSMS) and Install on Your Laptop

    From May 2019, Microsoft changed their platform to provide free download MS Software for CSU Students !

    NEW INSTRUCTION from May 2019 !

    How to Access MS Azure site to Download VS, MS SQL Server and Other Software

    1. Go to www.portal.azure.com
    Azure Potal Site
    2. Sign in with your CSU ID # and password.
    3. In the box on top, where it says "Search resources, services and docs", type "Education".
    4. Select "Education (preview)" from the list.
    5. Select "Software" from the list in the upper left corner.
    6. You should now be able to select from a variety of software, including Windows 10, Windows Server, Visio, Visual Studio, SQL Server, Project, Access, etc.
    7. Lots of documentation is under the tab marked "Learning".


    For any questions on this site or creating your account in this site, send email to TA or Dr. Jackie Woldering (j.woldering@csuohio.edu) !


    See Lab0 Section for Instructions to Download and Install SQL Server and Visual Studio on Azure Potal Site

    SQL Server Installation Guide: See Lab Section for the Detail :br>
    1.Download and Installation of MS SQL SERVER Package : Read Instructions Carefully Below !

    Any SQL Server Standard/Developers/Enterprise Version with Service Packs recommended
    Make Sure to Install a Matching Version of Visual Studio first before installing SQL Server (Please see installation Guide in the Lab section for the detail)
    Read the installation Guides in the Lab0 Section FIRST before Starting to download !
    2. After everything is set up correctly, Run SQL Server Management Studio - a Client for your SQL Server (It is Under Microsoft SQL Server of your Start menu of your Window) to Play with Your SQL Server
    3. If your SQL Server is not running (You don't see the green arrow on your server name on the Top Left corner of your SQL Server Management Studio), Start the server manually in your SERVICE list - Search with "Service" to find the list. (Read the General Installation Instructions for this in the Lab0 Section !)


    3. Jan 12, 2026:

    For New Students, You Have to Create Your Account in Azure Potal site to Download MS Software -- Visual Studio, Window SQL Server

    How to Create Your Account to Login the Azure Potal Site to Download MS SQL Server

    1. Go to www.portal.azure.com
    Azure Potal Site
    
The students MUST use a vikes.csuohio.edu or csuohio.edu email address!

Note that the students should use CSUID@vikes.csuohio.edu, not f.lastname@vikes.csuohio.edu.

So, for example, 2345678@vikes.csuohio.edu, rather than m.jones@vikes.csuohio.edu.

Education Hub

This is where your students can download their software.

1)       Please clear the cache, cookies and browsing history. Then using Internet Explorer or Microsoft Edge, please enable the In-private browsing by pressing CTRL + Shift + P on the Keyboard or, for Chrome CTRL + Shift + N.

2)       Go to https://portal.azure.com/#view/Microsoft_Azure_Education/EducationMenuBlade/~/overview  or https://azureforeducation.microsoft.com/devtools (to be redirected to the current Azure site For the old MS Imagine program) 

Azure Software for Teaching
This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use. Learn more at azureforeducation.microsoft.com
then click the blue sign in button.

3)       Enter your email address then hit Next.

4)       Enter the password then Sign In.

5)       Once signed in, it should take you to the Student Verification page. Please enter your school email address twice and a verification link will be sent to your email.

6)       Copy and paste the link to a new tab then hit continue.

7)       Please agree to the Terms and Conditions.


After completing the above mentioned steps, you should be able to access the Azure portal. 
    
If you were directed on the Azure Home page, please type in “Education” on the search box then select “Education” from the search results.

    Send an email to TA or Dr. Jackie Woldering (j.woldering@csuohio.edu) for any questions


    1. Jan 12, 2026:
    The Link to this class webpage is in the Announcement of the Class Blackboard.
    If you have a trouble to display the webpage correctly with MS Internet Explorer, open it with Google Chrome


    Each Semester Schedule for Final Exam, University Holidays, Last Days to Add and Drop:
    See University's Official Academic Calendar for the Semester and the Final Exam Schedules



    Final Exam schedule for Spring 2026 Class:

    May 6 Wed 6:00p-8:00PM in Class !





    Midterm Exam Info:

    See the Links for the Midterm Subjects ONLY here !! (All the links to the rest of the lecture notes are disabled)


    For Fall Semesters, The Midterm will be Tentatively on Wed Oct 16. (Will be announced in class)
    For Spring Semesters, The Midterm will be Tentatively on Wed March 18. (Will be announced in class)
    For Summer Semesters, Midterm will be Tentatively on the 4th Week of Tuesday or Thursday. (Will be announced in class)


    Topics to Focus on for the Midterm:

    Chapter 1, 2, 3, 4 and E-R Modeling Lecture Notes
    Architecture of SQL Server to Maintain Meta data and Data values, What SQL Server Does When Any DDL is Submitted
    All the Database Constraints : PK Constraint, Entity Constraint (Not Null Constraint), Two Properties to Choose a Correct PK attributes, FK Constraint
    Correct Transformation Rules for Multi-Valued, Composite Attributes, 5 different Relationship Types: 1-1, 1-N, N-M, Weak Relationship, Unary 1-N
    First and Third Normal Form Transformation rules
    Basic SQL Syntax: SQL Join, Union


    One Page Note Is NOT Allowed to the Midterm !

    CIS430/530 Midterm Exam Question Types and Patterns:

    Closed Book. There will be about 6-7 Questions.
    Q1: 10 small T/F questions on facts on Architecture of RDBMS, Sql Server, DDL, DML, SQL Syntax, Database Constraints
    The rest of the 5 Questions are all short answer questions on problem solving with a simple small example.
    For example, per given a block of E-R diagram, write DDL to transform the E-R diagram to a correct Database scheme.
    1-2 Questions for writing SQL.





    Final Exam Info:


    Final Exam of Spring 2026 Will Be On:
    Wed May 6 at 6:00PM-8:00PM

    See the Links for the Lecture Notes to Focus on for the Final Here !
    See the Links for the Final Subjects ONLY here !! (Scroll Down to the Lecture Note Section; All the links to the rest of the non-related lecture notes are disabled)


    The Subjects to Focus on for the Final Exam

    T/F Questions on:
    Aggregate Functions Count, Sum Results on Duplicates, Null values,
    Output of Group By Column on the Left Outer Join Result with Null values,
    How a View Is Updated When a Base table is changed, Difference Between View and User Base Table,
    SQL Processing Algorithm with a Correlated Subquery 
      
    One Question on Each Subject
    The Third Normal Form Normalization to Transform a Multi-Valued and Composite Attributes to Correct Relational Table Scheme (The Same Question from the Midterm)
    Complex SQL with Left Outer Join, Aggregation with Group By and Having, Correlated Subquery
    What is the result of Left Outer Join?
    Two ways to Save a SQL Result in a SQL Server: View, Insert into Select
    Transaction for Concurrency Control - Understanding Begin Transaction, Commit, Abort, Roll Back in the Example of Two Concurrent Users' Transactions
    Stored Procedure to Create and Populate a Table from a Query result, Extended Embedded SQL, Dynamic SQL (Prepared Statement) with JDBC/ODBC
    calculation of Query Processing Cost in the Number of Block I/Os, How much the Number of Block I/O Can Be Reduced with Primary Index
    Clustered Index, Secondary Index

    A Hand-Written (Should not Be Copy and Pasted from the Lecture Slides) Standard One Page Note Sheet (Front and Back) Is Allowed to the Final !
    Typed/Printed or Copy and Pasted Lecture Notes are NOT Allowed.





    One Page Note Each Side is Allowed to the Final !

  • CIS430/530 Class Syllabus
  • CIS430 ABET Class Syllabus

  • Lab Assignments


    Lab0 :Installation of MS SQL Server and Its Client MS SQL Server Management Studio (SSMS) (Due by the End of the Second Week)

    Submit the screen captures (in doc file) of your SQL Server (Named after your Computer Name) running in your client SSMS Window at the beginning of Lab 1 Report !




    Read all the instruction Guides below FIRST before Starting Downloading!


    General Things to Know for SQL Server Installation:

    You Need to Install the following in the order:
    1. MS Visual Studio 2019 or higher
    2. 2019 MS SQL Server Developer Version (MS SQL Server) or higher
    3. MS SQL Server Management Studio (Client for MS SQL Server)


    Any MS SQL Server Standard/Developers/Enterprise Version Recommended. No Express Version !

    Make Sure to Install a Matching Version of Visual Studio First Before Installing 2019 or 2022 MS SQL Server

    Where to Download and Install MS Visual Studio 2022 or 2019 Community Version


    Note that Visual Studio 2019 Needs to Be Installed First Before Installing 2019 SQL Server.

    Where and How to Download and Install MS Visual Studio 2019


    Install 2022 Visual Studio for 2022 SQL Server

    Where and How to Download and Install MS Visual Studio 2022 Prepared by TA Dharmik Rajesh Kurlawala



    How to Download and Install 2019 or 2022 Microsoft SQL Server Developer’s Version on Window 10

    (If You Had Already Installed Any Older Version of MS SQL Server Running on Your Computer, No Need to Install Another SQL Server !)


    New Changed Installation Guides As of Dec 2022:

    You Need Window 10 or 11 and Visual Studio 2019 or Higher to Install 2019 SQL Server !
    You Need Window 10 or 11 and Visual Studio 2022 to Install 2022 SQL Server !

    Instructions for Downloading and Installation of SQL Server 2019 or 2022:

    Where to Go to Download a MS SQL Server Developer Version:

    NOTE That There Are TWO Sites to Download a MS SQL Server !


    1. Azure Potal Education site for CSU Students:

    You Can Download 2019 SQL Server Developer Package From Microsoft Azure Portal Site Below:

    Azure Potal Site
    Choose(or Search) Education (with Your Azure Account Credential Created with Your CSU ID and PW)
    From MS AZURE Portal Site => Education Menu for CSU Students


    How to Download and Install Microsoft SQL Server 2019 Developer and its Client - MS SQL Management Studio (SSMS) from AZURE Portal Site: (MOST RECENT Instructions as of Jan 2023) !!

    Where/How to Install a Client - SQL Management Studio (SSMS) ***************

    Where/How to Install a Client - SQL Management Studio (SSMS) (as of Dec 2025) ***************
    Step by Step Guides for Installation of Microsoft SQL Server 2019 and the Client - SQL Management Studio (SSMS) ***************

    IMPORTANT NOTE !!

    Do NOT Choose PolyBase OPTION
    NEW ERROR !! Do Not Select PolyBase Feature to Avoid Any Installation Errors Caused by the Recent MS Update



    What To DO When the Installation Went Wrong and You want to Remove them to Reinstall:

    DO NOT Try to Delete the Files by Yourself Manually !!
    How to Remove SQL Server and Its Client Software from My Laptop *************************



    Client SSMS Installation:

    Where/How to Install a Client - SQL Management Studio (SSMS) ***************

    How to Use a MS SQL Server Client - SSMS (MS SQL Server Management Studio) NEW POST !!




    OR
    Another MS Site to Download for SQL Server 2025 Integrated with Data Warehouse (Analysis Service) and Data Science Integration Server Package

    2. Microsoft Download site for 2025 SQL Server Package (This includes 2025 SQL Server, Analysis Service (DW), and Data Integration Service (Data Mining) all as a Package

    As of Dec 2025, Only 2025 SQL Server Developer Edition is Available to Download Free in This Site! (You Can No Longer Choose 2022 Anymore !)

    Microsoft Downloading Site

    How to Download and Install SQL Server 2025 for Windows (By TA Lei Li) **************************************



    You can download either 2022 or 2025 SQL Server in Window Container here !
    2022 or 2025 SQL Server in Window Container:

    Microsoft Downloading Site for 2022 or 2025 SQL Server in Window Container
    How to Install 2025 MS SQL Server on Docker on New Mac OS (By TA Lei Li) ********************






    2022 SQL Server Developer

    From this site, you can get a bundled Package of SQL Server (with all Other BI Servers together to Install)

    For Step by Step Installation Guide for SQL Server 2022 Developer Edition Downloaded from this site

    See Below for Installation of MS SQL Server 2022 (Developer Version) and Its Client - MS SQL Management Studio (SSMS), and How to Use SSMS to Create the First Database and a Table

    How to Download and Install a 2022 SQL Server with Basic Option Without PolyBase (from MicroSoft Download Site) (RECOMMENDED) *********************
    You Can Choose Either Custom or Basic Option in this installation. Basic Option Without PolyBase is recommended !!


    How to Download and Install a 2022 SQL Server with Custom Option (from MicroSoft Download Site)


    The Newest Full Instruction for 2022 SQL Server, Analysis Service and Integration Service: How to Download and Install a 2022 SQL Server from MicroSoft Download Site



    Another Step by Step Installation Guide for SQL Server 2019 Developer Edition Downloaded from This Site

    Installation Guide of SQL Server 2019 Developer Edition







    How to Install MS SQL Server on Mac:


    Instructions for the NEW Version of MAC OS AFTER 2021:

    The new M1/M2 chips or Apple Chip in the recent MAC does not support any VM installation for MS SQL Server since it has issues with VM.
    The best way is to install Docker as it is free for individual and easy to configure. Please see the Instruction for Docker below.

    Newly Changed Docker Installation Procedure for MAC:

    How to Install 2025 MS SQL Server on Docker on New Mac OS (By TA Lei Li) ********************


    How to Install 2022 MS SQL Server on Docker and Client Software (Azure Data Tool) on New Mac OS (By TA Dharmik Rajesh Kurlawala) ********************


    IMPORATANT NOTE !!
    DO NOT COPY and Paste blindly the set up command from the Instruction !!
    You need run that one docker command with YOUR OWN USER ID and Password !!!!!

    The "PASSWORD = YOU CAN ENTER YOUR OWN" and "MSSQL_USER = ... " lines are just there to let you know that you can substitute your own values for the PASSWORD and MSSQL_USER environment variables if you wish. Those are the credentials you'll use when connecting to the database.

    IMPORTANT NOTES !!
    Those Docker instructions work on Linux. However, copy-pasting from the PDF might add some line breaks to your command, so I'd suggest you copy-paste the following version into a terminal:

docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=MyPassword123#" -e "MSSQL_PID=Developer" -e "MSSQL_USER=SA" -p 1433:1433 -d --name=sql mcr.microsoft.com/azure-sql-edge

    
You need only run that one docker command. 
The "PASSWORD = YOU CAN ENTER YOUR OWN" and "MSSQL_USER = ... " lines are just there to let you know that you can substitute your own values for the PASSWORD and MSSQL_USER environment variables if you wish.
Those are the credentials you'll use when connecting to the database.






    PREVIOUS YEARS' Instructions (These might NOT be working anymore in the NEW version):

    You need only run that one docker command. The "PASSWORD = YOU CAN ENTER YOUR OWN" and "MSSQL_USER = ... " lines are just there to let you know that you can substitute your own values for the PASSWORD and MSSQL_USER environment variables if you wish. Those are the credentials you'll use when connecting to the database. How to Install 2022 MS SQL Server with Container and Client Software (Azure Data Tool) on New Mac OS with Intel M1 or M2 Chips

    How to Install 2022 MS SQL Server with Docker on New Mac with Apple Chip

    How to Install MS SQL Server on Macbook with Intel Chips M1 and M2




    If you can not set up a Docker, please craete a Azure Cloud SQL Server instance. Please see the instructions in the Azure Cloud section far below at the end of the Installation section.




    The old Mac that are comtatitble with VM, you can follow the instruction for VM set up below.


    Old Mac before 2021:
    Installation of MS Sql Server in Docker For Old Mac:
    How to insall SQL Server on Mac

    Or

    Instructions ONLY for the OLD Version of MAC OS Before 2021:

    1. Install Mac OS X Host VM(Virtual Machine) from
    Oracle VM
    Oracle VM Download
    2. Install Window 10 OS on the VM on your Mac then
    3. Install Visual Studion on Window 10 OS on the VM on your Mac then
    4. Install MS SQL Server on the Window VM.








    For Linux:
    If you want to install a SQL Server on Linux, download and install Open Source PostgreSQL directly to your Linux system.

    PostgreSQL to download
    How to Install PostgreSQL on Linux (Digital Ocean Tutorial)
    Information to download and Install for PostgreSQL and Client Tools

    Tips by TA Collin:
    It is important to remember to switch to the "postgres" Linux user before performing administrative tasks.
    This is covered in the DigitalOcean guide above, where a new database and Linux user account are created with the same name.

    Client Software to Download for Postgresql:
    Data Grip: Client Tool for PostgreSQL
    DBBeaver: Client Tool for PostgreSQL


    PostgreSql Tutorials:
  • PostgreSql Tutorial (TutorialPoint site)
  • PostgreSql Tutorial

    • How to Switch a Database in PostgreSQL

    Command list in PostgreSQL



    How to Install MS SQL Server Directly on Linux (NoT For This Course; Recommended Only for Advanced Users)
    How to Install MS SQL Server on Linux
    How to Install MS SQL Server on Linux




    Tips for What To Do After Installation

    Read the installation Guides in the Lab0 Section FIRST for Starting to download !

    1. Make Sure to Install a Matching Version of Visual Studio first before installing SQL Server

    Where and How to Download and Install MS Visual Studio 2019
    Note that Visual Studio 2019 Needs to be Installed First Before Installing 2019 SQL Server.

    2. After everything is installed and set up correctly, Run SQL Server Management Studio - a Client for your SQL Server (It is Under Microsoft SQL Server of your Start menu of your Window) to Play with Your SQL Server

    3. If your SQL Server is not running (You don't see the green arrow on your server name on top left corner of your SQL Server Management Studio), Start the server manually in your SERVICE list - Search with "Service" to find the list. (Read the General Installation Instuctions for this in the Lab0 Section !)

  • Guides for Installing MS SQL Server and Creating Your First Database
  • Guides for Installing MS SQL Server, Error Resolution Tips and Creating Your First Database



    • The Client Software - MS SQL Server Management Studio (SSMS) Together. It will be installed together at the end of installation of 2019 SQL Server.
    See 2019 SQL Server Installation Guides for Installation of the Client SSMS

    How to Use the MS SQL Client - MS Sql Server Management Studio (SSMS) to Connect to Your SQL Server:
    How to Use SQL Server Client - MS Sql Server Management Studio (SSMS) NEW POST !
    Guide for How To Use a Client - SSMS


    If You Need to Download the Client Software SSMS Alone Seperately from the MS Download site, then Install SSMS after Installing a SQL Server. See the new instruction !
    Download and Install the Client: SQL Server Management Studio (SSMS) Download SSMS


    If you want to use MS SQL Server with Naive Client - SQLCMD (Command Line Client):
  • How to USE SQLCMD from SSMS in SQL Server Query -> SQLCMD Mode











    • Azure Cloud

    What To Do If My Laptop Died In the Middle of Lab Due:

    Creating a Database Server Instance on AZURE Cloud:

    Step1: Get a Free Student Credit to Use an AZURE SQL Server Instance
    How to Apply an Azure Free Student Account to Create a Database Server in Azure Cloud Service (Updated by TA Kajal and Margish on August 2023)

    Step2: Create a AZURE SQL Server Instance for You to Use for Labs
    Quick Tutorial: How to Set Up and Create a Database on Azure Cloud

    Other Azure Tutoral Sites:
    Quick Tutorial: How to Create a SQL Database on Azure Cloud
    Quick Tutorial: Getting Started : How to Set Up and Create a Database on Azure Cloud
    Tutorial: How to Create a SQL Database on Azure Cloud


    Step3: Get a Client to Access Your AZURE SQL Server

    Quick Tutorial: How to Use SSMS to Connect to and Query Azure SQL Database or Azure SQL Managed Instance

    FAQs and Azure Query Editor Set Up
    You can choose Any Client tool available to access your cloud SQL Server


    If you have any questions or help on the insatllation and set up, please send email to TA !




    Important Notes on Use of Azure Cloud Server

    The Azure cloud server was suggested as the LAST resolution, it is not recommended as a primary system for CIS430.
    It was suggested as an emergency resolution ONLY when you don't have your own computer system to download, install and set up your own database server or when there is any accidental non-recoverable crash of your system.

    We don't control the MS Azure account at all, which means we cannot help you with the unusual issue on MS Azure student credit accounts or a trouble to obtain the free student credit account. That is something you have to resolve with Microsoft Azure team, it is NOT something we can help with.
    Note that You do NOT need to request and use a database server from Azure Cloud for CIS430 if you have a laptop to download and set up your own databbase server.

    Azure Cloud DB Server is just a backup plan when you don't have any computer to download (from Education site of Azure portal site and Azure portal site is NOT Azure Cloud Service) and set up your own database server and a client for CIS430.
    2GB storage is needed when you actually download the server physically in your laptop to install. You don't download and install the database server physically by yourself when you request to use an Azure Cloud database server since it is already installed and running on their Azure Cloud site.
    You only need to go to the site to use their client site or download their client: Query Editor to connect and login to your database server running on Azure Cloud site.
    So there are NO installation issues when you use a database server you requested on Azure Cloud Server.

    Don't wait for MS Azure site to fix your problem. Looks like MS changed $100 free student credit policy partially, instead, they provide $200 free credit to try their new COSMO DB on Azure Cloud. I do not recommend it in this course since Cosmo DB is for Semi-structured DB for Big Data.

    Please use your server you installed and set up on your laptop for CIS430/530.











    If You Can NOT Install a MS SQL Server or a Use Azure SQL Server Instance, You Can Install MySQL for This Course.
    However MySQL (the common stand-alone version) is not a Full Enterprise Level Database Server. It is your own risk for the differences coming from MySql's limitation
    MS SQL Server is Recommended for this Course to Learn a Full Enterprise Level of Database Systems .


    Installation Guides for MySql Server:

    MySql Download
    MySql Download and Installation
    MySql Tutorial
    How to Create MySQL Database

    MySql Tutorial:

  • How to Create a database and select the database to create a table in MySQL
  • How to Create a database and select the database to create a table in MySQL
  • Example Script to Create a database and select the database to create a table in MySQL

  • For Most Recent Updated Document, See Project 5 Section of Lab Section of CIS 408 Internet Programming Site !









    • FAQs:

    FQAs on Installation of SQL Server as of 2022

    Q: There is only VS Community version is available, is it ok with SQL 2019 Server to install?

    A: It is ok as long as VS Community version is updated to 2019. Note that MS VS Community version is the least featured, it is NOT the VS 2019 which is a Enterprise (Professional) Version. The VS 2019 is best with 2019 SQL Server. Please follow the instructions.

    You can download VS 2019 at Azure Potal -> Education site below
    AZURE Potal Education Menu to Download VS 2019 and MS SQL Server 2019


    Q: For VM set up for Mac OS, Only Window 11 is available instead of Window10. Is it ok to install SQL Server 2019 on Window 11?

    A: It should be OK !
    Please download Window 10 or Window 11 at:
    Window 10 to download


    Q:How to Install a SQL Server on Mac

    A: The new M1 chip in MAC does not support installation of SQL and it has issues with VM.
    The best way is to use Docker as it is free for individual and easy to configure. Please see the Mac section for the Instruction for Docker.
    If you can not set up a Docker, please create a Azure Cloud SQL Server instance. Please see the instructions in the Azure Cloud section.
    The old Mac that are compatible with VM, you can follow the instruction for VM set up.


    Q1: I am wondering if I can just download PostgreSQL alone directly on Linux, or if I also have to create a Virtual Machine and install a SQL Server on it like the Mac users do?

    A: Yes, you can download a PostgreSQL directly on Linux. Only MAC needs a VM to set up to provide Win OS for a MS SQL server because None of SQL servers is based on MAC OS.

    Q2:For Lab Outputs, Do I need the VM for a standardized SQL Server lab output as instructed on the class webpage, or can I just download PostgreSQL by itself and use that instead?

    A: If you are using PostgreSQL, it’s Client is not SSMS, it has its own a Client interface, which will be downloaded and installed together with a PostgreSQL server on Linux directly,
    where you can write SQL/DDL/DML to Submit for the labs. The lab output will be the screen captures of the PostgreSQL Client results. No, you don't need VM for this.

    Q3: I’ve created an account and database on Azure portal site since I am a Mac user. Microsoft does offer a tool to write SQL queries on Mac by connecting to Azure SQL database – Azure Data Studio. Can we use that for lab submissions instead of SSMS on virtual box?
    A: Yes. You can connect to Azure directly from MAC witout VM with the client tool.


    Q4:I realized that my system username and my Laptop name were different than my name after I installed SQL server and SSMS (SQL Server Management Studio).
    So I finally changed the laptop name (and username) to my name and reinstalled the SQL server and SSMS management studio.
    I have been trying to uninstall the server and then install it again. But it is not connecting to the server, it's showing SQL Server Connection errors saying there is no SQL Server by the name.

    A: I never recommended to change your computer name after you installed SQL Server and SSMS. Do Not Change Your Host Name after You Installed SQL Server and SSMS !
    Your SQL server was already named after your old computer name at the installation time, so as SSMS. If you changed your computer name after installing SQL server, you are calling troubles
    You have to change your SQL Server name to your new computer name and propagate it into SSMS as well.

    If the issue still persists, the problem can be fixed by uninstalling SQL Server with other related services installed together and SSMS then freshly reinstall SQL Server and SSMS to pick up the changed computer name as SQL Server instance name
    You will have to remove your SQL Server and all the other Services installed along together with the SQL Server and the SSMS through Add/Remove program in Window
    then reinstall all freshly again to pick up your new host name for a SQL Server instance name and propagate it to SSMS at the installation time.

    Most of the times when uninstallation is needed, students often uninstall the SQL Server only. But there are other services that gets installed along with the SQL server.
    You need to remove each of those services from Add/Remove Programs and functions in Window.
    That will allow the user to freshly reinstall the SQL server as a new instance.

    Q:My SQL Server was working fine a couple of days ago. But today when I was trying to log in, It keeps giving me the error message saying "Cannot connect to the Server". I was trying to fix it by following some of the how to fix it on Youtube, but none of them work.
    So, I am just wondering if you have anything recommend me to try in order to fix the problem?
    A: There are many reasons for this issues. Without knowing that what kind of system activities had been done in your system to cause the issue, it could be coming from the following issues.
    Your error may be coming from your own network issue like your firewall blocks the connection to your Sql server or a wrong TCP/IP setting, or your recent window update made wrong changes/overwrote default files of your SQL Server.

    For example, because a recent windows update may have erased your mssqlsystemresource.ldf file and mssqlsystemresource.mdf file from your binn folder under ms sql program files, then the solution is to get a copy of those files from TA's program files. I guess those files are not unique to each computer. She then put those files in the binn folder and the issue was fixed.

    Sometime this kind of glitch can be fixed by next window update. Try the most recent window update and check the firewall and TCP/IP configuration.
    However, MS SQL Server is usually well maintained, it doesn't require the manual changes like those, try to avoid if possible. If you can't resolve it, use the Azure Cloud for your labs.

    The network related Connection issues are hard to fix because they could be coming from so many different causes in the individual user's environment including Window Update that are usually hidden from the users.
    Microsoft seems to provide the new tool to resolve this new issue.

    If You Have a Error: Unable to Connect to the SQL server, Try below:

    Important Trouble Shooting Tip for the Network Related SQL Server Connection Problems (Shared by Alex Theroux)

    I managed to resolve my initial error of being unable to connect to the SQL server.
    I had tried restarting and several other things. I'm also not sure how much having mutliple versions (2008 or later) of sql on my machine had an effect.
    What did end up solving my problem was after some digging I found the SQL Server Configuration Manager.
    The server was stopped for some reason and telling it to start allowed me to reconnect right away.
  • MS SQL Server Configuration Manager When You Have a Connection Issue to Your SQL Server

    • Some Trouble Shooting Tips:

    When the installation errors are coming from your unusual/incorrect Window OS Setting

    1. Uninstall the SQL and all the programs that were installed with it through Add/Remove Program,
    then go to the Azure link as suggested in class page and redownload the file. Then reset the settings you have done for troubleshooting.
    And finally reinstall SQL.
    Also, make sure to install Visual studio 2019 before the SQL installation.

    2. If you want you can reset the OS and make sure to uninstall the SQL, after that first install Visual studio 2019 and then run the SQL installer.
    Even after these, if you are getting the same error then go to below folder –
    C:\Users\your_username\AppData
    Then select Local folder and then look for Temp folder, then do a right click on Temp folder and then select properties.
    In properties select Security and then select Everyone and select the Full Control checkbox and finally apply the changes and then try installing.
  • Trouble Shooting Error Resolutions When Set Up SQL Server with ODBC/JDBC



















    • Instructions for Old SQL Server Versions:
    (Note that They May Not Be Available to Download in MS Azure Site Anymore)
    If your computer does not have enough memory to install 2019 or 2017 SQL Server, you can try one of the older versions below.
    Instructions for Any version From 2017 SQL Servers:

    SQL Server Installation Guides Specific for Each Version and Each Platform:

    Installation Guides for 2017 SQL Server:

    2017 SQL Server:

    Where to Go and How to Download a 2017 SQL Server with Analysis Service and Integration Service (SSDT) Together from the MS Site
    (Note that Mixed Mode was chosen in this Guide. Instead, You can Choose Window Authentication)

    Installation Guides for 2017 Visual Studio + SQL Server (with SQL Management Studio) + Analysis Service + Integration Service (MS SQL Data Tool) ALL Together NEW POST !
    Note that You can Still choose Window Authentication !
    FAQs for Installation Guides for 2017 Vs + SQL Server (with SQL Management Studio) NEW POST !
    Guides for Downloading and installing 2017 Visual Studio and 2016 SQL Server


    1. Install Visual Studio 2016 or higher first before installing 2016 SQL Server.
    Install Visual Studio 2013 or higher first before installing 2014 SQL Server.

    Note that any old versions of Visual Studio (2010 or 2012 VS) won't work with 2014 SQL Server. Visual Studio (2013 or 2015 VS) won't work with 2016 SQL Server.

    2. 2014 SQL Server won't work that well on Window 8 OS. Recommend it on Window 7 or Window 10.

    3. 2016 SQL Server can be installed only on Window 8 or Window 10. It can NOT be installed on Window 7 OS or any lower version.

    4. For Those who want to install Enterprise Edition
    Follow the steps to install MS SQL Server Enterprise Edition and Choose Window Authentication Mode and then Add Current User (add you as administrator in your system) in Server Configuration and Database Engine Configuration steps.

    5. For Any SQL 2016 Server, Any MS SQL Server Package Does not Have the Client Software - SQL Server Management Studio. Download it separately from the MS Download site and install it after Installing a SQL Server. See the new instruction !
    Download and Install the Client: SQL Server Management Studio (SSMS) Download SSMS











    The Lab Submission Link and the Deadline of Each Lab Will Be Posted on the Class Blackboard !
    You Have to Start Working on Labs Before the Submission Link Are Created on Blackboard for Each Lab Submission.

    If You did an Extra Credit Part, Mention about What Part is Done for Extra Credit at the Front(Cover) Page of Your Report in Bigger and Bold Font !

    Always Follow the Deadline of Each Lab Posted on the Class Blackboard.
    The Deadlines mentioned on the Class Webpage Are Tentatively Scheduled at the Beginning of Each Semester.

    Please Identify Your Course When You Ask Me in Email !






    Required Labs:


    The Lab Submission Link and the Deadline of Each Lab Will Be Posted on the Class Blackboard ! Wait For the Lab Submission Links Are Created on Blackboard for Each Lab

    Read CAREFULLY Below the Lab1 Section for How to Create Lab Output Report !




  • Important Things to Know to Create Your Lab Report to Prove that Labs are Done By You in Your Own SQL Server !!

    • IMPORTANT NOTES !!!!

    Do NOT Change Your Computer Name or Your UserId AFTER You Install a SQL Server

    (Your Account with UserId Was Created during the Installation) !!!
    The System Will NOT Allow you to change your userid
    You will have Network related system TROUBLES/PROBLEMS if you change your computer name AFTER you installed a SQL Server !!

    If your userid and your Computer Name are not YOU, Explain why your username/Computer Name are not YOU to me, TA in email, and in the Lab report!



  • Guides for Installing MS SQL Server and Creating Your First Database

  • How To Debug in the SQL Text Editor




    • Lab1 :

  • Lab Assignment 1 on Creating and Populating Tables

    • Notes for Labs:

    1. Make Sure to Create a Database Named Company_YourLastName (Use the first 5 characters of your last name or 3 chars of first name + 3 chars of last name) 
    2. Show the Execution in Screen capture in ONE window.
    3. Make Sure to Insert All 4 Departments in Department table.
    4. Make Sure to Show Your Table Contents with Select * From your_table_name; (with the Screen capture of the Select Query Results) in your report right after Inserting all the data.

  • Example of Lab Output Screen Captures Should Show each Query and the Server Response together in a Window of Your SSMS and Your SQL Server
    • For Lab1, Show a Screen Capture for Each SQL Command in the Lab1 Report
    Write each Sql command in each .sql file to Execute one by one to See How Your Sql Server Responses to DDL, DML, and SQL



    Create Database named in the following rule:

    Company_YourLastName (For Example, Company_Chung),
    OR, In Case There ARE More Than One Person with the Same Last Names in Class:
    Company_3Characters of Your First Name Followed by 3 Characters of Your Last Name with Each First Letter in Capital
    For Example, Your Name is Sunnue Chung, Create Database Named: Company_SunChu
    Either Way Is OK As Long As TA Can Recognize Your Database with Your Name (Don't Send Me an Email Asking Which Naming Rule to Follow; It is NOT Important!)


    To Name Your .sql Files to be identifed with your name, for example, use Prefix with only the first 3 characters of your first name followed by the first 3 characters of your last name with Each First Character in Uppercase.
    For Example, Your Name is Sunnie Chung, then SunChu_CreateTable.sql


    Notes for Lab Output Report:

    1. Make Sure to Show Your SQL Code and the Executed Result by Your Server in the SAME Window in a Screen Capture !

    2. Make Sure to Show your Server Name in the SAME Window with Your Query and the Result !

    3. If Your Lab Report Does Not Show Enough Information about Your Server Name, Your Userid Folder Name, Your Sql File Names Are Related to Your Name,
    I Will Assume that You Are Hiding Someone Else's System Information for Cheating.

    4. Copy and paste every code in the doc file with the screen captures of the execution results together. It is preferred for TA to read since sometimes the codes inside the screen captures look too small to read.
    For the rest of the coming Labs, copy and paste each text of your query outside of the screen capture to show them clearly.





    FAQs for Lab0 and Lab1:

    Q: For all labs is it required that our file names follow the format outlined in Lab0? 
    A: No need for all the rest of the Labs. Only for Lab0 and Lab1, but recommend to do for the rest of the Labs; It is NOT important in grading.

    Q: Also, must we create a new .sql file for each command? What I did was use the same .sql file for each command I ran in Lab 1. 
    A: Yes, only for the Lab 1. It may look redundant and silly, I know you can do all in one sql file.
    But it is needed for more than 1/3 of the class that are confused and not be able to debug errors generated by all the commends executed in one sql file. 

    Q: In terms of set up procedure for Lab 0, what would you like to see from non-Microsoft users? 
    Here's what I have for Lab 0:
    A screenshot of the database connection window.
    screenshot of a terminal window next to my database client with my computer's hostname and other specific information listed.

    A: It should be good enough. Lab0 is to force everyone to complete their system set up and get used to use the client and the SQL server for Lab1.
    The Screenshots with the computer name and the server's name are needed to prevent cheating for a small bad student.
    So it is OK if your client does not provide the name of host and server. TA and I know about this.
    Please mention that you used different client Sofware at the beginning of the Lab0 and Lab1 for TA.

    Please don't email me with any more questions on this because it is not an important thing !   







    SQL Server Data Types:
  • Data Types in MS SQL Server
  • Numeric Data Types in MS SQL Server
  • DateTime Data Types in MS SQL Server
  • DateTime Query in MS SQL Server

    • Basic DDL and DML Examples:
  • Example SQL: Create Database, Delete/Drop Table
  • Example SQL: Handling NULL values New Post !
  • FAQs for Lab1




    • For Mac:

    See the Installation Guides for Mac in the Lab0 Section Above
  • How to Display DB Tables On a Client on MAC


    • For PostgreSql:

  • PostgreSQL Server Download
  • PostgreSql Tutorial: Tutorialpoint
  • PostgreSql Tutorial

    • How to Switch a Database in PostgreSQL

    Command list in PostgreSQL








    Lab2 : Creating Company Database

    Lab2_1 on E-R Modeling :
    Creating an E-R Diagram of Company database with Identifying Entities and Realtionships

  • Lab Assignment 2_1 on Creating E-R Diagram
  • FAQs for Lab 2_1

    • Note that You have to identify all the attributes for each Entity
    and Identify Key attributes, Multi-Valued Attributes, Composite Attributes, and Derived Attributes in your E-R Diagram.

    Some ER Diagram Tool avaliable below. You can use any E-R Diagram Tool for Lab2_1.
    You may also draw your ER Diagram in a Doc file or in a Pencle and Paper only for Lab2_1.
  • Useful ER Diagram Tool
  • Microsoft Visio ER Diagram Tool






    • Lab2_2 on Creating a Database Scheme from E-R Diagram

  • Lab Assignment 2_2 for Creating a Database Scheme from E-R Diagram and Initial Loading with Data
  • ER Diagrams for Lab2_2
  • Final Database Scheme and Data to Insert for Lab2_2

    • Additional Requirements for Lab2_2 !

    1. Make sure to insert one tuple with your name and info as an employee working for Headquater Dept with Dno = 1 then show your tuple inserted in Employee table.

    2. Before Creating Tables in Correct Scheme Using DDL for Part2, Remove the Tables tested in Part1 From the Server with Delete Table and Drop Table !
    Show the effect of Delete and Drop followed by Select statement in Your Lab2-2 Report.

    3.For Visualizing with a Database Diagram:
    The Azure Cloud clients and the Mac Docker clients Do not Have the Visulaizing ER Diagram Feature/Tool.
    In such cases, It is ok Not to Provide a Database Diagram. Instead, Please Provide the screenshot of Your Object Explorer or Table List in Your Client to Show Your Tables with PK add FKs Created in Lab2_2.



    Lab Guides:

  • Tips with Examples For Creating Database Schema and Initial Loading*********
  • Tips For Creating First Database Schema and Initial Loading *************
  • Error Shooting Tips for Lab2_2 *****
  • Data Types in MS SQL Server
  • Delete Table and Drop Table
  • Example Test of Database Integrity Constraints

    • FAQs for Lab2_2:

  • FAQs for Lab 2_2

    • Q: So I completed my database schema and I’m driving to use the database diagram tool but it’s giving me an error.

    A:
    This could be coming from many different reasons - your access control issue, your SQL server version that doesn't support, or your database scheme creation. See the Error Shooting Tips for the resolution above.
    If you can't find any resolution, then expand your Table scheme with all the columns in your Object Explorer
    to show the ENTIRE database scheme with PK, FK columns marked in multiple screen captures in the report. That can replace your DB diagram.









    Lab3:
  • Lab Assignment 3 for This Semester (Without Correlated SubQ) (Not for the Summer Semester)
  • Lab Assignment 3 (With Correlated SubQ) for The Summer Semester

    • IMPORTANT NOTES for Lab3:

    For Part 1 of Lab3:

    - Do NOT add yourself to the Department 7. Insert your Employee.Dno with 1.
    - You can Make Up for the rest of the column values for your own.

    1. Add yourself into the Employee table, and
    2. Insert Your 2 Dependent Information (2 records) into the Dependent table, and
    3. Assign 2 projects to yourself by inserting two records into the Works_On table.

    4. Then you need to write 1 SQL statement (Select From Where) to retrieve All the Information of your Dependents and Projects using Join Conditions based on the PK-FK Relationships.

    The expected output should be multiple records (depends on how many records you will insert on the Dependent and Works_on table) with all the information from the 3 tables.


  • Example SQLs to run and see on Self Join
  • Example SQLs to run and see on Set Operators
  • Example SQLs to run and see on Different Join on Composite Keys NEW POST
  • Example SQLs to run and see on IN/NOT In, EXISTS/NOT EXISTS with Correlated Subquery








    • Lab4:
  • Lab Assignment 4 For This Semester !

    • Q5 is not required for CIS 430 Students. It is for Extra Credit for CIS430. It is required for CIS530.

    Notes on Lab4:

    Q:for the dependents to be inserted, the date of birth is not given in the Lab4, do I need to take any suitable date from my side or I should insert NULL for the column?

    A: Do NOT insert Null. If any column values to be inserted were not given in the Lab4, it means that those are not importatnt, make up your own to insert. Those values do not matter to the query results.



  • Example LOJ with Group By and Aggregate Fns








    • Lab5:
  • Lab Assignment 5 on View, Stored Procedure, and Cursor


  • FAQs for Lab5
  • Example of Insert Into Select
  • Example of Creation and Execution of Stored Procedure and Cursor
  • MS Specific Syntax for Creation and Execution of Stored Procedure
  • MS Specific Syntax for Cursor





    • Lab6: (Extra Credit for Spring 24 !)
  • Lab Assignment 6 on Referential Integrity and Logging Audit table with Trigger (Not for This Semester)
  • Lab Assignment 6 on Logging Salary Audit with Trigger

    • Important Note for Lab6:
    For the column DML_Type, Insert a string either update, insert, or delete for each triggering DML statement.

    If the Triggering Update statement changed 4 rows, all the 4 rows need to be inserted into the Audit Table.
    Usually ROW trigger will do this one by one by executing the trigger body per row updated.
    However, in MS Trigger Syntax, there is no ROW trigger, everything goes as Table Inserted and Deleted.
    So you have to use a Cursor for each row execution to be inserted into Audit table.

  • Trigger in MS SQL Server Specific Syntax
  • Lab6 Output Example
  • FAQs for Trigger Lab6 on Employee Salary Audit table
  • FAQs for Trigger Lab on RI and Audit table (Note that This FAQs Includes Trigger to Implement RI and Audit Logging) -- Not For this Semester !









    • Extra Credit Labs for CIS430 Students:

    Choose One out of the following Extra Projects 6_1 or 6_2, or Lab7 below:

    Lab6_1: (Required for CIS530 and Honor Contract Students!)

  • Lab Assignment 6_1 (Due By Last Day of Classes of the Semester) Required For CIS530/Honor/Contract Course Students

    • IMPORTANT NOTES for Lab6_1 !!!

    - Create an E-R Diagram. You don't need to Create a Class Diagram for This Lab.
    - For the CRUD Functions, You Have To Created a Stored Procedure for Each of the CRUD Operations (Don't worry about E(xecute), which means Stored Procedure Execution)

  • FAQs for Lab Assignment 6_1

  • Extra Credit Lab Assignment 6_2 (Due By Last Day of Classes of the Semester) Required For CIS530/Honor/Contract Course Students
  • Extra Credit Lab Assignment 6_3: Do research on How Relational Database Server Achieves Database Privacy/Security and Write a Summary Report on it. (in Minimum 4 Pages)(Due By Last Day of Classes of the Semester)(NOT for This Semester!)







    • Lab7 : (Extra Credit for CIS430 !)

    Lab7 Is the Required Labs For CIS 530 and Honor Contract Students!!

    Lab7 For Extra Credit for CIS430 Students

    You May Choose Any Platform with Any Language for a Web Application for Lab7

  • Lab Assignment 7 (Due By Last Day of Classes of the Semester) (Required for CIS530 and Extra Credit for CIS430)



    • The Recent WAMP Sever Package Requires a New Microsoft C# Integration that Is Not Properly Configured, Which Causes Errors in Installation.
    Instead, Use the MAMP Server Package below which is very similar.

  • MAMP Server with Aphathe Webserver, MySQL, and PHP



    • You can set up Apache Webserver (HTTP Server) on Window with WAMP/MAMP Server
  • Apathe Webserver on Window and Download Site !



  • WAMP Server Download Site !
  • Trouble Shooting WAMP Server SetUp for Lab7
  • Example HTML Code with Form Element: How to Create User Input Boxes in a Webpage to send HTTP Request to a PHP Server script
  • Example code of ODBC in a Web application Server in PHP


  • For More on HTML and WAMP Server, See Project 5 of Lab Section of My CIS 408 Internet Programming Class Site !

    • MySql Tutorial
  • How to Create a database and select the database to create a table in MySQL
  • Example Script to Create a database and select the database to create a table in MySQL

    • LAMP Server Set Up:
  • LAMP Server Set Up Guide

  • PHP Guide: My First PHP Page
  • PHP Guides with Database/ODBC API

    • Note that the sample codes here have deprecated and replaced with new methods in the new version of PHP (because of the web security, it changes very fast almost every six months), make sure to replace them with the updated methods in php.net site above !
  • index.php
  • search_musicapp.php
  • search_musicapp1.php










    • Lab6_2: (For Honor/Contract Course Students) Semi-structured Database with MongoDB:
    Create Semi-Structured Database for the 100 Business Yelp data in JSON with MongoDB
    See MongDB and Node JS Guide below for Extra Credit Lab8 !
  • Extra Credit Lab Assignment 8 Required For Honor/Contract Course Students

    • Data Source:
    Zip file for JASON files -- with the valid JSON format

    Yelp Challenge Data Set


    NOSQL Database: Object Relational Mapping for Semi Structured Database
    MongoDB:
    Lecture Notes on Mongo DB
    Introduction to Mongo DB
    Mongo DB: Databases/Collections
    Mongo DB: Document Format
    Mongo DB: CRUD Operations
    Mongo DB: Aggregations
    Mongo DB Query Examples Comparison with SQL
    Mongo DB Join Operator: lookup with unwind for array

    Mongo DB Setup:
    MongoDB Download
    Class Note_22_3: MongoDB Getting Started
    Class Note_22_3: MongoDB Shell options to start
    MongoDB Site: How to Import Data Set
    Class Note_22_3: MongoDB Resources
    Mongo DB Documentation

    Set up Guide for MEAN Stack: Also See Project 3 Section For Node JS Set up Guide or CIS 408 Class Lectures: Scroll down to Node JS: MEAN Stack Section
    Node JS with Mongo DB Setup Guide
    Node JS Setup Guide
    Node JS with Mongo DB Setup Guide
    Angular JS with Node JS with Setup Guide

    Node JS API for MongoDB:
    NodeJS: Integrating to MongoDB
    Mongoose: Node JS API for MongoDB: For connecting/querying to MongoDB:
    Mongoose To Set Up
    Mongoose Guide

    Application Examples Built with Node JS
    Sample Web Application Using Node JS with Mongo DB
    Sample Web Application with Angular JS and MS SQL Server

  • Text Book Link at the CSU Library: Fundamentals of Database Systems 7th Edition
  • Text Book: Fundamentals of Database Systems 7th Edition
  • CIS430/530 Text Book to Loan at the CSU Libarary : Fundamentals of Database Systems 6th Edition

  • Class Lecture Notes with Tentative Schedule

    Class Chapter / Topic / Specific Objectives / Activities
    Special Topics



    Senior Design Projects on Big Data and AI:

    2022 - 2024:
    Big Data and AI Projects:

  • Intellestate: Intelligent Real Estate Property Recommendation System: The Candidate of Best Senior Project in Engineering College of 2023) (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • Sign to Speech: Sign Language Translating Glove (Created From CIS430 and CIS492/593 Data Mining)
  • Infectious Disease Predictor (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • Social Media Opinion Analysis for Congress Bills (Created From CIS430, CIS492/593 Big Data, and CIS408)


    • 2020 - 2021:
    Big Data and AI Projects:

  • Social Media Opinion Analysis System for Prediction of 2020 Presidential Election (The Candidate of Best Senior Project in Engineering College of 2021) (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • 2021 Senior Project: Stock Market Analysis Service System (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • Intelligent Infectious Disease Tracking System (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • Product Review Sentiment Analysis System (Created From CIS430 and CIS492/593 Big Data)


    • 2019 - 2017:
    Big Data and Data Science Projects:

  • The Candidate of Best Senior Project in Engineering College of 2019 by Joel Stell et. al. (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • Web Search Engine (Google Like) over Research Paper Repository by Nick McCoy, et al (Created From CIS430, CIS492/593 Big Data, and CIS408)
  • The Best CS Senior Project Winner of 2017 (Created From CIS430, CIS492/593 Big Data, and CIS408) by Mike D'Arcy and Utkarsh Patel



    The Best Senior Design Projects Created from CIS408 and CIS430:

    The First Prize Winner of 2016 Senior Project From CIS430 and CIS408 by Nick White (Now in FaceBook), et al




    Special Topics of Database for Independent Study:
    Special Study Guides for Data Science by Nick White (Now in FaceBook) From Independent Study with Nick White (Now in FaceBook and The First Prize Winner of 2016 Senior Design )



    Database Career Opportunities


    • 1



    INTRODUCTION:

    Architecture of Modern Enterprise Database Management Sysrem (DBMS):

    Client - Server Architecture:

    Database Applications

    Examples of Common Client Forms (Software) for a Database Server (SQL Server)



    What Does a Database Management System (DBMS) Do for the Most Important Enterprise Applications:

    Class Note 1_1: Overview of Database Management System (DBMS) and Database

    Examples of DIfferences Between (Raw and Big) Data and Information/Knowledge



    Textbook Chapter 1 : Databases and Database Users


    Class Note 1_2: First Look on Relational Database Management Systems (RDBMS) and Big Data




    2-3


    Introduction to Relational Database Management System (RDBMS - SQL Server) and Architecture of RDBMS

    Lecture Note on RDBMS Architecture

    Chapter 2: Archetecture of Relational Database Management System (RDBMS - SQL Server) and Use of SQL Server

    RDBMS Archetecture


    Database Query Language to SQL Server - Relational Database Management System (RDBMS):

  • DDL(Data Definition Language): Creating a Database Scheme with DDL(Data Definition Language): Create Table Statement
  • DML(Data Manupilation Language) : Insert/Update/Delete Statements
  • SQL(Structured Query Language for Retrieval) : Select_From_Where Statement


    • How To Create a Database in MS Sql Server

    How to Create a Database and a Table in Step by Step ************

    To Create a Table in MS Sql Server, You have to Create a Database first at master level with "Create Database"
    Once Your Database was created, You Have to Change Your Current Database from master to Your Database with "Use" Command
    Once You Changed Your Current Database to Your Database, then You Can Create a Table in Your Current Database

    Do NOT Create a Table in the Master Level !!!!!!

    All in One Script: Examples of DDL and DML to Create a Database and a Table in MS SQL Server ************


    To Create Any Database Object (Table) in RDBMS, There are Always Two Steps to Follow:
    Database Object (Tables) Creation in 2 Steps:

    Step1: Create a Database Scheme (Meta Data) in DDL
    Create a Scheme of Table (Structure of a Table) First -- The server stores and maintains this scheme information of the table into the System Catalogues (System Tables)
    Example of System Catalogue/Data Dictionary to store and maintain Users' Meta Data of Databases

    Step2: Populate with Database Instances (record values) in DML (Insert, Update, Delete)
    Insert Each Record in a Row (Tuple) into the Table (Populate the table with the contents)


    Examples of Creating a Table Scheme in DDL and Populating the Table in DML: Insert ************

    Examples of Create, Insert, Delete, and Drop Table ************


    Examples of Alter Table to Add/Change the Existing Column in the Existing Table in DDL ************
    Examples of Alter Table to Change the Scheme of the Existing Table in DDL ************


    Important Lecture on Database in DDL/DML/SQL:

    Difference Between DELETE and DROP Statements:

    There are Always Two Steps to Create a Table in RDBMS:
    Step1: Create Meta data in DDL and
    Step2: Populate Data Records in DML

    Therefore, to Remove a Table from the Server, it takes Two Steps:
    1. Delete Table to Remove the Contents (Tuples/database objects) of the Table first
    2. Drop Table to Remove the Scheme info of the Table from the System Cataloges

    Examples:
    Create Database, Create Table, Delete Table, Drop Table************


    Important Lecture in Inserting Records to a Table
    How to Insert a Missing value into a Column with Null: When There is No Value for a Column of a Tuple Because it is Missing/Unknown/Unapplicable,
    You Have to Mark it (any missing value) with a special character symbol: null in the Insert statement
    You Should NOT insert ' ' (space) or '' (null character) as a missing value for a column since space or null character are stored as the corresponding unicodes
    Examples:
    Examples of SQL Server Handling Missing Values with Null in Insert IMPORTANT !!******



    Data Types in SQL Server:*****************
  • Data Types in MS SQL Server
  • Numeric Data Types in MS SQL Server
  • DateTime Query in MS SQL Server
  • DateTime Data Types in MS SQL Server



    • Summary of Database Operations in DDL/DML/SQL:
    Examples of Database Operations: Create Table in DDL, Insert, Update, and Delete in DML, and Query(Retrieve) with SELECT


    Examples of Basic of SQL (Structured Query Language):
    Examples of Basic of SQL Part I
    Examples of Basic SQL Part II
    3-5





    Relational Database Design

    Phase 1: E-R Modeling



    Lecture Note on Entity Relationship (E-R) Model for Relational Database Design IMPORTANT !!******

    More On E-R Modeling with the Entity-Relationship

    Lecture Note on How to Identify Cardinality of Relationships in E-R Model

    Running Example to Develop an Entity-Relationship(E-R) Diagram to Create Company Database




    Chapter 15 : Important Guidelines for Correct Database Design with Normalization in First - Third Normal Forms




    Lecture Note on Important Database Constraints/Rules:

    Chapter 3: Relational Database Model and Database Constraints IMPORTANT !!***********





    Phase 2: Transformation from E-R Diagram to a Relational Database Scheme

    Creation of a Realtional Database in a Correct Scheme in RDBMS (Relational Database Management System):

    E-R Diagram of Company Database


    How to Transform E-R Daigram to a Correct Database Scheme

    Relational Database Constraints and Rules for Transformation Rules from E-R Daigram to a Database Scheme:

    Transformation Rules from an Entity-Relation (E-R) Diagram to a Correct Database Scheme:

  • Lecture Note on How to Transform ER Diagram to a Correct Database Scheme IMPORTANT !!*********


    • DDL to Create a Database Scheme:

  • Chapter 4: (The First Half of Chap4): DDL and Data Types to Create a Database Scheme IMPORTANT !!*********

  • Tips with Examples For Creating Database Schema and Initial LoadingIMPORTANT !!*********

  • Example of the effects of Constraints with Inserting NULL values IMPORTANT !!



    • Lecture Note on Referential Integrity Constraint




    6-7


    Basic SQL and DML:

    Chapter 4: Later Half of Chapter 4: Basic SQL and DML

    SQLRelated Lectures:

    Lecture Notes on SQL Processing Semantics

    SQL Operators to Process SQL: Selection (Filtering), Join, Union


    Lecture Notes: Union Queries and Aggregation with Group By Scroll Down to Slide 45 for Union Queries



    Important SQL Examples: Join and Set Operators (Union)

  • Example SQLs to run and see on Self Join
  • Example SQLs to run and see on Set Operators
  • Example SQLs to run and see on Different Join on Composite Keys
    • Important SQL Examples: Correlated Subquery with In/Not In and Exists/Not Exists

  • Example SQLs to run and see on IN/NOT In, EXISTS/NOT EXISTS with Correlated Subquery
  • More Example SQLs for IN/NOT In, EXISTS/NOT EXISTS with Correlated Subquery




    • Summary of SQL -- Quick SQL Tutorial
    Introduction to SQL/DML



    Class Note_9: All Basic SQL Examples to Remember



    7-9


    Advanced SQL:

    Class Note_8: Chapter 5 Complex SQL IMPORTANT !!

    SQL Examples:

    Group By with Aggregation Query Examples:

    Example SQLs to run and see on IN/NOT In, EXISTS/NOT EXISTS with Correlated Subquery IMPORTANT !!

    More Example SQLs for IN/NOT In, EXISTS/NOT EXISTS with Correlated Subquery IMPORTANT !!

    Examples of LOJ with Group By and COUNT behavior IMPORTANT !!
    Examples of LOJ with Group By and Aggregate Functions IMPORTANT !!
    Example OUTPUT of LOJ with Group By and Aggregate Fns IMPORTANT !!
    Variations of Group By on LOJ with Aggregate Fns -- COUNT IMPORTANT !!
    Examples of Correlated SubQ combined with Group By and Aggregation IMPORTANT !!



    Other Advanced SQLs:
    MS SQL Server: Identity Column and Index Creation (See the last 5 slides at the end for Identity Column and Index)

    MS SQL: Grant and Revoke
    Class Note_8_3: Database Security: Grant and Revoke NEW POST !
    Class Note_8_4: Database Transaction: COMMIT and ROLL Back NEW POST !
    10


    Relational Algebra:

    Class Note_10: Lecture Note on DBMS Archetecture in detail
    Class Note_11: Chapter 6 Relational Algebra
    Class Note_12: Lecture Notes On Relational Algebra
    Class Note_13: Chapter 6 Relational Calculus with SQL
    Class Note_2_1: Example of Query Execution Plan Generated by Optimizer
    10-14



    SQL Extensions: View, Trigger, Transaction

    Main Class Note: SQL Extensions: View, Trigger, Transaction NEW UPDATE !


    Common Table Expression (CTE) as Inline View (Temporary View within SQL):

    Common Table Expression (Inline View) in MS SQL


    There Are Two Ways to Save a SQL Query Result Permanently in a SQL Server:
    1) Create View as Select
    2) Create Table with Insert Into ... Select

  • View

    • Examples of View:

    Simple Examples of How to Create, Select, and Alter View
    Note you have to use Alter View in MS SQL instead of Replace View statement

    Good Examples of How to Create, Select, and Alter View


  • Insert ... Into Select
    • Examples:
    Example of Insert Into Select

    How to Update from Two tableJoined Result



    Advanced Topics: Database Security with View:
    Class Note_15_4: Database Security
    Class Note_8_3: Database Security: Grant and Revoke Example



    Trigger:

    Class Note_14_3: How DB Sever Updates a Row in a Table NEW UPDATE !
    Trigger in MS SQL Server Specific Syntax
    Class Note_15_4: Example of Instead of Triggerin SQL Server
    Class Note_4_1:Referential Integrity Constraint



    Transaction:

    Examples of Transaction, Commit, RollBack

    Class Note_14_2: Example of Transaction with Two Users
    13-14



    Stored Procedure, Embedded/Dynamic SQL, Table Function, User Defined Type:

    Class Note_15: Database Programming: Embedded SQL/Dynamic SQL, Cursor, Stored Procedure, Table Function, UDF
    Class Note_15_1: Example of Stored Procedure
    Example of Creation and Execution of Stored Procedure and Cursor in MS SQL Syntax

    Class Note_8_2: MS SQL Server: Identity Column and Index Creation (Scroll Down to Slides 40 - 44)

    Examples of Creation and Execution of Cursor Syntax in PostgreSql
    Note that the For loop in a database server does not need to create a seperate Cursor since a Cursor is already built in the Foor loop in a database server.

    MicroSoft Specific Syntax for Creation and Execution of Stored Procedure

    MicroSoft Specific Syntax for Cursor




    Extended Embedded SQL, Dynamic SQL with JDBC(Java Database Connectivity)/ODBC(Open Database Connectivity)

    Web Application with JDBC(Java Database Connectivity)/ODBC(Open Database Connectivity) to Connect to Database Server from Application Server:

    Class Note_16_1: MySQL Applications Using Java & JDBC

    Class Note_16: Building Web Applications with RDBMS and Java & JDBC

  • Example code of ODBC in a Web application Server in PHP

    • Class Note_17: Lecture Notes On Embedded SQL Using Java & JDBC



    Review:

    Client - Server Architecture of Database System (Server):

    Database Applications

    Examples of Common Client Forms (Software) for a Database Server (SQL Server)




    Design Guide of REST API Based Web Application Server Implementation with Database Server as Backend:

    1. One Server Module to Handle Resources (objects/records, files, or blocks) for Each HTTP Method Type to Handle Get/Post/Put/Delete Requests
    2. Mapping Each Method to each CRUD (Create/Retrieve/Update/Delete) Operation in Database Server with Stored Procedures

    HTTP Method Types of Client HTTP Request Handled by the Corresponding REST API Based Web Application Server Modues that Calls (routes) to Stored Procedures for Each CRUD (Create, Retrieve, Update, and Delete) Operations in Database Server

    Mapping between HTTP Methods of Web Application Client HTTP Requests to the Database CRUD (Create, Retrieve, Update, and Delete) Operations in Database Server

  • Get ==> Retrieve/Search Resources ==> SP_Search(input parameters): Select

  • Post ==> Create, Update Resources ==> SP_Insert(input parameters)/SP_Create, SP_Update(input parameters): Create/Insert/Update

  • Put ==> Change /Update State of Resources ==> SP_Update(input parameters) or SP_Insert(input parameters): Update

  • Delete ==> Delete Resources ==> SP_Delete(input parameters): Delete


    • Example Php Codes of Simple Web Application on WAMP Server Platform with MySql Server

    Example Codes of a Rest API Based Web Application on .Net Framework to Handle(Map) CRUD Operations to a Call each Stored Procedure in MS SQL Server








    Building a Web Application Server in PHP, JDBC/ODBC, and Database Server

    For a Client side of HTML in a Webpage:
  • Example HTML Code with Form Element: How to Create User Input Boxes in a Webpage to send HTTP Request to a PHP Server script

    • For a Severside script in PHP:
    Class Note_20: Chapter 15:Database Programming Using PHP
    Class Note on Database Programming with Introduction of PHP/ODBC
    Class Note_18_1: PHP Tutorial
    Class Note_19: Database Programming: PHP



    Advanced Database Programming: Object Relational Database

    Class Note_15_2: Introdction To Object Relational DBMS using UDF and UDT

    For More Advanced Subjects in the Database for AI industry, You Must Take CIS492/593/DSA469 Big Data and CIS492/593/DSA460 Data Mining BEFOR You Graduate For Your Career!!!

    Syllabus for CIS492/593/DSA469 Big Data

    Syllabus for CIS492/593/DSA469 Data Mining <




    14-16


    Database File System and Index:

    Class Note_21: Chapter 17:Disks-FileStructure-Hashing
    Class Note_22: Chapter 18:Indexing for File Structure
    MSDN: Create Index


    17

    Class Note_23: Chapter 12:XML
    Class Note_24:Introduction to Semi-Structured Database and XML

    ==> Completion of Homeworks/Labs is required for obtaining a passing grade.

    • This is a tentative scale and
    it could be changed

    Letter
    Grade

    Quality Points

    		 


      A

    > 93%  

    		A: Outstanding (student's performance is genuinely excellent)

      A-

    90% - 93%

    		 

      B+

    87% - 90%   

    		 

      B

    82% - 87%

    		B: Very Good (student's performance is clearly commendable but not necessarily outstanding)

     

      B-

    80% - 82%

    		 

     

      C

    75% - 80%

    		C: Good (student's performance meets every course requirement and is acceptable; not distinguished)
        D 65%-75% D: Below Average (student's performance fails to meet course objectives and standards)

     

      F

    <65%

    		F: Failure (student's performance is unacceptable)

    ADA Adherence. If you need course adaptations or accommodations because of a disability, if you have emergency medical information to share with me, or if you need special arrangements in case the building must be evacuated, please make an appointment with me as soon as possible. My office location and hours are listed on top of this syllabus. If you need further information, please contact the ACCESS office, phone number 687-5106.

     

    Programming standards

    • Every program must include your name, CSU ID number, Class, Section Number, Hours, the words 'Homework # ...', and a short description of the assignment. For example: 
       ' Name: Mark Zuckerberg  
 ' ID: 1234567            
 ' Homework #1            
 ' Description: Computing the average life of a light bulb
    • Every variable should have a meaningful name (this includes function/procedure/subprogram names).
    • Every portion of the program should be as cohesive (single purposed) as possible. This leads to a large number of small functions.
    • Every function (including the main function) should be preceded by a comment indicating its arguments and a description of the transformation it performs.
    • Non-obvious code within a function should be explained.
    • Code should not be over commented.