`
guoyiqi
  • 浏览: 1001741 次
社区版块
存档分类
最新评论

数据库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. 

 

分享到:
评论

相关推荐

    数据库开发 Oracle数据库 SQL开发教程 全套PPT课件 共20个章节.rar

    数据库开发 Oracle数据库 SQL开发教程 第01章 Oracle数据库基础(共60页).pdf 数据库开发 Oracle数据库 SQL开发教程 第02章 编写简单的查询语句(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第03章 限制...

    Mysql数据库SQL实战

    数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战数据库SQL实战...

    人大金仓数据库SQL手册

    ### 人大金仓数据库SQL手册知识点详述 #### 一、概述 人大金仓数据库SQL手册是一份详尽的文档,旨在帮助用户更好地理解和使用人大金仓数据库管理系统(KingbaseES)中的SQL语言。该手册涵盖了SQL语言的基础知识、...

    SAP-HANA数据库SQL参考手册.pdf(中文版)

    SAP HANA数据库SQL参考手册详细介绍了这些规则和约定,为数据库管理员和开发人员提供了一个全面的指南,帮助他们在SAP HANA平台上有效地执行SQL操作。该手册不仅涵盖了基础知识,如数据类型、操作符和函数,还包括...

    小区物业管理系统数据库SQL语句

    小区物业管理系统配套数据库SQL语句,在Oracle中导入该SQL语句,数据库建立成功

    完整的员工数据库SQL文件

    完整的员工数据库SQL文件,方便学习、练习MySQL。欢迎大家下载交流

    SAP-HANA数据库SQL中文完全参考手册

    SAP-HANA数据库SQL中文完全参考手册,本文档完全翻译为中文,适合看不懂原版官方英文文档的人,内容做了精美的排版,里面包括各种函数,语法,限制信息等。内容非常完备,掌握这本手册,相信一定能掌握HANA

    全国的大学名录数据库sql

    全国的大学名录数据库sql文件,有近20万条数据,非常适合做需要填写会员学校名称的网站

    图书管理系统以 JAVA+数据库SQL Server 2000

    附加数据库SQL Server 2000 (1)将TM\05\Database文件夹中的扩展名为db_library_Data.MDF和db_library_Log.LDF的两个文件拷贝到SQL Server安装路径下的Data文件夹中。 (2)打开SQL Server 2000中的“企业管理器”...

    全国各地省市区分布数据库SQL文件数据库

    标题"全国各地省市区分布数据库SQL文件数据库"指的是一个包含了中国所有省份、城市及区县地理信息的数据集,它以SQL文件的形式存在。这类数据通常用于地理信息系统(GIS)、数据分析、网站开发等领域,以便在这些...

    nacos2.0.2版本依赖数据库sql脚本

    nacos2.0.2版本依赖数据库sql脚本

    可动态更新的全球IP数据库SQL

    可动态更新的全球IP数据库SQL可动态更新的全球IP数据库SQL可动态更新的全球IP数据库SQL可动态更新的全球IP数据库SQL可动态更新的全球IP数据库SQL可动态更新的全球IP数据库SQL可动态更新的全球IP数据库SQL可动态更新...

    oracle数据库sql练习题(含答案)

    oracle数据库sql练习题(含答案)用于练习增删改查操作。

    枣糕商城的数据库SQL文件

    枣糕商城的数据库SQL文件

    《5种数据库sql语句大全》

    5种数据库sql语句大全,轻松写sql跨库

    5种数据库sql语句大全

    5种数据库sql语句大全,绝对值得收藏。本人多年积累,拿出来与大家分享。

    网络数据库SQL Server2000

    网络数据库SQL Server2000

    ERP项目数据库SQL文件

    ERP项目数据库SQL文件,ERP项目数据库SQL文件,ERP项目数据库SQL文件ERP项目数据库SQL文件ERP项目数据库SQL文件ERP项目数据库SQL文件,ERP项目数据库SQL文件,ERP项目数据库SQL文件ERP项目数据库SQL文件

    英文单词数据库sql文件14.7万.zip

    14.7万英文单词SQL数据库,含近义词,反义词,简短英文解释,派生词等指针表. 解压缩后运行restore-mysql.bat自动导入到数据库.如果报错,请手动按顺序导入5个sql文件: schema.sql data.sql unconstrain.sql ...

    基于C#学生选课信息管理系统使用VS2010+SQL2008编写VS2017正常使用源码+数据库sql.zip

    基于C#学生选课信息管理系统使用VS2010+SQL2008编写VS2017正常使用源码+数据库sql.zip基于C#学生选课信息管理系统使用VS2010+SQL2008编写VS2017正常使用源码+数据库sql.zip基于C#学生选课信息管理系统使用VS2010+SQL...

Global site tag (gtag.js) - Google Analytics