Database代写:NCC32DatabaseDesignandDevelopment


根据题目场景,完成数据库设计,包括E-R图,数据库 DDL ,以及SQL查询语句。
![Database](https://upload.wikimedia.org/wikipedia/commons/thumb/f/f2/DVD_Rental_Query.png/220px-
DVD_Rental_Query.png)

Introduction

This assignment requires you to demonstrate knowledge and skills you have
acquired throughout this module by producing a database and producing a report
that addresses the tasks given below. In order to complete the assignment, you
will need to choose an appropriate organisation to research and base your
database project on.

Choosing an appropriate organisation

Step 1: select an industry in which you are interested. For example,
commercial airlines, commercial banking, retail, beauty, IT, publishing,
hospitality, leisure and tourism, automotive, or construction etc.
Step 2: select one or more organisations in that industry to research. You
should investigate the sort of data they hold and the types of transactions
they carry out. For example the education college shown in the appendix holds
data about the courses, staff, modules, laboratories and equipment. Their
transactions might include allocating modules to courses, staff to modules,
and assigning laboratories to courses and allocating equipment to
laboratories.
Do not chose a college for your organisation as this has been used as an
example.
You should reference the businesses or organisations that you investigate.
Step 3. Gathering materials
For your chosen type of organisation gather materials relating to their data
and transactions. You could do this by looking on-line, through personal
contacts with someone who works in the industry or simply by using a
particular business’s services.
Materials that you might gather include:

  • Invoices
  • Receipts
  • Order Forms
  • Customer Records
  • Delivery Notes
  • Complaints Forms
  • Booking Forms
  • Descriptions of daily tasks
  • Interviews with staff members
  • Company reports

Task 1 - Description of Business

Give a written overview of your organisation.
You should include an outline of the context in which they operate. For
example a college would operate in an environment in competition with other
colleges.
You should outline their day-to-day operations, their data requirements and
the types of transactions they carry out. You might include scans and/or
diagrams of any documents you have gathered, which should be suitably
anonymised so as not to show any personal data that might be included. You
should explain the purpose of these documents in the context of the
organisation.
You should outline the scope of the database project that you will undertake.
This should be suitably ambitious enough to include at least ten entities in
the data model. You should make clear what will be included within the scope
of the project and what will not be included. This discussion of scope should
include both data and operational issues.

Task 2 - ER and Data Dictionary

Produce an entity relationship diagram for your organisation and an
accompanying data dictionary. Your entity relationship diagram should include
at least TEN (10) entities. You should use the UML format.
Briefly explain the purpose of normalisation, and clearly justify why each of
your entities is in 3NF.
Using an example from your design, explain how normalisation solves the
problem of update anomalies
Define the Integrity constraints on the tables in your design. You should
clearly include Domain constraints, Business rules (Table constraints) and
Propagation constraints in your data dictionary.

Task 3 - Scripts to create table structures

Show the SQL scripts that you have used to create your database in a suitable
database environment. You should include scripts for tables, columns, primary
and foreign keys and any other database objects that you use. You should
include screen shots of the scripts running within the database environment.
For high marks, your scripts should implement integrity constraints you
identified in Task 2.
You should provide an explanation of how you developed the scripts, order of
running, and any issues encountered with them.

Task 4 - Data population

You should create suitable data for your database. Show the SQL insert scripts
that you have used to populate your database with this data. You should
include screen shots of the scripts running within the database environment.
You should provide an explanation of how you developed the scripts, order of
data population, and any issues encountered with them.

Task 5 - Enhancements

Discuss how you would design and implement THREE (3) enhancements to your
current systems. These could include changes in scope, data requirements,
functionality, or user interface design.
You should provide a rationale for each of your enhancements that demonstrates
how they would be useful for your system and chosen organisation.

Task 6 - SQL reports

Use SQL to produce FIVE (5) queries that will be useful for your organisation.
For each query you should supply the following:

  • Rationale for query - what is its purpose and what is it trying to retrieve
  • SQL script running in database environment
  • Result set of query shown in database environment
    Query scripts and results should be shown as running in the database
    environment. They should be well-formatted and easy to read.
    To achieve higher marks queries should be of a reasonable level of complexity.
    This will involve the joining of two or more tables, the use of renaming
    columns to ensure a user friendly result set, and the inclusion of descriptive
    columns from the tables.

Task 7 - Distributed Database Option

In the future your organisation could expand by merging with other similar
companies.
Describe the factors that might make them consider implementing a distributed
database.

Task 8 - Reflective Practice

Using the Rolfe, G., Freshwater, D. and Jasper, M. (2001) model, critically
review the learning that you have undertaken in order to complete this
assignment.
Based upon your learning, your reflection should include a description; an
analysis and; an action plan in order to bring about improvements in the
future.

Appendix - Worked example

DO NOT use this example or a variation of it for your own assignment.
The example below shows a written scenario that could be derived from
investigating an organisation. There are also some documents of the sort that
it would be possible to collect examples of from your own organisation. From
this material it is possible to identify the entities that would constitute
the database. Note that there is some overlap of entities between the
documents.
Attributes names would be those shown in the documents. It is acceptable to
introduce new attributes to take account of the data needs of the
organisation. It is also worth noting that data in its current form is not
necessarily normalised and it would be up to the developer to ensure that it
is when the new database model is being developed.

Scenario

Lowry College is Further Education College based in Kent, United Kingdom. They
specialise in science education.
They want a database to help them manage teaching and resources for their
various courses. This database will not be concerned, at least initially, with
the allocation of students to courses.
Each course will have a number of staff allocated to it. A member of staff
might be allocated to more than one course. Staff are defined by type:
Teacher, Technical Support, Administration, Other.
Courses are made up of modules. A module might be part of more than one
course. Modules are defined by type ‘Core’, ‘Elective’ or ‘Optional’. Some
modules such as ‘Biochemistry of Life’ are taught on different courses.
A course might have one or more laboratories associated with it. A laboratory
is administered by one particular course.
A module will be taught in a particular laboratory. A laboratory might host
many different modules. Laboratories have equipment in them. A piece of
equipment might be allocated to more than one laboratory. Equipment is defined
by type.

Submission requirements

  • Your submission should be in the form of a single word-processed document that includes any necessary diagrams.
  • The word count for the document is 2000 words (excluding text in any diagrams). You should explain any assumptions you have made.
  • A digital version must be submitted on a CD, USB flash drive or other similarly acceptable medium, along with a copy of the developed database.

文章作者: SafePoker
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 SafePoker !
  目录