Microsoft SQL Server - Lesson 22: Introduction to Relational Databases

	Introduction to Relational Databases

The Primary Key Constraint

Relational Databases

A relational database is a system in which information flows from one database object to another. For example, on a bank database, you can use one object to create accounts for customers and use another object to process transactions that the owners of bank accounts need. The reason is that the same customer may need to perform various transactions, regularly.

Relational Databases

Instead of creating a new account every time the customer wants to perform a new transaction, you can use one account as a reference and bring up this account whenever the customer wants to deposit or withdraw money.

To apply the rules of relational databases, you create some types of relationships among the objects of the database.

The transactions among the various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are various issues that must be dealt with:

You must be able to uniquely identify each record from a table (A) without any confusion. For example, if you create a list of cars on a table, you should make sure that there is a unique (no duplicate) tag number for each car because each car should have one and must have one tag number. This ensures that there are no duplicate records on the table.
A table (A) that holds information should make that information available to other tables (such as B)
Two tables must not serve the same purpose. Once you have unique information on each table, one table can make its data available to other tables that need it so that the same information should not be entered in more than one table

These problems are solved by specifying a particular column or a group of columns as the "key" of the table. Such a column or group of columns is called the primary key.

In a relational database, which is the case for most of the databases you will be creating, each table should have at least one primary key. As an example, a primary key on a Customers table of a bank database can be set on a Bank Account field because each customer should have a unique bank account number.

Once you have decided that a table will have a primary key, you must decide what type of data the field(s) will hold. If you are building a table that can use a known and obvious field as unique, an example would be the shelf number of a library, you can set its data type as char (or nchar) or varchar (or nvarchar) and make it a primary key. In many other cases, for example if you cannot decide on a particular field that would hold unique information, an example would be customers Contact Name, you should create your own unique field and make it the Primary Key. In most cases when you hesitate, the data type of the primary key should be an integer, usually int.

Among the rules you must follow, a table can have only one primary key. If you use one column as the primary key, that column cannot hold a NULL value.

Practical Learning: Introducing Relationships

Start the computer and log in
Launch Microsoft SQL Server and click Connect
Right-click the name of the server and click New Query

To start a new database, type the following code:

CREATE DATABASE RealEstate1;
GO
USE RealEstate1;
GO
CREATE SCHEMA Listing;
GO

To formally create the database, on the main menu, click Query -> Execute

Visually Creating a Primary Key

To create a primary key in SQL Server Management Studio, create a column and specify its data type:

Then, on the toolbar, click the Set Primary Key button
You can also right-click a column and click Set Primary Key

Here is an example:

Table

Creating a Primary Key With SQL

To create a primary column using SQL, the primary thing to do is, on the right side of the column definition, type PRIMARY KEY. Here is an example:

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);

Creating a Primary Key Constraint

In the SQL, you can give a specific name to a primary key. To do this, you can first create the column. Then, somewhere before the closing parenthesis of the table, specify the primary key column using the following formula:

CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)

In this formula, the CONSTRAINT keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:

CREATE TABLE Persons
(
    PersonID int NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID)
);
GO

By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:

CREATE TABLE Persons
(
    PersonID int NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO

Practical Learning: Creating Primary Keys

Click inside the Query Editor and press Ctrl + A

To create some tables that have primary keys, type the following code:

USE RealEstate1;
GO
CREATE TABLE Listing.PropertiesTypes
(
    PropertyType nvarchar(20) PRIMARY KEY not null,
    [Description] nvarchar(max),
);
GO
CREATE TABLE Listing.PropertiesConditions
(
    Condition nvarchar(20),
    [Description] nvarchar(max),
    CONSTRAINT PK_PropertiesConditions PRIMARY KEY(Condition)
);
GO
CREATE TABLE Listing.SalesStatus
(
    SaleStatus nvarchar(20),
    [Description] nvarchar(max),
    CONSTRAINT PK_SalesStatus PRIMARY KEY(SaleStatus)
);
GO

To formally create the database, on the main menu, click Query -> Execute

Records, Data Entry, and the Primary Key

Introduction

A field that is the primary key of a table must always have a value for each record. This means that NULL and the empty string are not allowed. If you fail to provide a value for a the primary key column, you would receive an error. Here is an example:

CREATE DATABASE Exercise3;
GO
USE Exercise3;
GO
CREATE TABLE Genders
(
    GenderID int not null PRIMARY KEY,
    Gender nvarchar(20)
);
GO

INSERT INTO Genders(Gender) VALUES(N'Male');
GO

This would produce:

Records, Data Entry, and the Primary Key

The first detail to which you must pay attention is the data type of the primary key column.

Practical Learning: Creating Values for Primary Keys

Click inside the Query Editor and press Ctrl + A

To create a few records and add values for primary keys, type the following code:

USE RealEstate1;
GO
INSERT INTO Listing.PropertiesTypes
VALUES(N'Condominium', N'A condominium, also called condo, is a unit built in a small, medium, or large building. It ressembles an apartment. It may have one, two, or more bedrooms.'),
      (N'Townhouse', N'A townhouse, sometimes called a town house or town home, is a relatively small house attached to at least another house.'),
      (N'Single Family', N'A single family is a stand-alone house. It may have one, two or three levels, also called stories.');
GO
INSERT INTO Listing.PropertiesConditions
VALUES(N'Excellent', N'An excellent property is one that has everything perfect or almost. There are no major repairs to be made.'),
      (N'Good', N'A property is good if it is good enough to be sold. It may be less than perfect but it is wholly acceptable.'),
      (N'Needs Repair', N'This type of condition indicates that one or more repairs are necessairy. The property in this condition is not ready for sale.'),
      (N'Bad Shape', N'A property is in bad shape if it requires a mojor or many repairs.');
GO
INSERT Listing.SalesStatus
VALUES(N'Ready For Sale', N'The property is currently available for sale.'),
      (N'Sold', N'The property has been sold.');
GO

To formally create the database, on the main menu, click Query -> Execute

The Type of Value of a Primary Key

One of the most common values of a primary key is an integer. In most cases, when creating such a column, you should assign it the integer data type. If you know for sure the limits of the primary key, you can apply a tinyint or a small int. Here are examples:

USE Exercise1;
GO
CREATE TABLE Personnel.Genders
(
	GdrNbr tinyint,
	Gender nvarchar(20),
	CONSTRAINT PK_Genders PRIMARY KEY(GdrNbr)
);
GO
CREATE TABLE Personnel.MaritalsStatus
(
	StatusCode smallint,
	MaritalStatus nvarchar(32),
	CONSTRAINT PK_MaritalStatus PRIMARY KEY(StatusCode)
);
GO

When performing data entry on a field whose primary key is an integer, provide the desired value but make sure the value is unique among the other values of the same column.

Besides integers, strings are among the most used types for a primary key. You have a choice between the char, the varchar and their variances. You should never usse the text data type as a primary key. When performing data entry, make sure you provide a value in single-quotes for each record.

Rules and Suggestions for Primary Keys

There are a few rules and suggestions you should follow creating a primary key:

Even though they may appear unique, you should avoid using the values that can change. For example, in the US, everybody has a unique social security number (SSN) but a person is allowed to change that number
Avoid using propert names as primary keys. Yes this is redundant some people would be tempted to do it. First of all, there is no guaranty that a combination of names would be unique (do you know how many Michael Jordans or Michael Jacksons exist?). Even if you absolutely think that the names in a database are unique, if you working for a business or a project you will not overview for the rest of your life, for the same reason as in the first point, people are allowed to change their names
You should avoid using private information for a primary key because the value of a primary key may have to be used in public reports where you don't want to communicate private information. For example, although every employee of a company in the US should have a unique SSN, if a business receipt or commercial invoice must identify the employee who made the sale, you would not want the employee's SSN on the paper handed to customers
You should not apply a decimal type (double, real, decimal, money, smallmoney, etc) for a primary key. The reason is that the value of a primary should be fixed and precise but decimal values can be unpredictable

You should not apply a date or time data type for a primary key because its value is unpredictable (it can be non-deterministic)

The Foreign Key Constraint

Introduction

Continuing with our bank database, imagine a customer comes to the bank to deposit money. We already established that it would be redundant to create a new account every time the customer comes to perform a transaction. Instead, you would get the customer's information from his or her account, provide that information to the table used to process transactions. As we described earlier, the account table should be able to provide its data to the other tables that would need that data. To make this flow of information possible from one table to another, you must create a relationship between them.

To make it possible for a table B to receive data from a table A, the table B must have a column that represents the table A. This columns acts as an "ambassador" or a link. As a pseudo-ambassador, the column in the table B almost doesn't belong to that table: it primarily allows both tables to communicate. For this reason, the column in the table B is called a foreign key.

A foreign key is a column on a table whose data is coming from another table. Unlike a primary key that must be unique, a table can have as many foreign keys as possible since each foreign key would relate to a different table. Still, Microsoft recommends a maximum of 253 foreign keys per table.

Creating a Foreign Key in the Table Design View

To create a foreign key in the Table Design window, in the table that will receive the key, simply create a column with the following rules:

The column should have the same name as the primary key column of the table it represents (but this is not a requirement)
The column must (this is required) have the same data type as the primary column of the table it represents

Obviously, in order to have information flowing from one table to another, the table that holds the primary information must have been created. You can create it before or after creating the other table, as long as you haven't established any link between both tables, it doesn't matter what sequence you use to create them.

The table that contains a primary key and that holds the information that another table would use is called the primary table or the parent table. The table that will receive the information from the other table is called the foreign table or the child table.

Creating a Foreign Key in the Relationships Dialog Box

To create a foreign key in a table:

From the Object Explorer, open the child table in Design View
Right-click anywhere in the table and click Relationships...
In the Foreign Key Relationships dialog box, click Add
A default name would be suggested to you. You can accept or change it. To change the name of the foreign key, in the right side, expand Identity and edit the string in the (Name) field:
If necessary, in the same way, you can create other foreign keys by clicking Add. To delete an existing foreign key, first select it under Selected Relationships and click Delete.
Once you are ready, click Close

Creating a Foreign Key in SQL

You can also create a foreign key in the SQL. The basic formula to use is:

FOREIGN KEY REFERENCES ParentTableName(ForeignKeyCcolumn)

The FOREIGN KEY expression and the REFERENCES keyword are required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);
GO

A Constraint on a Foreign Key

Based on the above technique, notice that the foreign key does not have an object name as we saw for the primary key. If you do not specify a name for the foreign key, the SQL interpreter would automatically create a default name for you. Otherwise, to create a name, after creating the column, enter the CONSTRAINT keyword followed by the desired name and continue the rest as we saw above. Her is an example:

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID int NULL CONSTRAINT FKGenders
                       FOREIGN KEY REFERENCES Genders(GenderID)
);
GO

You can also create a foreign key as its own constraint, which is another technique to name a foreign key. To assign a desired name to a foreign key, you must create it as a constraint. To do this, after defining the column that holds the foreign key before the end of defining the table, create a constraint using the following formula:

CONSTRAINT Name FOREIGN KEY(Foreign Key) REFERENCES Parent(Foreign Key)

Here is an example:

CREATE TABLE Genders
(
    GenderID int NOT NULL,
    Gender nvarchar(20) NOT NULL,
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO

CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL,
    GenderID int,
    Comments nvarchar(max),
    CONSTRAINT FK_Genders FOREIGN KEY(GenderID) REFERENCES Genders(GenderID)
);
GO

Practical Learning: Creating Foreign Keys

Click inside the Query Editor and press Ctrl + A

To create a table that has some foreign keys, type the following code:

USE RealEstate1;
GO
CREATE TABLE Listing.Properties
(
    PropertyNumber nvarchar(12),
    [Address] nvarchar(60),
    City nvarchar(50),
    [State] nchar(2),
    ZIPCode nvarchar(12),
    PropertyType nvarchar(20) FOREIGN KEY(PropertyType) REFERENCES Listing.PropertiesTypes(PropertyType),
    Bedrooms smallint,
    Bathrooms float,
    Stories smallint,
    FinishedBasement bit,
    IndoorGarage tinyint,
    YearBuilt smallint,
    Condition nvarchar(20) CONSTRAINT FK_PropertiesConditions FOREIGN KEY REFERENCES Listing.PropertiesConditions(Condition),
    MarketValue money,
    SaleStatus nvarchar(20),
    CONSTRAINT PK_Properties PRIMARY KEY(PropertyNumber),
    CONSTRAINT FK_SalesStatus FOREIGN KEY(SaleStatus) REFERENCES Listing.SalesStatus(SaleStatus)
);
GO

To formally create the database, on the main menu, click Query -> Execute

Establishing a Relationship

Introduction

As mentioned already, a relational database is one in which information flows from one table to another. To prepare the tables for this, you create primary and foreign keys, which we have done so far. Once the tables are ready, you can link them, which is referred to as creating a relationship between two tables.

If you didn't create a foreign key with SQL code, you can create it when establishing a relationship between two tables.

Creating a Relationship

To create a relationship between two tables

Open the child table in the design view
Right-click (anywhere in) the table and click Relationships...
If the (necessary) foreign key doesn't exist, click Add and specify its name under Identity) in the right side.
Under Selected Relationships, click the foreign key that will hold the relationship
In the right side, expand Tables And Columns Specification
Click its ellipsis button
In the Primary Key Table combo box, select the parent table that holds the primary data
Under the parent table, click and select its primary key column
Under Foreign Key Table, make sure the name of the current table is set.
Under the name of the child table, click and select the name of the foreign key column. Here is an example:
Click OK.
When a relationship has been created, it would show in the Tables And Column Specification section:
In the same way, you can create other relationships by clicking Add and configuring the link.
Once you have finished, click Close

Diagrams

A diagram is a window that visually displays the relationships among tables of a database. To create a diagram:

In the Object Explorer, in the database node, you can click Database Diagrams. In some case, you may receive a message box informing you that the database doesn't have an owner:

If this happens, click OK. Right-click the name of the database in the Object Explorer and click Properties. In the left list, click Files. In the Owner text box, type sa

Click OK. Click Database Diagrams again
A dialog box will inform you that this database doesn't have a diagram

Read the message and click Yes
Right-click Database Diagrams and click New Database Diagram
In the Add Table dialog box, click each table and click the Add.
Alternatively, you can double-click a table to add it
In the Add Table dialog box, you can click Close.
On the toolbar, you can click the Zoom button and select a larger or smaller value.
To move a table, you can drag its title bar. Here is an example:
To establish a relationship, you can click the gray box on the left of any column from the parent table and drop it on any column in the child table. A better way is to click the gray box of the primary key column from the parent table, drag that box then drop it on the foreign key column of the child table. Here is an example:
A Tables and Columns dialog box would come up. It would display the column that was dragged and the column on which you dropped.
If you had selected just any column, it would show but it may not be the one you wanted to drag; that is, it may not be the actual column that is supposed to manage the relationship.
Regardless, under Primary Key Table, you should select the parent table
Under the parent table, select its primary column
Under Foreign Table, select the foreign key column. Here is an example:
Once you are ready, click OK
Another dialog box titled Foreign Key Relationship would come up. It allows you to manage the relationship:

After reading (and/or performing more actions on) that dialog box, click OK. A link would be created between the tables
In the same way, you can create other relationships.
When you have finished, you can save and close the diagram
A dialog box will inform you that the tables that were involved in the relationship(s) must be saved:

Read the message box and click Yes

Practical Learning: Creating a Diagram

In the Object Explorer, right-click Databases and click Refresh
Expand the AltairRealtors node
Click its Database Diagrams node
A dialog box will inform you that this database doesn't have a diagram:

Read the message and click Yes
Right-click Database Diagrams and click New Database Diagram
In the Add Table dialog box, click PropertiesTypes (Listing) and click the Add button
Double-click PropertiesConditions (Listing) to add it
In the Add Table dialog box, double-click Properties (Listing) and SalesStatus (listing)
On the Add Table dialog box, click Close.
Notice that, based on how we created the database and its objects, the relationships have been created already:
To save the diagram, on the Standard toolbar, click Save
Set its name to dgmAltairRealtors and click OK
Close the diagram window.
If a message box asks you whether you want to save the tables, click Yes

Data Entry on Related Tables

Data Entry on a Primary Key

As mentioned already, when creating the records of a table, assign the appropriate value for the primary key. Each value must be unique.

Data Entry on a Foreign Key

The value of a foreign key column must exist in the primary key of the table it represents. If the value cannot be found in the parent table, you would receive an error. If you need to use a value that is not in the parent table, first created it there, and then return to the child table to create the record.

Practical Learning: Creating Values for Foreign Keys

Click inside the Query Editor and press Ctrl + A

To create a few records and add values for foreign keys, type the following code:

USE RealEstate1;
GO
INSERT INTO Listing.Properties
VALUES(N'52408180', N'1640 Lombardo Ave', N'Silver Spring', N'MD', N'20904', N'Single Family', 4, 2.5, 3, 1, 2, 1995, N'Good', 495880.00, N'Ready For Sale'),
      (N'68830614', N'10315 North Hacht Rd', N'College Park', N'MD', N'20747', N'Single Family', 4, 3.5, 3, 1, 2, 2000, N'Excellent', 620724.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, Condition, FinishedBasement, Stories, MarketValue, SaleStatus)
VALUES(N'96114604', N'6366 Lolita Drive', N'Laurel', N'MD', N'20707', N'Good', 1, 2, 422625.00, N'Sold');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, PropertyType, Bedrooms, IndoorGarage, MarketValue)
VALUES(N'39485797', N'9002 Palasko Hwy', N'Tysons Corner', N'Condominium', 2, 2, 422895.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [State], ZIPCode, Bedrooms, YearBuilt, MarketValue, SaleStatus)
VALUES(N'42081159', N'DC', N'20011', 2, 1982, 312555, N'Ready For Sale');
GO     
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode, PropertyType, Bedrooms, YearBuilt, MarketValue, SaleStatus)
VALUES(N'97172037', N'Alexandria', N'22024', N'Single Family', 3, 1965, 345660.00, N'Sold');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State], PropertyType, Condition, Bedrooms, Bathrooms, MarketValue)
VALUES(N'20880417', N'4140 Holisto Crt', N'Germantown', N'MD', N'Condominium', N'Excellent', 2, 1, 215495.00);
GO
INSERT INTO Listing.Properties 
VALUES(N'92747400', N'9522 Lockwood Rd', N'Chevy Chase', N'MD', N'20852', N'Townhouse', 3, 2.5, 3, 0, 1,  1992, N'Bad Shape', 415665.00, N'Ready For Sale'),
      (N'20708150', N'14250 Parkdoll Rd', N'Rockville', N'MD', N'20854', N'Townhouse', 3, 2.5, 2, 1, 0, 1988, N'Good', 325995.00, N'Sold');
GO    
INSERT INTO Listing.Properties(PropertyNumber, City, PropertyType, Bedrooms, YearBuilt, MarketValue)
VALUES(N'29304857', N'Washington', N'Townhouse', 4, 1975, 366775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
            ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, YearBuilt, MarketValue)
VALUES(N'28085040', N'10340 Helmes Street #408', N'Silver Spring', N'MD', N'20906', N'Condominium', N'Good', 1, 1, 2000, 242775.00);
GO
INSERT INTO Listing.Properties 
VALUES(N'24747299', N'1008 Coppen Street', N'Silver Spring', N'MD', N'20906', N'Single Family', 3, 3, 3, 1, 3, 1996, N'Excellent', 625450.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode, PropertyType, Stories, YearBuilt, MarketValue)
VALUES(N'39485070', N'Chevy Chase', N'20956', N'Single Family', 3, 2001, 525450.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State], PropertyType, Condition, Bedrooms, MarketValue, SaleStatus)
VALUES(N'29380597', N'686 Herod Ave #D04', N'Takoma Park', N'MD', N'Condominium', N'Excellent', 2, 360885.00, N'Sold');
GO
INSERT INTO Listing.Properties 
VALUES(N'29744618', N'14005 Sniders Blvd', N'Laurel', N'MD', N'20707', N'Townhouse', 4, 1.5, 3, 1, 0, 2002, N'Needs Repair', 412885.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode, Condition, Bedrooms, Stories, YearBuilt)
VALUES(N'28074085', N'Silver Spring', N'20905', N'Good', 4, 2, 1965);
GO
INSERT INTO Listing.Properties 
VALUES(N'92417926', N'680 Prushia Rd NE', N'Washington', N'DC', N'20008', N'Single Family', 5, 3.5, 3, 0, 3, 2000, N'Good', 555885.00, N'Ready For Sale'),
      (N'29407906', N'14688 Parrison Street', N'College Park', N'MD', N'20742', N'Single Family', 5, 2.5, 2, 1, 2, 1995, N'Excellent', 485995.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, YearBuilt, MarketValue, SaleStatus)
VALUES(N'81115599', N'10340 Helmes Street #1012', N'Silver Spring',
       N'MD', N'20906', N'Condominium', N'Good', 1, 1, 2000, 252775.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties 
VALUES(N'44759760', N'4201 Vilamar Ave', N'Hyattsville', N'MD', N'20782', N'Townhouse', 3, 2, 2, 1, 2, 1992, N'Excellent', 365880.00, N'Ready For Sale');
GO

To formally create the database, on the main menu, click Query -> Execute

Data Entry and NULL

NULL should be avoided in any column that is involved in a relationship. When creating the records of a table that has a primary key, if you know that the child table will have records that do not have a value in the parent table, in the parent table, create a "dummy" record that has a value such as N/A, Not Available, Unknown, or something like that.

Practical Learning: Dealing With NULL Foreign Values

Click inside the Query Editor and press Ctrl + A

To show the listing of the properties, type the following code:

USE RealEstate1;
GO
SELECT PropertyNumber [Property #], City, [State],
       PropertyType [Type], Bedrooms Beds, 
       Bathrooms Baths, YearBuilt [Year],
       Condition, MarketValue "Market Value", SaleStatus "Status"
FROM Listing.Properties;
GO

To execute, on the main menu, click Query -> Execute.
Notice that you get 20 records. Also notice that a few properties have NULL as their status

To set a condition, such as to see only properties that are currently listed as being on sale, change the statement as follows:

USE RealEstate1;
GO
SELECT props.PropertyNumber [Property #],
       props.City, props.[State],
       props.PropertyType [Type],
       props.Bedrooms Beds, 
       props.Bathrooms Baths, 
       props.YearBuilt [Year],
       props.Condition, 
       props.MarketValue "Market Value", 
       props.SaleStatus "Status"
FROM Listing.Properties props
WHERE props.SaleStatus = N'Ready For Sale';
GO

To execute, press F5
Notice that you get 10 records of properties that are ready for sale:

To see properties that are not listed as being on sale, change the statement as follows:

USE RealEstate1;
GO
SELECT props.PropertyNumber [Property #],
       props.City, props.[State],
       props.PropertyType [Type],
       props.Bedrooms Beds, 
       props.Bathrooms Baths, 
       props.YearBuilt [Year],
       props.Condition, 
       props.MarketValue "Market Value", 
       props.SaleStatus "Status"
FROM Listing.Properties props
WHERE NOT(props.SaleStatus = N'Ready For Sale');
GO

To execute, press F5
Notice that you get 3 records
Click inside the Query Editor and press Ctrl + A

Remember that we don't know the status of properties whose sale status is NULL: we don't know if those properties are ready for sale, if they have been sold, or else. But, we want to see all properties that are not currently listed as being on sale, even, or including, those whose sale status is not known. To take care of this, type the following code (we could have UPDATEd the table and the records, especially if we were working in a production or commercial environ, but we are choosing the easier way, which consists of recreating this small database):

CREATE DATABASE RealEstate2;
GO
USE RealEstate2;
GO
CREATE SCHEMA Listing;
GO
CREATE TABLE Listing.PropertiesTypes
(
    PropertyType nvarchar(20) PRIMARY KEY not null,
    [Description] nvarchar(max),
);
GO
CREATE TABLE Listing.PropertiesConditions
(
    Condition nvarchar(20),
    [Description] nvarchar(max),
    CONSTRAINT PK_PropertiesConditions PRIMARY KEY(Condition)
);
GO
CREATE TABLE Listing.SalesStatus
(
    SaleStatus nvarchar(20),
    [Description] nvarchar(max),
    CONSTRAINT PK_SalesStatus PRIMARY KEY(SaleStatus)
);
GO

INSERT INTO Listing.PropertiesTypes
VALUES(N'Unknown', N'The property type was not specified or was not known'),
      (N'Condominium', N'A condominium, also called condo, is a unit built in a small, medium, or large building. It ressembles an apartment. It may have one, two, or more bedrooms.'),
      (N'Townhouse', N'A townhouse, sometimes called a town house or town home, is a relatively small house attached to at least another house.'),
      (N'Single Family', N'A single family is a stand-alone house. It may have one, two or three levels, also called stories.');
GO
INSERT INTO Listing.PropertiesConditions
VALUES(N'Unknown', N'Unknown property condition'),
      (N'Excellent', N'An excellent property is one that has everything perfect or almost. There are no major repairs to be made.'),
      (N'Good', N'A property is good if it is good enough to be sold. It may be less than perfect but it is wholly acceptable.'),
      (N'Needs Repair', N'This type of condition indicates that one or more repairs are necessairy. The property in this condition is not ready for sale.'),
      (N'Bad Shape', N'A property is in bad shape if it requires a mojor or many repairs.');
GO
INSERT Listing.SalesStatus
VALUES(N'N/A', N'The sale status of this property is not definitely spaecified'),
      (N'Ready For Sale', N'The property is currently available for sale.'),
      (N'Sold', N'The property has been sold.');
GO
CREATE TABLE Listing.Properties
(
    PropertyNumber nvarchar(12),
    [Address] nvarchar(60),
    City nvarchar(50),
    [State] nchar(2),
    ZIPCode nvarchar(12),
    PropertyType nvarchar(20) default N'Unknown',
    Bedrooms smallint,
    Bathrooms float,
    Stories smallint,
    FinishedBasement bit,
    IndoorGarage tinyint,
    YearBuilt smallint,
    Condition nvarchar(20) default N'Unknown',
    MarketValue money,
    SaleStatus nvarchar(20) default N'N/A',
    CONSTRAINT PK_Properties PRIMARY KEY(PropertyNumber),
	CONSTRAINT FK_PropertiesTypes FOREIGN KEY(PropertyType) REFERENCES Listing.PropertiesTypes(PropertyType),
	CONSTRAINT FK_PropertiesConditions FOREIGN KEY(Condition) REFERENCES Listing.PropertiesConditions(Condition),
    CONSTRAINT FK_SalesStatus FOREIGN KEY(SaleStatus) REFERENCES Listing.SalesStatus(SaleStatus)
);
GO

INSERT INTO Listing.Properties
VALUES(N'52408180', N'1640 Lombardo Ave', N'Silver Spring', N'MD', N'20904', N'Single Family', 4, 2.5, 3, 1, 2, 1995, N'Good', 495880.00, N'Ready For Sale'),
      (N'68830614', N'10315 North Hacht Rd', N'College Park', N'MD', N'20747', N'Single Family', 4, 3.5, 3, 1, 2, 2000, N'Excellent', 620724.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, Condition, FinishedBasement, Stories, MarketValue, SaleStatus)
VALUES(N'96114604', N'6366 Lolita Drive', N'Laurel', N'MD', N'20707', N'Good', 1, 2, 422625.00, N'Sold');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, PropertyType, Bedrooms, IndoorGarage, MarketValue)
VALUES(N'39485797', N'9002 Palasko Hwy', N'Tysons Corner', N'Condominium', 2, 2, 422895.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [State], ZIPCode, Bedrooms, YearBuilt, MarketValue, SaleStatus)
VALUES(N'42081159', N'DC', N'20011', 2, 1982, 312555, N'Ready For Sale');
GO     
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode, PropertyType, Bedrooms, YearBuilt, MarketValue, SaleStatus)
VALUES(N'97172037', N'Alexandria', N'22024', N'Single Family', 3, 1965, 345660.00, N'Sold');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State], PropertyType, Condition, Bedrooms, Bathrooms, MarketValue)
VALUES(N'20880417', N'4140 Holisto Crt', N'Germantown', N'MD', N'Condominium', N'Excellent', 2, 1, 215495.00);
GO
INSERT INTO Listing.Properties 
VALUES(N'92747400', N'9522 Lockwood Rd', N'Chevy Chase', N'MD', N'20852', N'Townhouse', 3, 2.5, 3, 0, 1,  1992, N'Bad Shape', 415665.00, N'Ready For Sale'),
      (N'20708150', N'14250 Parkdoll Rd', N'Rockville', N'MD', N'20854', N'Townhouse', 3, 2.5, 2, 1, 0, 1988, N'Good', 325995.00, N'Sold');
GO    
INSERT INTO Listing.Properties(PropertyNumber, City, PropertyType, Bedrooms, YearBuilt, MarketValue)
VALUES(N'29304857', N'Washington', N'Townhouse', 4, 1975, 366775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
            ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, YearBuilt, MarketValue)
VALUES(N'28085040', N'10340 Helmes Street #408', N'Silver Spring', N'MD', N'20906', N'Condominium', N'Good', 1, 1, 2000, 242775.00);
GO
INSERT INTO Listing.Properties 
VALUES(N'24747299', N'1008 Coppen Street', N'Silver Spring', N'MD', N'20906', N'Single Family', 3, 3, 3, 1, 3, 1996, N'Excellent', 625450.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode, PropertyType, Stories, YearBuilt, MarketValue)
VALUES(N'39485070', N'Chevy Chase', N'20956', N'Single Family', 3, 2001, 525450.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State], PropertyType, Condition, Bedrooms, MarketValue, SaleStatus)
VALUES(N'29380597', N'686 Herod Ave #D04', N'Takoma Park', N'MD', N'Condominium', N'Excellent', 2, 360885.00, N'Sold');
GO
INSERT INTO Listing.Properties 
VALUES(N'29744618', N'14005 Sniders Blvd', N'Laurel', N'MD', N'20707', N'Townhouse', 4, 1.5, 3, 1, 0, 2002, N'Needs Repair', 412885.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode, Condition, Bedrooms, Stories, YearBuilt)
VALUES(N'28074085', N'Silver Spring', N'20905', N'Good', 4, 2, 1965);
GO
INSERT INTO Listing.Properties 
VALUES(N'92417926', N'680 Prushia Rd NE', N'Washington', N'DC', N'20008', N'Single Family', 5, 3.5, 3, 0, 3, 2000, N'Good', 555885.00, N'Ready For Sale'),
      (N'29407906', N'14688 Parrison Street', N'College Park', N'MD', N'20742', N'Single Family', 5, 2.5, 2, 1, 2, 1995, N'Excellent', 485995.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, YearBuilt, MarketValue, SaleStatus)
VALUES(N'81115599', N'10340 Helmes Street #1012', N'Silver Spring',
       N'MD', N'20906', N'Condominium', N'Good', 1, 1, 2000, 252775.00, N'Ready For Sale');
GO
INSERT INTO Listing.Properties 
VALUES(N'44759760', N'4201 Vilamar Ave', N'Hyattsville', N'MD', N'20782', N'Townhouse', 3, 2, 2, 1, 2, 1992, N'Excellent', 365880.00, N'Ready For Sale');
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A

To show the listing of the properties, type the following code:

USE RealEstate2;
GO
SELECT PropertyNumber [Property #], City, [State],
       PropertyType [Type], Bedrooms Beds, 
       Bathrooms Baths, YearBuilt [Year],
       Condition, MarketValue "Market Value", SaleStatus "Status"
FROM Listing.Properties;
GO

To execute, on the main menu, click Query -> Execute.
Notice that there are no more NULL fields in the properties types, the conditions, and the status:
Click inside the Query Editor and press Ctrl + A

To show only properties ready for sale, change the statement as follows:

USE RealEstate2;
GO
SELECT props.PropertyNumber [Property #],
       props.City, props.[State],
       props.PropertyType [Type],
       props.Bedrooms Beds, 
       props.Bathrooms Baths, 
       props.YearBuilt [Year],
       props.Condition, 
       props.MarketValue "Market Value", 
       props.SaleStatus "Status"
FROM Listing.Properties props
WHERE props.SaleStatus = N'Ready For Sale';
GO

To execute, press F5
Notice that you get 10 records of properties that are listed as ready to be sold:

To see properties that are not listed as being on sale, change the statement as follows:

USE RealEstate2;
GO
SELECT props.PropertyNumber [Property #],
       props.City, props.[State],
       props.PropertyType [Type],
       props.Bedrooms Beds, 
       props.Bathrooms Baths, 
       props.YearBuilt [Year],
       props.Condition, 
       props.MarketValue "Market Value", 
       props.SaleStatus "Status"
FROM Listing.Properties props
WHERE NOT(props.SaleStatus = N'Ready For Sale');
GO

To execute, press F5
Notice that, this time, you get all properties that are not ready to be sold, including properties whose availability status is not known:

Conclusion: On foreign keys, avoid using leaving their values empty and avoid setting them NULL

Constraints Maintenance

Introduction

There are many issues you need to be concerned about in a relational database. Some issues are related to the columns of tables and some issues have to do with records. This means that you should always think about structures of tables when you decide to either add new columns or delete records.

If you decide to delete a table, first check if it is involved in a relationship. If so, is it the parent (is it providing its records to another table?) or the child (is one of its columns receiving values from a parent table?)? If a table is a child, you can easily delete it using any of the techniques we know already. If a table is a parent, you will receive an error.

Adding a Primary Key

After creating a table or when inheriting a table created by someone else, you may find out that it lacks a primary key. You can add it, of course following some rules. You have two options.

Imagine you have the following table:

CREATE TABLE Employees
(
    FirstName nvarchar(20),
    LastName nvarchar(20),
    DepartmentCode nchar(6)
);
GO

You can add the PRIMARY KEY expresion after defining the new column. Here is an example:

ALTER TABLE Employees
ADD EmployeeNumber int not null PRIMARY KEY;
GO

As an alternative, you can add a column, and then use the CONSTRAINT formula to define the primary key. Here is an example:

ALTER TABLE Employees
ADD EmployeeNumber int not null
CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber);
GO

Adding a Foreign Key

Just as you add a primary key to an already created table, you can also add a new column that is a foreign key. Consider the following table named Persons:

CREATE TABLE Genders
(
    GenderID int not null PRIMARY KEY,
    Gender nvarchar(20)
);
GO
CREATE TABLE Persons
(
    PersonID int PRIMARY KEY NOT NULL,
    FirstName nvarchar(20),
    LastName nvarchar(20) NOT NULL
);
GO

The formula to add a foreign key to an existing table is:

ALTER TABLE TableName
ADD NewColumnName DataType Options
    FOREIGN KEY REFERENCES ParentTableName(ColumnNameOfOtherTable);
GO

Here is an example of adding a foreign key to the above Persons table:

ALTER TABLE Persons
ADD GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID);
GO

Composite Keys

Introduction

So far, we have used primary keys made of only one column. Sometimes, that one column is not enough to uniquely identify a record. An alternative is to use more than one column. A composite key is a primary key that uses more than one column.

To visually create a composite key, while the table is being designed, click the row header of one of the columns. Press and hold Ctrl (or Shift, if the columns are in a range). Then click the row header of each of the columns that will be members of the primary key (or the row header of the column at the end of the range). After making the selections, release Ctrl (or Shift). Then:

Right-click the selection and click Set Primary Key

Composite Keys

On the Table Designer toolbar, click the Set Primary Key button

In both cases each of the columns that participate in the primary key would display the icon on its row header:

Composite Keys

In the same way, you can involve as many columns as you want. One of the ways to create a composite key is to use the foreign keys that represent the primary keys of other tables.

To create a composite primary key in SQL, in the parentheses of the primary key, include the names of the columns separated by commas. Here is an example:

CREATE DATABASE InformationTechnologyJournal;
GO
USE InformationTechnologyJournal;
GO

CREATE SCHEMA Publishing;
GO
CREATE SCHEMA Authorship;
GO

CREATE TABLE Authorship.Reviewers
(
	ReviewerNumber nchar(6) not null,
	FirstName nvarchar(24),
	MiddleName nvarchar(24),
	LastName nvarchar(24),
	Citizenship nvarchar(40),
	Constraint PK_Reviewers Primary Key(ReviewerNumber)
);
GO
CREATE TABLE Publishing.Affiliations
(
	AffiliationCode nchar(5) not null,
	AffiliationName nvarchar(60),
	Constraint PK_Affiliations Primary Key(AffiliationCode)
);
GO
CREATE TABLE Authorship.ReviewersAffiliations
(
	ReviewerNumber nchar(6) not null,
	AffiliationCode nchar(5) not null,
	Constraint PK_ReviewersAffiliations Primary Key(ReviewerNumber, AffiliationCode)
);
GO

Candidate Keys

A candidate key is a column that participates in a primary key. If the primary key contains only one column, that column is a candidate key. If the primary key is made of more than one column, each one of the columns is a candidate key.