Create foreign key constraint the table student
Activity 4.1: Creating Database Objects with SQL in SQL Azure
SQL Azure provides a query tool to run SQL statements. In the following, we will use the query tool to execute SQL statements.
Figure 1 Specify Database Settings
4. Select the database Computer_Store and click MANAGE in the bottom of the page.
7. Let us enter the following SQL statements to create tables. After the SQL statements are entered, click Run.
---Create tables
CREATE TABLE CUSTOMER
(
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(30),
LastName VARCHAR(30) NOT NULL,
Phone CHAR(12),
Street VARCHAR(30),
City VARCHAR(30),
State VARCHAR(30),
Zip CHAR(5)
)
GO
| ( |
|---|
) |
|
|---|
ItemID INT,
Qoh INT DEFAULT 0 CHECK( Qoh >= 0) NOT NULL,
Price MONEY DEFAULT 0 CHECK (Price >= 0) NOT NULL, CONSTRAINT Inventory_ItemId_fk
FOREIGN KEY (ItemID)
REFERENCES ITEM (ItemID)
)
GOCREATE TABLE ORDERS
| ( |
|---|
CREATE TABLE SHIPPING
(
ShippingID INT PRIMARY KEY,
OrderID INT NOT NULL,
EmployeeID INT NOT NULL,
ShippingDate DATETIME NOT NULL,
Street VARCHAR(30) NOT NULL,
City VARCHAR(30) NOT NULL,
State VARCHAR(30) NOT NULL,
Zip CHAR(5) NOT NULL,
CONSTRAINT Shipping_OrderId_fk
FOREIGN KEY (OrderID)
REFERENCES ORDERS (OrderID), CONSTRAINT Shipping_EmployeeId_fk
FOREIGN KEY (EmployeeID)
REFERENCES EMPLOYEE (EmployeeID) )
GOCREATE TABLE ORDER_INVENTORY
(
OrderID INT,
InventoryID INT,
Quantity INT DEFAULT 0 CHECK( Quantity >= 0) NOT NULL, CONSTRAINT OInventory_OrderID_fk
FOREIGN KEY (OrderID)
REFERENCES ORDERS (OrderID),
CONSTRAINT OInventory_InventoryID_fk
FOREIGN KEY (InventoryID)
REFERENCES INVENTORY (InventoryID), CONSTRAINT OrderID_InventoryID_pk
| 8. |
|---|
9. To verify that the tables are indeed created in the database Computer_Store, click the Design link. The created tables should be listed as shown in Figure 4.
12. Click Design to switch back to the design window. Click the Edit icon next to the table CUSTOMER as shown in Figure 6.
After the tables are created, SQL statements can be used to enter data in those tables. The data entry process will be covered later in this chapter.
Activity 4.2 Database Management with SQL
CONSTRAINT Student_FacultyID_fk
FOREIGN KEY (FacultyID)
REFERENCES FACULTY (FacultyID)
ALTER TABLE CLASS ADD CONSTRAINT CLASS_SemesterID_fk
FOREIGN KEY (SemesterID) REFERENCES SEMESTER (SemesterID)ALTER TABLE CLASSROOM ADD CONSTRAINT CLASS_BuildingID_fk FOREIGN KEY (BuildingID) REFERENCES BUILDING (BuildingID)
Figure 9 Foreign Key
Activity 4.3 Populating Database Tables with Data
1. Assume that you have logged on to the Windows Azure Management Portal. Select SQL DATABASES and the database Computer_Store. Click MANAGE to log on to the SQL Database portal shown in Figure 10.
VALUES(2,'Keren','Wu','281-333-1111','345 Bagby','Houston','TX','77002') INSERT INTO CUSTOMER
VALUES(3,'Dan','Parr','361-111-2222','324
Miori','Victoria','TX','77903')
INSERT INTO CUSTOMER
VALUES(4,'Lisa','Garcia','806-333-1111','874
Wilson','Amorillo','TX','79105')
INSERT INTO CUSTOMER
VALUES(5,'Susan','Holly','512-111-2222','4848
Lakeside','Austin','TX','78767')
INSERT INTO CUSTOMER
VALUES(6,'Al','Dean','972-111-3333','1398 International
Rd','Dallas','TX','75201')
INSERT INTO CUSTOMER
VALUES(7,'David','Jouns','281-000-1111','267
College','Houston','TX','77487')
INSERT INTO CUSTOMER
VALUES(8,'Bud','Fry','512-234-0000','2396
Anderson','Austin','TX','78767')
INSERT INTO CUSTOMER
VALUES(9,'Jane','Young','212-111-4444','1563 Madison','New
York','NY','10159')
INSERT INTO CUSTOMER
VALUES(10,'Robert','Smith','334-555-1111','2995
Post','Montgamery','AL','35824')
INSERT INTO CUSTOMER
VALUES(11,'Roy','Munoz','303-333-0000','201
Bellaire','Denver','CO','80222')
INSERT INTO CUSTOMER
VALUES(12,'Paul','Sitka','650-555-3333','1240 Villa','Mountain
View','CA','94041')
INSERT INTO CUSTOMER
VALUES(13,'Gary','Sherman','508-333-7777','145
Corporate','Cambridge','MA','02142')
INSERT INTO CUSTOMER
VALUES(14,'Larry','Hursh','281-000-1234','1248 Katy
Frwy','Houston','TX','77429')
INSERT INTO CUSTOMER
VALUES(15,'David','Reed','281-444-9999','579 S.Mason','Houston','TX','77290')
INSERT INTO CUSTOMER
VALUES(16,'John','Shum','281-123-2345','389 Brooks','Houston','TX','76385')INSERT INTO CUSTOMER
VALUES(21,'Aian','Ramos','281-777-1111','466 Sugar','Houston','TX','77449')INSERT INTO CUSTOMER
VALUES(22,'Mike','Garcia','281-000-2222','1400 Richmond','Houston','TX','77562')INSERT INTO CUSTOMER
VALUES(27,'Wanda','Burton','281-012-0123','4221 Gulf Frwy','Houston','TX','77903')INSERT INTO CUSTOMER
VALUES(28,'Steve','Perry','281-456-0000','827 Louetta','Houston','TX','76450')--- inserting records into table INVENTORY INSERT INTO INVENTORY
VALUES(1,1,5,1500)
INSERT INTO INVENTORY
VALUES(2,1,500,1400)
INSERT INTO INVENTORY
VALUES(3,2,2,799)
INSERT INTO INVENTORY
VALUES(4,2,454,749)
INSERT INTO INVENTORY
VALUES(5,3,12,1799)
INSERT INTO INVENTORY
VALUES(6,3,200,1699)
INSERT INTO INVENTORY
VALUES(7,4,58,1824)
INSERT INTO INVENTORY
VALUES(8,5,56,1999)
INSERT INTO INVENTORY
VALUES(9,6,23,1699)
INSERT INTO INVENTORY
VALUES(10,7,455,499)
INSERT INTO INVENTORY
VALUES(11,8,79,1999)
INSERT INTO INVENTORY
VALUES(12,9,51,2499)
INSERT INTO INVENTORY
VALUES(13,10,222,1040)
INSERT INTO INVENTORY
VALUES(14,11,344,649)
INSERT INTO INVENTORY
VALUES(15,12,450,449)
INSERT INTO INVENTORY
VALUES(16,13,899,299)
INSERT INTO INVENTORY
VALUES(17,14,682,449)
INSERT INTO INVENTORY
VALUES(18,15,739,649)
INSERT INTO INVENTORYVALUES(19,16,145,409)
INSERT INTO INVENTORY
VALUES(20,17,500,654)
INSERT INTO INVENTORY
VALUES(21,18,130,599)
INSERT INTO INVENTORY
VALUES(22,19,5,1350)
INSERT INTO INVENTORY
VALUES(23,19,100,1300)
--- inserting records into ORDERS
INSERT INTO ORDERS
VALUES(1,1,'2012-02-01')
INSERT INTO ORDERS
VALUES(2,2,'2012-05-01')
INSERT INTO ORDERS
VALUES(3,3,'2011-02-01')
INSERT INTO ORDERS
VALUES(4,4,'2011-03-01')
INSERT INTO ORDERS
VALUES(5,5,'2011-03-01')
INSERT INTO ORDERS
VALUES(6,6,'2011-04-01')
INSERT INTO ORDERS
VALUES(7,7,'2011-04-01')
INSERT INTO ORDERS
VALUES(8,8,'2012-05-01')
INSERT INTO ORDERS
VALUES(9,9,'2011-05-01')
INSERT INTO ORDERS
VALUES(10,10,'2011-05-01')
INSERT INTO ORDERS
VALUES(11,11,'2011-06-01')
INSERT INTO ORDERS
VALUES(12,12,'2011-06-01')
INSERT INTO ORDERS
VALUES(13,13,'2012-07-01')
INSERT INTO ORDERS
VALUES(14,14,'2012-07-01')VALUES(14,14,2)
INSERT INTO ORDER_INVENTORY
VALUES(15,15,1)
INSERT INTO ORDER_INVENTORY
VALUES(16,16,1)
INSERT INTO ORDER_INVENTORY
VALUES(17,17,1)
INSERT INTO ORDER_INVENTORY
VALUES(18,18,2)
INSERT INTO ORDER_INVENTORY
VALUES(19,19,1)
INSERT INTO ORDER_INVENTORY
VALUES(20,20,1)
INSERT INTO ORDER_INVENTORY
VALUES(21,21,3)
INSERT INTO ORDER_INVENTORY
VALUES(22,22,1)
INSERT INTO ORDER_INVENTORY
VALUES(23,23,1)
INSERT INTO ORDER_INVENTORY
VALUES(24,1,1)
INSERT INTO ORDER_INVENTORY
VALUES(25,2,1)
INSERT INTO ORDER_INVENTORY
VALUES(26,6,1)
INSERT INTO ORDER_INVENTORY
VALUES(27,7,1)
INSERT INTO ORDER_INVENTORY
VALUES(28,8,1)
INSERT INTO ORDER_INVENTORY
VALUES(29,9,2)
INSERT INTO ORDER_INVENTORY
VALUES(30,10,1)
INSERT INTO ORDER_INVENTORY
VALUES(30,3,1)
INSERT INTO ORDER_INVENTORY
VALUES(31,2,1)
INSERT INTO ORDER_INVENTORY
