关联式资料库正规化基本原理.doc

啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊5条回复 - 发帖时间: 2000年1月1日资料表若未经正规化过程,日后在资料库维护上将出现问题.每一个栏位内只能有一个单元值错误范例学号姓名电话AA0001林一0912345678 / 07.啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊关联式资料库正规化基本原理啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊關聯式資料庫正規化基本原理資料表若未經正規化過程，日後在資料庫維護上將出現問題。1. 每一個欄位內只能有一個單元值錯誤範例學號姓名電話AA0001林一0912345678 / 07-6968121正確範例學號姓名電話AA0001林一09123456782. 若兩資料表之關係為一對多時(例如: 一位老師上許多課程,每一課程只有一位老師), 需將一方資料表之主要鍵置於多方之資料表中. 此一方資料表之主要鍵即為”外來鍵”(Foreign Key).3. 每一資料表之各欄位需與主要鍵功能相依.4. 若兩資料表之關係為多對多時(例如:一位學生上許多課程,一門課程有許多學生上), 中間需新增一聯結資料表. 5. 資料表之關係為一對多時(例如: 一位老師上許多課程,每一課程只有一位老師), 需將一方資料表之主要鍵置於多方之資料表中. 此一方資料表之主要鍵即為”外來鍵”(Foreign Key).6.資料表之關係為多對多時(例如:一位學生上許多課程,一門課程有許多學生上), 中間需新增一聯結資料表. 聯結資料表之主要鍵即為原本被連結之兩個資料表的主要鍵(也是外來鍵)之”和”.正規化練習題1. 請依據下列敘述，並按照關聯式資料庫與正規化理論，繪出個體關係圖 (E-R Diagram)。l 高苑技術學院位於高雄縣， 目前有學生與老師若干人。 學生與老師都有編號。 l 學生分屬不同班級(如五資四甲)，每一班級都有一個編號。 每班都須註明教室位置如: 資管大樓301室。l 每一位老師都教一種以上之課程。 每一課程都有一編號、名稱、上課時數以及授課期間， 例如: ( 92346，資料庫應用系統設計，三小時，89年度上學期)等。 l 每一堂課都包括數十名學生。2. 下圖為某資料庫之關聯圖, 請標明其關聯性(直接畫於圖上即可) 3. 下圖為Pubs資料庫之關聯圖, 請標明其關聯性(直接畫於圖上即可)新增資料庫create database MyDatabaseUse MydatabaseGo新增資料表Create table t1(id ntext not null primary key,name ntext not null);資料表中插入資料insert into t1values(a0002, john)修改資料表alter table t1add address ntext刪除資料表drop table t2練習題1. 新增一個資料庫名為test。create database test2. 在test資料庫中建立一個資料表名為test1，其中有兩個欄位分別為t_id, t-name, 資料型態為int, varchar(10)。create table test1(t_id int, t_name varchar(10)3. 在test1中新增一筆資料: (1, aaa)。insert test1values(1, aaa)Name rule4. 建立一規則名為id_rule，其條件為ID開頭不可為AB，例如AB001。並將id_rule聯繫到Myt1資料表中的t_id欄位。create rule tname_ruleas tname not like ab%exec sp_bindrule tname_rule, t1.tidinsert t1values(5, aaa, 222222)匯入資料1. 新增Foodmart2000資料庫2. 點選Foodmart2000資料庫-右鍵-所有工作-匯入資料-下一步3. D:Program FilesMicrosoft Analysis ServicesSamplesfoodmart 2000.mdb 4. 5. 6. 全選-下一步-完成1. 執行-新增資料庫-名稱為northwind12. 按住northwind-右鍵-所有工作-匯入-下一步-選擇資料來源-資料庫(Northwind)-目的(Northwind1)-全選所有資料表-完成3. 打開Query Analyzer-輸入:Use northwind1Go即可在northwind1資料庫中開始執行查詢(Select)及更新(Update)查詢1. Select 欄位 From 資料表2. Update 資料表 Set 欄位邏輯運算子的優先順序: ( ), */, + -, Not, And, Or1. select orderid from orders where orderdate #08-aug-94#2. select * from employeeswhere firstname like m%3. update ordersset employeeid = 2 where orderid = 102484. update shippersset phone = (503) 555-3199where shipperid = 45. update ordersset freight = (freight-1) * 1.15where shipvia = speedy express6. select productid, unitprice * unitsonorder as 庫存金額from productswhere unitprice * unitsonorder reorderlevel *207. select employeeid, regionfrom employeeswhere region is nulland country is not null8. select distinct shipvia from orders9. if(select count(tid) from t1) 3print more than 3else print less than 3練習題1. 更新訂貨資料表, 將運送國家為Mexico 的運費通通乘以1.5倍update ordersset freight = freight * 1.5 where shipcountry = mexico2. 在訂貨資料表中找出有幾種不同的運費(freight) select count(distinct(freight) from orders3. 在員工資料表中找出first name中有n, 但不在開頭與結尾的員工id與姓名select * from employeeswhere firstname like %n%and firstname not like n%and firstname not like %n4. 在訂貨資料表中找出平均運費與總運費, 並將查詢出來之欄位改名為平均運費與總運費 select avg(freight) as 平均運費, sum(freight) as 總運費 from orders5. 在訂貨資料表中找出shipvia = 1或2, 而且運費20的order id select * from orderswhere (shipvia = 1 or shipvia = 2) and freight 206. 更新訂貨資料表, 將運送地區為空值的欄位通通輸入999 update ordersset shipregion = 999where shipregion is null7. 在訂貨資料表中找出運費打9折後, 小數點取2位的金額, 並將查詢出來之欄位改名為新運費select round(freight * 0.9, 2) as 新運費 from orders函數1. SELECT Right(ShipName,3) FROM Orders;2. SELECT Left(ShipName,3) FROM Orders;3 SELECT DATEPART(Q, requireddate) FROM orders;4. SELECT len(ShipName) FROM Orders;5. select max(freight) from orders6. SELECT DATEPART(YYYY, requireddate) FROM orders7. SELECT DATEPART(m, requireddate) FROM orders8. select cast(A + customerid as varchar(6) as New_customerid, customeridfrom orders9. select cast(freight * 0.9 as numeric(5, 2) as new_freightfrom orders10. select cast(freight * 9.9 as float) as new_freightfrom orders11.select count(orderid) from orderswhere shippeddate is null2). 排序(order by)與分組(group by)1. select freight from orders order by freight 2. select freight from orders order by freight desc3. SELECT country, count(city) as city_number from employees group by country 4. SELECT country, count(city) as city_number from employees group by country having count(city) 45. select count(orderid), datepart(yyyy, orderdate) as yearfrom ordersgroup by datepart(yyyy, orderdate)練習題1. 在orders 資料表中按照shipcountry分組, 並查詢每一組的平均運費, 並按照平均運費降冪排序select avg(freight) as avg_freight, shipcountry from orders group by shipcountryorder by avg(freight) desc2. 在客戶資料表中, 找出有哪些城市有至少有3個客戶 SELECT city, count(customerid) from customers group by city having count(customerid) 33. 在orders 資料表中按照shippeddate的月份分組並排序, 條件為該月份的運送次數超過60次 SELECT count(shippeddate), DATEPART(m, shippeddate) FROM ordersgroup by DATEPART(m, shippeddate)having count(shippeddate) 60order by DATEPART(m, shippeddate)4. 在 產品資料表中找出每一個供應商供應了幾種產品 select count(productid), supplieridfrom productsgroup by supplierid5. 在orders 資料表中按照Orderdate分組(已季為單位)並找出每一季的運費總額 select sum(freight) as totalfreight, datepart(q, orderdate) as quarterfrom ordersgroup by datepart(q, orderdate)6. 在orders 資料表中先按shippeddate 分組, 再按orderdate分組(以年為單位)並找出每一年中有幾筆訂貨 select count(orderid), datepart(yyyy, shippeddate) as shipyear, datepart(yyyy, orderdate) as orderyearfrom ordersgroup by datepart(yyyy, shippeddate), datepart(yyyy, orderdate)With cube & With rollupselect count(orderid), datepart(yyyy, orderdate) as orderyear, datepart(yyyy, shippeddate) as shipyearfrom ordersgroup by datepart(yyyy, shippeddate), datepart(yyyy, orderdate) with cubeselect count(orderid), datepart(yyyy, orderdate) as orderyear, datepart(yyyy, shippeddate) as shipyearfrom ordersgroup by datepart(yyyy, shippeddate), datepart(yyyy, orderdate) with rollup練習題SuppliersS_IDS_nameS_rankteladdressdiscountJoin-DateAB23455446Super Co.,A3A3B(02)98765432台北市大華街45號0.952000-1-1DC33668423Supreme BV741(03)5463211桃園市美美路23號0.952000-5-5AZ12219423OfferYouBV741(07)6547892高雄市九如路87號0.92002-1-1XT88763487TMCRA3A3B(07)4521786高雄市九如路1號0.82001-6-301. 按照s_rank分組, 並列出有哪些s_rank中至少有5個suppliers select count(s_id), s_rankfrom Suppliersgroup by S_rankhaving count(s_id) 52. 按照s_rank分組, 並查詢每一組的平均discount, 並按照平均discount降冪排序select avg(discount) , s_rank from suppliers group by s_rankorder by avg(discount) desc3. 按照JoinDate(加入日期)分組(以年為單位), 並找出每一年的供應商加入數目 select count(s_id), datepart(yyyy, Joindate)from suppliersgroup by datepart(yyyy, joindate)4. 更新資料表, 將address在台北市的discount通通改為0.955. 更新資料表, 將address為空值的欄位通通輸入9996. 公司舉辦雙十回饋活動, 凡供應商S_id中恰有2個10者(不論相連與否), 皆可獲贈價值1000之禮品, 請查詢哪些公司可獲贈禮品7. 在資料表中將S_rank開頭為A的供應商的discount再打9折, 小數點取2位, 並將查詢出來之欄位改名為new_discount8. 在資料表中找出S_rank有A或B的S_id與S_name, 但不能同時有A或B9. 在資料表中找出S_rank = A3A3B或BV741, 而且discount = 0.8的s_id10. 在資料表中找出有幾種不同的discount, 亦即查詢出來的結果需為一個數字11. 查詢JoinDate 比2000-1-1晚的S_idCASE在employees資料表中查詢title這個欄位，但需將欄位名稱改為中文也就是稱號。還有欄位中的Mr.改為先生，Ms.改為小姐。select lastname as 姓, firstname as 名 , case titleofcourtesywhen Ms. then 小姐when Dr. then 博士when Mrs. then 太太when Mr. then 先生end as 稱號from employeesselect pub_id, case countrywhen USA then 美國when France then 法國when Germany then 德國end as 國家from publishers變數宣告1.declare string char(50)set string = this is the table of ordersselect string as str, *from orders2.declare today datetimeset today = getdate()select today as 現在時間, *from orders3. declare counter intset counter = 1select counter as c, *from orders檢視表(View)1. 建立檢視表(View)的好處 a. 資料保密 b. 使用者親和2. 建立檢視表(View)的語法 Create view view_name as Select 例如: create view view_orders as select * from orders3. 如下畫面, 在Query Analyzer建立完檢視表後, 可至Enterprise manager 中查看次查詢1. select city from suppliers where city in (select city from employees)2. select orderid, freight from orders where freight = (select min(freight) from orders)3. select freight from orders where freight all (select unitprice from products)4. select freight from orders where freight any ( select unitprice from products)5. select unitprice from productswhere exists (select freight from orders where unitprice freight)習題1. 用次查詢找出employees, suppliers資料表中有哪些country 是相同的, 資料不可重複 2. 用次查詢找出order details 資料表中unitprice 平均值的資料3. 用次查詢找出order details 資料表中有哪些數量(quantity)比products資料表中所有的訂單數量(unitsonorder)還要大的4. 用次查詢找出order details 資料表中有哪些數量(quantity)比products資料表中任何一個訂單數量(unitsonorder)大的聯合資料表查詢A. Join的用法1. select count(orderid) from orders 830select count(orderid) from order details 2155select count(supplierid) from suppliers 91select count(customerid) from customers 29select count(productid) from products 171select count(productid) from order details 21552. select o.orderid, od.orderidfrom orders o, order details od 1788650print 830 * 2155 17886503. select o.orderid, od.orderidfrom orders o, order details odwhere o.orderid = od.orderid 21554. select o.orderid, od.orderidfrom orders o join order details odon o.orderid = od.orderid 21555. select orderid from orderswhere orderid in (select orderid from order details) 8306. select orderid fromorder detailswhere orderid in (select orderid from orders ) 21557. select distinct orderid from order detailswhere orderid in (select orderid from orders ) 830B. Join(水平合併)的種類8. select count(distinct city) from suppliers 29select count(supplierid) from suppliers 29select count(distinct city) from employees 5select count(employeeid) from employees 99. select s.city, e.cityfrom suppliers s join employees eon s.city = e.city10. select s.city, e.cityfrom suppliers s left join employees eon s.city = e.city11. select s.city, e.cityfrom suppliers s right join employees eon s.city = e.city12. select s.city, e.cityfrom suppliers s full join employees eon s.city = e.city13. select * from customers as c join orders as oon c.customerid = o.customeridC. Union(垂直合併)1. SELECT City, CompanyName, ContactName FROM CustomersUNION SELECT City, CompanyName, ContactNameFROM SuppliersORDER BY City, CompanyName; 120相同欄位可union2. select contactname as 名單from customersunion select cast(firstname + lastname as varchar(20) as 名單from employees不同欄位但資料型態相同即可union3. SELECT CityFROM CustomersUNION SELECT CityFROM Suppliers 93無重複值4. SELECT CityFROM CustomersUNION all SELECT CityFROM Suppliers 120有重複值練習題: 1. 如果現在新年快到了, 想要對所有的人(包括:員工, 客戶, 供應商等)寄賀卡, 則如何將northwind中所有的人名匯總起來且不重複, 並將它建為一個檢視表? select contactname as 賀卡名單from customersunion select cast(firstname + lastname as varchar(20) as 賀卡名單from employeesunion select contactname as 賀卡名單from suppliers2. 想了解客戶與訂單之間的關係, 則應如何作水平合併?select *from customers as c join orders as oon c.customerid = o.customerid3. 想了解哪些城市裡面同時有客戶與供應商, 而哪些城市只有客戶與供應商, 或只有供應商沒有客戶select c.city, s.cityfrom customers c full join suppliers son c.city = s.city在northwind資料庫中想了解哪些城市裡面只有供應商沒有客戶select c.city, s.cityfrom customers c right join suppliers son c.city = s.city在northwind資料庫中想了解哪些城市裡面同時有客戶與供應商, 而哪些城市只有客戶與供應商, 或只有供應商沒有客戶select c.city, s.cityfrom customers c full join suppliers son c.city = s.city4. 想了解供應商,產品,訂單明細之間的關係, 則應如何作水平合併?(用where)select *from suppliers s, products p, order details odwhere s.supplierid = p.supplierid and ductid = ductid5. 建立檢視表以了解每一客戶總共買了多少金額的產品, 又檢視表上面的客戶總數與客戶資料表中的客戶總數是否相同? 如有不同, 為什麼?create view view_customer_total_expense asselect c.customerid, sum(od.unitprice * od.quantity * (1-od.discount) as totalfrom customers c, orders o, order details odwhere c.customerid = o.customerid and o.orderid = od.orderid group by c.customeridselect distinct customerid from customers 91select customerid from customers 91select distinct customerid from orders 89預存程序(Stored Procedure)1. create proc procshippersas select * from shippersexec procshippers2. create proc proc_e_idn intas select * from employeeswhere employeeid = nexec proc_e_id 13. exec sp_helptrigger orders, updateexec sp_help change_order4. exec sp_helptext change_order5. create procedure mypro2with encryptionas select * from t1exec mypro26. create proc myproc2tid int, tname varchar(40), tphone varchar(10)as insert t1values(tid, tname, tphone)exec myproc2 10, bcd, 333333練習題:1. 建立預存程序, 使得DBA只要輸入country, city的資料(例如UK, London)即可查出裡面所有屬於該country, city的員工資料create proc proc_employees_city_countrycity varchar(20), country varchar(40)as select * from employeeswhere city = city and country = country2.create proc customersalesbyyeary datetime asselect c.customerid, sum(od.unitprice * od.quantity * (1-od.discount) as totalfrom customers c, orders o, order details odwhere c.customerid = o.customerid ando.orderid = od.orderid and datepart(yyyy, o.orderdate) = ygroup by c.customeridexec customersalesbyyear 19963. create proc proc_suppliers_avg_price_by_year_monthsid int, year datetime, month datetime asselect s.supplierid, avg(od.unitprice) as averagefrom suppliers s, products p, order details od, orders owhere s.supplierid = p.supplierid and ductid = ductid and od.orderid = o.orderid and s.supplierid = sid and datepart(yyyy, o.orderdate) = yearand datepart(m, o.orderdate) = monthgroup by s.supplierid在northwind資料庫中建立預存程序使得使用者只需輸入一個產品id,即可了解每一產品的id, name, 供應商的id, companyname, address等5項資料create proc proc_vpspid intasselect ductid, ductname, p.supplierid, panyname, s.addressfrom products p, suppliers swhere p.supplierid = s.supplierid andp.supplierid = pid自訂函數1. create function fn_orders_orderdate(orderdate datetime)returns tableas return ( select * from orderswhere orderdate stock)select * from fn_products_stock(10)習題:1. 建立預存程序， 使得使用者只需輸入兩個日期(日期1, 日期2)，即可在Employees資料表中找到birthdate欄位比日期1早，而且hiredate欄位比日期2晚的所有員工資料。 create proc myproc21date1 datetime, date2 datetimeas select * from employeeswhere birthdate date1and birthdate date22. 同上題，將預存程序改為自訂函數。create function fn_employees_date(date1 datetime, date2 datetime)returns tableas return ( select * from employeeswhere birthdate date2)3. 建立預存程序, 使得使用者只需輸入一個ID號碼, 即可在employees資料表中找到該ID號碼的所有資料。 create proc myproc1n intas select * from employeeswhere orderid = n4. 建立預存程序以及一個自訂函數， 使得使用者只需輸入一個日期，即可在Orders資料表中找到Orderdate 比該日期早的所有資料。create proc myproc9date1 datetimeas select * from orderswhere orderdate date1exec myproc9 1996-12-31create function fn_orders_orderdate(orderdate datetime)returns tableas return ( select * from orderswhere orderdate 20begin rollbackPrint 資料不會改變endupdate productsset unitprice = 20where productid = 2update productsset unitprice = 20where productid = 203. create trigger changeunitprice3on productsafter updateas if(select sum(unitprice) from deleted) 2000begin rollbackraiserror( 資料不會改變, 16, 1)end4. 先建立新資料表-shippers1, 裡面欄位皆與shippers相同create table shippers1(shipperid int not null,companyname nvarchar(40) not null,phone nvarchar(40) not null)再建立trigger如下create trigger update_shippers_phoneon shippersafter updateasif update(phone)begininsert shippers1select shipperid, companyname, phonefrom deletedif rowcount = 0print no changeend更新shippers資料表如下後, 再到shippers, shippers1看看有什麼不同update shippersset ph