2016年春-數(shù)據(jù)庫原理與實用技術(shù)實驗.期末大作業(yè)-張云春

1、 數(shù)據(jù)庫原理與實用技術(shù)實驗2016 年期末大作業(yè)項目任課教師：張云春、劉宇GOAL：The goal of this project is to provide a realistic experience in the conceptualdesign, logical design, implementation, operation, and maintenance of a relationaldatabase and associated applications.First, I shall describe the application, then the categories

2、 of requirements, andthen some suggestions on how deeply you need to go in each category. A real projectof this sort would require a substantial development team working for several months(or more). You will do this alone over several weeks. I have chosen to go withindividual rather than group proje

3、cts because the goal of this project is for you togain a personal appreciation of the depth and breadth of issues that go into the designof a database application, rather than to have you specialize in just one aspect (andrely on others for the rest).The project can go well beyond the minimal requir

4、ements I outline at the end. Iencourage such extensions. They could turn into a senior design project or otherindependent work.要求：1. 需要每位同學熟練掌握數(shù)據(jù)庫的邏輯設(shè)計、物理設(shè)計、數(shù)據(jù)庫的常規(guī)使用，因為數(shù)據(jù)庫是計算機學科中的基礎(chǔ)學科，要求每位同學都熟練掌握數(shù)據(jù)庫設(shè)計的各種方法和技術(shù)，因此，期末大作業(yè)需要每位同學單獨完成。2. 撰寫實驗報告期末大作業(yè)：數(shù)據(jù)庫設(shè)計，給出整個設(shè)計的詳細流程；按照課程上介紹的相關(guān)步驟，給出每個步驟的大概內(nèi)容，包括實體、弱實體、屬性、轉(zhuǎn)換

5、規(guī)則的使用、數(shù)據(jù)庫中的創(chuàng)建語句等。具體的內(nèi)容可以參考大作1 業(yè)項目報告中的詳細要求。3. 數(shù)據(jù)庫中的數(shù)據(jù)表不得少于 5 張，否則認為設(shè)計失敗。4. 對每張數(shù)據(jù)表輸入不少于 20 條的樣本數(shù)據(jù)用于測試，樣本數(shù)據(jù)要有代表性。5. 數(shù)據(jù)庫開發(fā)環(huán)境選用 SQL Server，版本不限。6. 大作業(yè)報告中，必須給出 E-R 圖（使用專業(yè)工具繪制），以及實體和聯(lián)系的基數(shù)（cardinality）；實體與屬性的基數(shù)可以不寫，但是在分析時必須明確哪些屬性不能取 NULL 值；并給出從 E-R 圖到關(guān)系表格的轉(zhuǎn)換過程，以及每個轉(zhuǎn)換所對應的轉(zhuǎn)換規(guī)則（6 條轉(zhuǎn)換規(guī)則）。7. 最后，附上相關(guān)的設(shè)計總結(jié)與心得體會。項目

6、相關(guān)內(nèi)容說明Package Delivery Company DatabaseRequirements:(1) Application descriptionThe application is designed to manage package delivery company (similar to順豐, 申通, 圓通, 中通, EMS 等). The company needs to keep track ofpackages shipped and their customers. To find out more about this application,think about

7、 any experiences you may have had shipping packages and receivingpackages, and browse shippersweb sites.In our hypothetical company, the manager assigned to solicit data designproposals is not very computer literate and is unable to provide a very detailedspecification.Here are a few points to consi

8、der: There are different kinds of service possibly based upon the type ofpackage (flat envelop, small box, larger boxes, etc.), the weight of thepackage, and the timeliness of delivery (overnight, second day, orlonger). Some customers have a contract with the shipper and bill theirshipments to an ac

9、count number. They are billed monthly. Othercustomers are infrequent customers and pay with a credit card. Certain2 shipments are prepaid, as is might be the case of someone is returningsomething that was purchased by phone or Internet (e.g. returningclothes that dont fit, or returning malfunctionin

10、g electronics). For the most part, the shipping company does not care what is beingshipped. However, there are cases where it matters. Some examplesinclude:- hazardous materials- international shipments, for which a customs declaration statingthe contents and their value is needed The company needs

11、to track packagefsrom the time the customer dropsit off (or it is picked up by the company) until the time it is deliveredand signed for. Take a look at the online tracking offered by variousshipping companies to get an idea of how this service works. If you arehaving something shipped to you, youll

12、 find you can get every littledetail of where the package is, where it has been, and to where it iscurrently headed. Beyond what the customer sees, the company itselfneeds to know on which truck or plane or warehouse the package is atany point in time. Tracking is not just an “in the present” issue.

13、 The company may want tolook back in time and find out where the package was yesterday, forexample. It may also want to look at data from the standpoint of a truckor warehouse. There are other aspects to the operation of the company besides packagetracking such as routing of trucks and planes, the a

14、ssignment of staff tothem, etc. For this assignment, well consider only the package handlingand billing aspects of the database.仔細閱讀上面的說明，這些說明包含了用戶的需求，但不是全部的需求。基于如上的分析，要求每位同學給出完整的需求分析，并撰寫“需求分析”文檔。上面的需求有的可以推導出實體、屬性等；有的則可以作為功能語句3 （如觸發(fā)器、存儲過程、索引等）來實現(xiàn)，需要在分析時進行說明。(2) Data Generation:For simplicity, the de

15、sign does not require perfectly realistic data so that youdont have to ensure that. However, you should strive for a good degree ofrealism in your data.Note the comments on collaboration below and that sharing data with othersis acceptable as long as appropriate credit is given to your source.The fo

16、llowing outlines may help you with your design:a) E-R Model Construct an E-R diagram representing the conceptual design of thedatabase. At minimum you must include all the entity and relationship sets impliedby this handout. You may go beyond the minimum. Remember that themanager who defined the spe

17、cifications is not computer literate so thespecifications should not be viewed as necessarily being precise andcomplete. Be sure to identify primary keys, relationship cardinalities, etc.b) Relational Mode After creating an initial relational design from your E-R design, refine itbased on the princi

18、ples of relational design (Chapter 6). Create the relations (Tables)in SQL Server database. Create indices and constraints as appropriate. If as you refine your design, you discover flaws in the E-R design, goback and change it. Your final E-R design must be consistent with yourrelational design.c)

19、Populate Relations Include enough data to make answers to your queries interesting andnontrivial for test purposes. At least 20 rows are supposed to be inserted in each table. (Optional) You may find it helpful to write a program to generate test4 data or collect data from a real package delivery co

20、mpany.d) Queries: You should run a number of test queries to see that you have loadedyour database in the way you intended. The queries listed below are thosethat your clients (the managers from the package delivery company) mayfind of interest. They may provide further hints about database design,

21、sothink about them at the outset of your work on this project. Assume truck 9527 is destroyed in a crash. Find all customers who had apackage on that truck at the time of the crash. Find all recipients whohad a package on that truck at the time of the crash. Find the lastsuccessful delivery by that

22、truck prior to the crash. Find the customer who has shipped the most packages in the past year. Find the customer who has spent the most money on shipping in the pastyear. Find the city with the most customers. Find those packages that were not delivered within the promised time. Generate the bill f

23、or each customer for the past month. Consider creatingseveral types of bills.- A simple bill: with customer, address, and amount owed.- A bill listing charges by type of service.- An itemize billing listing each individual shipment and thecharges for it.Customers like their bills to be readable. Whi

24、le the client will accept arelational table coming from SQL Server as the bill, it would be “nice”to have a good-looking bill.e) Interfaces (OPTIONAL ): There are several types of users who access thedatabase, and several applications that run on their own. The database administrator (you) may use SQL either via the commandline or SQL Developer. Customer service needs a lookup application through menus to check thestatus of his/her packages.5 Online customers (whether by mobile phone