Category: SQL

[MSSQL Express Download]

[Download SQL Server Management Studio (SSMS)]

Connect to database instance: COMPUTER_NAME\SQLEXPRESS.

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.

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);

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);

— Create an auto-incrementing surrogate primary key in MSSQL

CREATE TABLE fruit (
    fruit_id integer IDENTITY (100, 10) PRIMARY KEY,
    type varchar(10)
);

INSERT INTO fruit (type)
VALUES ('Apple'), ('Grape'), ('Watermelon');

SELECT * FROM fruit;

DROP TABLE fruit;

Create an auto-incrementing surrogate primary key in PostgreSQL

CREATE TABLE fruit (
    fruit_id integer GENERATED ALWAYS AS IDENTITY (START WITH 100 INCREMENT BY 10) PRIMARY KEY,
    type character varying (10)
);

INSERT INTO fruit (type)
VALUES ('Apple'), ('Grape'), ('Watermelon');

SELECT * FROM fruit;

DROP TABLE fruit;

Have no real-world meaning or significance

Simply provide a unique value for row IDs

Typically in the form of an incrementing integer value

Bank account number, library card number, driver’s license number, etc.

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;

Composite Keys use a unique combination of multiple values to identify rows.

A Natural Primary Key is a column of values in your existing data that is guaranteed to be unique for every row.

Natural Key Pitfalls

Watch out for columns that appear to hold unique values simply because they contain only a few rows.

Only choose a natural key column if it is impossible to have duplicates, not merely improbable.