Tutorial: Design a relational database in Azure SQL Database using Azure Data Studio (ADS)

Applies to: Azure SQL Database

Azure SQL Database is a relational database-as-a-service (DBaaS) in the Cloud (Azure). In this tutorial, you learn how to use the Azure portal and Azure Data Studio (ADS) to:

Prerequisites

To complete this tutorial, make sure you've installed:

• Azure Data Studio (latest version)
• BCP and SQLCMD (latest version).
• If you don't have an Azure subscription, create a trial account before you begin.
• If you don't already have an Azure SQL Database created, visit Quickstart: Create a single database.

Sign in to the Azure portal

Sign in to the Azure portal.

Create a server-level IP firewall rule

Azure SQL Database creates an IP firewall at the server-level. This firewall prevents external applications and tools from connecting to the server and any databases on the server unless a firewall rule allows their IP through the firewall. To enable external connectivity to your database, you must first add an IP firewall rule for your IP address (or IP address range). Follow these steps to create a server-level IP firewall rule.

1. After the deployment completes, select SQL databases from the Azure portal menu or search for and select SQL databases from any page.

2. Select yourDatabase on the SQL databases page. The overview page for your database opens, showing you the fully qualified Server name (such as contosodatabaseserver01.database.chinacloudapi.cn) and provides options for further configuration.

   

3. Copy this fully qualified server name for use to connect to your server and databases from SQL Server Management Studio.

4. Select Networking under Settings. Choose the Public Access tab, and then select Selected networks under Public network access to display the Firewall rules section.

   

5. Select Add your client IPv4 on the toolbar to add your current IP address to a new IP firewall rule. An IP firewall rule can open port 1433 for a single IP address or a range of IP addresses.

6. Select Save. A server-level IP firewall rule is created for your current IP address opening port 1433 on the server.

7. Select OK and then close the Firewall settings page.

Your IP address can now pass through the IP firewall. You can now connect to your database using SQL Server Management Studio or another tool of your choice. Be sure to use the server admin account you created previously.

Connect to the database

Use Azure Data Studio to establish a connection to your database.

1. Open Azure Data Studio.

2. In the New Connection from the Object Explorer to create a new connection and enter the following information. Leave other options as default.

   Setting	Suggested value	Description
   Connection type	Microsoft SQL Server	This value is required.
   Server name	The fully qualified Azure SQL Database logical server name	For example, your_logical_azure_sql_server.database.chinacloudapi.cn.
   Authentication type	SQL Server Authentication	Use SQL Server Authentication to enter a user name and password.
   	Microsoft Entra authentication	To connect using Microsoft Entra ID, if you're the Microsoft Entra server admin, choose Microsoft Entra ID - Universal with MFA support. For more information, see Configure and manage Microsoft Entra authentication with Azure SQL.
   Login	The server admin account	The account that you specified when you created the server.
   Password	The password for your server admin account	The password that you specified when you created the server.

   

3. Select Connect. The Object Explorer window opens in ADS.

4. In Object Explorer, expand Databases and then expand yourDatabase to view the objects in the sample database.

5. In Object Explorer, right-click yourDatabase and select New Query. A blank query window opens that is connected to your database.

Create tables in your database

Create a database schema with four tables that model a student management system for universities using the Table Designer in Azure Data Studio:

• Person
• Course
• Student
• Credit

The following diagram shows how these tables are related to each other. Some of these tables reference columns in other tables. For example, the Student table references the PersonId column of the Person table. Study the diagram to understand how the tables in this tutorial are related to one another. For an in-depth look at how to create effective normalized database tables, see Designing a Normalized Database. For information about choosing data types, see Data types. By default, tables are created in the default dbo schema, meaning the two-part name of a table will be dbo.Person, for example.

1. In Object Explorer, Select yourDatabase which expands the dropdown menu of all processes stored in this database, right-click the Tables folder, select New Table. A blank Table Designer opens that is connected to your database.

2. Use the Table Designer interface to create these four tables in your database. To learn more about creating tables using the Table Designer, refer to the Table Designer documentation :

   • Person Table

     

     Be sure to configure the Primary Key settings for the Person Table as shown below:

     

   • Student Table

     

     Be sure to configure the Primary Key settings for the Student Table as shown below:

     Be sure to configure the Foreign Key settings for the Student Table as shown below:

   • Course Table

     

     Be sure to configure the Primary Key settings for the Course Table as shown below:

   • Credit Table

     

     Be sure to configure the Foreign Key settings for the Credit Table as shown below:

     Be sure to configure the Check Constraint settings for the Credit Table as shown below:

   If you prefer to use T-SQL to create the four new tables, here's the T-SQL to execute in a new query window.

   -- Create Person table
   CREATE TABLE Person
   (
       PersonId INT IDENTITY PRIMARY KEY,
       FirstName NVARCHAR(128) NOT NULL,
       MiddelInitial NVARCHAR(10),
       LastName NVARCHAR(128) NOT NULL,
       DateOfBirth DATE NOT NULL
   )
   
   -- Create Student table
   CREATE TABLE Student
   (
       StudentId INT IDENTITY PRIMARY KEY,
       PersonId INT REFERENCES Person (PersonId),
       Email NVARCHAR(256)
   )
   
   -- Create Course table
   CREATE TABLE Course
   (
       CourseId INT IDENTITY PRIMARY KEY,
       Name NVARCHAR(50) NOT NULL,
       Teacher NVARCHAR(256) NOT NULL
   )
   
   -- Create Credit table
   CREATE TABLE Credit
   (
       StudentId INT REFERENCES Student (StudentId),
       CourseId INT REFERENCES Course (CourseId),
       Grade DECIMAL(5,2) CHECK (Grade <= 100.00),
       Attempt TINYINT,
       CONSTRAINT [UQ_studentgrades] UNIQUE CLUSTERED
       (
           StudentId, CourseId, Grade, Attempt
       )
   )
   

3. Expand the Tables node under yourDatabase in the Object Explorer to see the four new tables you created.

   

Load data into the tables

1. Create a folder called sampleData in your local workstation Downloads folder to store sample data for your database. For example, c:\Users\<your user name>\Downloads.

2. Right-click the following links and save them into the sampleData folder.

   • SampleCourseData
   • SamplePersonData
   • SampleStudentData
   • SampleCreditData

3. Open a new Windows command prompt window and navigate to the sampleData folder. For example, cd c:\Users\<your user name>\Downloads.

4. Execute the following bcp commands to insert sample data into the tables replacing the values for server, database, user, and password with the values for your environment.

   bcp Course in SampleCourseData -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
   bcp Person in SamplePersonData -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
   bcp Student in SampleStudentData -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
   bcp Credit in SampleCreditData -S <server>.database.chinacloudapi.cn -d <database> -U <user> -P <password> -q -c -t ","
   

You have now loaded sample data into the tables you created earlier.

Query data

Execute the following T-SQL queries to retrieve information from the database tables.

This first query joins all four tables to find the students taught by 'Dominick Pope' who have a grade higher than 75%. In a query window, execute the following T-SQL query:

-- Find the students taught by Dominick Pope who have a grade higher than 75%
SELECT  person.FirstName, person.LastName, course.Name, credit.Grade
FROM  Person AS person
    INNER JOIN Student AS student ON person.PersonId = student.PersonId
    INNER JOIN Credit AS credit ON student.StudentId = credit.StudentId
    INNER JOIN Course AS course ON credit.CourseId = course.courseId
WHERE course.Teacher = 'Dominick Pope'
    AND Grade > 75;

This query joins all four tables and finds the courses in which 'Noe Coleman' has ever enrolled. In a query window, execute the following T-SQL query:

-- Find all the courses in which Noe Coleman has ever enrolled
SELECT  course.Name, course.Teacher, credit.Grade
FROM  Course AS course
    INNER JOIN Credit AS credit ON credit.CourseId = course.CourseId
    INNER JOIN Student AS student ON student.StudentId = credit.StudentId
    INNER JOIN Person AS person ON person.PersonId = student.PersonId
WHERE person.FirstName = 'Noe'
    AND person.LastName = 'Coleman';

Related content

• Tutorial: Design a relational database in Azure SQL Database
• What's new in Azure SQL Database?
• Configure and manage content reference - Azure SQL Database

Next step

Advance to the next tutorial to learn about designing a database using Visual Studio and C#.