.

DAT536: Database Development and Design

New Zealand Diploma in Information Technology Technical Support Level 5
DAT536: Database Development and Design Version 1.2, Level 5
theoretical and practical aspects of database design
Ⓒaspire2 international 

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.

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 i nformation. 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 meet s the organizational data storage and retrieval requirements for one of the organi s ation s below:

NoOrganisationBrief Description
1Mt Albert Football Club

The football club has players and employees. The description is as follows:

Players are identified by Player ID. Their names, addresses, phone numbers must be recorded.

Physios are identified by Physio ID. Their Names, Specialization (Orthopedic, Chiropractic, and Acupuncture ) and Office locations and phone numbers are recorded.

The club has various employees identified by an employee ID, an employee type (Manager, Receptionist, Coach, IT Admin). Their names, email addresses, phone nu mber and office locations are stored.

A Player is assessed by many Physios appointed to them by their coach. Employees can be assigned to one physio.

2Queens Street Video Game Rental Shop

The company structure - 1 manager , 2 shop attendants and one back office IT person who is also the DBA. They are identified by an employee ID. The game shop has an inventory to organise its games by genre. The games are identified by a product ID, and have a price, the video games are shipped directly from US and Japan.

The members buy games from employees. They have membership plans like monthly rental, weekly rental and daily rental . The system also stores member ID, member name, member Phone number and address.

The project document should have the following structure–

Part-A

  1. Business overview
  2. Business requirements
  3. Expected Project Outcomes
  4. Technical and administrative details

Part–B

  1. ER Diagram
  2. Relational schemas

Part-C

  1. Database Implementation
  2. Database Testing

The details of the above mentioned tasks are given below.

Part A–Project Planning

In this part, you are required to formulate the plan for the chosen scenario of your project:

  1. Business Overview– explain the background of the chosen business scenario
  2. Business requirements– list the client’s requirements
  3. Expected Project Outcomes– list your project deliverables
  4. Technical and administrative details- the description should cover the following- DBMS software used, storage, backup and recovery plans

Part B– Database Design

  1. ER Diagram- in the diagram identify primary key, foreign key (if any), cardinalities, constraints (if any) and relationships
  2. Relational schemas–state the relational schemas and specify the normal forms the schema satisfies

Part C–Database Implementation and Testing

In this task you need to briefly explain and provide a reason for choosing your SQL commands.

  1. Database Implementation– Implement your design in Part B by creating the database, the tables and their relationships
  2. Database Testing– test your database by executing sql queries
    1. Populate your database with at least 20 test records
    2. Test insertion and deletion of records
    3. Test relationship between the tables
  3. Provide the export file for the database
  • ⯈ Pacific Institute of Hotel Management Studies
  • ⯈ University of Canterbury
  • ⯈ Victoria University of Wellington
  • ACG Education
  • Ara Institute of Canterbury (Christchurch)- CPIT
  • Aspire 2
  • Auckland College of Education
  • Auckland Grammar School
  • Auckland Institute of Studies
  • Auckland University of Technology (AUT)
  • Avondale College
  • Christchurch College of Education
  • Campbell Institute
  • Christchurch College of English Language (CCEL)
  • Cornell Institute of Business and Technology
  • Design and Arts College
  • Dunedin College of Education
  • Eastern Institute of Technology (EIT)
  • Hamilton Teachers' Training College
  • International Travel College
  • Languages International
  • Le Cordon Bleu New Zealand
  • Lincoln University
  • Manukau Institute of Technology (MIT)
  • Massey University
  • Nelson Marlborough Institute of Technology (NMIT)
  • New Zealand Institutes of Technology and Polytechnics
  • NorthTec, formerly Northland Polytechnic
  • Otago Polytechnic
  • Otago Polytechnic
  • Palmerston North College of Education
  • Southern Institute of Technology (SIT)
  • Tai Poutini Polytechnic
  • Taylors College
  • The Open Polytechnic of New Zealand
  • The University of Auckland
  • The University of Auckland English Language Academy (ELA)
  • Toi Ohomai Institute of Technology
  • UCOL Universal College of Learning
  • Unitec Institute of Technology
  • University of Canterbury International College
  • University of Otago
  • University of Otago Language Centre and Foundation Year
  • University of Waikato
  • Waikato Institute of Technology
  • Wānanga
  • Wellington College of Education
  • Wellington Institute of Technology
  • Western Institute of Technology at Taranaki
  • Whitireia Community Polytechnic
  • Whitireia, New Zealand
  • Yoobee School of Design
.