说出3个会发生的问题如所有资料用单一资料表储存.doc

獅子會中學2002-2003年度中七Visual FoxPro 功課4.4 Multiple databases多個數據庫 Accessing Multiple TablesExercise 23 Answer1. Identify three problems in updating if all the information is placed in a single table. Hence, explain why a database usually consists of more than one table.說出3個會發生的問題如所有資料用單一資料表儲存，並解釋為何資料表通常以多於一個資料表去儲存。i) Changing data Changing information in one record have to change information in all records.修改資料 修改一記錄需修改所有記錄ii) Adding new field Adding new field has to ensure each record add the appropriate data.增加欄位 增加欄位要在所有關記錄加入資料iii) Deleting record Delete one record will delete another record with the same data in the related field.刪除記錄 刪除一個記錄會刪除同資料欄的其他記錄The above problem is mainly caused by data redundancy which can be solved using multiple tables relating each others by foreign keys.以上問題主要是因為資料重覆而至，解決方法是用多個資料表，將有關資料表作關聯。2. In a RDBMS, tables are logically linked together. Explain the terms “logical linked”.在RDBM 資料表是邏輯的關關，解釋何謂邏輯關聯。Logical linked means that tables in the same database has foreign keys linked with each other.邏輯關聯即資料庫中的資料表由關鍵欄關聯3. In Visual FoxPro, multiple tables can be open simultaneously in different work areas. Write command to在Visual FoxPro 中，多個資料表是可以同時在不同的工作區開啟，寫出執行下列工作的指令a) assign an alias to a work area 在工作區加入別名Use filenameA ALIAS tableAExample: use school.dbf alias schb) choose a specified work area for a table 選取資料表工作區SELECT nWorkAreaExample: select 1 c) switch between different work areas 選取不同的工作區SELECT tableASELECT tableBExample:select tableAuse school.dbfselect tableBuse inventory.dbfd) access information from a remote work area. 取得其他工作區的資料Using qualified field names.To access field1 of a table open in work area B, useB-field1 or B.field1“B-” or “B.” is called the qualifier.4.Consider the following commands: 下列指令SELECT ASET RELATION TO B INTO CIdentify the parent table, the child table and the common key expression.說出父母資料表，子女資料表，及關鍵欄位The parent table:AThe child table:CCommon key expression:B5. A computer game records the results of the players in two files:電腦遊戲用下列資料表去記錄結果We have to open the files in two work areas and set up relation so that every record in the table result could be mapped to the corresponding record in player.假設兩資料表需做到互相關聯去取得每項資料(a) What are the two commands to assign exercise23-5-player in work area and give alias splayer for the work area.那兩句指令可將exercise23-5-player 資料表用別名splayer 建立工作區select 0use exercise23-5-player alias splayer(b) Add two commands to assign exercise23-5-result in another work area and give alias sresult for the work area.加多兩句指令去將資料表exercise23-5-result用別名sresult建立另一工作區select 0use exercise23-5-result alias sresult(c) Add the command to select splayer and set index key playcode加入指令去將splayer成為索引檔案，索引鍵為playcodeselect splayerindex on playcode to splayer(d) Add command to set relationship with splayer and sresult. Parent is sresult, child is splayer 加入指令去將splayer及sresult作關聯， sresult 作為父母資料表，child作為子女資料表。select sresult set relation to playcode into splayer(e) Write down the results for the following: 寫出下列指令的結果List , points for points2506. A fashion company stores the sales records of the customers in four related files:一間服裝店用四個資料表儲存銷售記錄，顧客記錄，點存記錄，購貨記錄SALES.DBFCUSTOMER.DBFSTOCK.DBFFields NameType WidthDecSaleCodeCharacter10DescriptionCharacter30PURCHASE.DBFFields NameTypeWidthDecOrderNo.Numeric3-SaleCodeCharacter10Define and explain the relation of the following tables ?解釋各資料表的關係。(one to one, one to many, many to one, many to many)(a) What is the relation from Sales to Customer ? It is one-to-one relationship. Customer_id can be used as key field. Either table can be parent table. (b) What is the relation from Stock to Purchase ?It is one-to-many relationship. Example: the record 021 Cello in Stock is linked to both order “021 2” and “021 3” in Purchase. Purchase is parent table and stock is child table.(c) What is the relation from Purchase to Stock?It is many-to-many relationship. SaleCode in purchase can be linked to different SaleCode in stock.(d) What is the relation from Purchase to Sales?It is many-to-one relationship. Different SalesCode in purchase can be linked to one SaleCode in stock. (e) Assume that the files sales.dbf and customer.dbf are open in two work areas. Write down the commands to set up the relationship between Sales and Customer. In your answer, you need to explicity write the creation of index files and the selection of work area.假設sale.dbf及customer.dbf資料表是在兩個不同的工作區開啟，寫出指令去使兩資料表關聯，你需說出如何建立索引檔案及如何選取工作區。Use sales in 0 alias rsalesUse customer in 0 alias rcustomerSelect rcustomerIndex on custom_id to rcustomerSelect rsalesSet relation to custom_id into rcustomer(f) Write ONE command to display the sales information, including the customer name, telephone, date of purchase and payment, in a Browse w