UWB Physics Education Research Website Prototype & Documentation for Phases of the Web Development Cycle

Client Documentation

BIT 113 Website Prototype Cycle – Phase Documentation

Link to PER Website: http://studentfolders.student.cascadia.edu/bit113/PER/prototype/home.html

Link to PER Documentation Directory: http://studentfolders.student.cascadia.edu/bit113/PER/Client_Doc_2/

Link to PER Presentation Slides: https://docs.google.com/presentation/d/1dEbRPU5CIGWeNze8REsDJx4fFdFLWlhSTtf-TzCR6_A/edit#slide=id.g10235077323_0_87

Access to upload files to BIT 113 User Interface Development directory site. http://faculty.cascadia.edu/brianb/ftpsetup.html

Database – Final Project

Tasks

  1. Download the datasets

In many database problems or applications, the data that will be used to populate the database are usually available as a flat file/.CSV (comma separated values) file, or perhaps on the internet. Click on the links below to download the datasets that will be used for this project. These datasets are in CSV formats.

  1. Census_Data  Download Census_Data: This dataset contains a “hardship index,” for each Chicago community area, for the years 2008 – 2012. Check here (Links to an external site.) for more information about the data.
  2. Chicago_Public_Schools  Download Chicago_Public_Schools: This dataset shows all school level performance data used to create CPS School Report Cards for the 2011-2012 school year. Check here (Links to an external site.) for more information about the data.
  3. Chicago_Crime_Data  Download Chicago_Crime_Data: his dataset reflects reported incidents of crime that occurred in the City of Chicago from 2001 to present, minus the most recent seven days. Check here (Links to an external site.) for more information about the data.

The datasets made available in this project are much smaller than the original datasets to make it easier to complete the project.

  2. Create Database and Load data

Before writing queries to analyze the datasets you downloaded in task 1, you need to create a database and load the datasets as tables. Use the Azure graphical interfaces to create a new database on any of your database instances and name it FinalProject. If you have forgotten how to do this, review Create a database video in section 3 of the learning materials.

Then follow the steps in the Microsoft documentation here (Links to an external site.) to load each of the datasets you downloaded in task 1 as separate tables into the database.

While most of the steps required to successfully complete this Project can be gathered by reviewing the learning videos and the steps already in the documentation, you may have to Google some things to troubleshoot the process or to research a step of which you are unfamiliar. This is expected as part of the learning process and greatly encouraged. Welcome to the world of computing.

Database – Two Ways to Add a Primary Key Constraint in Postgres

Add column and constraint in one step

ALTER TABLE employees

ADD EmployeeID INT GENERATED ALWAYS AS IDENTITY(START WITH 1000 INCREMENT BY 1) PRIMARY KEY;

Add column constraint in two steps

ALTER TABLE employees
ADD EmployeeID INT GENERATED ALWAYS AS IDENTITY(START WITH 1000 INCREMENT BY 1)

ALTER TABLE employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

Database – Two Ways to Add a Primary Key Constraint in MSSQL

Add column and constraint in one step

ALTER TABLE employees

ADD EmployeeID INT IDENTITY(1000, 1) PRIMARY KEY;

Add column constraint in two steps

ALTER TABLE employees
ADD EmployeeID INT IDENTITY(1000,1);

ALTER TABLE employees
ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

DATABASE – Composite primary key

A composite key allows you to have a composite key across more than one column.

CREATE TABLE HotelRooms (
    CheckinDate date NOT NULL,
    RoomNumber  char(3) NOT NULL,
    GuestName   varchar(50),
CONSTRAINT PK_CheckinRooms PRIMARY KEY (CheckinDate, RoomNumber)
);

-- Verify that it will not allow two rows with the same checkin + room combination
INSERT INTO HotelRooms VALUES
('2020-10-15', '201', 'Dr. White');

INSERT INTO HotelRooms VALUES
('2020-10-15', '315', 'Ms. Green');

INSERT INTO HotelRooms VALUES
('2020-10-16', '201', 'Mr. Brown');

INSERT INTO HotelRooms VALUES
('2020-10-15', '201', 'Mrs. Blue');

SELECT * FROM HotelRooms;
DROP TABLE HotelRooms;