根据题目场景，完成数据库设计，包括E-R图，数据库DDL，以及SQL查询语句。
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.