關聯式資料庫.docVIP

關聯式資料庫 Relational Data Base 基本定義 definition Record(資料錄)：橫列資料。單位：筆。 Field(欄)：縱行資料。 Primary key(主鍵欄位)：通常是「識別碼」欄位。每一筆資料錄均必須有不同的識別碼。若是須要多個欄位才能識別出不同之資料錄，則稱之為「複合主鍵」(composite primary keys)。 A primary key is a field whose values are unique. For example, ID is most commonly used as a primary key. Composite primary keys are a set of keys whose composite values are unique. Foreign key(外鍵欄位)：本表之(外鍵)欄位為另一表的主鍵欄位。透過外鍵欄位可以將各個表格關聯起來。(註：在本表外鍵欄位下的值可能非唯一，但其在所對應到的它表主鍵欄位中，必須是唯一) A foreign key in a table must be a primary key of another table. Different tables are related via foreign key(s). Noted that values of foreign key can be duplicate. Schema (綱目)：欄位名稱，不包括資料錄。資料庫設計的對象是綱目，而非資料錄。(在設計階段亦無從得知真正的資料錄為何)。例如上表之綱目為：(姓名，身分證字號，年齡，通訊處，公司代號，年資)。 A schema is a set of field names without order and record. The object of database design is schema instead of records. As a matter of fact, it may be no record when a database is designed. For example, the schema of the upper table in the following figure is denoted as (name, staff-ID, age, address, company-ID, years-on-duty). Company-ID is a foreign key of the upper table, while it is the primary key of the lower table. 【作業 HW】 表單Students的綱目(schema)為(姓名，學號，年齡，通訊處，家長，班級)。表單Class的綱目為(班級，班長，導師，人數)。試問此二表單之主鍵、外鍵各為何?理由為何?Class有外鍵嗎? There are two tables, namely STUDENTS and CLASS. The schema of STUDENTS is (name, student-ID, age, address, parent, class-ID). The schema of CLASS is (class-ID, chief, teacher-in-charge, number-of-students). Please indicate the primary key(s) and foreign key(s) of these two tables with your reasons. Does CLASS have any foreign key? 【作業HW】 請設計由多個表單所組成的交通資料庫綱目，並分別指出其主鍵、外鍵。 Please design a set of transportation schema, and indicate their primary and foreign keys. 基本原理 Principle 將一張大表，拆成數張小表，但須可以組合成原來的表格。 A big table can be decomposed into several smaller tables. These smaller tables should be composed back to the original table without losing any record or having any extra record that is not in the original table. 將表一拆成表二、三，再組成(join