数据库sql

service QQ 928900200

 

 

 

Monash University,    Faculty of Information Technology
FIT1004 (Data Management)   Assignment 2
2nd Semester 2014
 
 
Cities Data Model
 
 
We will  now  proceed  to  develop  a  database model  to maintain  city  data. The  database model
must  satisfy  the  requirements  stated  in  the  following  description. Where  the  data  type  of  an
attribute is not specified, you may make assumptions about the type and size.
 
For each city, you might have recorded the city’s name, latitude, longitude, elevation in metres,
GMT  standard  time  zone,  land  area  in  square kms  (km2), population  count  and  a number of
cultural  features.   We wish  to  include  the  source  and  census  date  for  your  population  figure
only.
 
You should also have recorded the country in which the city is located and the region of that
country.
 
Countries are  identified by  the  ISO3166-1 2-character country codes.   We wish  to  record  this
code and the name of the country, its land area in square kms, the currency used by the country
and  the  capital  city  of  the  country.    Currency  codes  (ISO4217  Currency  Char  3  code)  are
generally based on the ISO3166-1 country codes with an extra character added - e.g., for South
Africa  the  ISO3166-1  code  is  ZA,  the  currency  code  for  the  rand  is  ZAR.    Some  codes  are
based on  larger zones, which share a currency.   As an example of  this,  the currency  in France
(FR)  -  a member  of  the  Euro  zone  -  is  the  Euro  (EUR).    For  this model, we  need  only  the
currency code of the country - the actual currency name is not required.
 
Regions,  for our purposes, will be  taken  to describe primary  sub-divisions of a country  (state,
province,  county,  etc).    Worldwide  regions  are  coded  using  ISO3166-2  -  and  these  codes
consist  of  the  ISO3166-1  2-character  country  codes,  a  hyphen  separator,  followed  by  up  to  3
characters.    For example, Victoria (the Australian state of Victoria) has an  ISO3166-2 code of
AU-VIC.    For each region, we also wish to record the ISO3166-2 region name.
 
Each  country  might  have  several  spoken  languages.    We  will  code  languages  using  the
ISO639-1  language  codes  -  for  example,  in Malaysia  the  spoken  languages  include Malay
(MS),  English  (EN)  and  Chinese  (ZH).    For  each  country we wish  to  record  the  principal
languages spoken in the country and which language/s is the official language/s of the country.  
Finer language specification is available via ISO639-2 - however, for this task, ISO639-1 will
suffice.    For any given language code, we need to be able to output both the code and its full
language name.
 
There are a number of internet locations where you can lookup ISO codes.
 
 
 
 
For each city, you might have recorded several cultural features, or tourist attractions.    For each
attraction, you might have  recorded  the name, address, phone  (if one was available) and a 200 (max)  character  description.  You  also  recorded  a  type  using  the  coding:  MU=Museum,
BG=Botanical Gardens, HS=Historical Site.    For the database model, we wish to extend this so
that "extra" categories can be added.
 
As well  as  recording  the  above  data,  your model must  also  record  details  of  airline  flights,
which  fly  between  cities.    Airlines  are  identified  by  a  2  character  IATA  code
(http://www.iata.org/ps/publications/pages/code-search.aspx) and have an airline name.   Each
airline arranges a  series of  routes, which  fly between cities. Routes are  identified, within  the
airline, by using  the airline  IATA code and assigning a  route number.    For example, Qantas
has an IATA code of QF, a  typical Qantas route could be QF431.   A route may have several
legs  in  which  a  flight  lands  and  departs  in  the  passage  between  its  route  origin  and  route
destination.    For this design task we are not interested in, and will not record, leg details. Your
model is required to record the route origin (departure) city and route destination (arrival) city.
Each route is flown multiple times, sometimes on the same day, as a flight.
 
A display reporting the status of flights has the general form:
 
Qantas (QF) Flight Status Report
 
Report Date/Time: 19:00 21/08/2011
 
Flight No  From  To  Scheduled Status  Actual Status
QF431  Sydney,  Melbourne,  Departure: 12:00 14/08/2014  Departure: 12:34 14/08/2014
  Australia  Australia  Arrival: 13:35 14/08/2014  Arrival: 14:03 14/08/2014
QF2809  Singapore,  Jakarta,  Departure: 18:50 21/08/2014  Departure: 18:55 21/08/2014
  Singapore  Indonesia  Arrival: 19:30 21/08/2014  Arrival:
QF431  Sydney,  Melbourne,  Departure: 12:00 22/09/2014  Departure:
  Australia  Australia  Arrival: 13:35 22/09/2014  Arrival:
QF10  London,  Melbourne,  Departure: 21:30 22/09/2014  Departure:
  UK  Australia  Arrival: 06:00 24/09/2014  Arrival:
 
The flight data represented in this report (scheduled vs actual) should be included in your
database model.
 
In preparing your database model, you should keep in mind that we will be implementing this
model in Oracle, where the DATE data type includes both date and time.
 
 
The Tasks
 
This assignment is an individual task.
 
1.  From  the  data  in  this  case  study  prepare  a  preliminary  conceptual  model
(Entity-Relationship  Diagram,  ERD).  This  ERD  must  show  all  entities,  primary  keys  (non
primary  key  attributes  are  not  required)  and  all  relationships.  The  full  connectivity  and
cardinality must  be  shown  for  each  relationship. This  diagram  is  better  to  be  drawn with  any
suitable computer software.    [ 2A : hurdle;    2B : 25 marks]  
 
2.    To help inform your design, based on your conceptual model from task 1, normalise the
data shown in Flight Status Report above. Your submitted normalisation must show all stages  -
i.e., UNF  (0NF)  to 1NF  to 2NF  to 3NF.   As part of  this normalisation, at each  stage, you are
required  to  show  all  functional  dependencies  (see  Fig  6.3  -  6.5  Coronel  and Morris  11th
Edition    or    Fig  6.3  -  6.5  Coronel, Morris  and  Rob  9th  Edition    or    Fig  5.3  -  5.5  Rob  and Coronel 8th Edition, or the relevant lecture OHS).    [15 marks]  
 
3.    From your task 1 and 2 answers prepare a  logical model -  this diagram must  include all
attributes,  with  primary  and  foreign  keys  noted.   All  entities must  be  in  at  least  3NF  –  you
should  check  that  your  entities  are  in  at  least  3NF,  however  this  check,  is  not  required  to  be
submitted  as  part  of  your  assignment  submission. All  relationships must  be  shown  including
update and delete rules being correctly assigned for each relationship. In preparing your logical
model you are required to use Power*Architect (or Visio).    [50 marks]  
 
4.    From  your  task  3  logical model,  create  an Oracle  schema  file  named  ass2-schema.sql
which  implements your  full design  including any check constraints. You must  run  this schema
file  against  your Oracle  database  account  to  ensure  it  runs  correctly  and  creates  the  required
database structure. The script output from this run of your schema file must be included as part
of  your  submission  in  a  file  called  <authcatename_studentId>_ass2-schema-run.txt  (where  the
authcatename is your name and studentId is your student Id.). To obtain full script output add the
command:  
 
  set echo on  
 
before your first SQL command  in your schema file. Run your script using  the Run Script (F5)
button in SQL Developer.    [10 marks]  
 
 
 
You are free to make any assumptions necessary, however, any assumptions you make must be
specified and justified in your documentation. You do not need to provide a written justification
for  attribute  data  type/size  assumptions  (if  necessary  your marker may  require  you  to  justify
these data type/size assumptions during your week 10 lab/tute discussion/presentation).
 
The assessment will consist of:
 
•  100 marks based on the criteria above, PLUS  
•  be available during  lab/tute  time  for  an  informal  interview with  your  tutor    -   both  for
assignment 2A (hurdle) [during your week 10 lab/tute] and assignment 2B. You must make sure
that your tutor approves on your assignment-2A (during lab/consultation session by ticking your
name) after you submit it.  
 
 
 
 
            
 
 
 
 
 
 
 
 FIT1004 Assignment 2 - Submission Guidelines
 
Please READ  these  instructions  carefully,  there are potentially  several  items which you
will need to submit.
 
Marks:
 
Due Date:
 
Late Penalty:
 
Extensions:
 
Assignment 2 is worth 20%.
More  specifically,  assignment  2A  is  a  hurdle  to  assignment  2B,  and  assignment  2B  is worth
20%.
 
Deadline  for  assignment  2A  (hurdle)  :  Mon  6/Oct/2014  1pm  before  the  discussion  in  your
lab/tute session that week (week 10).   You can submit earlier (e.g., in week 9) to get feedback in
your lab/tute.   For students not in Australia, confirm the deadline at your local campus.
 
Deadline  for  assignment  2B:    Mon  20/Oct/2014  11:55p.m.  Melbourne  time  (see
http://www.timeanddate.com/worldclock/full.html) or (if not  in Australia) as agreed at your
local campus.
 
Any submission after  the due date will  receive a deduction of between 2.5 and 5 marks per
day, this includes weekends.
 
An  extension  will  only  be  considered  with  supporting  documentation  from  a  health
professional or police statement, etc.    If an extension is granted the extension will then equal
the number of days specified on the doctor's certificate, with a maximum limit of one week.
 
 
 
 
 
 
 
 
 
 
Submission mode:
 
This assignment requires an electronic submission. Each student must submit the following:
 
A softcopy (electronic) submission
 
This  submission  must  be  contained  in  a  single  zip  archive  (zip  file)  with  the  filename
"<authcatename_studentId>_ass2.zip"  (e.g.,  nchm1_2546789_ass2.zip),  where  the  authcate
name  is  your  name  and  studentId  is  your  student  Id.  The  archive must  have  the  following
contents:
 
Assignment 2A - (due no later than start of week 10, submit through Moodle)
1.  A MS-Word or pdf document representing your  full submission requirements from Task1
(your preliminary conceptual ERD )    and
 Assignment 2B (due at start of week 12, submit through Moodle and (point #3) Turnitin)
 
1.   Your MS-Word or pdf document representing your full submission requirements from
Task1 (your final conceptual ERD) followed by all your normalisation steps/work from task2.  
 
2.   Your final source Power*Architect ERD file (the .architect file) from task 3, and  
 
3. A  script  file  (filename:  "<authcatename_studentId>_ass2-schema.sql)  containing  the DROP
TABLE  and  CREATE  TABLE  statements  and  all  constraints  needed  to  create  your  tables  in
Oracle.  Note  that  the  <authcatename_studentId>_ass2-schema.sql  script  must  ALSO  be
uploaded to turnitin in text format .txt 
  
4. The script output (filename: "<authcatename_studentId>_ass2-schema-run.txt) from a run of
this  ("<authcatename_studentId>_ass2-schema.sql)  file  against Oracle. The  script  file must  be
able to be executed in SQLDeveloper.  
 
 
The submissions must be made by  the above mentioned deadlines. Make sure  to click on
“Send for marking” to post your assignment submission. Once done, no more changes can
be applied.
Failure to submit all versions will result in at least a 10 mark grade penalty.
 
South  Africa  and Malaysia  based  students  should  confirm  these  requirements
with the local campus lecturer.