دعوة للإنضمام لأسرتنا
عزيزي الزائر الكريم .. زيارتك لنا أسعدتنا كثيراً .. و لكن لن تكتمل سعادتنا إلا بانضمامك لأسرتنا .. لذا نرجوا منك ملئ النموذج التالي من فضلك

اسم العضو
كلمة السر تأكيد كلمة السر
البريد الإلكتروني تأكيد البريد الإلكتروني



هل انت موافق على قوانين المنتدى؟


دروس تعليمية منقولة من المواقع الغربية هنا يتم نقل الدروس التعليمية من منتديات ومواقع غربية ، فى كل المجالات التى تفيد الجغرافيا

إضافة رد
 
LinkBack أدوات الموضوع إبحث في الموضوع انواع عرض الموضوع
قديم 16-10-2012, 07:13 AM   المشاركة رقم: 1
المعلومات
الكاتب:
اللقب:

البيانات
التسجيل: Dec 2006
العضوية: 19
المشاركات: 278 [+]
بمعدل : 0.06 يوميا
اخر زياره : [+]
معدل التقييم: 2377
نقاط التقييم: 10
آداب الزقازيق is on a distinguished road

التوقيت

الإتصالات
الحالة:
آداب الزقازيق غير متواجد حالياً

المنتدى : دروس تعليمية منقولة من المواقع الغربية
افتراضي To assess your understanding of relational database design and development

Database Design: Assessed practical exercise (40% of course mark)

AIM: To assess your understanding of relational database design and development

For this assessed exercise, you have a choice between two database design and implementation tasks: a climate project or an archaeological sculpture project. These are of equal difficulty, but vary regarding the particular challenges you will face. In the case of the climate database, you will gain more practical, vocational experience of database implementation, while the sculpture project is more challenging in design terms and will require considerable background reading.

Project 1: Climate database


In climatological research as in other areas where GIScience is applied, appropriate management of the underlying data is critical both in terms of usability and disk space. Meteorological data files come in multiple formats and at different time steps depending on sensor and network. The purpose of this assignment is to design a normalised database, based on the information provided, that has sufficient flexibility to be used for typical query tasks for Britain (i.e. England, Scotland & Wales only) should the full range of data to be added.


Particular challenges:
· Understanding the network and sensor terminology used for UK climate databases;
· Design of a relational database that potentially involves the use of multiple primary keys;
· Implementation of database design within Access;
· Importation of existing digital files into Access, where many files contain significant errors.

Project 2: Sculpture database

'The corpus of Anglo-Saxon sculpture' is a major archaeological research initiative that is seeking to bring together and compile information upon Saxon and Viking sculpture with the aim of producing a series of highly detailed regional gazetteers. However, at present the data is available only in printed form, and in a simple flat-file format. This not only makes accessing the information held within the volumes difficult, but it also prevents future discoveries (either of new examples or additional information about those already known) being integrated effectively. your task is to design a normalised relational database for one of the existing corpus volumes that has sufficient flexibility to be used for typical query tasks should the full range of data be entered.

Particular challenges:
· Assessing the detailed text of the entries and texts provided, and comprehensively categorising the content;
· Design of a relational database that potentially involves a large number of small tables;
· Implementation of database design within Access;
· The design of forms to assist archaeologists to enter further data into your schema;
· Manual entry of example corpus entries into the Access tables.



Detailed briefs for both alternatives are attached. In either case, copies of your report and CD-Rom are due for submission on Tuesday 14 March. Please note that the CD-Rom is to supplement the marking process only. The written, printed document itself should contain:
· A review of the inherent data structure provided, with particular comment on its conformance with Cod’s normal forms;

· An Entity-Relationship diagram showing the conceptual design of your database;

· Screen prints from your implementation of the prototype database design in Access 2000, to include:

    • The relationship screen;
    • The design view from up to four tables of your choice, that show your ability to manage a variety of data types, primary and secondary keys and validation rules;
    • Design views of the queries required to achieve the example tasks listed for your particular brief, with a separate note of the associated SQL statements behind these;
    • Any forms or maps required by your particular project brief.


Your design should always be completed before you begin to upload or enter any data.

Project 1: Climate database


In climatological research as in other areas where GIScience is applied, appropriate management of the underlying data is critical both in terms of usability and disk space. Meteorological data files come in multiple formats and at different time steps depending on sensor and network. The attached sheets provide you with example structures for files provided over the Internet by a supplier of meteorological data. The purpose of this assignment is to design a normalised database, based on the information provided.



This database should have sufficient flexibility to be used for the following typical queries for Britain (i.e. England, Scotland & Wales only) should the full range of data to be added, and screen prints of these queries in design view should be provided as part of your final report:
  • Compute the monthly average temperature per weather station for 1998;
  • Assess the number of stations recording rainfall for which data is available in near real-time (i.e. automatic messages) on 16 January 1998;
  • Append daily rainfall records on a day-by-day basis;
  • Report minimum temperature on 16 January 1998, by station location and name;
  • Tabulate average daily rainfall for 1998 to 2001 firstly by Lamb-Jenkinson (L-J) weather type (appendix 1) and then by aggregated L-J type according to pressure group (high or anticyclonic, average & low or cyclonic, see Appendix 1).

Additionally, your report for this project should include:
  • Discussion of issues arising when importing the digital radiation data provided into a sub-section of your database;
  • A map (designed using ArcMap) plotting, for Great Britain, the average daily global radiation received per station in the year 1998.

For authenticity in regard to tasks that you might be asked to perform in the workplace or as part of research in the future, this exercise is based on a real data preparation task. The quality (e.g. formatting, completeness, accuracy) of data provided, and the level of explanation regarding these files, is typical of its genre. The volume of data that you are asked to process is however considerably smaller.



Project 1: Associated supporting documentation, information & references

Weather types (Project 1, Appendix 1)
o There are 28 ‘Lamb-Jenkinson’ weather types, which refer to the underlying pressure and direction of weather fronts on a particular day e.g. high pressure system from the north is type 8 (Appendix 2). See the web site http://www.cru.uea.ac.uk/~mikeh/datasets/uk/lamb.htm and Jones et al (1993) for further details if you are interested in the climatology.

Associated file for the year 1998: lamb_jenks.xls

UKMO Meteorological network types (Project 1, Appendix 2)
  • At any one station there may be more than one sensor, recording different weather variables such as rainfall or solar radiation;
  • Different locations may have a different range of sensors and may form part of more than one observation network.
  • The rainfall network (rain messages) is much larger than the synoptic weather network (ncm messages). The synoptic network is primarily used for weather forecasting, requiring data to be updated at the UK Meteorological office on an automatic basis.
  • Note that rainfall can be recorded at locations within the synoptic network as well as using instruments within the ‘rain’ network.


Data files (Project 1, Appendix 3)
Files for solar radiation, rainfall, sunshine hours and temperature are available for processing into the database. In the case of solar radiation, these data are for the complete year 1998; the other files are examples only.
Associated practical files:radt98.txt; temp98.txt; rndy98.txt; rnhr98.txt; wxdl98.txt

Counties & regions within Great Britain (Project 1)

Details of each station are given in the file UKMO_stations.txt (see Appendix 4)
See british_regions.xls for a list of counties in England, Scotland & Wales

These data files are available for download from the GY7025 assessment area on Blackboard

Appendix 1: Lamb-Jenkinson daily weather classification codes



0
anticyclonic
............
20
cyclonic
1
ANE
11
NE
21
CNE
2
AE
12
E
22
CE
3
ASE
13
SE
23
CSE
4
AS
14
S
24
CS
5
ASW
15
SW
25
CSW
6
AW
16
W
26
CW
7
ANW
17
NW
27
CNW
8
AN
18
N
28
CN

Extra codes:

-1 unclassifiable
-9 non-existant day (eg 31 February!)


Associated file for the year 1998: lamb_jenks.xls


Jones,P.D., Hulme,M. and Briffa,K.R. 1993: A comparison of Lamb circulation types with an objective classification derived from grid-point mean-sea-level pressure data Int. J. Climatol., 13, 655-664

Appendix 2: UKMO weather recording networks

Information regarding a particular meteorological record is identified using the station reference number, climate network type and message type. The station reference number is not necessarily unique on its own; uniqueness only applies with station reference number together with network type.

Network types

Network typeDescriptionDCNNClimate network ICAOLPMSRAINRain gauge networkSHIPOffshore networkWINDWind networkWMOWorld Meteorological Association network

These concepts of ‘network’ types and ‘message’ types (see over) can be confusing. Imagine that you are setting up some recording stations in Victoria Park for a research project; you have available a mixture of equipment, some old and some new. Some of the new equipment records data and sends it back directly to your PC; the older rain gauges you have to read directly and then enter the data manually. Additionally, you are being given data for the research period from two rain gauges that are sited in the park but that belong to De Montfort University.

In network and message type terms, the gauges that belong to De Montfort belong to a different ‘network’ to yours, but there was nothing to stop you from placing your equipment directly next to theirs i.e. records from the same location may belong to two different networks. Indeed, it is normal for several pieces of equipment to be located at one meteorological station. Commonly, a ‘network’ might be set up for one purpose, such as recording rainfall (e.g. RAIN, below) but may equally accept data of many types (rainfall, temperature etc) and the DCNN ‘network’ overleaf is an example of this. your automatic temperature gauges may belong to the same network (i.e. Leicester’s network in the VP scenario) as your old manual temperature ones, but are of a different ‘message’ type. Moreover, you might place a rainfall gauge that is emptied hourly next to one that is emptied daily, since small errors on the hourly readings add up fast and one daily reading is the more accurate measure at that scale; two records of different ‘message’ type at one location. Both ‘network’ and ‘message’ type are therefore needed to identify a particular recording instrument uniquely. I suggest that you draw different sets of automatic and non-automatic recording instruments for rainfall and temperature across Victoria Park, with rings around the networks and different symbolism for automatic and non-automatic sensors, to help disentangle these issues.

Automatic or instant messages are coded as ‘1’ within the data files, manual as ‘0’. Manual messages may take as much as 3 months to be fully quality checked and entered within a climatological database by a national agency. The table below contains missing data characteristics; this is normal.


Message types

Met Domain nameAutomation levelCLM71-06
CLM71-11
CLMSN
CLMSPN
CLM-UAN
CLNSN
CLNSPN
DLY3208
0
DLY3259
0
DRADR35
ESAWRADT
1
ESAWSOIL
1
ESAWWIND
1
HSUN3445
HWND6910
0
HWNDAUTO
1
METAR
1
MODLERAD
NCM
1
NCN
OWS-GTS
SREW
1
SSER
1
SSERADJ
1
SYNOP
1
UA32PLT
UA33PLTS
UA35TMP
UA36TMPS
WADRAIN
0
WAHRAIN
0
WAMRAIN
0




Appendix 3: Meteorological data files

File Descriptions

Parameter ID (& file name)
Description of contents
RADT
Global Solar Radiation, Diffuse Solar Radiation
WXDL
Sunshine Hours (Hrs)
TEMP
Maximum and minimum temperature (degrees)
RNHR
Summated hourly rainfall totals (mm)
RNDY
Daily rainfall total (mm)



Column Descriptions

Column
Value
1
Source ID – unique Met Office identifier for each recording instrument
2
Date & Time - to nearest minute in form YYYYMMDDHHMM
3
Period – number of hours over which value is derived
4
Count – number of readings over which record is derived
5
Parameter ID – unique weather parameter identifier
6
Met Domain Name – observation type identifier (message type)
7
Secondary Source id type – secondary source type (network type)
8
Secondary Source ID –source ID (WMO number etc) (network number, unique within network only)
9
Value – parameter value

1. The source ID (column 1) is an internal Met Office identifier for each individual reporting station. Unfortunately, it bears no resemblance to DCNN or WMO numbers (‘network’ numbers, column 8).
2. The timestamp gives the hour at which, or at the hour up until, the observed value relates. For temperature this would be an instantaneous value at that time whilst for rainfall it is a summation to that time.
3. The period is the number of hours over which an observation is made – this is useful for variables such as total rainfall, total radiation hours and so on.
4. The domain name provides some guidance as to how the observation was reported, for example a value of WADRAIN would indicate that the observation is from the Environment Agency'’ rainfall gauge network.
5. Along with the unique Met Office identifier, another station identifier may be provided. This may be a UK DCNN or a WMO number. This column tells you the type of number that is given in the following column
6. The value of the secondary source, this may be the numerical value of the WMO, DCNN number and so on. Note that a DCNN number may be the same as a WMO number – they are not unique.

Appendix 4: Station information file format



Column Descriptions

Column
Value
1
Secondary Source (Appendix 3 Column 7 – networktype)
2
Secondary Source ID (network number, Appendix 3 column 8)
3
Station name
4
Met Domain Name – observation type identifier (message type, Appendix 3 column 6)
5
County (if UK – else country)
6
Latitude
7
Longitude
8
Further ID number (ignore)
9
National projection system identifier (Note: OS = British)
10
Easting
11
Northing
12
Station opening day
13
Station opening month
14
Station opening year
15
Station closing day
16
Station closing month
17
Station closing year (9999 implies that the station is still open)
18
Postcode


Please note that theunique reference in Appendix 3 Column 1 is NOT listed in this table

Project 2: Sculpture database

'Corpus of Anglo-Saxon sculpture' is a major archaeological research initiative that is seeking to bring together and compile information upon Saxon and Viking sculpture with the aim of producing a series of highly detailed regional gazetteers. However, at present the data is available only in printed form, and in a simple flat-file format. This not only makes accessing the information held within the volumes difficult, but it also prevents future discoveries (either of new examples or additional information about those already known) being integrated effectively. your task is to design a normalised relational database that is suitable for containing the entries within Corpus Volume II (Cumberland & Westmorland), and to enter the entries for the letter of the alphabet to which you are assigned.

Your database should be developed with sufficient flexibility to be used for the following typical query tasks, should the full range of data be entered in due course.
· Anglo-Scandinavian Crosses appear to be better represented in the survey than Anglo-Saxon examples: is this simply because there are more crosses in the later periods?
· Does the ratio of human versus abstract iconography vary over time and space?
· Does Cross size remain constant through the period?
· How fragmented is the assemblage?
· Select a particular design motif (e.g. spiral scroll) and assess its frequency and distribution

Additionally, your report for this project should include:
  • Discussion of assumptions made and degree of coverage of the data categories.
    • The entries within the corpus are comprehensive and detailed; capturing the full wealth of information is beyond this exercise. Following a brief review of the entries, consider carefully what you will prioritise and why, and how you can design the database in a manner that might safely be extended by others.
  • Screen shots of forms to be used by archaeologists when entering details of new sites/sculptures;
  • A map (designed using ArcMap) plotting, for the area of the Corpus Volume and the entries that you have made, the type of archaeological feature found (e.g. cross-shaft, hogback).


For authenticity in regard to tasks that you might be asked to perform in the workplace or as part of research in the future, this exercise is based on a real data preparation task. Please contact me if you are interested in this project, and I will assign you a letter of the alphabet that will designate the entries that you should make (e.g. A – Entries between Addingham & Aspatria). Many research projects draw on digital GI information but require additional manual entries, and this element of the exercise is intended to assist you in budgeting resources at a later point in your career. Careful prioritisation of tasks within a given time slot that provides a ‘safe’ database yet also allows a return to the subject at a later date is also an important skill to develop. The volume of data that you are asked to process here is however considerably smaller than in real life.

References

British Academy (1984) Corpus of Anglo-Saxon stone sculpture, Volume II, Cumberland, Westmorland and Lancashire North-of-the-Sands, Oxford University Press: Oxford.



To assess your understanding of relational database design and development database design development relational understanding












عرض البوم صور آداب الزقازيق   رد مع اقتباس Share with Facebook
قديم 16-10-2012, 06:30 PM   المشاركة رقم: 2
المعلومات
الكاتب:
اللقب:
 
الصورة الرمزية مصر مصر

البيانات
التسجيل: Sep 2010
العضوية: 39886
العمر: 35
المشاركات: 1,970 [+]
بمعدل : 0.59 يوميا
اخر زياره : [+]
معدل التقييم: 1862
نقاط التقييم: 88
مصر مصر will become famous soon enough

التوقيت

الإتصالات
الحالة:
مصر مصر غير متواجد حالياً

كاتب الموضوع : آداب الزقازيق المنتدى : دروس تعليمية منقولة من المواقع الغربية
افتراضي رد: To assess your understanding of relational database design and development

السلام عليكم ورحمة الله وبركاته
كلام جميل جدا ولكننا نريد شئ موثق حتي نستفيد منه بشكل اقوي
بمعني ان ترفع الملف في المشاركة












عرض البوم صور مصر مصر   رد مع اقتباس Share with Facebook
إضافة رد

مواقع النشر (المفضلة)

الكلمات الدلالية (Tags)
assess, database, design, development, relational, understanding, your

أدوات الموضوع إبحث في الموضوع
إبحث في الموضوع:

البحث المتقدم
انواع عرض الموضوع

تعليمات المشاركة
لا تستطيع إضافة مواضيع جديدة
لا تستطيع الرد على المواضيع
لا تستطيع إرفاق ملفات
لا تستطيع تعديل مشاركاتك

BB code is متاحة
كود [IMG] متاحة
كود HTML معطلة
Trackbacks are متاحة
Pingbacks are متاحة
Refbacks are متاحة



الساعة الآن 02:07 PM بتوقيت مصر

 


Powered by vBulletin® Version 3.8.9 Beta 1
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Search Engine Optimization by vBSEO
الحقوق محفوظة لمنتدى للجغرافيين العرب

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105