Instructions and Guidelines for Project Submission

Task completion

• Project will be judged on the basis of completeness, correctness and clarity.
• Complete ALL tasks.
• Warning: All media must be virus free! Media containing viruses or media that cannot be run directly will result in a fail grade.

Format

• The report must have a title page with your name, class and ID number clearly printed.
• All written work must be done in size 12 font with 1.5 line spacing.
• All printouts must include your student ID (or group IDs) in the footer (printed).
• You may need to attach the task cover sheet found on Moodle to the front of the final submission of your task (please confirm with your tutor).

Submission

• The completed research must be handed to the tutor at the beginning of the class on the due date.
• Submit a printed and bound copy of your report along with the soft copy. Tutor will inform you on how to submit the soft copy.

Plagiarism

• By submitting your assignment you agree to Ntec’s policy on ‘Academic Dishonesty and Plagiarism’. Assignments completed using unfair means or plagiarised material will receive a FAIL grade.
• All tasks must be done in your own words and referenced.

Feedback

• You may request feedback from your lecturer to verify the accuracy of your marks.

Learning Outcomes covered:

1.     Apply a broad operational knowledge of database administration to meet typical organisational data storage and retrieval requirements. 2.     Apply the fundamentals of information systems concepts and practice to support and enhance organisational processes and systems.

Specific Instructions for This Project

1. This is an individual project.
2. At the submission of the project, the student will be interviewed by the lecturer. If the student fails to provide a satisfactory evidence that the work was done by themselves, the student will not be given any marks.
3. The sql queries should be able to run at the time the lecturer checks it else you will get a zero for Task 2 of this assessment.

 

Student Undertaking:

 

I have read and understood the instructions for exam given above. I understand that I will be given  zero marks and will be reported to the disciplinary committee if I am found cheating or engaging in any unfair practice.

Student Name:____________________________________________________________________

Student Signature: _________________________________________________________________

Date:_____________________________________________________________________________

 

Introduction

The purpose of this assessment is to expose you to theoretical and practical aspects of database design. You must choose a real life system that requires a database. Then, you must design a relational database schema that adequately represents the system information. In the process, you need to evaluate the system requirements and must use data modelling tools and normalisation techniques.

 

In this project, you are given a role as a database designer. You will need to design, implement and test a database that meets the organisational data storage and retrieval requirements for one of the organisations below:

 

No	Organisation	Brief Description
1	Mt Albert Football Club	The football club has 1 manager; an assistant manager; 3 physios; 1 coach; 17 players in all; one receptionist; one IT person who is also the DBA and one HR person. They buy sports equipment from 2 manufacturers and also have a contract with a local hospital.
2	Queens Street Video Game Rental Shop	The company structure- 1 manager, 2 shop attendants and one back office IT person who is also the DBA. The game shop has an inventory organised by genre, the video games ship directly from US and Japan, They have membership plans like monthly rental, weekly rental and daily rental
3	Glenn Innes Non-profit Organisation	The company structure- 1 Manager who also acts as a receptionist and HR person, 5 on field staff, 1 IT person who is also the DBA. They are working on the north island of homeless people and neglected children. They are in contract with red cross, and Auckland Hospital for supplies

 

Task 1 – Design Component (Total 25 Marks)

In this part, you are required to produce a proposal of the database design. The proposal should include:

1. An introduction that includes the following –
   1. Taking help from the project description and also adding suitable information formulate business rules for the database to be implemented. (5 marks)
   2. Write down a plan for the types of queries/commands you think should be implemented for creating, accessing, deleting and maintaining such a database. For e.g. “Will create a database called ‘Football Team’”, then describe the tables you would create and so on. (5 marks)
2. Provide the Entity-Relationship diagram of the database. (5 Marks)
3. State the relational schemas. (5 Marks)
4. For each relational schemas, specify the associated functional dependencies and the normal forms the schema satisfies. (5 Marks)

Task 2 – Implementation and Testing Component (Total 60 Marks)

In this assessment, you are going to use MySQL to implement the database. You are expected to do the following:

• Create the databse and tables and their relationships obtained in Task #1 and insert 10 to 15 records into each table.
• Test the database by writing and executing the planned SQL queries in Task #1.
• Create two (2) MySQL script files. The first script file contains all SQL statements including creating database and tables and inserting data. The second script file includes the formulated SQL queries.
• Test the script files by dropping (deleting) the database and executing the script files. Make sure there are no errors. You should obtain the developed database (with the data) and the output of the queries correctly.

Your project will be assessed based on the following criteria applicable to the above mentioned tasks:

1. < li>

Valid creation of database and tables. (20 Marks)

1. Implementation of the Relational Tables. (20 Marks)
2. Querying and processing of the database is properly executed (Select, Insert, Delete, Update and Drop). (20 Marks)

Task 3 – Presentation Component (Total 15 Marks)

The final presentation is to communicate the development of the database is driven application. You must prepare presentation slides for your presentation. The presentation will be 8 minutes. Followed by 2 minutes of question and answer. The following are guidelines and ideas for you to keep in mind while you prepare for your presentation.

• Explain the organisational data storage and retrieval requirements.
• Explain the developed database system.
• Explain the business rules.
• Show and explain the ER diagram.

 

 

Your presentation will be assess based on the following criteria:

1. Presentation skills (Eye contact, body language, poise). (5 Marks)
2. Presentation verbal skills (Enthusiasm, Elocation) (2.5 Marks)
3. Subject knowledge of the project. (2.5 Marks)
4. The visual presentation of the slide (Organisation, mechanic) (5 Marks)

 

Task 1 Component	5 Marks	4 Marks	3 Marks	2 Marks	1 Marks	0 Marks	Mark Allocated	Mark Obtained
1.              An introduction that includes the following- a.     Taking help from the project description and also adding suitable information formulate business rules for the database to be implemented	The business rules are logical, clear, concise and easy to understand. And the student has also used rules apart from those mentioned in the description	The business rules are clear, concise and easy to understand with few exceptions (e.g. business rules are not evaluated properly).	The business rules are partially adequate and need more clarity. The business rules are not stated concisely.	The business rules are not adequate and need more clarity. The business rules are not stated concisely.	Unclear and minimal business rules are stated or not stated at all.	No business rule has been defined.	5
1.              An introduction that includes the following- b.     Write down a plan for the types of queries/commands you think should be implemented for creating, accessing, deleting and maintaining such a database. ….	The queries are realistic. The queries reflect the requirements of the proposed system.	The queries are realistic. The queries mostly reflect the requirements of the proposed system.	The queries are partially realistic. The queries partially reflect the requirements of the proposed system.	The queries are partially realistic. The queries partially reflect the requirements of the proposed system. Many error	The queries are not realistic. The queries do not reflect the requirements of the proposed system.	The queries are not formulated.	5
2. Provide the Entity-Relationship diagram of the database.	The ER diagram is developed completely. The relevant entities are created. The attributes are logically created. The relationships between entities are correctly defined. The cardinalities are correctly specified.	The ER diagram is developed completely. The relevant entities are created. Most attributes are logically created. Most relationships between entities are correctly defined. The cardinalities are correctly specified.	The ER diagram is developed completely. Most entities are created. Most attributes are logically created. Some relationships between entities are correctly defined. The cardinalities are correctly specified.	The ER diagram is partially developed. Some entities are created. Some attributes are logically created. Minimal relationships between entities are correctly defined. The cardinalities are partially correctly.	The ER diagram is partially developed. Some entities are created. Some attributes are logically created. Little relationships between entities are wrongly defined. The cardinalities are wrong.	The ER diagram is not developed.	5
3. State the relational schemas.	The notation is used correctly for all elements of the diagram. Relation schemas capture all entity sets and relationships from the team’s E-R diagram and account for suggested revision to that diagram. A convincing explanation is included for any suggested revisions not accepted or for deviations from the original design	The notation is used correctly for elements of the diagram. Relation schemas capture most entity sets and relationships from the team’s E-R diagram and account for most suggested revisions to that diagram.	The notation is used correctly for most elements of the diagram. Relation schemas capture most entity sets and relationships from the team’s E-R diagram and account for most suggested revisions to that diagram. Some correction need to clarify the relational schemas.	The notation is used correctly for most elements of the diagram. Relation schemas capture few, if any, entity sets and relationships from the team’s E-R diagram. Document does not account for suggested revisions to that diagram.	Document does not use an appropriate notation or the notation is used incorrectly for most elements of the diagram. Relation schemas capture few, if any, entity sets and relationships from the team’s E-R diagram. Document does not account for suggested revisions to that diagram.	The relational schemas diagram not developed.	5
4. For each relational schemas, specify the associated functional dependencies and the normal forms the schema satisfies.	Relation schemas specify all functional dependencies and the normal forms correctly.	Relation schemas specify all functional dependencies and the normal forms with little error.	Relation schemas specify all functional dependencies and the normal forms with some error.	Relation schemas specify few functional dependencies and the normal forms with some error.	Relation schemas specify few, if any, functional dependencies and the normal forms.	No functional dependencies and the normal forms	5
Task 1 Sub Total							25

Task 2 Component	20 Marks	18 Marks	15 Marks	10 Marks	5 Marks	0 Marks	Mark Allocated	Mark Obtained
1. Valid creation of database and tables.	The required tables are created. The fields are logically created. The data types are correctly specified.	The required tables are created. Most fields are logically created. The data types are correctly specified	The required tables are created. Some fields are logically created. Some data types correctly specified.	Some of the tables are created. Some fields are logically created. Some data types are correctly specified.	Some of the tables are created. Some fields are not logically created. Some data types are not correctly specified.	There are no tables created.	20
2. Implementation of the Relational Tables.	The relationships between tables are defined. Cardinality and modality are correctly specified.	Most relationships between tables are correctly defined. Cardinality and modality are correctly specified.	Some relationships between tables are correctly defined. Cardinality and modality are partially correct.	Minimal relationships between tables defined. Cardinality and modality are correctly specified.	Minimal relationships between tables defined. Cardinality and modality are not correctly specified.	There are no relationships defined.	20
3. Querying and processing of the database is properly executed (Select, Insert, Delete , Update and Drop).	The queries are correctly defined and perfectly executed. Students have used all basic queries like Select, Insert, Delete, Update, Drop	Most queries are correctly defined and executed.	Some queries are correctly defined and executed	Minimal queries are correctly defined and executed.	There are no queries defined.		20
Task2 Sub Total							60

Task 3 Component	5 Marks	4 Marks	3 Marks	2 Marks	1 Marks	0 Marks	Mark Allocated	Mark Obtained
1. Presentation skills (Eye contact, body language, poise).	Holds attention of entire audience with the use of direct eye contact, without looking at notes. Movements seem fluid and help the audience visualize. Student displays relaxed, self-confident nature about self, with no mistakes.	Holds attention of entire audience with the use of direct eye contact, seldom looking at notes.  Movements seem fluid and help the audience visualize. Student displays relaxed, self-confident nature about self, with no mistakes.	Consistent use of direct eye contact with audience, but still returns to notes. Made movements or gestures that enhances articulation. Makes minor mistakes, but quickly recovers from them; displays little or no tension.	Consistent use of direct eye contact with audience, but still returns to notes. Very little movement or descriptive gestures. Displays mild tension; has trouble recovering from mistakes.	Displayed minimal eye contact with audience, while reading mostly from the notes. Very little movement or descriptive gestures. Displays mild tension; has trouble recovering from mistakes.	No eye contact with audience, as entire report is read from notes.  No movement or descriptive gestures. Tension and nervousness is obvious; has trouble recovering from mistakes.	5
Task 3 Component	2.5 Marks	2 Marks	1.5 Marks	1 Marks	0.5 Marks	0 Marks	Mark Allocated	Mark Obtained
2. Presentation verbal skills (Enthusiasm, Elocation)	Demonstrates a strong, positive feeling about topic during entire presentation. Student uses a clear voice and correct, precise pronunciation of terms so that all audience members can hear presentation.	Occasionally shows positive feelings about topic. Student’s voice is clear. Student uses a clear voice and correct, precise pronunciation of terms so that all audience members can hear presentation.	Occasionally shows positive feelings about topic. Student’s voice is clear. Student pronounces most words correctly. Most audience members can hear presentation.	Shows some negativity toward topic presented. Student’s voice is low. Student incorrectly pronounces terms. Audience members have difficulty hearing presentation.	Shows some negativity toward topic presented. Student’s voice is low. Student mumbles, incorrectly pronounces terms, and speaks too quietly for a majority of students to hear.	Shows absolutely no interest in topic presented. Student mumbles, incorrectly pronounces terms, and speaks too quietly for a majority of students to hear.	2.5
3. Subject knowledge of the project.	Subject knowledge is evident throughout the project. All information is clear, appropriate, and correct.	Subject knowledge is evident in much of the project. Most information is clear, appropriate, and correct.	Some subject knowledge is evident. Some information is confusing, incorrect, or fl awed.	Some subject knowledge is evident. Most information is confusing, incorrect, or flawed.	Subject knowledge is not evident. Information is confusing, incorrect, or flawed.	Student unable to answer any question.	2.5
Task 3 Component	5 Marks	4 Marks	3 Marks	2 Marks	1 Marks	0 Marks	Mark Allocated	Mark Obtained
4. The visual presentation of the slide (Organisation, mechanic)	Student presents information in logical, interesting sequence which audience can follow. Presentation has no misspellings or grammatical errors.	Student presents information in logical sequence which audience can follow. Presentation has no more than one misspellings and/or grammatical errors.	Student presents information in logical sequence which audience can follow. Presentation has no more than two misspellings and/or grammatical errors.	Audience has difficulty following presentation because student jumps around. Presentation has three misspellings and/or grammatical errors.	Audience cannot understand presentation because there is no sequence of information. Student’s presentation has four or more spelling and/or grammatical errors.	No visual presentation.	5
Task 3 Sub Total							15

Total	100
Final Weighting (%)	100 x 0.7

 

General Comments：
Student Name	Student ID	Date
Assessor Name	Assessor Signature	Date