实验二 :SQL Server English Query MS Access QBE Query 使用摘要一 试验要求 P2-P3 二 具体试验 1. 安装, 导入示例数据库 :NorthWind P2-P3 2. 为 NorthWind 重构 ER 图 P4-P5 3. 查询下列 Query P5-P17 4.English Query P17-P27 (1)English Query 的不便之处 P17 (2) 查询只遇到的问题 P17 (3) 一些有趣的查询 P17-P27 5. 熟悉 QBE Query P27-P38 1
一 实验目的 : 1) 了解自然语言查询的处理方法 2) 巩固实体关系理论和 ER 模型相关知识 3) 学会使用 SQL server 所支持的 NLP 查询 二 实验平台 : 1) 操作系统 :Window2000 或者 Windows XP 2) 数据库系统 :SQLServer2000( 个人版 ) MS Access 2000 或 Access 2003 三 实验步骤和问题 : 1 安装 English Query 组件在组件安装完毕后, 启动 English Query 工程, 新建工程, 导入示例数据库 :NorthWind 导入 SQL Server 中已经存在的一个样例数据库 NorthWind 它是一个包含商品, 客户, 供货商, 运货商, 订单等若干实体的一个实例数据库 2 为 NorthWind 重构 ER 图 依照模型所展示的实体关系列表, 画出 ER 图, 描述各个实体的属性及实体间的关系 3 查询下列 Query 2
以下列出一些例子查询, 按照初始化模型的设计, 这些 Query 系统是不能正确回答的 需要通过建立同义词字典 添加新的关系才能支持这样的语义查询 应该怎样使得查询可以正确执行呢? A 说明建立实体关系的语义属性的过程, 并给出正确的查询结果 B 同时给出这些查询的 SQL 语句和关系代数表达式 1. List all the customers. (all condition) 2. Show the customers and their addresses. (projection) 3. Show the customers whose city is London. (where condition) 4. Show the order date and shipped date of the order 10250. (where condition and projection) 5. List the customers and their mobile phone. (Adding Synonyms to an Entity) 6. Show the order whose shipment is 65.83 (Adding Synonyms to an Entity) 7. List the customer who is Marketing Manager. (Creating a Relationship) 8. Show the order whose customer comes from London (Adding a New Relationship) 9. Which supplier sells Northwoods Cranberry Sauce (Adding a New Relationship) 10. Who sells Northwoods Cranberry Sauce (Adding a New Relationship) 11. Show the 1996 products (complex model for join) 4 Open Question: 设计 Query 同学们可以根据表中的各种实体和关系来测试复杂的查询 提出一些更加有意思的查询来测试 English Query 组件的查询支持能力 能发现它不能支持的查询, 并找出原因 5 熟悉 QBE Query Microsoft Access 2000 或 2003, 部分支持 QBE 查询的功能 使用也比较简单 1. 在 Access 中导入 NorthWind 数据库中的部分表和数据 这个工作需要同学们耐心的手动完成, 导入的表有 :Customers 表. Orders 表, Employee 表. Supplier 表和 Products 表 数据可以选择性的插入部分, 但请注意表之间的参照关系, 不要选择连接结果为 0 的数据, 将不便于 Join 查询的实验 2. 将上面给出的 Query 语句的 QBE 表达式在 Access 中作查询实验 将结果展示出来 3. 因为语义查询所支持的复杂查询是有限的, 同样的, 同学们也可以参考本章的附加习题中的 Query, 来设计一些复杂的有意思的查询, 更深入的熟悉和使用 Access 的查询功能 四 : 试验过程和问题求解 1 安装 English Query 组件 (1) 安装先在安装向导中, 选择 SQLServer2000 安装组件, 然后再安装组件种选择安装 English Query, 然后按照向导就可以成功安装 English Query 了 (2) 导入示例数据库 :NorthWind 单击开始, 选择程序, 再选择 Microsoft SQL Server, 选择 English Query, 选择 Microsoft English Query, 进入了 English Query 3
2 为 NorthWind 重构 ER 图 (1) NorthWind 中各个实体集的关系图如下 : 4
(2)The illustration shows an overview of the tables, fields, and joins in the Northwind database (3) 为 NorthWind 重构 ER 图 Custome rs Orders Details m ha ve 1 Products 1 order have 1 m m m m supply type Orders m m 1 1 shiper process 1 1 Suppliers Categori es Shippers Employees 3 查询下列 Query 1.List all the customers. (all condition) English Query: 可以直接查询 SQL 语句 :select dbo.customers.customerid from dbo.customers 关系代数 :Π customerid(customers) 5
2.Show the customers and their addresses. (projection) English Query: 可以直接查询 SQL 语句 :select dbo.customers.customerid, dbo.customers.address from dbo.customers 关系代数 :Π customerid, CustomersAddress (Customers) 3.Show the customers whose city is London. (where condition) English Query: 可以直接查询 6
SQL 语句 :select dbo.customers.customerid from dbo.customers where dbo.customers.city='london' 关系代数 :Π customerid(δcustomers.city='london' (Customers)) 4.Show the order date and shipped date of the order 10250. (where condition and projection) English Query: 可以直接查询 SQL 语句 :select dbo.orders.orderid, dbo.orders.orderdate, dbo.orders.shippeddate from dbo.orders where dbo.orders.orderid=10250 关系代数 :ΠorderID,OrderDate,Orders.ShippedDate (δorders.orderid=10250 (Orders)) 7
5.List the customers and their mobile phone. (Adding Synonyms to an Entity) English Query:(a) 单击 customer 前的加号, 双击下面的 customer_ phone. (b) 在新弹出的对话框中, 单击 Words 后面的.., 单击出现的 mobile phone, 单击 OK. SQL 语句 :select dbo.customers.customerid, dbo.customers.phone from dbo.customers 关系代数 :ΠcustomerID,Customers.Phone (Customers) 8
6.Show the order whose shipment is 65.83 (Adding Synonyms to an Entity) English Query: (a) 单击 order 前的加号, 双击下面的 order_freight. (b) 在新弹出的对话框中, 单击 Words 后面的.., 单击出现的 shipment, 单击 OK. SQL 语句 :select dbo.orders.orderid from dbo.orders where dbo.orders.freight=65.83 关系代数 :ΠOrderID (δorders.freight=65.83 (Orders)) 9
7.List the customer who is Marketing Manager. (Creating a Relationship) English Query: (a) 单击 customer 前的加号, 双击下面的 customer_contact_title. 在新弹出的对话框中, 单击 Add values of the entity 前的小格, 打上对号, 10
单击 OK. (b) 单击 customer 前的加号, 双击下面的 customer_company_name. 在新弹出的对话框中, 单击 Add values of the entity 前的小格, 打上对号, 单击 OK. (c) 右键单击 Relationships, 选择 Add Relationship (d) 在新弹出的对话框中, 单击 Add, 选择 customer, 再单击 Add, 选择 customer_contact_title, 单击 Add, 选择 Adjective Phrasing, 单击 OK, 单击 OK, 再单击 OK (e) 进行查询 SQL 语句 :select dbo.customers.customerid from dbo.customers where dbo.customers.contacttitle='marketing Manager' 关系代数 :ΠCustomerID (δcontacttitle='marketing Manager' (Customers)) 11
8.Show the order whose customer comes from London (Adding a New Relationship) English Query: (a) 单击 customer 前的加号, 双击下面的 customer_city. (b) 在新弹出的对话框中, 单击 Add values of the entity 前的小格, 打上对号, 单击 OK. (c) 右键单击 Relationships, 选择 Add Relationship (d) 在新弹出的对话框中, 单击 Add, 选择 customer, 再单击 Add, 选择 customer_city, 单击 Add, 选择 Verb Phrasing, 单击 OK, 在 sentence 后面选择 Subject Verb Object, Subject 后选择 customer,verb 后选择 comes from,direct 后选择 customer_city. 单击 OK, 再单击 OK (e) 进行查询 SQL 语句 :select dbo.orders.orderid from dbo.customers, dbo.orders where dbo.customers.city='london' and dbo.customers.customerid=dbo.orders.customerid 关系代数 :ΠOrderID (δcustomers.city='london' (Customers Orders)) 12
9.Which supplier sells Northwoods Cranberry Sauce (Adding a New Relationship) English Query: (a) 单击 product 前的加号, 双击下面的 product_name. (b) 在新弹出的对话框中, 单击 Add values of the entity 前的小格, 打上对号, 单击 OK. (c) 右键单击 Relationships, 选择 Add Relationship (d) 在新弹出的对话框中, 单击 Add, 选择 product, 再单击 Add, 选择 product_name, 单击 Add, 选择 Verb Phrasing, 单击 OK, 在 sentence 后面选 13
择 Subject Verb Object, Subject 后选择 supplier,verb 后选择 sell,direct 后选择 product_name. 单击 OK, 再单击 OK (e) 进行查询 SQL 语句 :select distinct dbo.products.supplierid from dbo.products where dbo.products.productname='northwoods Cranberry Sauce' 关系代数 :ΠSupplierID (δproductname='northwoods Cranberry Sauce' (Products)) 14
10.Who sells Northwoods Cranberry Sauce (Adding a New Relationship) English Query: 完成九后可直接查询 SQL 语句 :select distinct dbo.products.supplierid from dbo.products where dbo.products.productname='northwoods Cranberry Sauce' 关系代数 :ΠSupplierID (δproductname='northwoods Cranberry Sauce' (Products)) 15
11.Show the 1996 products (complex model for join) English Query: (a) 右键单击 Relationships, 选择 Add Relationship (b) 在新弹出的对话框中, 单击 Add, 选择 product, 单击 Add, 选择哦 Order_date, 再单击 Add, 选择 Customers, 在 When 后选择 Order_date, 单击 Add, 选择 Verb Phrasing, 单击 OK, 在 sentence 后面选择 Subject Verb Object, Subject 后选择 Customers,Verb 后选择 Order,Direct 后选择 product. 单击 OK, 再单击 OK (c) 进行查询 SQL 语句 :select distinct dbo.products.productname from dbo."order Details", dbo.orders, dbo.products where dbo."order Details".OrderID=dbo.Orders.OrderID and dbo.orders.orderdate>='19960101' 关系代数 :ΠProductName (δorderdate='1996' (Products Orders Order Details)) 16
4 Open Question: 设计 Query (1)English Query 的不便之处许多弹出的窗口大小不能变化, 很多单词显示不出来, 使用时除了了看使用手册别无它法 (2) 查询只遇到的问题 (a)sells 不识别, 学要改成 sell 才可以运行 (b) 下例中,List the current products 用视图方法要用很多步骤, 但用直接添加关系的方法可以得出同样的结论, 而且要简单的多 (3) 一些有趣的查询 1.What are products? ( 学习怎样使用注视以及产品说明 ) English Query: 双击 product, 出现对话框, 在 Help 后面添加 It is good. 17
2.Show all the products ( 学习怎样使用高级选项, 设置 SQL 条件 ) English Query: First create the relationship some products are discontinued and add the SQL condition to determine the discontinued products. To create the relationship some products are discontinued 1. On the Semantics tab of the Model Editor, drag product from the entity list in the left-hand pane onto the Canvas pane. 2. Right-click the product entity in the Canvas pane, and select Add Relationship. 3. In the New Relationship dialog box, click Add for Phrasings. 4. In the Select Phrasing dialog box, double-click Adjective Phrasing. 5. In the Adjective Phrasing dialog box, do the following: a. In Subject box, select products. b. In the Adjective Type box, select Single Adjective. 18
c. In the Adjective that describes subject box, type discontinued. d. Click OK. Next, add to the relationship a SQL condition that identifies which products are discontinued. To add a SQL condition 1. Click the Database tab. 2. On the Database tab, select This relationship is true only when the following SQL condition is true and type dbo.products.discontinued=1. 3. Click OK. The Canvas pane should look like: Next, you want to add the default relationship option to the product entity To add the default relationship option 1. In the Canvas pane, double-click product. 2. In the Entity dialog box, click Advanced. 3. In the Advanced Entity Properties dialog box, click the Entity Defaults tab. 4. On the Entity Defaults tab, click Add. 5. In the Add Entity Default dialog box, do the following: a. In the Relationship list, select some_products_are_discontinued. b. Select Invert. c. In the Default conditions invalid when these relationships are used list, double-click the following relationships to list them as relationships in which this default conditions are invalid. 19
product_names_are_the_names_of_products products_have_categories products_have_product_ids The relationships move from the left-hand list to the right-hand list. You have just indicated that when questions are asked about the product names, categories, or product IDs do not use the default conditions that you set. d. Click OK. 6. If you see some_products_are_discontinued in the Defaults for: product box, click OK. If you do not see some_products_are_discontinued in the Defaults for: product box, click Add and repeat Step 5. 7. Click OK again. 8. Click Start on the Standard toolbar and do the following: a. Select show the products in the Query list of the Model Test window. b. Press ENTER. 20
3.List the current products ( 学习使用引入视图, Begin, by importing a view into your English Query model. To import a view 1. From the Model menu, choose Import Tables. 2. In the Available list of the New Database Tables and Views dialog box, double-click dbo.current Products List. The dbo.current Products List entry moves to the Selected list. 3. Click OK. The dbo.current Product List appears in the Tables list on the SQL tab of the Model Editor. 4. Click the SQL tab and look for dbo.current Product List. 21
Now that you have imported the Current Product List view, you have to add a primary key to the dbo.current Product List table to join it to the dbo.product table. To add Primary key information to dbo.current Product List 1. Double-click dbo.current Product List. 2. Right-click in the gray column preceding ProductID. A shortcut menu appears with the Primary Key command at the top. 3. Click Primary Key. A yellow key appears in the gray column to the left of ProductID. 4. Click OK. Next you create a join between the ProductID of dbo.current Product List and dbo.products To create a join 1. From the Model menu, choose Add Database Object, and then Add Join. 2. In the New Join dialog box, do the following: a. In the Table list under Source select dbo.products. b. In the Table list under Destination, select dbo.current Product List. c. Click Add. 3. In the Join Condition dialog box, do the following: a. In the From source table field list, select ProductID. b. In the To destination table field list, select ProductID. c. Click OK. 4. Click OK. The Join Conditions list should contain ProductID~ProductID. If ProductID~ProductID does not appear in the Join Conditions list, click Cancel and repeat steps 1 through 3. 22
5. Expand dbo.products. 6. Expand Joins. dbo.products.productid~dbo.current.product List.ProductID should appear. Next you want to specify that CurrentProducts.ProductName in the Current Products List view is a duplicate of the ProductName field in the Products table. Because the Current Product List view is a subset of the Products table, the names of the products are the same. You could have English Query treat product names as different fields, but it is more efficient to view them as one field. To do this, you want to set CurrentProducts.ProductName as a denormalized field of Products.ProductName. To make the CurrentProducts.ProductName a denormalized field of Products.ProductName 1. Double click dbo.current Product List. 2. In the Table dialog box, select ProductName. 3. Click the Advanced tab. 4. For Denormalized, select dbo.product and ProductName. 5. Click OK. Now that you denormalized ProductName, you can specify that you want English Query to recognize only the products in the Current Product List. Instead of loading all the values in the Product table each time you have a query that uses product, you can limit the number of items that are loaded to only those in the Current Product List. Doing this decreases the loading time. This process is possible only because you denormalized ProductName in the previous step. To specify where to load values from 1. Click the Semantics tab. 2. In the left pane of the Semantics tab of the Model Editor window, expand product in the Entities list, and then double-click product_name. 3. In the Entity dialog box, click Add values of entity to model. 23
You must select this option before you try to select the Load Words from option in the Advanced Entity Properties dialog box. If you eliminate this step, you will be unable to select the Load Words from option. 4. Click Advanced. 5. In the Advanced Entity Properties dialog box, do the following: a. In Load Words from, select dbo.current Product List. b. Click OK. 6. Click OK. Next, create an adjective relationship that shows some products are current. To create an adjective relationship 1. If a diagram appears in the Canvas pane, right-click the pane, but not on the diagram, and select Clear. 2. From the left pane of the Semantics tab, drag product to the Canvas pane. 3. In the Canvas pane, right-click product and select Add Relationship. 4. In the New Relationship dialog box, click Add for Phrasings. 5. In the Select Phrasing dialog box, double-click Adjective Phrasing. 6. In the Adjective Phrasing dialog box, do the following: a. In the Subject list, select products if it is not already selected. b. In Adjective Type, select Single adjective. c. In Adjective that describes subject, type current. d. Press ENTER. e. Click OK. Next, add a SQL condtion that identifies which products are current. To add a SQL condition 24
1. Click the Database tab. 2. On the Database tab, select This relationship is true only when the following SQL condition is true and type dbo.products.discontinued=0. 3. Click OK. The Canvas pane should look like the following: 4. Test your model by typing List the current products in the Query box of the Model Test window and pressing ENTER. Your results should look like: Note If these results do not appear in the Results pane, click the (View Results) on the Model Test window toolbar. 5. Close the Model Test window. Now that you can ask questions about current and discontinued products, expand your model to ask questions about how products are ordered. 25
26
用直接添加关系做出同样的结果 ( 如下 ): 5 熟悉 QBE Query 1.List all the customers. (all condition) 27
2.Show the customers and their addresses. (projection) 28
3.Show the customers whose city is London. (where condition) 29
4.Show the order date and shipped date of the order 10250. (where condition and projection) 30
5.List the customers and their mobile phone. (Adding Synonyms to an Entity) 31
6.Show the order whose shipment is 65.83 (Adding Synonyms to an Entity) 32
7.List the customer who is Marketing Manager. (Creating a Relationship) 33
8.Show the order whose customer comes from London (Adding a New Relationship) 34
9.Which supplier sells Northwoods Cranberry Sauce (Adding a New Relationship) 35
10.Who sells Northwoods Cranberry Sauce (Adding a New Relationship) 36
11.Show the 1996 products (complex model for join) 37
38