Reference no: EM132659860
This assignment requires you to demonstrate the knowledge and skills you have acquired throughout this unit by designing and creating a functional database application that addresses the tasks given below. In order to complete this assignment, you should choose an appropriate organisation on which to conduct research and base your database application.
Select an industry in which you are interested. For example, commercial airlines, commercial banking, retail, beauty, healthcare, fitness, publishing, hospitality, leisure and tourism, automotive, construction etc.
From the industry selected, you should investigate the sort of data they hold and the types of transactions they carry out. For example, an educational establishment will hold data about students, staff, rooms, units, assessments and results. Their transactions might include adding students to units, allocating staff to teach units, allocating rooms to units, allocating different assessments to units and recording the results for each unit assessment the student undertakes.
Task 1 Analysis and Design
Produce the following documents:
a) Identify and discuss your chosen organisation, with a minimum of SIX (6) entities, and outline the likely data and information that the company would require. List and explain the functions and transactions that a database application might perform that would support some of the day to day functions of the organisation. Summarise the advantages of a DBMS to the organisation.
b) Draw an ERD (Entity Relationship Diagram) that shows data entities that have been identified in part a) and how they relate to one another. The ERD should support the transactions that have been identified in part a) and should be fully normalised to 3rd normal form (3NF).
c) Create a data dictionary for the entity relationship diagram using the entities identified in part b). The data dictionary should identify the tables, attributes, primary and foreign keys, data types and any constraints/ business rules from your chosen organisation.
Task 2
a) Create all of the normalised tables using SQL. Show your SQL scripts and the finished tables.
b) Enter sample data in all tables (minimum THREE (3) rows per table).
c) Write at least FIVE (5) queries that show your understanding of SQL. SQL statements that should be used should include: Select, Update, Delete, From, Where, And, Count, Ascending, Order By. TWO (2) of the queries should join TWO
(2) or more tables together.
Task 3 Assessment and evaluation
Provide a written assessment and evaluation of the work you have undertaken. Your discussion should include:
• Any assumptions that you have made and how they impact the design.
• How well your database meets the data and information requirements of your chosen organisation.
• How the database supports the transactions that you identified in task.
• Any problems you had with your ER model and how you overcame them.